Sorting with Hibernate

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<String>. 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.