Hibernate criteria query with subquery joining two columns
I have a table, "Quote", mapped in hibernate that has a composite key of an integer id and a date, and several additional columns. I'd like to write a criteria query that uses a DetachedCriteria to get the row for each id with the greatest date.
In sql, I might write a query like
SELECT * FROM Quote q1
INNER JOIN (SELECT id, max(date) as maxdate FROM Quote
GROUP BY id, date) q2
ON q1.id = q2.id AND q1.date = q2.maxdate
In hibernate, I think can create a DetachedCriteria for the "group by" subquery like this (where Quote is the class mapping the table, and "Qid" is a composite id class for the key, with properties id and date, accessed by a "qid" property of the Quote class):
DetachedCriteria maxDateQry = DetachedCriteria.forClass(Quote.class);
maxDateQry.setProjection(
Projections.projectionList()
.add(Projections.max("qid.date", "maxdate"))
.add(Projections.groupProperty("qid.id")));
However, I'm not sure how to use this in a criteria query that would be equivalent to the outer part of the sql, above. I'm looking for something along the lines of
Criteria criteria = session.createCriteria(Quote.class);
criteria.add(
Restrictions.and(
Property.forName("qid.id").eq(maxDateQry???),
Property.forName("qid.date").eq(maxDateQry???)));
List<Quote> quoteList = criteria.list();
Where the two Property.forName's above relate the outer table to the corresponding columns of the subquery. If the inner join provided only one value, I would simply give the DetachedCriteria a single Projection and pass the DetachedCriteria straight into Property.forName(...).eq(..). I'm not sure how to use the DetachedCriteria with two values (id and maxdate) in the Projection.
Had exactly the same problem and couldn't find an exact solution for a multi-column subquery match. What I did was rewrite the query so that it only needs to match the subselect on ONE column. So instead of
SELECT * FROM Quote q1
INNER JOIN (SELECT id, max(date) as maxdate FROM Quote
GROUP BY id, date) q2
ON q1.id = q2.id AND q1.date = q2.maxdate
Try
SELECT * FROM Quote q1 where g1.date =
(SELECT max(inner.date) FROM Quote inner where inner.id = g1.id
GROUP BY inner.id)
The key difference is that all bar the MAX condition of the JOIN criteria is now inside the inner SELECT.
The criteria/detached criteria for this looks like:
DetachedCriteria innerCriteria = DetachedCriteria.forClass(Quote.class, "inner")
.add(Restrictions.eqProperty("inner.id","q1.id"))
.setProjection(Projections.projectionList().add(Projections.max("inner.date")));
DetachedCriteria outerCriteria= DetachedCriteria.forClass(ClmClaim.class, "q1");
outerCriteria.add(Subqueries.propertyEq("q1.date", innerCriteria ));
The SQL produced looks like:
select
this_.<Blah> as blah2_49_0_,
this_.<Blah> as blah2_50_0_,
this_.<Blah> as blah2_51_0_,
from
Quote this_
where
this_.date = (
select
max(inner_.date) as y0_
from
Quote inner_
where
inner_.claim_number=this_.claim_number
);
You'll note that there is no 'group by' in the SQL because it's not needed. I would expect one to be there if there were more than one match condition in the subselect.
Anyway, hope it helps. It's the last thing I'm going to do before Christmas!
I have a similar use case. I'm pretty sure it can't be done with Criteria. Hibernate doesn't support joins in the from clause: https://hibernate.atlassian.net/browse/HHH-3356 (still open at time of writing).
The Christmas 2013 answer is pretty good, but unfortunately that correlated subquery is really bad in my use-case of:
But this one is fine (for MySQL 5.6.25 anyway):
SELECT * FROM Quote q1
WHERE (q1.id, q1.date) IN
(
SELECT id, max(date)
FROM Quote
GROUP BY id, date
)
I came here looking for an answer to
DetachedCriteria maxDateQry = DetachedCriteria.forClass(Quote.class);
maxDateQry.setProjection(
Projections.projectionList()
.add(Projections.groupProperty("qid.id"))
.add(Projections.max("qid.date", "maxdate"))
);
Criteria criteria = session.createCriteria(Quote.class);
Object environmentIdAndStartedDateProperty = "(environmentId, startedDate)" // but not this, some sort of magic
criteria.add(
Subqueries.in(environmentIdAndStartedDateProperty, maxDateQry));
List<Quote> quoteList = criteria.list();
But it looks like no such magic exists. I had to give up and use hql , which is probably for the best because according to the Hibernate 4.2 documentation the hibernate criteria API is deprecated anyway: https://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/apb.html
链接地址: http://www.djcxy.com/p/37058.html上一篇: 休眠JPA父