JPA条件API:与子选择一起加入 [英] jpa criteria-api: join with subselect
本文介绍了JPA条件API:与子选择一起加入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
此查询用于检索一对多关系中的最后一条记录(请参阅
)
This query is used to retrieve last records in a one-to-many relationship (see 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条件的subselect建立此连接- api?可能吗?
My question: How can I build this join with the subselect with the jpa criteria-api? Is it possible? If not, possible with jpql?
到目前为止,我的代码是:
My code so far:
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();
推荐答案
对于JPA2.0,这样的查询无法已实现,但是我们可以通过重组查询来解决它
With JPA2.0 a query like this could not be implemented, but we can solve it by restructuring the query
SELECT p.*
FROM customer c
/* This part gets the maximum date of a customer purchase
We will replace it with a subquery in the where
INNER JOIN (
SELECT customer_id, MAX(date) MaxDate
FROM purchase
GROUP BY customer_id
) MaxDates ON c.id = MaxDates.customer_id */
/* This part crosses the maximum date of a customer with the purchase itself to obtain the information
INNER JOIN purchase p ON MaxDates.customer_id = p.customer_id
AND MaxDates.MaxDate = p.date*/
-- We make the crossing with the purchase (there will be N tickets per customer, with N being the number of purchases)
INNER JOIN purchase p on p.customer_id = c.id
-- In the where clause we add a condition so that these N entries become that of the maximum date
WHERE p.date = (
SELECT MAX(p2.date)
FROM purchase p2
WHERE p2.customer_id = c.id)
;
使用criteria-api的实现就像这样
the implementation with criteria-api would be like this
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Purchase> query = cb.createQuery(Purchase.class);
Root<Customer> root = query.from(Customer.class);
Join<Customer,Purchase> join = root.join(root.get("purchases"),JoinType.INNER);
Subquery<Date> sqMaxdate = cq.subquery();
Root<Purchase> sqRoot = sqMaxDate.from(Purchase.class);
Join<Purchase,Consumer> sqJoin = sqRoot.join(sqRoot.get("customer"),JoinType.INNER)
sqMaxDate.select(cb.max(sqRoot.get("date")));
sqMaxDate.where(cb.equal(sqJoin.get("id"),root.get("id")));
query.where(cb.equal(join.get("date"),sqMaxDate.getSelection()));
query.select(join);
TypedQuery<Purchase> typedQuery = entityManager.createQuery(query);
return typedQuery.getResultList();
这篇关于JPA条件API:与子选择一起加入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文