I'm having trouble setting up a Criteria query which would find a set of objects where one of the columns has a maximum value. For example, a table like this
Code:
CREATE TABLE Quote (
id INTEGER NOT NULL,
quoteDate DATE NOT NULL,
value DOUBLE,
CONSTRAINT PK_Quote PRIMARY KEY(id, quoteDate)
)
Suppose I wanted the Quote for each id with the greatest quoteDate. In SQL, I might write
Code:
SELECT * FROM Quote q1
INNER JOIN
(SELECT id, max(quoteDate) as maxDate FROM Quote
GROUP BY id) q2
ON q1.id = q2.id and q1.quoteDate = q2.maxDate
Using Hibernate, I can create a Quote class for the table, and I think a DetachedCriteria for the subquery would look like this
Code:
DetachedCriteria maxDateQry = DetachedCriteria.forClass(Quote.class);
maxDateQry.setProjection(
Projections.projectionList()
.add(Projections.max("quoteDate", "maxDate"))
.add(Projections.groupProperty("id")));
However, I'm not sure how to use this DetachedCriteria in a Criteria to create an inner join on the two conditions (id's match and quoteDate == maxDate). I'm looking for something along the lines of
Code:
Criteria criteria = session.createCriteria(Quote.class);
criteria.add(
Restrictions.and(
Property.forName("id").eq(maxDateQry), // not exactly right
Property.forName("quoteDate").eq(maxDateQry) // not exactly right
));
List<Quote> latestQuotes = criteria.list;
The "Property.forName(name).eq(detachedCriteria)" construct would work with a DetachedCriteria with one output value, but it is not right for this case. I would appreciate any suggestions for rewriting this query.
Login
FAQ
Search