Hibernate order by with nulls last

user_59666 0
Viewed 36438

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?

sql-order-by null hibernate java
8 Answers

We can create Pageable object with following Sort parameter: 

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

We can prepare HQL as well:

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

For future travellers... I solved this by overriding the Hibernate dialect. I needed to add null first for asc and null last for desc by default in CriteriaQuery, which is for some reason not supported. (It's supported in legacy 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)
  }
}
user_25216280
Related