case insensitive sort using spring data

Question

how can I do case insensitive sorting using Spring-data Pageable?

I have this method in my Repository

public interface ItemRepository extends QueryDslPredicateExecutor<Item>{
    @Query("SELECT o FROM Item o WHERE o.status = ?1")
    Page<Item> findByStatus(Item.Status status, Pageable pageable);
}

I want to be able to call that with:

itemRepository.findByStatus(Status.completed, new PageRequest(0, 10, Direction.ASC, "lower(name)")

Note the lower function in the property string. That doesn't work as Spring-data expects a property there. That will get translated to something like:

SELECT o FROM Item o WHERE o.status = ?1 ORDER BY o.lower(name)

which of course won't work as there is no 'lower' property on the object.

Is there a way to make this work?

Answer 1

Sort.Order.ignoreCase() was introduce around 8 months ago into spring-data-jpa, look here:

https://jira.springsource.org/browse/DATAJPA-296

https://github.com/kraymondksc/spring-data-jpa/commit/c3adce0bd36799d3754a5f4c61aee64982abd7e0

Once you have an appropriate version of spring-data-jpa (I think since 1.4 M1, I have 1.4.1) you can write something like this:

Sort.Order order = new Sort.Order(Sort.Direction.ASC, "name").ignoreCase();
itemRepository.findByStatus(Status.completed, new PageRequest(0, 10, new Sort(order));