如何使用JPA Criteria API查询OneToMany列表字段获取指定客户的CustomerRequest

// 传入要查询的目标customerId
public List<CustomerRequest> findCustomerRequestsByCustomerId(int targetCustomerId) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<CustomerRequest> cq = cb.createQuery(CustomerRequest.class);
    Root<CustomerRequest> root = cq.from(CustomerRequest.class);
    
    // 关联到中间表CustomerRequest2Customer
    Join<CustomerRequest, CustomerRequest2Customer> join2Mid = root.join("listCustomerRequestToCustomer");
    // 关联到Customer实体
    Join<CustomerRequest2Customer, Customer> join2Customer = join2Mid.join("customer");
    
    // 拼接查询条件：客户ID匹配，同时可保留你原有的priority=1的条件
    Predicate customerMatch = cb.equal(join2Customer.get("id"), targetCustomerId);
    Predicate priorityMatch = cb.equal(root.get("priority"), 1);
    cq.where(cb.and(customerMatch, priorityMatch));
    
    // 去重，避免OneToMany关联查询产生重复的CustomerRequest记录
    cq.distinct(true);
    
    return entityManager.createQuery(cq).getResultList();
}

• 关联配置校验：你当前提供的实体定义中，CustomerRequest的@OneToMany注解的mappedBy值写的是customerRequestId，但CustomerRequest2Customer中对应关联CustomerRequest的字段名是customerRequest，需要把mappedBy改为customerRequest否则关联查询会报错
• 大数据量场景下更推荐使用EXISTS子查询实现，不需要额外去重，性能更优，代码示例如下：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<CustomerRequest> cq = cb.createQuery(CustomerRequest.class);
Root<CustomerRequest> root = cq.from(CustomerRequest.class);

// 构造EXISTS子查询
Subquery<CustomerRequest2Customer> subquery = cq.subquery(CustomerRequest2Customer.class);
Root<CustomerRequest2Customer> subRoot = subquery.from(CustomerRequest2Customer.class);
subquery.select(subRoot);
Predicate subCustomerMatch = cb.equal(subRoot.get("customer").get("id"), targetCustomerId);
Predicate subRequestMatch = cb.equal(subRoot.get("customerRequest"), root);
subquery.where(cb.and(subCustomerMatch, subRequestMatch));

cq.where(cb.exists(subquery), cb.equal(root.get("priority"), 1));
return entityManager.createQuery(cq).getResultList();

内容的提问来源于stack exchange，提问作者julienduchow