Mybatis 使用注解和Provider类实现动态条件查询

Mybatis 提供了基于Xml和注解的自定义SQL查询，相比Xml的方式，注解更加便捷、优雅。为了防止遗忘具体的细节编写，摘录一段曾经写过的代码。
此处，一大片注解内容十分不优雅，更好的办法是通过调用Provider类的指定方法生成SQL。

1.注解内拼写 Mybatis SQL 脚本

@Repository
public interface CustomerFeedMapper extends BaseCrudMapper<CustomerFeed> {

    @Select("<script>"
            + "SELECT customer_id,COUNT(customer_id) total "
            + "FROM t_customer_feed "
            + "<where> "
            + "<if test='feedQO.feedTypes != null and feedQO.feedTypes.size()>0'> "
            + "type IN "
            + "<foreach item='type' collection='feedQO.feedTypes' open='(' close=')' separator=','> "
            + "#{type}"
            + "</foreach> AND "
            + "</if>"
            + "<if test='feedQO.customerIds != null and feedQO.customerIds.size()>0'> "
            + "customer_id IN "
            + "<foreach item='customerId' collection='feedQO.customerIds' open='(' close=')' separator=','> "
            + "#{customerId}"
            + "</foreach> AND "
            + "</if>"
            + "<if test='feedQO.timePoint != null'>"
            + "create_time > #{feedQO.timePoint} "
            + "</if>"
            + "</where>"
            + "GROUP BY customer_id "
            + "ORDER BY create_time DESC "
            + "</script>")
    @Results({
            @Result(property = "customerId", column = "customer_id"),
            @Result(property = "total", column = "total")
    })
    List<CustomerFeedSummaryVO> summary(@Param("feedQO") CustomerFeedQO feedQO);
}

2.基于org.apache.ibatis.jdbc.SQL对象构建SQL

Mapper 接口

在 mapper 接口的方法上添加注解 @SelectProvider 配置其两个属性 type （构建SQL的类）和 method （构建 SQL 的类中的方法）

@Repository
public interface UserCustomerRelationMapper extends BaseCrudMapper<UserCustomerRelation> {
    /**
     * Page by customer attrs list.
     * @param userCustomerRelationQO the user customer relation qo
     * @return the list
     */
    @SelectProvider(type = UserCustomerRelationProvider.class, method = "listByCustomerAttr")
    List<UserCustomerRelation> pageByCustomerAttrs(@Param("condition") UserCustomerRelationCondition userCustomerRelationQO);
}

SelectProvider 类实现

此 Provider 类无需继承实现其他类，只要实现接口方法中注解 @SelectProvider 的 method 属性指定的方法 listByCustomerAttr ，Mapper 接口中的参数，将以 Map<String,Object> 的形式传入我们实现的指定方法。

public class UserCustomerRelationProvider {

    /**
     * List by customer attr string.
     * @param params the params
     * @return the string
     */
    public String listByCustomerAttr(Map<String, Object> params) {

        UserCustomerRelationCondition qo = (UserCustomerRelationCondition) params.get("condition");

        SQL querySql = new SQL();
        querySql.SELECT("ucr.user_id as userId,ucr.customer_id as customerId,ucr.create_time as createTime,ucr.update_time as updateTime")
                .FROM("t_user_customer_relation ucr", "t_customer_attr ca")
                .WHERE("ucr.customer_id=ca.objectId");
        String userId = qo.getUserId();
        if (StringUtils.isNotBlank(userId)) {
            querySql.WHERE("ucr.user_id=#{condition.userId}");
        }
        Long customerId = qo.getCustomerId();
        if (customerId != null) {
            querySql.WHERE("ucr.customer_id=#{condition.customerId}");
        }
        List<CustomerAttr> customerAttrs = qo.getCustomerAttrs();
        if (!CollectionUtils.isEmpty(customerAttrs)) {
            for (CustomerAttr customerAttr : customerAttrs) {
                String key = customerAttr.getKey();
                if (StringUtils.isNotBlank(key)) {
                    querySql.WHERE(String.format("ca.`key`='%s'", key));
                }
                String value = customerAttr.getValue();
                if (StringUtils.isNotBlank(value)) {
                    querySql.WHERE(String.format("ca.`value`='%s'", value));
                }
            }
        }
        return querySql.toString();
    }

}

参考链接

MyBatis注解Annotation介绍及Demo