Spring Data Case Insensitive Search Example

In this tutorial, we will see Spring Data Case Insensitive Search Example Using Spring Boot and Oracle.

Consider we have an entity called Student.java as below.

and in the database, we have some records as below.

In the above table observe the name column.

 

In this tutorial, we are going to cover the below scenario. First, we will see in brief how to write Spring Data JPA Repository methods for different search/retrieve scenario, later we will have a complete example.

First Scenario – We want to perform search/retrieve operation on the basis of the name which can be the case insensitive(Suppose name is John then we will able to find records if we pass JOHN or JoHN or joHN etc).

public List<Student> findByNameIgnoreCase(String name);

Generated Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where upper(student0_.name)=upper(?)

Output –

 

Second Scenario (like + case insensitive) – We want to perform search/retrieve operation on the basis of the name which can be the case insensitive and contain some word(Suppose if name is JohnMichel  or johNmic or SmithJOHN then we will able to find records which contain john or John or JOHN).

public List<Student> findByNameContainingIgnoreCase(String name);

Generated Query –

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where upper(student0_.name) like upper(?)

 

Output –

We have all record which contains word john(case insensitive).

 

 

Third Scenario (like + case insensitive) – Similar to second one only difference we are going to use Named Parameter here.

@Query("select s from Student s where lower(s.name) like lower(concat('%', :studentName,'%'))")
public List<Student> findByNameLikeUsingNamedParameter(@Param("studentName") String name);

Generate Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where lower(student0_.name) like lower(‘%’||?||’%’)

Output –

 

Fourth Scenario – Similar to the last one only difference we are going to use @Query annotation.

@Query("select s from Student s where lower(s.name) like lower(concat('%', ?1,'%'))")
public List<Student> findByNameLikeUsingQueryAnnotation(String name);

Generate query –

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where lower(student0_.name) like lower(‘%’||?||’%’)

Output –

 

Sample Repository.

 

 

Let’s see Spring Data Case Insensitive Search Example From scratch.

We will have below REST API.

http://localhost:9091/student/saveall

http://localhost:9091/student/findbynameignorecase/john

http://localhost:9091/student/findbynamecontainingignorecase/john

http://localhost:9091/student/findbynamelikeusingnamedparameter/john

http://localhost:9091/student/findbynamelikeusingqueryannotation/john

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next.  Fill all details(GroupId – springdatacaseinsensitive, ArtifactId – springdatacaseinsensitive and name – springdatacaseinsensitive) and click on finish. Keep packaging as the jar.

Spring Data Case Insensitive Search Example

Modify pom.xml

 

Note – In pom.xml we have defined javac.exe path in configuration tag. You need to change accordingly i.e where you have installed JDK.

The directory structure of Spring Data JPA findById() Vs  getOne() example.

If you see any error for oracle dependency then follow these steps.

Directory structure –

 

Student.java

 

StudentController.java

 

Note – See more details about @Controller and RestController here.

 

StudentRepository.java – interface

 

StudentService.java – interface

StudentServiceImpl.java

Note – See more about @Component, @Controller, @Service and @Repository annotations here.

SpringMain.java

See more details about @ComponentScan here.

JpaConfig.java

 

See more details about @Configuration annotations here.

application.properties

Let’s run the SpringMain class(run as java application).

First, perform save all operation then test all REST API using postman.

http://localhost:9091/student/saveall

Request data.

 

That’s all about Spring Data Case Insensitive Search Example Using Spring Boot and Oracle.

You may like.

Other Spring Data JPA and Hibernate tutorials.

Spring Data JPA Docs.

Summary – We have seen how to define repository methods for case insensitive search.

 

 

 

Top