Hibernate, Oracle, Spring Data JPA and NULLS FIRST

Hibernate, Oracle, Spring and NULLS FIRST

so an unusual little problem popped from a colleague the other day, they want to sort in ascending order a result set from an Oracle DB, but with null values at the top of the list.  we're using Spring Data JPA and Oracle has a specific sql statement to help

 select * from some_entity order by end_state asc nulls first

nice - but how do we do this in JPA?

so, Hibernate offers the interceptor pattern to modify the generated sql just before it goes off for execution.  we're going to use that to mark it up with our suffix of NULLS FIRST

here's the code

package de.incompleteco.spring.data.support.hibernate;

import org.hibernate.EmptyInterceptor;

public class OracleNullsFirstInteceptor extends EmptyInterceptor {

 private static final long serialVersionUID = 1L;

 @Override
 public String onPrepareStatement(String sql) {
  //check if it's our statement
  if (sql.contains("asc")) {
   sql = sql + " nulls first";//append the oracle specific code
  }//end if
  //return
  return sql;
 }
 
}

but, there's a catch with this design, we don't want NULLS FIRST appended to every statement, just when we use ASC.  so, in Spring Data JPA, we need to specify a Sort to help flag as ASC

        ...
        repository.findAll(Sort.Direction.ASC);

finally, here's the Spring config to support adding the interceptor

...
  
   
  

one last thing to note, if you want to test this in both an in-memory and against Oracle without changing the code, have a look at the H2 database, it plays nice with the NULLS FIRST appendage.