这个查询用于检索一对多关系中的最后记录(参见 SQL join: selecting the last records in a one-to-many relationship)。
SELECT p.*
FROM customer c
INNER JOIN (
SELECT customer_id, MAX(date) MaxDate
FROM purchase
GROUP BY customer_id
) MaxDates ON c.id = MaxDates.customer_id
INNER JOIN purchase p ON MaxDates.customer_id = p.customer_id
AND MaxDates.MaxDate = p.date;
我的问题: 如何使用JPA Criteria API 建立包含子查询的连接?这是否可能?如果不行,那么用JPQL可以吗?
到目前为止,我的代码:
final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<Purchase> query = cb.createQuery(Purchase.class);
final Root<CustomerEntity> root = query.from(Customer.class);
// here should come the join with the sub-select
final Path<Purchase> path = root.join(Customer_.purchases);
query.select(path);
final TypedQuery<Purchase> typedQuery = entityManager.createQuery(query);
return typedQuery.getResultList();