Get record with max id, using Hibernate Criteria

Question

Using Hibernate's Criteria API, I want to select the record within a table with the maximum value for a given column.

I tried to use Projections, creating an alias for max(colunName), then using it in restrictions.eq(), but it keeps telling me "invalid number".

What's the correct way to do that with Hibernate?

Answer 1

You can use a DetachedCriteria to express a subquery, something like this:

DetachedCriteria maxId = DetachedCriteria.forClass(Foo.class)     .setProjection( Projections.max("id") ); session.createCriteria(Foo.class)     .add( Property.forName("id").eq(maxId) )     .list(); 

References

• Hibernate Core Reference Guide
  • 15.8. Detached queries and subqueries

Answer 2

I found that using addOrder and setMaxResults together worked for me.

Criteria c = session.createCriteria(Thingy.class); c.addOrder(Order.desc("id")); c.setMaxResults(1); return (Thingy)c.uniqueResult(); 

Using the MySQL dialect, this generates a SQL prepared statement about like this (snipping out some of the fields):

select this_.id ... from Thingy this_ order by this_.id desc limit ? 

I am not sure if this solution would be effective for dialects other than MySQL.