Specification算是JPA中比较灵活的查询方式
public Page<WorkOrder> getWorkOrders(Integer size, Integer page, String sort, WorkOrderExample example){
sort = sort == null ? WorkOrder_.SUBMIT_TIME : sort;
page = page == null || page < 2 ? 0 : page - 1;
size = size == null || size < 0 ? 10 : size;
Specification<WorkOrder> specification = (Root<WorkOrder> root, CriteriaQuery<?> cq, CriteriaBuilder cb) -> {
List<Predicate> predicates = new LinkedList<>();
if(example.getOrderId() != null && !"".equals(example.getOrderId())){
predicates.add(cb.like(root.get(WorkOrder_.WORK_ORDER_ID), "%" +example.getOrderId() + "%"));
}
if(example.getKeyWord() != null && !"".equals(example.getKeyWord())){
predicates.add(cb.like(root.get(WorkOrder_.ORDER_DESCRIBE),"%" + example.getKeyWord() + "%"));
}
if(example.getStatus() != null && !"".equals(example.getStatus())){
predicates.add(cb.like(root.get(WorkOrder_.STATUS),"%" + example.getStatus() + "%"));
}
if(example.getOrderUserId() != null && !"".equals(example.getOrderUserId())){
predicates.add(cb.like(root.get(WorkOrder_.ORDER_USER_ID),"%" + example.getOrderUserId() + "%"));
}
// if(example.getId() != null && !"".equals(example.getId())){
// predicates.add(cb.equal(root.get(WorkOrder_.INNER_ORDER_CLASSIFICATION),example.getId()));
// }
if(example.getClassificationId() != null && !"".equals((example.getClassificationId()))){
WorkOrderClassification workOrderClassification = workOrderClassificationRepository.findById(example.getClassificationId()).orElse(null);
String path = workOrderClassification.getPath();
Join<WorkOrder, WorkOrderClassification> joinClassification = root.join("workOrderClassification", JoinType.LEFT);
predicates.add(cb.like(joinClassification.get(WorkOrderClassification_.PATH),path + "%"));
}
if(example.getId() != null && !"".equals(example.getId())){
WorkOrderClassification workOrderClassification = workOrderClassificationRepository.findById(example.getId()).orElse(null);
String path = workOrderClassification.getPath();
Join<WorkOrder, WorkOrderClassification> joinClassification = root.join("workOrderClassification", JoinType.LEFT);
predicates.add(cb.like(joinClassification.get(WorkOrderClassification_.PATH),path + "%"));
}
return cb.and(predicates.toArray(new Predicate[0]));
};
return workOrderRepository.findAll(specification, PageRequest.of(page, size, Sort.by(Sort.Direction.DESC, sort)));
}
本文使用的案例是用Specification进行条件动态查询,然后将查询结果进行分页展示。
各参数作用:size:每页显示记录条数、page:页数、sort:排序方式、example:前端传回来的查询条件对象
1、创建Specification对象,用于封装查询条件。
2、Root:查询的根对象、CriteriaQuery:顶层查询对象,自定义查询方式、CriteriaBuilder:查询的构造器,封装了很多的查询条件
3、
自定义查询条件:
1、实现Specification接口(提供泛型:查询的对象类型)
2、实现toPredicate方法,构造查询条件
3、借助root和cb(
root:获取需要查询的对象属性
CriteriaBuilder:构造查询条件,内部封装了很多查询条件(模糊匹配,精准匹配)
)
//一个条件查询
//精确查询,使用equal判断是否相等
predicates.add(cb.equal(root.get(User_.ID), example.getId()));
//模糊查询,使用like判断是否包含name
predicates.add(cb.like(root.get(User_.NAME), "%" + example.getName() + "%"));
将User对象中的ID值和example中的ID值进行匹配(cb.equal()),然后将查询条件添加到predicates链表中
//多条件查询
将多个predicate组合到一起
cb.and(predicates.toArray(new Predicate[0]));
以与的形式将多个查询条件拼接到一起
//添加排序
List<T> findAll(@Nullable Specification<T> spec, Sort sort);
在findAll()方法中传递sort对象参数
Sort sort = new Sort(desc,id) 第一个参数:排序的方式 第二个参数:按照哪个属性排序
//分页查询
Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);
PageRequest是Pageable接口的实现类
PageRequest(int page,int size)
4、多表连接查询
WorkOrderClassification workOrderClassification = workOrderClassificationRepository.findById(example.getClassificationId()).orElse(null);
String path = workOrderClassification.getPath();
Join<WorkOrder, WorkOrderClassification> joinClassification = root.join("workOrderClassification", JoinType.LEFT);
predicates.add(cb.like(joinClassification.get(WorkOrderClassification_.PATH),path + "%"));