Hibernate Tips: How to Handle NULL Values while Ordering Query Results in a CriteriaQuery

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please post a comment below.

Question:

You showed in a previous tip how to handle null values in a JPQL ORDER BY clause. Can I do the same in a CriteriaQuery? I can’t find a method for it.

Solution:

The handling of null values in the ORDER BY clause of a CriteriaQuery requires a workaround. Unfortunately, the NULLS FIRST and NULLS LAST clauses, which I showed in a previous Hibernate Tip, are a Hibernate-specific extension of the standard. They are not supported by JPA’s CriteriaQuery. But you can get similar results by using the coalesce method of the CriteriaBuilder.

Implement your own NULLS FIRST handling

The coalesce method expects two Expressions and returns the first one that is not null. You can use it to replace a null value with one that’s either smaller or bigger than any other values in your database. By doing that, you can simulate the behavior of a NULLS FIRST or NULLS LAST clause.

I use the coalesce method in the following example to return all Book entities in descending order of their publishingDate and return the ones without a publishingDate first.

Depending on the data you need to order, this might get complicated. In this example, it’s pretty easy.

I use the coalesce method to replace all null values with PostgreSQL’s maximum date before the database orders the result set. So, I can be sure, that there are no books with a bigger publishingDate stored in the database and the ones without a publishingDate are returned first.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Book.class);
Root root = cq.from(Book.class);

// Descending order
// replace null values with PostgreSQL's max date 
cq.orderBy(cb.desc(cb.coalesce(root.get(Book_.publishingDate), LocalDate.of(294276, 12, 31))));

List books = em.createQuery(cq).getResultList();

So, how does this code work?

I get a CriteriaBuilder instance, use it to create a CriteriaQuery that returns Book entities and define the FROM clause.

In the next step, I specify the ordering of the result set. I first call the coalesce method and provide the publishingDate attribute and a LocalDate representation of PostgreSQL’s maximum date as parameters. This replaces all null values with the maximum date. The Book_ class is part of the JPA metamodel and provides a type-safe way to reference entity attributes. The result of the coalesce method is provided as a parameter to the desc method of the CriteriaBuilder to define a descending order, which then gets set as the ORDER BY clause of the query.

If you execute that query, Hibernate generates the following SQL statement for it. This is, of course, not as efficient as a NULLS FIRST clause, but it’s your only option if you want to use the Criteria API. If you don’t need to define your query at runtime, you should prefer a native or a JPQL query with a NULLS FIRST clause instead.

14:57:45,765 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as id1_0_,
        book0_.price as price2_0_,
        book0_.publishingDate as publishi3_0_,
        book0_.title as title4_0_,
        book0_.version as version5_0_ 
    from
        Book book0_ 
    order by
        coalesce(book0_.publishingDate,
        ?) desc
14:59:21,350 INFO  [org.thoughts.on.java.model.TestCriteriaOrderNull] - Book [id=3, version=0, title=JPA for Beginners, price=19.0, publishingDate=null]
14:59:21,350 INFO  [org.thoughts.on.java.model.TestCriteriaOrderNull] - Book [id=2, version=0, title=Hibernate Tips - More than 70 solutions to common Hibernate problems, price=19.0, publishingDate=2017-04-04]
14:59:21,350 INFO  [org.thoughts.on.java.model.TestCriteriaOrderNull] - Book [id=1, version=0, title=Java Persistence with Hibernate, price=31.99, publishingDate=2015-11-05]

Learn more:

If you liked this article, you might also be interested in:

• Hibernate Tips: How to Handle NULL Values while Ordering Query Results in JPQL
• Ultimate Guide to JPQL Queries with JPA and Hibernate
• Native Queries – How to call native SQL queries with JPA