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?

score 38 · Accepted Answer · answered Oct 10 '10 at 14:39

38

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

answered Oct 10 '10 at 14:39

Pascal Thivent

562,542
136
1,062
1,124

i used it, but the something am not happy with is that: it need to run two queries, thanks again man – Amr Faisal Oct 11 '10 at 10:38
@AmrFaisal See my answer below on how to do this with one single query. – WhyNotHugo Jul 05 '12 at 18:56

score 25 · Answer 2 · answered Aug 08 '12 at 20:47

25

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.

answered Aug 08 '12 at 20:47

amacleod

1,450
2
15
23

2

It should be used with caution, because `order by` with `limit` is generally [slower](http://stackoverflow.com/a/426785/1251549) then `max`/`min` sql statements. – Cherry Nov 17 '15 at 03:40

score 6 · Answer 3 · answered Sep 07 '11 at 16:36

6

Use

addOrder(Order.desc("id"))

and fetch just the first result :)

answered Sep 07 '11 at 16:36

WhyNotHugo

9,423
6
62
70

2

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! – Renato Gama Apr 19 '12 at 20:45
4

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. – WhyNotHugo Apr 20 '12 at 03:13

score 4 · Answer 4 · answered Oct 10 '10 at 12:01

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.

answered Oct 10 '10 at 12:01

meriton

68,356
14
108
175

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 :) – Amr Faisal Oct 10 '10 at 13:17

score 0 · Answer 5 · answered Jun 18 '15 at 12:57

0

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);

answered Jun 18 '15 at 12:57

code chimp

359
2
8
21

score 0 · Answer 6 · answered Dec 21 '17 at 07:21

    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);
    }

score 0 · Answer 7 · answered Apr 30 '18 at 19:20

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) );

score -2 · Answer 8 · edited Sep 13 '11 at 20:55

-2

For the max() function in hibernate:

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

edited Sep 13 '11 at 20:55

Jérôme Verstrynge

57,710
92
283
453

answered Dec 01 '10 at 07:01

JEEVA

1

I think this only returns the encounterId instead of the record object. – jamesdeath123 Dec 09 '14 at 05:12

Get record with max id, using Hibernate Criteria

8 Answers8

References

Linked