需求/背景
用户表对应的用户实体:
public class User {
@Id
Long id;
//姓名
String name;
//性别,男0女1
String sex;
//年龄
Integer age;
//部门, 用户-部门多对一关系
@ManyToOne
Department dept;
}
前端需要实现这样的查询:
其中部门支持多选;
实现
分析
jpa里的复杂查询一般使用@Query
完成, 但是@Query
并不支持动态过滤条件, 过滤条件在编译时就已经确定;
动态过滤可能的实现方式:
- criteria api
jpa里标准的实现方式, 但是我对sql比较熟, 对criteria api非常不熟, 感觉它是反人类的, 并不想学. - 拼接sql/jpql
最常见的实现方式, 根据查询条件拼接sql, 用entityManager.createQuery(sql)
查询; 简单好理解, 但是失去了jpa对jpql的类型检查, 可能编译不出错但是运行时出错; - 一种查询条件对应一个方法
简单粗暴, 3个条件至少要实现8个方法; - 使用sql编写技巧实现动态查询; 下面展示具体的实现方式;
使用sql编写技巧实现动态查询
@Repository
public interface UserRepository extends JpaRepository<User, Long> , JpaSpecificationExecutor<User>{
/**
* 根据多个过滤条件查询用户
* @param sex, 性别, 如果为null表示不限制性别, 查询所有性别;
* @param minAge, 年龄下限, 如果小于零表示不限制年龄
* @param maxAge, 年龄上限
* @param deptIds, 部门id的list, 一定不能为空, 包含-1L表示查询所有部门
* @return
*/
@Query("select user from User user where" +
"(:sex is null or user.sex = :sex) and" +
"(:minAge < 0 or (user.age>=:minAge and user.age<=:maxAge) ) and" +
"(-1L in :deptIds or user.dept.id in :deptIds)")
List<User> findUser(String sex,Integer minAge,Integer maxAge,List<Long> deptIds);
}
完整的jpql语句:
select user from User user where
(:sex is null or user.sex = :sex) and
(:minAge < 0 or (user.age>=:minAge and user.age<=:maxAge) ) and
(-1L in :deptIds or user.dept.id in :deptIds)
实现的效果:
如果sex参数为null, 就不对sex字段进行过滤, 相当于没有这个过滤条件, 如果sex不是null就对sex字段过滤;
如果minAge参数为null, 就不对age字段过滤, 否则按照minAge和maxAge对年龄进行过滤;
如果deptIds(部门id列表)包含-1, 就不过滤, 否则过滤;
sex、age、dept字段可以任意组合, 实现动态过滤的效果;
这种方式相对来说实现简单好理解, 但是可能会影响一些查询速度, 需要service层配合处理参数;