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?

Forsythia

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

Ecumenicalism

i used it, but the something am not happy with is that: it need to run two queries, thanks again man – Forsythia

@AmrFaisal See my answer below on how to do this with one single query. – Cleptomania

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.

Charters

It should be used with caution, because order by with limit is generally slower then max/min sql statements. – Cabbala

Answer 3

Use

addOrder(Order.desc("id"))

and fetch just the first result :)

Cleptomania

this is bad because you bring the entire list form the database then you discard every other records. Would not be very good in the case you have a huge table! – Rhizocarpous

No, I said fetch the first, this would be something like "SELECT ... LIMIT 1" or something (sorry, haven't used hibernate in a long while now). What you're saying is fetching several, and just using the first; they're different things. – Cleptomania

Answer 4

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.

Grenade

You are great man, i did use the second option, using two queries, but i was searching if it could be done using criteria api :) – Forsythia

Answer 5

The cleaner solution would also be :

DetachedCriteria criteria = DetachedCriteria.forClass(Foo.class).setProjection(Projections.max("id"));
Foo fooObj =(Foo) criteria.getExecutableCriteria(getCurrentSession()).list().get(0);

Cabinet

Answer 6

    Date maxDateFromDB = null;
    Session session = (Session) entityManager.getDelegate();
//Register is and Entity and assume maxDateFromDB is a column.
//Status is another entity with Enum Applied.
//Code is the Parameter for One to One Relation between Register and Profile entity.
    Criteria criteria = session.createCriteria(Register.class).setProjection(Projections.max("maxDateFromDB") )
    .add(Restrictions.eq("status.id", Status.Name.APPLIED.instance().getId()));
    if(code != null && code > 0) {
        criteria.add(Restrictions.eq("profile.id", code));
    }
    List<Date> list = criteria.list();

    if(!CollectionUtils.isEmpty(list)){
        maxDateFromDB = list.get(0);
    }

Provocation

Answer 7

To do it entirely with Detached Criteria (because I like to construct the detached criteria without a session)

DetachedCriteria maxQuery = DetachedCriteria.forClass(Foo.class)
    .setProjection( Projections.max("id") );
DetachedCriteria recordQuery = DetachedCriteria.forClass(Foo.class)
    .add(Property.forName("id").eq(maxId) );

Midwifery

Answer 8

For the max() function in hibernate:

criteria.setProjection(Projections.max("e.encounterId"));

Fite

I think this only returns the encounterId instead of the record object. – Graph