Hibernate order by with nulls last

Question

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?

Answer 1

There appears to be a change request/bug ticket open for this

Answer 2

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);
    }

}