Hibernate 排序依据最后为空

Question

Hibernate 与 PostgreSQL DB 一起使用时，按列对 desc 进行排序时，空值会高于非空值。

SQL99 标准提供关键字“NULLS LAST”来声明空值应低于非空值。

使用 Hibernate 的 Criteria API 可以实现“NULLS LAST”行为吗？

Answer 1

鉴于 HHH-465 尚未修复，并且在不久的将来不会得到修复由于 Steve Ebersole 给出的原因，您最好的选择是全局或专门使用附加到问题的 CustomNullsFirstInterceptor 来更改 SQL 语句。

我将其发布在下面供读者参考（感谢 Emilio Dolce）：

public class CustomNullsFirstInterceptor extends EmptyInterceptor {

    private static final long serialVersionUID = -3156853534261313031L;

    private static final String ORDER_BY_TOKEN = "order by";

    public String onPrepareStatement(String sql) {

        int orderByStart = sql.toLowerCase().indexOf(ORDER_BY_TOKEN);
        if (orderByStart == -1) {
            return super.onPrepareStatement(sql);
        }
        orderByStart += ORDER_BY_TOKEN.length() + 1;
        int orderByEnd = sql.indexOf(")", orderByStart);
        if (orderByEnd == -1) {
            orderByEnd = sql.indexOf(" UNION ", orderByStart);
            if (orderByEnd == -1) {
                orderByEnd = sql.length();
            }
        }
        String orderByContent = sql.substring(orderByStart, orderByEnd);
        String[] orderByNames = orderByContent.split("\\,");
        for (int i=0; i<orderByNames.length; i++) {
            if (orderByNames[i].trim().length() > 0) {
                if (orderByNames[i].trim().toLowerCase().endsWith("desc")) {
                    orderByNames[i] += " NULLS LAST";
                } else {
                    orderByNames[i] += " NULLS FIRST";
                }
            }
        }
        orderByContent = StringUtils.join(orderByNames, ",");
        sql = sql.substring(0, orderByStart) + orderByContent + sql.substring(orderByEnd); 
        return super.onPrepareStatement(sql);
    }

}

Answer 2

您可以在 hibernate 属性中配置“nullsfirst”/“nullslast”，以便默认情况下任何标准调用都会选择它：hibernate.order_by.default_null_ordering=last（或=first< /代码>）。

有关详细信息，请参阅此休眠提交。

Answer 3

我们可以使用以下Sort参数创建Pageable对象：

JpaSort.unsafe(Sort.Direction.ASC, "ISNULL(column_name), (column_name)")

我们也可以准备HQL：

String hql = "FROM EntityName e ORDER BY e.columnName NULLS LAST";

Answer 4

这是我（Pascal Thivent）对课程的更新：

for (int i = 0; i < orderByNames.length; i++) {
    if (orderByNames[i].trim().length() > 0) {
        String orderName = orderByNames[i].trim().toLowerCase();
        if (orderName.contains("desc")) {
            orderByNames[i] = orderName.replace("desc", "desc NULLS LAST");
        } else {
            orderByNames[i] = orderName.replace("asc", "asc NULLS FIRST");
        }
    }
}

这解决了问题：

如果 sql 在 order by 后有限制/偏移，则会中断 – Sathish 2011 年 4 月 1 日 14:52

另外，这里介绍了如何在 JPA (hibernate) 中使用它：

Session session = entityManager.unwrap(Session.class);
Session nullsSortingProperlySession = null;
try {
    // perform a query guaranteeing that nulls will sort last
    nullsSortingProperlySession = session.getSessionFactory().withOptions()
        .interceptor(new GuaranteeNullsFirstInterceptor())
        .openSession();
} finally {
    // release the session, or the db connections will spiral
    try {
        if (nullsSortingProperlySession != null) {
            nullsSortingProperlySession.close();
        }
    } catch (Exception e) {
        logger.error("Error closing session", e);
    }
}

我已经在 postgres 上对此进行了测试，它修复了“空值高于我们遇到的非空问题。

Answer 5

另一种变体，如果您动态创建 SQL 并且不使用 Criteria API：

ORDER BY COALESCE(,'0') [ASC|DESC]

这适用于 varchar 或数字列。

Answer 6

对于未来的旅行者...我通过覆盖 Hibernate 方言解决了这个问题。我需要在 CriteriaQuery 中默认先为 asc 添加 null，最后为 desc 添加 null，但由于某种原因不支持。 （它在旧版 CriteriaAPI 中受支持）

package io.tolgee.dialects.postgres

import org.hibernate.NullPrecedence
import org.hibernate.dialect.PostgreSQL10Dialect

@Suppress("unused")
class CustomPostgreSQLDialect : PostgreSQL10Dialect() {

  override fun renderOrderByElement(expression: String?, collation: String?, order: String?, nulls: NullPrecedence?): String {
    if (nulls == NullPrecedence.NONE) {
      if (order == "asc") {
        return super.renderOrderByElement(expression, collation, order, NullPrecedence.FIRST)
      }
      if (order == "desc") {
        return super.renderOrderByElement(expression, collation, order, NullPrecedence.LAST)
      }
    }
    return super.renderOrderByElement(expression, collation, order, nulls)
  }
}

Answer 7

似乎有为此打开的更改请求/错误票

Answer 8

如前所述，此功能已在 Hibernate 4.2.x 和 4.3.x 版本中实现。

它可以用作例如：

Criteria criteria = ...;
criteria.addOrder( Order.desc( "name" ).nulls(NullPrecedence.FIRST) );

Hibernate v4.3 javadocs are less omissive 此处。