Hibernate最后以空值排序 [英] Hibernate order by with nulls last

问题描述

SQL99标准提供了关键字NULLS LAST来声明空值值应该低于不为空。

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

解决方案

鉴于 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（）; 
} 
} 
字符串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）; 
返回super.onPrepareStatement（sql）; 
} 
 
} 
  

Hibernate used with PostgreSQL DB while ordering desc by a column puts null values higher than not null ones.

SQL99 standard offers keyword "NULLS LAST" to declare that null values should be put lower than not nulls.

Can "NULLS LAST" behaviour be achieved using Hibernate's Criteria API?

解决方案

Given that HHH-465 is 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 CustomNullsFirstInterceptor attached to the issue either globally or specifically to alter the SQL statement.

I'm posting it below for the readers (credits to 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);
    }

}

这篇关于Hibernate最后以空值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！