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

---

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 leave a comment below.

Question:

I need to order the result of a JPQL query based on a column that contains NULL values. How can I define how NULL values shall be handled?

Solution:

The handling of NULL values while ordering your query result is harder and at the same time simpler than you might expect.

The JPA specification defines that during ordering, NULL values shall be handled in the same way as determined by the SQL standard. The standard specifies that all null values shall be returned before or after all non-null values. It’s up to the database to pick one of the two options.

That makes the handling of NULL values more or less undefined. The SQL standard fixed that by introducing NULLS FIRST and NULLS LAST clauses which enable you to define the position of NULL values.

Unfortunately, the JPA standard doesn’t support these clauses. But most JPA implementations, including Hibernate, support them anyway and there is a request to add them to the JPA specification.

Let’s take a look at 2 Hibernate examples that use these clauses.

Returning NULL values first

The following query selects all Book entities in the descending order of their publishing date and puts NULL values first.

TypedQuery q = em.createQuery("SELECT b FROM Book b ORDER BY b.publishingDate DESC NULLS FIRST", Book.class);
List books = q.getResultList();

As you can see in the following log message, Hibernate generates an SQL query using a NULLS FIRST clause.

12:06:25,080 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
        book0_.publishingDate DESC nulls first

Returning NULL values last

And you can, of course, replace the NULLS FIRST with a NULLS LAST clause to change the position of records containing NULL values.

TypedQuery q = em.createQuery("SELECT b FROM Book b ORDER BY b.publishingDate DESC NULLS LAST", Book.class);
List books = q.getResultList();

As you can see in the following log message, Hibernate now generates an SQL query using a NULLS LAST clause.

12:06:25,080 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
        book0_.publishingDate DESC nulls last

Learn more:

You can learn more about JPQL in the following articles:

• Ultimate Guide to JPQL Queries with JPA and Hibernate
• Hibernate Tips: How to use pagination with JPQL
• Hibernate Tips: How to downcast entities in JPQL queries
• Hibernate Tips: How to call a standard function in a JPQL query