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



Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, 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:

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!


Related Articles

Responses

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.