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.
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.
Don’t want to read? You can watch it here!
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]
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.
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
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.