get max value record from table in hibernate

Question

How to get max value record from table in hibernate?

Answer 1

You could use a projection:

Criteria criteria = session
    .createCriteria(Person.class)
    .setProjection(Projections.max("age"));
Integer maxAge = (Integer)criteria.uniqueResult();

Answer 2

AFAIK, Projections will only retrieve a subset of the columns (or is it just one column?) you want.

If your data object is like so:

class Person {
    private String id;
    private String name;
    private int age;
    ...
}

and want the oldest person in the table, the following seems to work:

...
Person oldest = 
    (Person) session.createCriteria(Person.class)
    .addOrder(Order.desc("age"))
    .setMaxResults(1)
    .uniqueResult();
...

The Hibernate log (with show_sql, format_sql, and use_sql_comments all set to true) shows

select
    *
from
    ( /* criteria query */ select
        this_.ID as ID1_12_0_,
        this_.NAME as NAME_12_0_,
        this_.AGE as AGE_12_0_
    from
        PERSON this_
    order by
        this_.AGE desc )
where
    rownum <= ?

Which seems correct. Note that this is on Hibernate 3.3.2 with Oracle 11 XE.

Answer 3

Use the max(...) aggregate function:

select max(cat.weight) from Cat cat

Reference

• Hibernate Core Reference Guide
  • 14.7. Aggregate functions

Answer 4

You could use sub-query:

SELECT * FROM Employee WHERE age IN (SELECT MAX(age) age FROM Employee)