目前的spring data jpa已经帮我们干了CRUD的大部分活了,但如果有些活它干不了(CrudRepository接口中没定义),那么只能由我们自己干了。这里要说的就是在它的框架里,如何实现自己定制的多条件查询。下面以我的例子说明一下:业务场景是我现在有张订单表,我想要支持根据订单状态、订单当前处理人和订单日期的起始和结束时间这几个条件一起查询。
先看分页的,目前spring data jpa给我们做分页的Repository是PagingAndSortingRepository,但它满足不了自定义查询条件,只能另选JpaRepository。那么不分页的Repository呢?其实还是它。接下来看怎么实现:
Repository:
import com.crocodile.springboot.model.Flow;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository; import java.util.List; public interface FlowRepository extends JpaRepository<Flow, Long> {
Long count(Specification<Flow> specification); Page<Flow> findAll(Specification<Flow> specification, Pageable pageable); List<Flow> findAll(Specification<Flow> specification); }
Service:
/**
* 获取结果集
*
* @param status
* @param pageNo
* @param pageSize
* @param userName
* @param createTimeStart
* @param createTimeEnd
* @return
*/
public List<Flow> queryFlows(int pageNo, int pageSize, String status, String userName, Date createTimeStart, Date createTimeEnd) {
List<Flow> result = null; // 构造自定义查询条件
Specification<Flow> queryCondition = new Specification<Flow>() {
@Override
public Predicate toPredicate(Root<Flow> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
List<Predicate> predicateList = new ArrayList<>();
if (userName != null) {
predicateList.add(criteriaBuilder.equal(root.get("currentOperator"), userName));
}
if (status != null) {
predicateList.add(criteriaBuilder.equal(root.get("status"), status));
}
if (createTimeStart != null && createTimeEnd != null) {
predicateList.add(criteriaBuilder.between(root.get("createTime"), createTimeStart, createTimeEnd));
}
return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
}
}; // 分页和不分页,这里按起始页和每页展示条数为0时默认为不分页,分页的话按创建时间降序
try {
if (pageNo == 0 && pageSize == 0) {
result = flowRepository.findAll(queryCondition);
} else {
result = flowRepository.findAll(queryCondition, PageRequest.of(pageNo - 1, pageSize, Sort.by(Sort.Direction.DESC, "createTime"))).getContent();
}
} catch (Exception e) {
LOGGER.error("--queryFlowByCondition-- error : ", e);
} return result;
}
上面我们可以看到,套路很简单,就是两板斧:先通过Specification对象定义好自定义的多查询条件,我这里的条件是当传了当前用户时,那么将它加入到查询条件中,不传该参数自然就不加,同理,传了订单状态的话那是通过相等来判断,最后,如果传了起始和结束时间,通过between来查在起始和结束之间的数据;第二板斧调用我们在Repository中定义好的findAll方法,如果分页就用带Pageable分页对象参数的方法,不分页不带该参数即可。
如果你的自定义查询条件里需要模糊查询,比如我有个订单ID要支持模糊查询,也很简单:
if (orderId!= null) {
predicateList.add(criteriaBuilder.like(root.get("orderId"), "%" + orderId+ "%"));}
最后我们看回到FlowRepository的第一个方法count,它是返回不分页的多查询的总记录数的,套路也是一样的:
/**
* 查记录数
*
* @param status
* @param userName
* @param createTimeStart
* @param createTimeEnd
* @return
*/
public Long getCounts(String status, String userName, Date createTimeStart, Date createTimeEnd) {
Long total = 0L;
Specification<Flow> countCondition = new Specification<Flow>() {
@Override
public Predicate toPredicate(Root<Flow> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
List<Predicate> predicateList = new ArrayList<>();
if (userName != null) {
predicateList.add(criteriaBuilder.equal(root.get("currentOperator"), userName));
}
if (status != null) {
predicateList.add(criteriaBuilder.equal(root.get("status"), status));
}
if (createTimeStart != null && createTimeEnd != null) {
predicateList.add(criteriaBuilder.between(root.get("createTime"), createTimeStart, createTimeEnd));
}
return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
}
}; try {
total = flowRepository.count(countCondition);
} catch (Exception e) {
LOGGER.error("--getCountsByCondition-- error: ", e);
}
return total;
}