1. Overview
This article illustrates how to Sort with Hibernate, using both the Hibernate Query Language (HQL) and the Criteria API.
2. Sorting With HQL
Sorting with Hibernate’s HQL is as simple as adding the Order By clause to the HQL query string:
String hql = "FROM Foo f ORDER BY f.name";
Query<Foo> query = session.createQuery(hql, Foo.class);
After this code is executed, Hibernate will generate the following SQL query:
Hibernate: select foo0_.ID as ID1_0_, foo0_.NAME as NAME2_0_ from
FOO foo0_ order by foo0_.NAME
The default sort order direction is ascending. This is why the order condition, asc, is not included in the generated SQL query.
2.1. Using an Explicit Sorting Order
To specify the sorting order manually – you’ll need to include the order direction in the HQL query string:
String hql = "FROM Foo f ORDER BY f.name ASC";
Query<Foo> query = session.createQuery(hql, Foo.class);
In this example, setting the asc clause in the HQL was included in the generated SQL query:
Hibernate: select foo0_.ID as ID1_0_, foo0_.NAME as NAME2_0_
from FOO foo0_ order by foo0_.NAME ASC
2.2. Sorting by More Than One Attribute
Multiple attributes, together with an optional sorting order, can be added to the Order By clause in the HQL query string:
String hql = "FROM Foo f ORDER BY f.name DESC, f.id ASC";
Query<Foo> query = session.createQuery(hql, Foo.class);
The generated SQL query will change accordingly:
Hibernate: select foo0_.ID as ID1_0_, foo0_.NAME as NAME2_0_
from FOO foo0_ order by foo0_.NAME DESC, foo0_.ID ASC
2.3. Setting Sorting Precedence of Null Values
By default, when the attribute to sort by has null values, it is up to the RDMS to decide the precedence. This default treatment can be overridden by placing a NULLS FIRST or NULLS LAST clause in the HQL query string.
This simple example places any nulls at the end of the result list:
String hql = "FROM Foo f ORDER BY f.name NULLS LAST";
Query<Foo> query = session.createQuery(hql, Foo.class);
Lets see the is null then 1 else 0 clause in the generated SQL query:
Hibernate: select foo0_.ID as ID1_1_, foo0_.NAME as NAME2_1_,
foo0_.BAR_ID as BAR_ID3_1_, foo0_.idx as idx4_1_ from FOO foo0_
order by case when foo0_.NAME is null then 1 else 0 end, foo0_.NAME
2.4. Sorting One To Many Relations
Lets analyze a complex sorting case: sorting entities in a one to many relation – Bar containing a collection of Foo entities.
We’ll do this by annotating the collection with the Hibernate @OrderBy annotation; we will specify the field by which the ordering is done, as well as the direction:
@OrderBy(clause = "NAME DESC")
Set<Foo> fooList = new HashSet();
Notice that clause argument to the annotation. This is unique to Hibernate’s @OrderBy, as compared to similar @OrderBy JPA annotation. Another characteristic that differentiates this approach from its JPA equivalent is that the clause argument indicates that the sorting is done based on the NAME column of the FOO table, not on the name attribute of Foo.
Now let’s look at the actual sorting of Bars and Foos:
String hql = "FROM Bar b ORDER BY b.id";
Query<Bar> query = session.createQuery(hql, Bar.class);
The resulting SQL statement shows that the sorted Foo’s are placed in a fooList:
Hibernate: select bar0_.ID as ID1_0_, bar0_.NAME as NAME2_0_ from BAR bar0_
order by bar0_.ID Hibernate: select foolist0_.BAR_ID as BAR_ID3_0_0_,
foolist0_.ID as ID1_1_0_, foolist0_.ID as ID1_1_1_, foolist0_.NAME as
NAME2_1_1_, foolist0_.BAR_ID as BAR_ID3_1_1_, foolist0_.idx as idx4_1_1_
from FOO foolist0_ where foolist0_.BAR_ID=? order by foolist0_.NAME desc
One thing to keep in mind is that it is not possible to to sort Lists as was the case with JPA. Hibernate documentation states:
“Hibernate currently ignores @OrderBy on @ElementCollection on e.g. List
. The order of elements is as returned by the database, undefined.”
As a side-note, it would be is possible to work around this limitation by using legacy XML configuration for Hibernate, and replacing the <List..> element with a <Bag…> element.
3. Sorting With Hibernate Criteria
The Criteria Object API provides the Order class as the main API to manage sorting.
3.1. Setting the Sorting Order
The Order class has two methods to set the sorting order:
- asc(String attribute) : Sorts the query by attribute in ascending order.
- desc(String attribute) : Sorts the query by attribute in descending order.
Let’s start with a simple example – sorting by a single id attribute:
CriteriaQuery<Foo> selectQuery = session.getCriteriaBuilder().createQuery(Foo.class);
selectQuery.from(Foo.class);
Query<Foo> query = session.createQuery(selectQuery);
query.setOrder(Collections.singletonList(Order.asc(Foo.class,"id")));
Note that the argument to the asc method is case sensitive and should match the name of the attribute to sort by.
Hibernate Criteria’s Object API explicitly sets a sorting order direction and this is reflected in the SQL statement generated by the code:
Hibernate: select this_.ID as ID1_0_0_, this_.NAME as NAME2_0_0_
from FOO this_ order by this_.ID sac
3.2. Sorting by More Than One Attribute
Sorting by multiple attributes only requires adding an Order object to the Criteria instance, as in the example below:
CriteriaQuery<Foo> selectQuery = session.getCriteriaBuilder().createQuery(Foo.class);
selectQuery.from(Foo.class);
Query<Foo> query = session.createQuery(selectQuery);
query.setOrder(Collections.singletonList(Order.asc(Foo.class,"id")));
The query that is generated in SQL is:
Hibernate: select this_.ID as ID1_0_0_, this_.NAME as NAME2_0_0_ from
FOO this_ order by this_.NAME asc, this_.ID sac
3.3. Setting Sorting Precedence of Null Values
By default, when the attribute to sort by has null values, it is up to the RDMS to decide the precedence. Hibernate Criteria Object API makes it simple to change that default and place nulls at the end of an ascending ordered list:
CriteriaQuery<Foo> selectQuery = session.getCriteriaBuilder().createQuery(Foo.class);
selectQuery.from(Foo.class);
Query<Foo> query = session.createQuery(selectQuery);
List<Order<? super Foo>> orderBy = new ArrayList<>(1);
orderBy.add(Order.by(Foo.class,"name", SortDirection.ASCENDING, NullPrecedence.LAST));
query.setOrder(orderBy);
Here is the underlying SQL query – with the is null then 1 else 0 clause:
Hibernate: select this_.ID as ID1_1_1_, this_.NAME as NAME2_1_1_,
this_.BAR_ID as BAR_ID3_1_1_, this_.idx as idx4_1_1_, bar2_.ID as
ID1_0_0_, bar2_.NAME as NAME2_0_0_ from FOO order by case when
this_.NAME is null then 1 else 0 end, this_.NAME asc
Alternatively, we can also place the nulls at the beginning of a descending ordered list:
CriteriaQuery<Foo> selectQuery = session.getCriteriaBuilder().createQuery(Foo.class);
selectQuery.from(Foo.class);
Query<Foo> query = session.createQuery(selectQuery);
List<Order<? super Foo>> orderBy = new ArrayList<>(2);
orderBy.add(Order.by(Foo.class,"name", SortDirection.ASCENDING, NullPrecedence.FIRST));
query.setOrder(orderBy);
The corresponding SQL query follows – with the is null then 0 else 1 clause:
Hibernate: select this_.ID as ID1_1_1_, this_.NAME as NAME2_1_1_,
this_.BAR_ID as BAR_ID3_1_1_, this_.idx as idx4_1_1_, bar2_.ID as
ID1_0_0_, bar2_.NAME as NAME2_0_0_ from FOO order by case when
this_.NAME is null then 0 else 1 end, this_.NAME desc
Note that, if the attribute to sort by is a primitive type like an int, a PresisitenceException will thrown.
For example, if the value of f.anIntVariable is null, then the execution of the query:
String jql = "Select f from Foo as f order by f.anIntVariable desc NULLS FIRST";
Query<Foo> sortQuery = entityManager.createQuery(jql. Foo.class);
will throw:
javax.persistence.PersistenceException: org.hibernate.PropertyAccessException:
Null value was assigned to a property of primitive type setter of
com.cc.jpa.example.Foo.anIntVariable
4. Conclusion
This article explores sorting with Hibernate – using the available APIs for simple entities as well as for entities in a one-to-many relation.
The implementation of this Hibernate Sorting Tutorial can be found in the github project – this is an Eclipse based project, so it should be easy to import and run as it is.