JPA 单表多条件+分组+排序+分页查询

前言 ： JPA 单表相关操作用以下示例代码可以完成
参考代码

    /**
     * 方法描述:  根据条件查询数据并分页
     *
     * @param condition 查询条件
     * @param pageParam 分页条件
     * @return com.tianshu.evp.commons.domain.webRequest.Pagination<com.tianshu.evp.orderService.feeconfig.dto.SocialCarDetailDto>
     * @author wqf
     * @date 2021/5/18 9:29
     */
 public Pagination<CarDetailDto> pageCarDetail(Condition condition, PageParam pageParam) {
        Pagination<CarDetailDto> pagination = new Pagination<>();
        int pageSize = pageParam.getPageSize();
        int currentPage = pageParam.getCurrentPage();
        pagination.setCurrentPage(currentPage);
        pagination.setPageSize(pageSize);
        //查询记录总数
        Integer totalCount = countQuery(condition);
        if (totalCount == 0) {
            pagination.setTotalPage(0);
            pagination.setTotalRecord(0L);
            pagination.setRecords(Collections.emptyList());
            return pagination;
        }
        //查询所有记录
        List<CarDetailDto> resultList = listQuery(condition, currentPage, pageSize);
        pagination.setTotalRecord(Long.valueOf(totalCount));
        int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
        pagination.setTotalPage(totalPage);
        //数据处理（根据你的业务对查询结果进行处理）
        //TODO
        pagination.setRecords(resultList);
        return pagination;
    }

    private List<CarDetailDto> listQuery(Condition condition, Integer currentPage, Integer pageSize) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //查询结果用CarDetailDto实体接收 需要注意的是：如果你只查询部分字段那么接收实体中需要有这些字段的构造方法，否则会报错
        CriteriaQuery<CarDetailDto> listQuery = cb.createQuery(CarDetailDto.class);
        //具体实体的Root Root根对象对应于from后面的表，如select * from a,b,c 那么a，b，c就相当于Root,Root,Root,Root是一个类型化的类，创建时要指定它的类型。
        //criteriaQuery查询主语句--它必须作用于实体类型（指明主表）,【它的初始化和泛型类的初始化一样】CarMonDo 查询数据集合结果封装对象
        Root<CarDetail> root = listQuery.from(CarDetail.class);
        Path<Long> stationId = root.get("stationId");
        Path<LocalDate> statTime = root.get("statTime");
        Path<BigDecimal> tPq = root.get("tPq");
        Path<BigDecimal> serviceAmt = root.get("serviceAmt");
        listQuery.multiselect(stationId, cb.sum(tPq).alias("tPq"), cb.sum(serviceAmt).alias("serviceAmt"), statTime);
        conditionEncapsulate(condition, cb, listQuery, root);
        TypedQuery<CarDetailDto> query = entityManager.createQuery(listQuery);
        query.setFirstResult((currentPage - 1) * pageSize);
        query.setMaxResults(pageSize);
        return query.getResultList();
    }

    private Integer countQuery(Condition condition) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Long> listQuery = cb.createQuery(Long.class);

        Root<CarDetail> root = listQuery.from(CarDetail.class);
        listQuery.select(cb.countDistinct(root));
        conditionEncapsulate(condition, cb, listQuery, root);
        TypedQuery<Long> query = entityManager.createQuery(listQuery);
        return query.getResultList().size();
    }

    private void conditionEncapsulate(Condition  condition, CriteriaBuilder cb,
                                      CriteriaQuery<?> listQuery, Root<CarDetail> root) {
        Path<Long> stationId = root.get("stationId");
        Path<LocalDate> statTime = root.get("statTime");
        List<Predicate> predicates = new ArrayList<>();
        //in 条件
        List<Long> orgIdList = condition.getOrgIdList();
        if (orgIdList != null) {
            //in 条件
            CriteriaBuilder.In<Long> in = cb.in(root.get("orgId"));
            for (Long id : condition.getOrgIdList()) {
                in.value(id);
            }
            predicates.add(cb.in(in));
        }
        //between
        predicates.add(cb.between(root.get("statTime"), condition.getStartTime(), condition.getEndTime()));
        //条件查询
        Predicate[] pre = new Predicate[predicates.size()];
        listQuery.where(predicates.toArray(pre));
        //分组
        listQuery.groupBy(stationId, statTime);
        //order by 排序
        List<Order> orderList = new ArrayList<>();
        orderList.add(cb.asc(stationId));
        orderList.add(cb.asc(statTime));
        listQuery.orderBy(orderList);
        //like等其他用法不会的话百度一下
    }

JPA查询结果对象封装