4

I created a sort rule like this for sorting my list: 

Sort sort = new Sort(new Sort.Order(Sort.Direction.ASC,"productOrder", Sort.NullHandling.NULLS_LAST), new Sort.Order(Sort.Direction.ASC,"producedYear", Sort.NullHandling.NULLS_LAST));

with this rule I want to sort the productOrder first, and then if the productOrder is the same then the producedYear will be compared and sorted. If there are null values presented, it should be sorted at the end of the list. productOrder will have type Long and producedYear will have type Double.

My Repository interface extends the JpaRepository:

 public interface ProductRepository extends JpaRepository<Product, String> {

    List<Product> findByDisabledAndValid(int disabled, int valid, Sort sort);
}

But the sorted list I received is containing the null values always at the beginning of the list. This means the null values will be sorted first, then come the productOrder, and finally the producedYear will be sorted. It seems that the third parameter that I defined on my Sort.Order method is not working.

Does anyone have an idea why? Thank you very much

Ock
  • 1,262
  • 3
  • 18
  • 38
  • 1
    Perhaps that helps: https://stackoverflow.com/questions/37598238/jpa-are-query-hints-vendor-specific – D. Braun Mar 26 '18 at 08:28
  • I found it again: The definitions of query hints are vendor-specific. It is for EclipseLink, but I think for Spring it is just the same: http://www.eclipse.org/eclipselink/documentation/2.7/concepts/queries006.htm I think it depends on the data base if it works or not. – D. Braun Mar 26 '18 at 08:32
  • Here it works for some data bases, but for instance not for mySQL: https://stackoverflow.com/questions/29401227/how-to-define-null-handling-in-spring-data-jpa-sorts?rq=1 – D. Braun Mar 26 '18 at 08:33
  • hi @D.Braun, thank you for your hint. This means that the Sorting function go with Spring is not database provider independent? Therefore I should implement my own Sorting function to work with all database provider? Am I correct? – Ock Mar 26 '18 at 08:41
  • I found in the source code of the Sort.NullHandling, that NULLS-LAST ist just a hint for the data base: https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Sort.NullHandling.html The Sort.Direction does not look like a hint: https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Sort.Direction.html I think you have to implement your own null handling for all data bases. – D. Braun Mar 26 '18 at 09:34
  • I see, then I think I should implement my own sorting function... – Ock Mar 26 '18 at 09:48

1 Answers1

-3

Instead of using the Sort.NullHandling.NULLS_LAST integrated with Spring, I solved the above problem by creating my own Sort function, like this:

Collections.sort(resultList, Comparator
                    .comparing(Product::getProductOrder, Comparator.nullsLast(Comparator.naturalOrder()))
                    .thenComparing(Product::getProducedYear, Comparator.nullsLast(Comparator.naturalOrder())));

Hope this help if someone also faces the same problem.

Regards!

Ock
  • 1,262
  • 3
  • 18
  • 38
  • 2
    This obviously ruins the whole advantage of sorting on the database side. Imagine what would happen if we moved database operatons (i.e. filtering, sorting) to our backend services... – pzeszko Dec 03 '19 at 10:05