Spring Data JPA: case insensitive orderBy

Question

I want to sort my entities by the name attribute with ascending direction and ignoring the case.

I've created an EntityRepository extending the Repository interface of Spring Data. Then I've declared the following find method:

List<Entity> findByNameOrderByNameIgnoreCaseAsc(String name);

But I get this error "No property ignoreCase found for type".

I can't find any reference to this case in the Spring Data JPA documentation.

I'm using spring-data-jpa version 1.11.0.

Semi

Try with findByNameOrderByNameAscIgnoreCase(String name) (reference) – Indiana

Does not works: "No property ignoreCase found". – Semi

what version of spring-data-jpa you use? what does your repository class extend? you're not providing enough information in the question. – Escuage

What sense does it make to order by name ignoring case when you filter by name not ignoring case? – Inborn

Answer 1

If you want just to return all entities with sorting by name with ignore case you can use this:

repository.findAll(Sort.by(Sort.Order.asc("name").ignoreCase()));

Burial

That's nice, because any other method in your repository can receive a Sort sort parameter, and you can use the very same solution on it. Thanks! – Illsuited

If you want to define an index correctly, it is useful to know that Hibernate translates this into ORDER BY LOWER(column). – Blubberhead

Answer 2

Spring Data IgnoreCase can be only used for finding by a property, and cannot be user in combination with Order by.

However you can get you results using Sort.Order.ignoreCase() and Pages http://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Sort.Order.html#ignoreCase--

Orel

Answer 3

If anyone stumbles in this question again:

I made a repository static method you can use to alter your page request, now it requests to the repository method to ignore case for all it's sorting.

static Pageable sortIgnoreCase(Pageable pageable) {
    Pageable result = pageable;
    if(pageable.getSort().isSorted()) {
        List ordersIgnoreCase = pageable.getSort().stream()
            .map(Sort.Order::ignoreCase)
            .collect(Collectors.toList());
        result = PageRequest.of(
                result.getPageNumber(),
                result.getPageSize(),
                Sort.by(ordersIgnoreCase)
        );
    }
    return result;
}

Hope it helps someone!

Kowatch

Answer 4

Try instead creating the repository method below (notice the added sort parameter)

List<Entity> findByName(String name, Sort sort);

Then extend the PagingAndSortingRepository in this repository (with Long being whatever your entity id type is):

extends PagingAndSortingRepository<Entity, Long>

Then call the method as so:

repository.findByName("nameToFind", Sort.by(Sort.Order.asc("name").ignoreCase()));

Monro

Answer 5

Your method should be like this.

List<Entity> findByNameIgnoreCaseOrderByNameAsc(String name);

Please refer https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-creation

Waac

I want to order by name ignoring the case of the name. Your method matches the entities using the name and ignoring the case. – Semi