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.
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?
Don’t want to read? You can watch it here!
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
Get this Hibernate Tip as a printable PDF!Join the free Thoughts on Java Library to get access to lots of member-only content, like a printable PDF for this post, lots of cheat sheets and 2 ebooks about Hibernate.
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
Hibernate Tips Book
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 as a paperback, ebook or PDF.