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

score 22 · Accepted Answer · edited May 23 '17 at 10:31

22

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

edited May 23 '17 at 10:31

Community

1
1

answered Jan 07 '17 at 11:37

Yan Khonski

12,225
15
76
114

I tried this and got `ERROR: COALESCE types timestamp without time zone and bytea cannot be matched`. Might have something to do with the specific way we build cb: `cb.coalesce(root.get('joinedModel').get('someDate'), MIN_DATE)` – P.Brian.Mackey May 09 '17 at 20:39
Really? It worked in my code. Please, check hibernate version, MIN_DATE. Also I used join method instead of get to navigate through entity's relations recordRoot = externalRoot.jolin("recordRelation"); Then recordRoot.get("field") – Yan Khonski May 09 '17 at 21:46
1

Discovered root cause. I used `LocalDateTime` as opposed to date. On a Postgres DB, this does not work. Switched back to `Date` as specified in the answer. – P.Brian.Mackey May 10 '17 at 00:44
I worked with LocalDateTime in Hibernate. By default, it does not support. Here is help: **hibernate-java8** http://stackoverflow.com/questions/27750026/java-8-localdatetime-and-hibernate-4. – Yan Khonski May 10 '17 at 08:21
Your answer helped me. However, I could not understand why `MIN_DATE` (which is just `new Date(0L)`) was passed in the second argument of `coalesce()`? – Mohammad Faisal Sep 07 '17 at 06:16
1

alternatively `criteriaBuilder.coalesce().value(recordRoot.get(RecordTable.FIELD_DATE_TIME))` can be used without need of `MIN_DATE` – Mohammad Faisal Sep 07 '17 at 07:30
Bumped into [this bug](https://bugs.eclipse.org/bugs/show_bug.cgi?id=341074) using Eclipselink. Had to append *criteriaBuilder.literal*. Otherwise, worked just fine! – Nico Van Belle Nov 13 '17 at 14:56
This runs into a problem if there is a distinct in the clause. See https://stackoverflow.com/questions/28328866/jpa2-criteriabuilder-order-by-order-by-expressions-must-appear-in-select-list – EFreak Feb 26 '19 at 09:50
@MohammadFaisal The second `MIN_DATE` argument to `coalesce()` is the trick to mandate `NULLS LAST` for descending order because `NULL` is replaced with the smallest possible value. Your alternative makes no sense in this context. `coalesce(NULL)` yields `NULL` and would be again `NULLS FIRST` effectively on descending order. – irieill Mar 09 '22 at 12:48

score 4 · Answer 2 · answered Jan 07 '17 at 11:43

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.

score 4 · Answer 3 · edited Aug 23 '18 at 12:49

4

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

edited Aug 23 '18 at 12:49

Kristian

1,099
1
11
24

answered Aug 23 '18 at 12:13

HarishSRana

41
2

score 1 · Answer 4 · answered Apr 12 '19 at 16:56

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

JPA criteria API order by NULL last

4 Answers4

Linked