【精品】MyBatis 动态条件查询 之 注解实现

原理

MyBatis提供了以下几个 SQL 注解，通过这些注解可以在运行的过程中，指定类名和方法名返回 SQL 来执行。针对于执行映射语句，MyBatis 实例化类，执行方法。

• @InsertProvider
• @UpdateProvider
• @DeleteProvider
• @SelectProvider

示例 实体类

@Data 
@Builder
public class Province {
    private Long id;
    private String name;
    private String area;
    private Integer priority;
    private Integer state;
}

查询类

@Data 
@Builder
public class ProvinceQuery {
    private String name;
    private String area;
    private Integer state;
}

Mapper接口

@Mapper 
public interface ProvinceMapper {
    @Select("""
       
            select * from tb_province
            
                
                    and `name` like CONCAT(CONCAT('%', #{name,jdbcType=VARCHAR}), '%')
                
                
                    and `area` like CONCAT(CONCAT('%', #{area,jdbcType=VARCHAR}), '%')
                
                
                    and `state` = #{state,jdbcType=INTEGER}
                
            
        
    """)
    List selectByCondition1(ProvinceQuery provinceQuery);

    @SelectProvider(type = ProvinceProvider.class,method = "selectByCondition2")
    List selectByCondition2(ProvinceQuery provinceQuery);
}

Provider实现类

public class ProvinceProvider {
    public String selectByCondition2(ProvinceQuery provinceQuery) {
        String sql = """
                
                    select * from tb_country
                        
                            
                                and `area` like CONCAT(CONCAT('%', #{area,jdbcType=VARCHAR}), '%')
                               
                            
                                and `name` like CONCAT(CONCAT('%', #{name,jdbcType=VARCHAR}), '%')
                            
                            
                                and `state` = #{state,jdbcType=INTEGER}
                            
                        
                    
                """;
        return sql;
    }
}

测试代码

@SpringBootTest
public class ProvinceMapperTest {
    @Resource
    private ProvinceMapper2 provinceMapper2;
    @Test
    void selectByCondition1(){
        final ProvinceQuery provinceQuery = ProvinceQuery.builder()
                .area("华")
                .build();
        final List provinceList = provinceMapper2.selectByCondition1(provinceQuery);
        provinceList.forEach(System.out::println);
    }

    @Test
    void selectByCondition2(){
        final ProvinceQuery provinceQuery = ProvinceQuery.builder()
                .name("山")
                .build();
        final List provinceList = provinceMapper2.selectByCondition2(provinceQuery);
        provinceList.forEach(System.out::println);
    }
}