Hibernate order by with nulls last [英] Hibernate order by with nulls last

问题描述

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

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

SQL99 标准提供关键字NULLS LAST"来声明空值应该低于非空值.

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

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

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

推荐答案

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

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.

我将在下面发布给读者(感谢 Emilio Dolce):

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 order by with nulls last的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！