Java Hibernate order by with nulls last
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3683174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate order by with nulls last
提问by mgamer
Hibernate used with PostgreSQL DB while ordering desc by a column puts null values higher than not null ones.
与 PostgreSQL DB 一起使用的 Hibernate 在按列排序 desc 时将空值置于高于非空值的位置。
SQL99 standard offers keyword "NULLS LAST" to declare that null values should be put lower than not nulls.
SQL99 标准提供关键字“NULLS LAST”来声明空值应该低于非空值。
Can "NULLS LAST" behaviour be achieved using Hibernate's Criteria API?
可以使用 Hibernate 的 Criteria API 实现“NULLS LAST”行为吗？
采纳答案by Pascal Thivent
Given that HHH-465is not fixed and is not going to get fixed in a near future for the reasons given by Steve Ebersole, your best option would be to use the CustomNullsFirstInterceptorattached to the issue either globally or specifically to alter the SQL statement.
鉴于HHH-465尚未修复，并且由于 Steve Ebersole 给出的原因，在不久的将来也不会修复，您最好的选择CustomNullsFirstInterceptor是全局或专门使用问题的附件来更改 SQL 语句。
I'm posting it below for the readers (credits to Emilio Dolce):
我在下面为读者发布（感谢 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);
}
}
回答by Matt
Here's my update to the class by (Pascal Thivent):
这是我（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");
}
}
}
This fixes the problem:
这解决了这个问题：
This breaks if sql has limit/offset after order by – Sathish Apr 1 '11 at 14:52
如果 sql 在 order by 后有限制/偏移，这会中断 – Sathish 2011 年 4 月 1 日 14:52
Also here's how you can use this within JPA (hibernate):
此外，您还可以在 JPA（休眠）中使用它：
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);
}
}
I've tested this on postgres and it fixes the 'nulls are higher than non-nulls' issue that we were having.
我已经在 postgres 上对此进行了测试，它解决了我们遇到的“空值高于非空值”问题。
回答by Gennady Nikolaev
Another variant, if you create SQL on the fly and don't use Criteria API:
另一个变体，如果您动态创建 SQL 并且不使用 Criteria API：
ORDER BY COALESCE(,'0') [ASC|DESC]
按合并排序(,'0') [ASC|DESC]
This works either for varchar or numeric columns.
这适用于 varchar 或数字列。
回答by José Andias
This feature has been implemented during Hibernate 4.2.x and 4.3.x releases as previously mentioned.
如前所述，此功能已在 Hibernate 4.2.x 和 4.3.x 版本期间实现。
It can be used as for example:
它可以用作例如：
Criteria criteria = ...;
criteria.addOrder( Order.desc( "name" ).nulls(NullPrecedence.FIRST) );
Hibernate v4.3 javadocs are less omissive here.
Hibernate v4.3 javadocs 在这里不那么疏忽。
回答by uwolfer
You can configure "nulls first" / "nulls last" in hibernate properties so it will be picked up by any criteria call by default: hibernate.order_by.default_null_ordering=last(or =first).
您可以在休眠属性中配置“先为空”/“最后为空”，以便默认情况下任何标准调用都会选择它：（hibernate.order_by.default_null_ordering=last或=first）。
See this hibernate commitfor details.
有关详细信息，请参阅此休眠提交。
