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

By Thorben Janssen

Criteria API, Query

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.

Question:

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.

Solution:

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]

Learn more:

If you liked this article, you might also be interested in:

 

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 as a paperback, ebook or PDF.

Tags

Criteria API, Query


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

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

  1. A better solution is :

    // this to keep null values at the end of ASC order
    Order nullLast = criteriaBuilder.asc(
    criteriaBuilder.selectCase().when(root.get(attrs.get(0)).isNull(), 1).otherwise(0));

    // then
    criteriaQuery
    .orderBy(
    nullLast,
    // … you other normal order <—-
    );

    thanks for the blog!

    Reply

  2. Hello, thanks for your help!
    I tried doing this but nothing changed on my sortBy.
    Basically I need every null value to be equal to -1 but it doesn’t seem to work.

    orderList.add(builder.desc(builder.coalesce(root.get(parent).get(child),-1)));

    Reply

  3. Hi Thorben,

    Thanks for your great tips! Your books and mails helps our team.

    Regarding dealing with NULL LAST, I found this hibernate property:

    hibernate.order_by.default_null_ordering= last (other values: first or none)

    Now I can sort with null values at the end.

    Reply

    1. Hi Marnix,

      that property defines a default handling for all ORDER BY clauses in all your queries. If that default is OK for you, using the property is much easier than implementing the NULL handling yourself.

      Regards,
      Thorben

      Reply

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}