Implementing NULLS LAST with JPA CriteriaBuilder API

Question

Spring/Hibernate/MySQL/JPA here. I have the following code:

public void setOrdering(
    SearchRequest searchRequest,
    CriteriaQuery query,
    CriteriaBuilder builder,
    Root<? extends MyEntity> root) {

  String sortParam = "reportedOn";

  Expression expression = builder.selectCase()
    .when(builder.isNull(root.get(sortParam)), root.get(sortParam))
    .otherwise(root.get(sortParam));

  Order order = (searchRequest.isAscending())
    ? builder.asc(expression)
    : builder.desc(expression);

  query.orderBy(order);

}

Basically, I'm trying to implement the CriteriaBuilder/JPA equivalent of:

SELECT
  *
FROM
  mytable
WHERE
  <lots of predicates here>
ORDER BY reported_on IS NULL, reported_on <ASC/DESC>

I already have the WHERE predicates added, I'm just struggling with the query.orderBy(...).

At runtime, when searchRequest.isAscending() is false, the results come back working just fine, with the records that contain a null reported_on value ordered at the end of the results.

But if searchRequest.isAscending() is true, the NULLS LAST attempt does not appear to work at all.

score 1 · Answer 1 · answered Jun 21 '21 at 19:49

1

You're mixing up the Spring and JPA APIs, here query is from the JPA API so you need to sort using something like:

CriteriaBuilder cb = ...
Root root = ...
query.orderBy(cb.asc(root.get("reportedOn")));

answered Jun 21 '21 at 19:49

Guillaume

14,306
3
43
40

Thanks @Guillaume (+1) but how would I add a `NULLS LAST` clause in there to force records with `null` reportedOn values to always show up at the end of the results, regardless of the ASC/DESC specification? – hotmeatballsoup Jun 21 '21 at 19:53
For instance, the I'm trying to make the `CriteriaBuilder` equivalent of: `SELECT * FROM mytable WHERE ORDER BY reported_on IS NULL, reported_on `. I already have the `WHERE` predicates added, I'm just struggling with the `query.orderBy(...)`. – hotmeatballsoup Jun 21 '21 at 19:55
Instead of using a simple path in the asc(...) call you'll need to pass an expression, see this question: https://stackoverflow.com/questions/41520991/jpa-criteria-api-order-by-null-last/41521006 – Guillaume Jun 21 '21 at 20:00
Thanks again @Guillaume (+1 again). Please see my latest edits. I am now passing in an expression but it only seems to work when I use `builder.desc(...)`, not `builder.asc(...)`. Anything look "off" to you? – hotmeatballsoup Jun 21 '21 at 20:35

score 1 · Accepted Answer · answered Jun 21 '21 at 21:16

1

It does not look like JPA's CriteriaBuilder supports NULLS LAST. I actually got this working using a SQL "hack":

String sortParam = "reportedOn";

Order order = (searchRequest.isAscending())
  ? builder.desc(builder.neg(root.get(sortParam)))
  : builder.desc(root.get(sortParam));

query.orderBy(order);

Basically ORDER BY -reported_on DESC does the same thing as ORDER BY reported_on ASC but it sorts records with NULL reported_on values all the way to the bottom of the search results, which is what NULLS LAST is supposed to do.

answered Jun 21 '21 at 21:16

hotmeatballsoup

385
6
58
136

if you want to define the nulls order for all the queries and not just one query at a time you should override public `String renderOrderByElement(String expression, String collation, String order, NullPrecedence defaultNullPrecedence)` in your dbms dialect and based on the values of order and defaultNullPrecedence and based on what you want you could invoke super with either NullPrecedence.LAST or NullPrecedence.FIRST or NullPrecedence.NONE – tremendous7 Jun 21 '21 at 23:41

Implementing NULLS LAST with JPA CriteriaBuilder API

2 Answers2