JPA criteria API order by NULL last

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);

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.

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