JPA 如何使用Join查询和Distinct去重

假设有表OnlineOrder（订单表） 和 OnlineOrderDetail（订单详情表）， 首先可以构建 Specification, 在查询时，使用Join。

Specification<OnlineOrder> specification = (root, query, cb) -> {
            List<Predicate> predicates = Lists.newArrayList();
            // join构建，默认时inner join
            Join<OnlineOrder, OnlineOrderDetail> join = root.join("details");
            Predicate detailP1 = cb.isNull(join.get("afterSaleAt"));
            Predicate detailP2 = cb.equal(join.get("saleIsFinished").as(Boolean.class), true);
            predicates.add(cb.or(detailP1, detailP2));
           
            // distinct 去重
            query.distinct(true);

            // 已收货订单
            predicates.add(cb.equal(root.get("orderStatus").as(String.class), OnlineOrderStatus.received.name()));
            // 收货时间早于7天前
            predicates.add(cb.lessThan(root.get("receivedAt").as(LocalDateTime.class), day7Ago));
            predicates.add(cb.isNull(root.get("billNo").as(String.class)));
            // 过滤掉不看的单子
            List<String> filters = criteria.getFilters();
            if (!CollectionUtils.isEmpty(filters)) {
                predicates.add(cb.not(root.get("orderNo").in(filters)));
            }

            Expression<LocalDateTime> createdAt = root.get("createdAt").as(LocalDateTime.class)；
            // 开始时间
            if (criteria.getBeginTime() != null) {
                Predicate p = cb.greaterThanOrEqualTo(createdAt, criteria.getBeginTime().atStartOfDay());
                predicates.add(p);
            }

            // 结束时间
            if (criteria.getEndTime() != null) {
                Predicate p = cb.lessThanOrEqualTo(createdAt, criteria.getEndTime().atStartOfDay());
                predicates.add(p);
            }

            query.where(cb.and(predicates.toArray(new Predicate[0])));
            query.orderBy(cb.desc(createdAt));
            return query.getRestriction();
        };
        return specification;
    }

下面是使用代码：

Specification<OnlineOrder> specification = this.buildSpecification(criteria);
return onlineOrderRepository.findAll(specification);

OnlineOrderRepository 代码如下：

public interface OnlineOrderRepository extends JpaRepository<OnlineOrder, String>, JpaSpecificationExecutor<OnlineOrder> {
}