Case insensitive Query with Spring CrudRepository

Question

With Spring CrudRepository Query; I want to select "DeviceType" entities with it's "name" property. But following query select the entitles on case sensitive manner. How I make it case insensitive way. Thanks.

public interface DeviceTypeRepository extends CrudRepository<DeviceType, Integer>, JpaSpecificationExecutor<DeviceType> {

    public Iterable<DeviceType> findByNameContaining(String name);

}  

Answer 1

Exactly as @Peter mentioned in the comment, just add IgnoreCase:

public interface DeviceTypeRepository 
    extends CrudRepository<DeviceType, Integer>, JpaSpecificationExecutor<DeviceType> {

    public Iterable<DeviceType> findByNameContainingIgnoreCase(String name);
}  

See documentation for a list of all supported keywords inside method names.

Answer 2

The following Spring data mongo query works for me. I would prefer to use List instead of Iterator

public interface DeviceTypeRepository extends CrudRepository<DeviceType,Integer>, JpaSpecificationExecutor<DeviceType> {
    List<DeviceType> findByNameIgnoreCase(String name);
} 

Answer 3

In my case adding IgnoreCase did not work at all.

I found that it is possible to provide options for the regular expression ,as well:

@Query(value = "{'title': {$regex : ?0, $options: 'i'}}")
Foo findByTitleRegex(String regexString);

The i option makes the query case-insensitive.

Answer 4

For those who uses custom JPA query Upper keyword and toUpperCase helps. The following code works for me

 return  entityManager.createQuery("select q from "table " q  where upper(q.applicant)=:applicant")
    .setParameter("applicant",applicant.toUpperCase().trim()).getSingleResult();

Answer 5

In my case adding IgnoreCase work like this. i would prefer to use list instead of iterator

public List<ContactEntity> findByNameIgnoreCaseContainingAndUserEntity(String name, UserEntity userEntity);

Answer 6

While, I think, answers already provided bring some bits of useful information, I also think that they are lacking.

Spring Data JPA's query building/generation mechanism parses the names of methods that are explicitly (by developer) declared in the custom repository interfaces (which extend CrudRepository<T, ID> or any of its subtypes) and based on those names, it generates native queries for the corresponding backing datastore/database.

Let's say, we have an managed type (i.e. @Entity) Person, as follows:

class Person {
    @Id
    private Integer id;
    private String firstname;
    private String lastname;
    private Integer age;
    
    //getters, setters, constructor, etc.
}

and a corresponding repository, that works with this entity:

interface PersonRepository<Person, Integer> {
}

In order to instruct Spring Data JPA to ignore the case of values provided as arguments to the user-declared repository methods, you can use:

1. IgnoreCase - for ignoring case-sensitivity of the specific field; or
2. AllIgnoreCase - for ignoring case-sensitivity of all the fields.

Note, that while you should be placing IgnoreCase right after the field you want to ignore case-sensitivity for, you can place AllIgnoreCase in almost any place of a method name that you declare.

Ignore case-sensitivity for the specific fields/columns:

Ignores the case of firstname and lastname values:

List<Person> findByFirstnameIgnoreCaseAndLastnameIgnoreCase(String firstname, String lastname);

Ignores the case of lastname value:

List<Person> findByFirstnameAndLastnameIgnoreCase(String firstname, String lastname);

Ignores the case of firstname value:

List<Person> findByFirstnameIgnoreCaseAndLastname(String firstname, String lastname);

Ignore case-sensitivity for all the fields/columns:

Placing AllIgnoreCase right before any parameter name:

List<Person> findByAllIgnoreCaseFirstnameAndLastname(String firstname, String lastname);

or after only some parameter name:

List<Person> findByFirstnameAllIgnoreCaseAndLastname(String firstname, String lastname);

or in the end, after all the parameter names:

List<Person> findByFirstnameAndLastnameAllIgnoreCase(String firstname, String lastname);

or even in the very beginning, swapping findBy with it:

List<Person> AllIgnoreCaseFirstnameAndLastname(String firstname, String lastname);

would all result in the same behaviour - ignoring case-sensitivity for all the fields/columns when generating query.