java JPA 标准 API 顺序按 NULL 最后

JPA criteria API order by NULL last

提问by Yan Khonski

I use JPA criteria API to fetch records from the datebase. I have entity Recordwith field dateTimewhich can be null. I would code:

我使用 JPA 标准 API 从数据库中获取记录。我有实体记录，字段dateTime可以为空。我会编码：

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

事实证明，具有 NULL dateTimeField 的记录首先显示。我使用 Postrgres 数据库。我会回答这个问题，因为我找到了解决方案。这是一个类似的帖子。 JPA Criteria Query API 和 order by null last

回答by Yan Khonski

Here I put an answer to this task.

我在这里给出了这个任务的答案。

First, Postgres by default returns nulls first.

首先，Postgres 默认首先返回空值。

SELECT * FROM record ORDER BY date_time_field DESC;

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

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

SELECT * FROM record ORDER BY date_time_field DESC NULLS LAST;

Second, I had to change applyOrderBymethod

其次，我不得不改变applyOrderBy方法

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, CriteriaBuilderfrom hibernate-jpa-2.1.

注意，来自 hibernate-jpa-2.1 的CriteriaBuilder。

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

回答by Neil Stockton

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

JPA 规范中没有任何内容可以控制 NULLS 的处理方式（并且所有 RDBMS 都偏爱默认值）。这在 JPQL（基于字符串的查询）或 Criteria API 中都不可用。使用 JPQL，所有主要的 JPA 提供者都允许使用

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

在订购条款中。对于 Criteria，您受到 Criteria API 的限制，因此没有任何可能。我知道 DataNucleus JPA 提供了 JPA Criteria API 的自定义版本，允许规范

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

but clearly that is specific to that JPA provider.

但显然这是特定于该 JPA 提供程序的。

回答by HarishSRana

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

JPA 或 Eclipselink 中没有任何内容可以在 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

它会给你最好的答案

回答by dobrivoje

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

我将 JPA 与 eclipselink 与 Postgres 一起使用，这里是 namedquery，它按预期工作。这是摘录：

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