JPA 使用 Specification 动态构建多表查询、复杂查询及排序实现

之前做项目都是用的mybatis，刚开始接触jpa的时候，觉得使用起来非常的简单方便，根本不用自己写什么，都jpa都给封装好了。
但是随着业务、表结构和表关系越来越复杂，发现仅仅是简单的应用JpaRepository已经无法满足业务需求了。
学习了一下，发现Specification提供了灵活的查询方式，特此记录。

我们其中一个业务场景需要三个表：ship_order_info,ship_order_records,ship_order_logs,逐级一对多的关系。如果有同学不清楚OneToOne 和 OneToMany的话，可以去看我上一篇博客，有简单的讲解：https://blog.csdn.net/weixin_40910372/article/details/102970162

这篇博客主要讲的是Specification的使用：

@Override
    @Transactional
    public List<ShipOrderInfoDto> findByConditionWithoutPageable(List<UUID> locationInfoIdList, List<UUID> carrierIdList, List<String> deliveryTypeList, Integer dayTime, List<String> orderLatestStatusList, Instant fromDate, Instant toDate) throws Exception {
        Specification specification =
                (Specification<ShipOrderInfo>) (root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> predicates = new ArrayList<>();

                    // 排除逻辑删除数据
                    predicates.add(criteriaBuilder.equal(root.get("dataStatus"), AuditModel.DATA_STATUS_ACTVIE));

                    /**
                     * ship_order_info 表的条件查询
                     */
                    if (null != locationInfoIdList && !locationInfoIdList.isEmpty()) {
                        List<Predicate> tempPredicates = new ArrayList<>();
                        locationInfoIdList.forEach(locationId -> tempPredicates.add(criteriaBuilder.equal(root.get("locationInfoId").as(UUID.class), locationId)));
                        predicates.add(criteriaBuilder.or(tempPredicates.toArray(new Predicate[tempPredicates.size()])));
                    }
                    if (null != carrierIdList && !carrierIdList.isEmpty()) {
                        List<Predicate> tempPredicates = new ArrayList<>();
                        carrierIdList.forEach(carrierId -> tempPredicates.add(criteriaBuilder.equal(root.get("carrierId").as(UUID.class), carrierId)));
                        predicates.add(criteriaBuilder.or(tempPredicates.toArray(new Predicate[tempPredicates.size()])));
                    }
                    if (!CollectionUtils.isEmpty(deliveryTypeList)) {
                        List<Predicate> tempPredicates = new ArrayList<>();
                        deliveryTypeList.forEach(deliveryType -> tempPredicates.add(criteriaBuilder.equal(root.get("deliveryType"), deliveryType)));
                        predicates.add(criteriaBuilder.or(tempPredicates.toArray(new Predicate[tempPredicates.size()])));
                    }

                    if (null != orderLatestStatusList && !orderLatestStatusList.isEmpty()) {
                        List<Predicate> tempPredicates = new ArrayList<>();
                        orderLatestStatusList.forEach(orderLatestStatus -> tempPredicates.add(criteriaBuilder.equal(root.get("orderLatestStatus").as(Integer.class), orderLatestStatus)));
                        predicates.add(criteriaBuilder.or(tempPredicates.toArray(new Predicate[tempPredicates.size()])));
                    }
                    if (null != fromDate) {
                        predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createdTime"), fromDate));
                    }
                    if (null != toDate) {
                        predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createdTime"), toDate));
                    }

                    /**
                     *  ship_order_records 表的条件查询 (关联表条件查询)
                     *
                     *  shipOrderRecordsList 为ShipOrderInfo类中的ShipOrderRecords 的list形式
                     *
                     */
                    //针对自动签收: 2019/6/1 00:00:00 < 10009时间 < (现在时间 - 传入时间)
                    if (null != dayTime) {
                        Join<ShipOrderInfo, ShipOrderRecords> join = root.join("shipOrderRecordsList", JoinType.LEFT);
                        predicates.add(criteriaBuilder.equal(join.get("shipStatus"), SfsOrderStatus.CARRIER_ARRIVE.getStatus()));
                        predicates.add(criteriaBuilder.lessThanOrEqualTo(join.get("createdTime"), DateFormatUtil.getPreviousTime(null,dayTime)));
                        predicates.add(criteriaBuilder.greaterThanOrEqualTo(join.get("createdTime"), DateFormatUtil.getInstant(year,month,day)));
                    }
                    return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
                };


        List<ShipOrderInfo> shipOrderInfoList = shipOrderInfoRepository.findAll(specification, Sort.by(new ArrayList<>()));

        List<ShipOrderInfoDto> list = new ArrayList<>();
        if (null == shipOrderInfoList || 0 == shipOrderInfoList.size()) {
            // throw new BusinessResourceNotFoundException(AppClientResponse.GENERAL_NOT_FOUND_ERROR.getStatusDesc());
            return list;
        }

        shipOrderInfoList.stream().forEach(x -> {
                    ShipOrderInfoDto shipOrderInfoDto = new ShipOrderInfoDto();
                    BeanUtils.copyProperties(x, shipOrderInfoDto);
                    if (null != x.getShipOrderRecordsList() && !x.getShipOrderRecordsList().isEmpty()) {
                        List<ShipOrderRecordsDto> subList = new ArrayList<>();
                        x.getShipOrderRecordsList().stream().forEach(y -> {
                            ShipOrderRecordsDto shipOrderRecordsDto = new ShipOrderRecordsDto();
                            BeanUtils.copyProperties(y, shipOrderRecordsDto);
                            subList.add(shipOrderRecordsDto);
                        });
                        shipOrderInfoDto.setShipOrderRecordsDtoList(subList);
                    }
                    list.add(shipOrderInfoDto);
                }
        );

        return list;
    }