一、JpaSpecificationExecutor原生查询及其概述
1.1 概述
Spring Data JPA为我们提供了一系列的接口比如：Repository、CrudRepository、PagingAndSortingRepository、JpaRepository、JPASpecificationExecutor等。我们只需要继承这些接口就可以很方便的对数据库进行操作。但是当我们运用JPASpecificationExecutor接口进行一些比较复杂的条件查询的时候，就会显得有点麻烦，下面先来个查询的demo体验一下：
1.2 原生条件查询
我们假设usersRepositorySpecification已经继承了相关接口，以name和age为条件进行查询：
/**
* JpaSpecificationExecutor 多条件测试
*/
@Test
public void testJpaSpecificationExecutor2() {
/**
* Specification<Users>:用于封装查询条件
*/
Specification<Users> spec = new Specification<Users>() {
//Predicate:封装了 单个的查询条件
/**
* Root<Users> root:查询对象的属性的封装。
* CriteriaQuery<?> query：封装了我们要执行的查询中的各个部分的信息，select from order by
* CriteriaBuilder cb:查询条件的构造器。定义不同的查询条件
*/
@Override
public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> list = new ArrayList<>();
list.add(cb.equal(root.get("name"),"张三三"));
list.add(cb.equal(root.get("age"),20));
Predicate[] arr = new Predicate[list.size()];
return cb.and(list.toArray(arr));
}
};
List<Users> list = this.usersRepositorySpecification.findAll(spec);
for (Users users : list) {
System.out.println(users);
}
}
二、对上述查询进行封装简化
-
QueryWraper类，包装用于构建JPA动态查询时所需的对象
public class QueryWraper<T> { public QueryWraper(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb, List<Predicate> predicates) { this.root = root; this.query = query; this.cb = cb; this.predicates = predicates; } /** * JPA Root */ private Root<T> root; /** * JPA CriteriaBuilder */ private CriteriaBuilder cb; /** * JPA Predicate 集合 */ private List<Predicate> predicates; /** * JPA 查询对象 */ private CriteriaQuery<?> query; /** * <pre> * 增加查询条件 * <pre> * @param predicate * @author jojo 2014-8-12 下午3:12:36 */ public void addPredicate(Predicate predicate) { this.predicates.add(predicate); } public Root<T> getRoot() { return root; } public void setRoot(Root<T> root) { this.root = root; } public CriteriaBuilder getCb() { return cb; } public void setCb(CriteriaBuilder cb) { this.cb = cb; } public List<Predicate> getPredicates() { return predicates; } public void setPredicates(List<Predicate> predicates) { this.predicates = predicates; } public CriteriaQuery<?> getQuery() { return query; } public void setQuery(CriteriaQuery<?> query) { this.query = query; } } -
AbstractConditionBuilder抽象类，提供比如:in条件、between条件、like条件等一系列复杂查询条件
public abstract class AbstractConditionBuilder<T> { /** * 添加in条件 * @param queryWraper * @param column 哪个属性 * @param values 在(in)哪些值里面 */ protected void addInConditionToColumn(QueryWraper<T> queryWraper, String column, Object values) { if (needAddCondition(values)) { Path<?> fieldPath = getPath(queryWraper.getRoot(), column); //得到最终的路径 if(values.getClass().isArray()) { queryWraper.addPredicate(fieldPath.in((Object[])values)); //in :创建Predicate以测试表达式是否为参数列表的成员,返回Predicate }else if(values instanceof Collection) { queryWraper.addPredicate(fieldPath.in((Collection<?>)values)); } } } /** * 添加between条件查询 * @param queryWraper * @param experssion * @param minValue 范围下限 * @param maxValue 范围上限 */ @SuppressWarnings({ "rawtypes", "unchecked" }) protected void addBetweenConditionToColumn(QueryWraper<T> queryWraper, String column, Comparable minValue, Comparable maxValue) { if (minValue != null || maxValue != null) { Path<? extends Comparable> fieldPath = getPath(queryWraper.getRoot(), column); if(minValue != null && maxValue != null){ queryWraper.addPredicate(queryWraper.getCb().between(fieldPath, minValue, processMaxValueOnDate(maxValue))); }else if(minValue != null){ queryWraper.addPredicate(queryWraper.getCb().greaterThanOrEqualTo(fieldPath, minValue)); }else if(maxValue != null){ queryWraper.addPredicate(queryWraper.getCb().lessThanOrEqualTo(fieldPath, processMaxValueOnDate(maxValue))); } } } /** * 当范围查询的条件是小于，并且值的类型是Date时，将传入的Date值变为当天的夜里12点的值。 * @param maxValue * @return * @author zhailiang * @since 2016年12月14日 */ @SuppressWarnings("rawtypes") private Comparable processMaxValueOnDate(Comparable maxValue) { if(maxValue instanceof Date) { maxValue = new DateTime(maxValue).withTimeAtStartOfDay().plusDays(1).plusSeconds(-1).toDate(); } return maxValue; } /** * 添加大于条件查询 * @param queryWraper * @param experssion * @param minValue */ @SuppressWarnings({ "rawtypes", "unchecked" }) protected void addGreaterThanConditionToColumn(QueryWraper<T> queryWraper, String column, Comparable minValue) { if (minValue != null) { Path<? extends Comparable> fieldPath = getPath(queryWraper.getRoot(), column); queryWraper.addPredicate(queryWraper.getCb().greaterThan(fieldPath, minValue)); } } /** * 添加大于等于条件查询 * @param queryWraper * @param experssion * @param minValue */ @SuppressWarnings({ "rawtypes", "unchecked" }) protected void addGreaterThanOrEqualConditionToColumn(QueryWraper<T> queryWraper, String column, Comparable minValue) { if (minValue != null) { Path<? extends Comparable> fieldPath = getPath(queryWraper.getRoot(), column); queryWraper.addPredicate(queryWraper.getCb().greaterThanOrEqualTo(fieldPath, minValue)); } } /** * 添加小于条件查询 * @param queryWraper * @param experssion * @param maxValue */ @SuppressWarnings({ "unchecked", "rawtypes" }) protected void addLessThanConditionToColumn(QueryWraper<T> queryWraper, String column, Comparable maxValue) { if (maxValue != null) { Path<? extends Comparable> fieldPath = getPath(queryWraper.getRoot(), column); queryWraper.addPredicate(queryWraper.getCb().lessThan(fieldPath, processMaxValueOnDate(maxValue))); } } /** * 添加小于等于条件查询 * @param queryWraper * @param experssion * @param maxValue */ @SuppressWarnings({ "unchecked", "rawtypes" }) protected void addLessThanOrEqualConditionToColumn(QueryWraper<T> queryWraper, String column, Comparable maxValue) { if (maxValue != null) { Path<? extends Comparable> fieldPath = getPath(queryWraper.getRoot(), column); queryWraper.addPredicate(queryWraper.getCb().lessThanOrEqualTo(fieldPath, processMaxValueOnDate(maxValue))); } } /** * <pre> * 添加like条件 * <pre> * @param queryWraper * @param column 指出要向哪个字段添加包含(like)条件 * @param value like什么 * @author jojo 2014-8-12 下午3:13:44 */ protected void addLikeConditionToColumn(QueryWraper<T> queryWraper, String column, String value) { if (StringUtils.isNotBlank(value)) { queryWraper.addPredicate(createLikeCondition(queryWraper, column, value)); } } /** * @param queryWraper * @param column 查哪个字段 * @param value 字段like什么 * @return * @author zhailiang * @since 2016年12月13日 */ @SuppressWarnings("unchecked") protected Predicate createLikeCondition(QueryWraper<T> queryWraper, String column, String value) { Path<String> fieldPath = getPath(queryWraper.getRoot(), column); Predicate condition = queryWraper.getCb().like(fieldPath, "%" + value + "%"); return condition; } /** * <pre> * 添加like条件 * <pre> * @param queryWraper * @param column * @param value * @author jojo 2014-8-12 下午3:13:44 */ @SuppressWarnings("unchecked") protected void addStartsWidthConditionToColumn(QueryWraper<T> queryWraper, String column, String value) { if (StringUtils.isNotBlank(value)) { Path<String> fieldPath = getPath(queryWraper.getRoot(), column); queryWraper.addPredicate(queryWraper.getCb().like(fieldPath, value + "%")); } } /** * 添加等于条件 * @param queryWraper * @param column 指出要向哪个字段添加条件 * @param value 指定字段的值 */ protected void addEqualsConditionToColumn(QueryWraper<T> queryWraper, String column, Object value) { if(needAddCondition(value)) { Path<?> fieldPath = getPath(queryWraper.getRoot(), column); queryWraper.addPredicate(queryWraper.getCb().equal(fieldPath, value)); } } /** * 添加不等于条件 * @param queryWraper * @param column 指出要向哪个字段添加条件 * @param value 指定字段的值 */ protected void addNotEqualsConditionToColumn(QueryWraper<T> queryWraper, String column, Object value) { if(needAddCondition(value)) { Path<?> fieldPath = getPath(queryWraper.getRoot(), column); queryWraper.addPredicate(queryWraper.getCb().notEqual(fieldPath, value)); } } /** * <pre> * * <pre> 得到属性的路径 * @param root * @param property * @return * @author jojo 2014-8-12 下午3:06:58 */ @SuppressWarnings("rawtypes") protected Path getPath(Root root, String property){ //Path代表一个简单或者复杂的属性路径 String[] names = StringUtils.split(property, "."); //比如路径是admin.role.resource，需要为resource创建path Path path = root.get(names[0]); for (int i = 1; i < names.length; i++) { path = path.get(names[i]); //创建与引用属性相对应的路径 } return path; } /** * <pre> * 判断是否需要添加where条件 * <pre> * @param value * @return * @author jojo 2014-8-12 下午3:07:00 */ @SuppressWarnings("rawtypes") protected boolean needAddCondition(Object value) { boolean addCondition = false; if (value != null) { if(value instanceof String) { if(StringUtils.isNotBlank(value.toString())) { addCondition = true; } }else if(value.getClass().isArray()) { if(ArrayUtils.isNotEmpty((Object[]) value)) { addCondition = true; } }else if(value instanceof Collection) { if(CollectionUtils.isNotEmpty((Collection) value)) { addCondition = true; } }else { addCondition = true; } } return addCondition; } } -
AbstractEventConditionBuilder抽象类，继承AbstractConditionBuilder类，根据传上来的条件对象，对AbstractConditionBuilder查询方法进行进一步封装。
public abstract class AbstractEventConditionBuilder<T, C> extends AbstractConditionBuilder<T> { /** * 查询条件 */ private C condition; /** * @param condition 查询条件 */ public AbstractEventConditionBuilder(C condition){ this.condition = condition; } /** * 向查询中添加包含(like)条件 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取，并且指出要向哪个字段添加包含(like)条件。(同一个字段名称) * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addLikeCondition(QueryWraper<T> queryWraper, String field){ addLikeCondition(queryWraper, field, field); } /** * 向查询中添加包含(like)条件 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取 * @param column 指出要向哪个字段添加包含(like)条件 * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addLikeCondition(QueryWraper<T> queryWraper, String field, String column){ addLikeConditionToColumn(queryWraper, column, (String) getValue(getCondition(), field)); //得到condition里面 field字段的值 } /** * 向查询中添加包含(like)条件,%放在值后面 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取，并且指出要向哪个字段添加包含(like)条件。(同一个字段名称) * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addStartsWidthCondition(QueryWraper<T> queryWraper, String field){ addStartsWidthCondition(queryWraper, field, field); } /** * 向查询中添加包含(like)条件,%放在值后面 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取 * @param column 指出要向哪个字段添加包含(like)条件 * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addStartsWidthCondition(QueryWraper<T> queryWraper, String field, String column){ addStartsWidthConditionToColumn(queryWraper, column, (String) getValue(getCondition(), field)); } /** * 向查询中添加等于(=)条件 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取，并且指出要向哪个字段添加条件。(同一个字段名称) * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addEqualsCondition(QueryWraper<T> queryWraper, String field){ addEqualsCondition(queryWraper, field, field); } /** * 向查询中添加等于(=)条件 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取 * @param column 指出要向哪个字段添加条件 * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addEqualsCondition(QueryWraper<T> queryWraper, String field, String column){ addEqualsConditionToColumn(queryWraper, column, getValue(getCondition(), field)); } /** * 向查询中添加不等于(!=)条件 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取，并且指出要向哪个字段添加条件。(同一个字段名称) * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addNotEqualsCondition(QueryWraper<T> queryWraper, String field){ addNotEqualsCondition(queryWraper, field, field); } /** * 向查询中添加等于(=)条件 * * @param queryWraper * @param field 指出查询条件的值从condition对象的哪个字段里取 * @param column 指出要向哪个字段添加条件 * @throws NoSuchMethodException * @throws InvocationTargetException * @throws IllegalAccessException */ protected void addNotEqualsCondition(QueryWraper<T> queryWraper, String field, String column){ addNotEqualsConditionToColumn(queryWraper, column, getValue(getCondition(), field)); } /** * <pre> * 向查询中添加in条件 * <pre> * @param queryWraper * @param field * @author jojo 2014-8-12 下午3:26:50 */ protected void addInCondition(QueryWraper<T> queryWraper, String field) { addInCondition(queryWraper, field, field); } /** * <pre> * 向查询中添加in条件 * <pre> * @param queryWraper * @param field * @param column * @author jojo 2014-8-12 下午3:27:46 */ protected void addInCondition(QueryWraper<T> queryWraper, String field, String column) { addInConditionToColumn(queryWraper, column, getValue(getCondition(), field)); } /** * <pre> * 向查询中添加between条件 * <pre> * @param queryWraper * @param field * @author jojo 2014-8-12 下午3:26:50 */ protected void addBetweenCondition(QueryWraper<T> queryWraper, String field) { addBetweenCondition(queryWraper, field, field+"To", field); } /** * <pre> * 向查询中添加between条件 * <pre> * @param queryWraper * @param field * @param column * @author jojo 2014-8-12 下午3:27:46 */ @SuppressWarnings("rawtypes") protected void addBetweenCondition(QueryWraper<T> queryWraper, String startField, String endField, String column) { addBetweenConditionToColumn(queryWraper, column, (Comparable)getValue(getCondition(), startField), (Comparable)getValue(getCondition(), endField)); } /** * <pre> * 向查询中添加大于条件 * <pre> * @param queryWraper * @param field * @author jojo 2014-8-12 下午3:26:50 */ protected void addGreaterThanCondition(QueryWraper<T> queryWraper, String field) { addGreaterThanCondition(queryWraper, field, field); } /** * <pre> * 向查询中添加大于条件 * <pre> * @param queryWraper * @param field * @param column * @author jojo 2014-8-12 下午3:27:46 */ @SuppressWarnings("rawtypes") protected void addGreaterThanCondition(QueryWraper<T> queryWraper, String field, String column) { addGreaterThanConditionToColumn(queryWraper, column, (Comparable)getValue(getCondition(), field)); } /** * <pre> * 向查询中添加大于等于条件 * <pre> * @param queryWraper * @param field * @author jojo 2014-8-12 下午3:26:50 */ protected void addGreaterThanOrEqualCondition(QueryWraper<T> queryWraper, String field) { addGreaterThanOrEqualCondition(queryWraper, field, field); } /** * <pre> * 向查询中添加大于等于条件 * <pre> * @param queryWraper * @param field * @param column * @author jojo 2014-8-12 下午3:27:46 */ @SuppressWarnings("rawtypes") protected void addGreaterThanOrEqualCondition(QueryWraper<T> queryWraper, String field, String column) { addGreaterThanOrEqualConditionToColumn(queryWraper, column, (Comparable)getValue(getCondition(), field)); } /** * <pre> * 向查询中添加小于条件 * <pre> * @param queryWraper * @param field * @author jojo 2014-8-12 下午3:26:50 */ protected void addLessThanCondition(QueryWraper<T> queryWraper, String field) { addLessThanCondition(queryWraper, field, field); } /** * <pre> * 向查询中添加小于条件 * <pre> * @param queryWraper * @param field * @param column * @author jojo 2014-8-12 下午3:27:46 */ @SuppressWarnings("rawtypes") protected void addLessThanCondition(QueryWraper<T> queryWraper, String field, String column) { addLessThanConditionToColumn(queryWraper, column, (Comparable)getValue(getCondition(), field)); } /** * <pre> * 向查询中添加小于等于条件 * <pre> * @param queryWraper * @param field * @author jojo 2014-8-12 下午3:26:50 */ protected void addLessThanOrEqualCondition(QueryWraper<T> queryWraper, String field) { addLessThanOrEqualCondition(queryWraper, field, field); } /** * <pre> * 向查询中添加小于等于条件 * <pre> * @param queryWraper * @param field * @param column * @author jojo 2014-8-12 下午3:27:46 */ @SuppressWarnings("rawtypes") protected void addLessThanOrEqualCondition(QueryWraper<T> queryWraper, String field, String column) { addLessThanOrEqualConditionToColumn(queryWraper, column, (Comparable)getValue(getCondition(), field)); } /** * * @param condition 从这个对象里面查 * @param field 要查询的属性 * @return */ private Object getValue(C condition, String field) { try { //得到condition对象中field属性的值 //使用示例：getProperty(book,"authors[0].favorites(food)") return PropertyUtils.getProperty(condition, field); } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) { e.printStackTrace(); } return null; } /** * @return the condition */ public C getCondition() { return condition; } /** * @param condition the condition to set */ public void setCondition(C condition) { this.condition = condition; } } -
MyImoocSpecification类，继承AbstractEventConditionBuilder类，并实现Specification接口，重写toPredicate()方法，使用时继承该类即可
public abstract class MySpecification<T, C> extends AbstractEventConditionBuilder<T, C> implements Specification<T> { //AbstractEventConditionBuilder里面封装了添加各种查询条件的方法 /** * @param condition */ public MySpecification(C condition) { super(condition); } /** * * 构建查询条件，子类必须实现addCondition方法来编写查询的逻辑。 * * 子类可以通过addFetch方法控制查询的关联和抓取行为。 * */ @Override public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) { if (Long.class != query.getResultType()) { addFetch(root); } List<Predicate> predicates = new ArrayList<Predicate>(); QueryWraper<T> queryWraper = new QueryWraper<T>(root, query, cb, predicates); addCondition(queryWraper); Predicate permissionCondition = getPermissionCondition(queryWraper); if (permissionCondition != null) { queryWraper.addPredicate(permissionCondition); } return cb.and(predicates.toArray(new Predicate[predicates.size()])); } /** * 添加权限条件，如果要查询的domain实现了{@link ManagedByOrgan}接口，那么传入的Condition对象也应该实现 * {@link ManagedByOrgan}接口， * 程序会尝试从Condition对象获取organFullId,然后作为like查询条件添加到查询中。 * 查出所有以传入的organFullId开头的domain. * * @param queryWraper * @return */ protected Predicate getPermissionCondition(QueryWraper<T> queryWraper) { return null; } /** * <pre> * 子类可以通过覆盖此方法实现关联抓取，避免n+1查询 * * <pre> * * @param root * @author jojo 2014-7-22 上午9:49:26 */ protected void addFetch(Root<T> root) { } protected abstract void addCondition(QueryWraper<T> queryWraper); //钩子方法，子类实现 自动调用 }
三、使用示例
3.1 创建条件封装对象
public class AdminCondition {
private String username;
/**
* @return the username
*/
public String getUsername() {
return username;
}
/**
* @param username the username to set
*/
public void setUsername(String username) {
this.username = username;
}
}
3.2 继承MySpecification类
传入条件封装对象，以及结果实体类型
public class AdminSpec extends ImoocSpecification<Admin, AdminCondition> {
public AdminSpec(AdminCondition condition) {
super(condition);
}
@Override
protected void addCondition(QueryWraper<Admin> queryWraper) {
addLikeCondition(queryWraper, "username");
}
}
3.3 调用
public class AdminServiceImpl implements AdminService {
@Autowired
private AdminRepository adminRepository;
public Page<AdminInfo> query(AdminCondition condition, Pageable pageable) {
Page<Admin> admins = adminRepository.findAll(new AdminSpec(condition), pageable);
return QueryResultConverter.convert(admins, AdminInfo.class, pageable);
}
}
4445
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
