Is it possible to select data with max value for a column using Criteria in Hibernate?

Question

Lets say I have the following mapping:

<hibernate-mapping package="mypackage">
    <class name="User" table="user">
  <id name="id" column="id">
   <generator class="native"></generator>
  </id>        
  <property name="name" />
  <property name="age" />
  </class>
</hibernate-mapping>

Is it possible to select the oldest user (that is age is maximal) using Criteria in Hibernate?

I can imagine that i could do this with 2 selects. (first select total number of users and then order the entries descending by age and select the first entry). But is it possible with a single select?

Thank you

Palo

Answer 1

Kind of. You need to order by age so that the oldest user is the first result from you query. Then you limit the result set to one and return that unique result.

User oldest = getSession().createQuery("SELECT u FROM User u ORDER BY u.age DESC")
    .setMaxResults(1).uniqueResult();

Answer 2

With the Criteria API you can do it like this:

session.createCriteria(User.class).addOrder(Order.desc("age")).setMaxResults(1)