JPA 通过Specification如何实现复杂查询

JPA 通过Specification实现复杂查询

JPA中继承BaseRepo之后,可以使用最基本的增删改查,如果想实现复杂查询,则需要借助Specification来完成这个功能:

下面就简单介绍一下Specification的使用

public void findAll(ConstructPlanPageReqEntity constructPlanPageReqEntity) {
	Integer pageNum = page.getPageNum();
        Integer pageSize = page.getPageSize();
        String costType = constructPlanPageReqEntity.getCostType();
        String name = constructPlanPageReqEntity.getName();
        String planMoneyStart = constructPlanPageReqEntity.getPlanMoneyStart();
        String planMoneyEnd = constructPlanPageReqEntity.getPlanMoneyEnd();
        String singMoneyEnd = constructPlanPageReqEntity.getSingMoneyEnd();
        String signMoneyStart = constructPlanPageReqEntity.getSignMoneyStart();
        long projectId = Long.parseLong(constructPlanPageReqEntity.getProjectId());
        String status = constructPlanPageReqEntity.getStatus();
        //分页
        pageNum=pageNum-1;
        Pageable pageable = PageRequest.of(pageNum, pageSize);
//多条件匹配查询
        Specification specification= new Specification() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
                ArrayList list = new ArrayList<>();
                Path costType1 = root.get("costType");
                Path name1 = root.get("name");
                Path projectId1 = root.get("projectId");
                Path status1 = root.get("status");
                if (projectId>0){
                    list.add(criteriaBuilder.equal(projectId1,projectId));
                }
                if (StringUtil.isNotEmpty(status)){
                    list.add(criteriaBuilder.equal(status1,status));
                }
                //条件查询
                if (StringUtil.isNotEmpty(costType)){
                    list.add(criteriaBuilder.equal(costType1,costType));
                }
                //模糊查询
                if (StringUtil.isNotEmpty(name)){
                    list.add(criteriaBuilder.like(name1,"%"+name+"%"));
                }
                //范围查询
                if (StringUtil.isNotEmpty(planMoneyStart)&&StringUtil.isNotEmpty(planMoneyEnd)){
                    try {
                        list.add(criteriaBuilder.between(root.get("planMoney"),NumberUtil.strToDouble(planMoneyStart),NumberUtil.strToDouble(planMoneyEnd)));
                    } catch (Exception e) {
                        throw new ApiException("规划金额查询失败");
                    }
                }
                 //排序
                criteriaQuery.orderBy(criteriaBuilder.asc(root.get("name")));
                Predicate[] array = new Predicate[list.size()];
                return criteriaBuilder.and(list.toArray(array));
            }
        };
}
 
  
 

以上代码实现了多条件查询,其中需要重写toPredicate方法,具体参数:

  • 用root.get()获取bean中的数据库对应字段
  • 用criteriaBuilder来组建条件查询语句

JPA 通过Specification如何实现复杂查询_第1张图片

上图是criteriaBuilder各种sql符号的方法名,根据需求组建不同的sql语句

criteriaBuilder.and(list.toArray(array))这句是最后定义各个sql查询条件的关系,这里用的and

至此,复杂sql语句就拼接完成,本人对Specification的使用未进行深入研究,个人觉得相对filter Strem的复杂查询来说Specification更繁琐,因此更倾向于通过Strem的复杂查询,这回就不多说了,下次就介绍下如何使用Stream进行复杂查询

spring-data-jpa Specification拼接复杂查询

  public Page findAll(Map params, ServiceItemConsumeStatus serviceItemConsumeStatus,ServiceItemStatus serviceItemStatus, Pageable pageable) {
        return dao.findAll(spec(serviceItemConsumeStatus, serviceItemStatus, params), pageable);
    }
    private Specification spec(final ServiceItemConsumeStatus serviceItemConsumeStatus, 
    final ServiceItemStatus serviceItemStatus, Map params) {
        Collection filters = SearchFilter.parse(params).values();
        final Specification fsp = SearchFilter.spec(filters, ServiceItem.class);
        Specification sp = new Specification() {
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                Predicate pred = fsp.toPredicate(root, query, cb);
                if (ServiceItemConsumeStatus.可消费.equals(serviceItemConsumeStatus)) {
                    pred = cb.and(pred, cb.gt(root.get("countLeft").as(int.class), 0));
                } else if (ServiceItemConsumeStatus.消费完毕.equals(serviceItemConsumeStatus)) {
                    pred = cb.and(pred, cb.le(root.get("countLeft").as(int.class), 0));
                }
                if (serviceItemStatus != null) {
                    pred = cb.and(pred, cb.equal(root.get("status"), serviceItemStatus));
                }
                return pred;
            }
        };
        return sp;
    }

以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。