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