JPA criteria API order by NULL last

Question

I use JPA criteria API to fetch records from the datebase. I have entity Record with field dateTime which can be null. I would code:

public List<Record> find(RecordFilter recordFilter, int page, int pageSize) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Record> criteriaQuery = criteriaBuilder.createQuery(Record.class);
    Root<Record> recordRoot = criteriaQuery.from(Record.class);

    /*
     * JOINS. Left Joins are used for optional fields, or fields inside of the optional fields.
     */
    Join<Record, Agency> recordAgencyJoin = recordRoot.join(RecordTable.FIELD_AGENCY);
    //Some other joins

    //This is where I had the problem. 
    applyOrderBy(criteriaQuery, criteriaBuilder, recordRoot);

    /*
     * Specify which columns to select and their order.
     * criteriaQuery.multiselect(....);
     */              
    applyMultiSelect(recordRoot, recordAgencyJoin, /*other joins*/ criteriaQuery);

    /*
     * criteriaQuery.where(somePredicate);
     */
    applyFilter(recordFilter, criteriaQuery, criteriaBuilder,
            recordRoot, recordAgencyJoin /*, other joins*/);
    TypedQuery<Record> query = entityManager.<Record>createQuery(criteriaQuery);
    RepositoryUtils.applyPagination(query, page, pageSize);
    return query.getResultList();
}


private void applyOrderBy(CriteriaBuilder criteriaBuilder, Root<Record> recordRoot, CriteriaQuery<Record> criteriaQuery) {
    //Other fields to be added to the final sort.

    Order dateTimeDescOrder = criteriaBuilder.desc(recordRoot.get(RecordTable.FIELD_DATE_TIME));
    criteriaQuery.orderBy(dateTimeDescOrder /*, other orders by*/);
}

It turns out, records with NULL dateTimeField are shown first. I use Postrgres database. I will answer this question because I found a solution. Here is a similar post. JPA Criteria Query API and order by null last

Answer 1

Here I put an answer to this task.

First, Postgres by default returns nulls first.

SELECT * FROM record ORDER BY date_time_field DESC;

https://stackoverflow.com/a/7621232/4587961

SELECT * FROM record ORDER BY date_time_field DESC NULLS LAST;

Second, I had to change applyOrderBy method

private void applyOrderBy(CriteriaBuilder criteriaBuilder, Root<Record> recordRoot, CriteriaQuery<Record> criteriaQuery) {
    //In the class code
    //private static final Date MIN_DATE = new Date(0L);
    final Date MIN_DATE = new Date(0L);

    //We treat records will NULL dateTimeField as if it was MIN_DATE.
    Order dateTimeDescOrder = criteriaBuilder.desc(
            //NULL values - last - WORKAROUND.
            criteriaBuilder.coalesce(recordRoot.get(RecordTable.FIELD_DATE_TIME), MIN_DATE));
    criteriaQuery.orderBy(dateTimeDescOrder);
}

Note, CriteriaBuilder from hibernate-jpa-2.1.

/**
 * Create an expression that returns null if all its arguments
 * evaluate to null, and the value of the first non-null argument
 * otherwise.
 *
 * @param x expression
 * @param y value
 *
 * @return coalesce expression
 */
<Y> Expression<Y> coalesce(Expression<? extends Y> x, Y y);

Answer 2

There is nothing in the JPA spec to control how NULLS are handled (and all RDBMS have their preference for the default). This is not available either in JPQL (string based querying) or Criteria API. With JPQL, all major JPA providers allow use of

NULLS [FIRST|LAST]

in the ordering clause. For Criteria you are constrained by the Criteria API so nothing is possible. I know that DataNucleus JPA provide a custom version of the JPA Criteria API that allows specification of

Order order = criteriaBuilder.asc(myExpression).nullsFirst();

but clearly that is specific to that JPA provider.

Answer 3

There is nothing in JPA or Eclipselink, that you can use case in CriteriaBuilder.

Example:

Order order1 = cBuilder.desc(cBuilder.selectCase().when(cBuilder.isNotNull(from.get('ColumnName1')), from.get('ColumnName1')).otherwise(from.get('ColumnName1')));
CQuery.orderBy(order1);

It will give you best answer

Answer 4

I use JPA with eclipselink with Postgres, and here is namedquery which works as expected. This is an excerpt :

,@NamedQuery(name = "ServerPasswords.forTheServer",
            query = "SELECT ss FROM ServerPasswords ss WHERE ss.fkIds = :IDServer AND ss.private = FALSE "
            + "ORDER BY ss.servce, ss.active DESC, ss.datefrom DESC NULLS LAST, ss.dateto NULLS LAST")

Answer 5

I would perform two sorts: First, split the list into chunks of null and non-null, then sort inside the chunks.

If you call the orderBy() method with argument type List<Order>, you can hand over subsequent search arguments.

  private void applyOrderBy(CriteriaBuilder criteriaBuilder, Root<Record> recordRoot, CriteriaQuery<Record> criteriaQuery) {
    //Other fields to be added to the final sort.

    List<Order> dateTimeDescOrder = Arrays.asList(
      // 1. split list into chunks of FIELD_DATE_TIME == null and FIELD_DATE_TIME != null, with nulls last.
      criteriaBuilder.desc(
        criteriaBuilder.selectCase().when(recordRoot.get(RecordTable.FIELD_DATE_TIME).isNull(), 0).otherwise(1)),
      // 2. subsequent sort within the chunks
      criteriaBuilder.desc(recordRoot.get(RecordTable.FIELD_DATE_TIME)));
    // order by order list
    criteriaQuery.orderBy(dateTimeDescOrder);
  }

Answer 6

In hibernate 6.2.9.Final you can specify nulls first/last in JpaOrder interface. Here is snippet:

import org.hibernate.query.sqm.NullPrecedence;
import org.hibernate.query.criteria.JpaOrder;

JpaOrder jpaOrder = (JpaOrder) criteriaBuilder.asc(myExpression);
JpaOrder jpaOrderWithNullLast = jpaOrder.nullPrecedence(NullPrecedence.LAST);