Jpa 使用Specification构建复杂查询
JPA and中添加or查询条件
eg: 查询当前用户支付状态为1,任务运行状态为1或0的所有任务信息。
sql: select * from task where user_id=1 and billing_status=1 and status=1 or 0
怎样将查询条件封装到jpa的查询对象中呢?
public List<EmailGradeTask> findTaskByUserIdAndStatusAndBillingStatus(Integer userId){
List<EmailGradeTask> gradeTaskList = emailGradeTaskRepository.findAll(new Specification<EmailGradeTask>() {
@Nullable
@Override
public Predicate toPredicate(Root<EmailGradeTask> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder
criteriaBuilder) {
//封装and查询条件
List<Predicate> list = new ArrayList<>();
list.add(criteriaBuilder.equal(root.get("userId"), userId));
list.add(criteriaBuilder.equal(root.get("billingStatus"), "1"));
Predicate[] predicates = new Predicate[list.size()];
Predicate predicateWhere = criteriaBuilder.and(list.toArray(predicates));
//封装or查询条件
List<Predicate> listPermission = new ArrayList<>();
listPermission.add(criteriaBuilder.equal(root.get("status"), 1));
listPermission.add(criteriaBuilder.equal(root.get("status"), 0));
Predicate[] predicatesPermissionArr = new Predicate[listPermission.size()];
Predicate predicatesPermission = criteriaBuilder.or(listPermission.toArray(predicatesPermissionArr));
//两条件合并
Predicate restriction = criteriaQuery.where(predicateWhere, predicatesPermission).getRestriction();
return restriction;
}
});
return gradeTaskList;
}
}