Spring JPA使用CriteriaBuilder动态构造查询

在使用Spring JPA提供的方法只能进行简单的CRUD，如果遇到复杂的情况就需要我们动态来构建查询条件了。这里我们来看使用CriteriaBuilder如何来构造查询。
核心代码：

entityManager.getCriteriaBuilder();
 CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
 Root<Order> root = query.from(Order.class);
 query.select(criteriaBuilder.count(root.get("id")));
 Predicate predicate = criteriaBuilder.equal(root.get("id"), 1);
 query.where(predicate);
 Long singleResult = entityManager.createQuery(query).getSingleResult();

1. 调用entityManager.getCriteriaBuilder()来获取CriteriaBuilder。CriteriaBuilder可以用于创建CriteriaQuery、CriteriaUpdate和CriteriaDelete。除此之外类似count、max等函数也是由CriteriaBuilder来创建的。其中Entitymanager可以使用@PersistenceContext注解来进行注入。
2. 调用criteriaBuilder.createQuery来创建CriteriaQuery。其中createQuery的参数是Query返回值类型。
3. 调用query.from(Order.class)。参数是对应于order表的实体类，query.from类似于sql中的from语句，该方法的执行等价于sql中的from order。
4. 调用 query.select创建映射。 query.select(criteriaBuilder.count(root.get(“id”)))等价于select count(id)。如果执行query.select(root)则等价于select *。
5. 使用CriteriaBuilder构造查询条件Predicate，该predicate也就是在where后面的条件子句。
6. 将Predicate放在 query.where中。
7. 最后执行查询获取数据。

@Autowired
    EntityManager entityManager;
    @Override
    public TableResultResponse<RiskAnalyzeRecordDto> groupByCreatDateAndDataTime(RiskAnalyzeRecordDto dto, Pageable pageable) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
        Root<RiskAnalyzeRecord> root = cq.from(RiskAnalyzeRecord.class);
        Predicate whereCondition;
        if (dto.getCreatedDateStart()!=null&&dto.getCreatedDateEnd()!=null){
            whereCondition = cb.and(
                    cb.like(root.<String>get("fullId"), dto.getFullId() + "%"),
                    cb.between(root.<Date>get("createdDate"),dto.getCreatedDateStart(),dto.getCreatedDateEnd())
            );
        }else if (dto.getCreatedDateEnd()!=null){
            whereCondition = cb.and(
                    cb.like(root.<String>get("fullId"), dto.getFullId() + "%"),
                    cb.lessThanOrEqualTo(root.<Date>get("createdDate"), dto.getCreatedDateEnd())
            );
        }else if (dto.getCreatedDateStart()!=null){
            whereCondition = cb.and(
                    cb.like(root.<String>get("fullId"), dto.getFullId() + "%"),
                    cb.greaterThanOrEqualTo(root.<Date>get("createdDate"), dto.getCreatedDateStart())
            );
        }else {
            whereCondition = cb.like(root.<String>get("fullId"), dto.getFullId() + "%");
        }
        if (StringUtils.isNotBlank(dto.getDataTime())){
            whereCondition = cb.and(whereCondition,cb.equal(root.get("dataTime"),dto.getDataTime()));
        }
        if (StringUtils.isNotBlank(dto.getRiskId())){
            whereCondition = cb.and(whereCondition,cb.equal(root.get("riskId"),dto.getRiskId()));
        }
        cq.multiselect(root.get("userName"),root.get("createdDate"),root.get("dataTime"), cb.sum(
                    cb.<Integer>selectCase().when(root.<Integer>get("record").isNotNull(), root.<Integer>get("record")).otherwise(0)
         )).where(whereCondition)
         .groupBy(root.get("userName"),root.get("createdDate"),root.get("dataTime")).orderBy(new OrderImpl(root.get("createdDate"),false));

        TypedQuery<Object[]> query = entityManager.createQuery(cq);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        List<Object[]> list = query.getResultList();
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<RiskAnalyzeRecord> root1 = countQuery.from(RiskAnalyzeRecord.class);
        countQuery.select(cb.count(root1.get("id"))).
                where(whereCondition).
                groupBy(root1.get("userName"),root1.get("createdDate"),root1.get("dataTime"));
        TypedQuery<Long> countTypeQuery = entityManager.createQuery(countQuery);
        int count =  countTypeQuery.getResultList().size();
        List<RiskAnalyzeRecordDto> dtoList = new ArrayList<>();
        for (Object[] object : list) {
            String userName= String.valueOf(object[0]);
            String dataTime= String.valueOf(object[2]);
            Integer record= Integer.valueOf(String.valueOf(object[3]));
            RiskAnalyzeRecordDto elem = new RiskAnalyzeRecordDto();
            elem.setUserName(userName);
            elem.setCreatedDate((Date) object[1]);
            elem.setDataTime(dataTime);
            elem.setRecord(record);
            dtoList.add(elem);
        }

        return new TableResultResponse(count, dtoList);
    }

1. 参考文档：​​JPA分组查询，求和，自定义查询字段，自定义VO承接​​  Spring JPA使用CriteriaBuilder动态构造查询