Get record with max id, using hibernate

Question

Hello,

i want to use hibernate to get record from db, such that it's with the max dbId, i tried to use projections, creating alias for 'max(dbId)', then using it in 'restrictions.eq', but it keeps telling me invalid number, i think this may be because it's an integer in db, and i mapped it as a string in my entity,

could any one help me with this,

Regards,

Answer 1

HQL:

from Person where person.id = (select max(id) from Person)

Untested. Your database needs to understand subselects in the where clause.

Too lazy to find out if/how such a subselect can be expressed with the criteria api. Of course, you could do two queries: First fetch the max id, then the entity with that id.

Answer 2

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