正文
Mybatis 使用注解和Provider类实现动态条件查询
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
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);
}
@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);
}
electProvider 类实现
此 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();
}
}
