Spring Data Case Insensitive Search Example

By
Rakesh
-

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.

package com.javatute.entity;

@Entity
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;
        
        //getter & setter

}

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.

package com.javatute.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Student;

@Repository
public interface StudentRepository extends JpaRepository<Student, Serializable> {

	public List<Student> findByNameIgnoreCase(String name);

	public List<Student> findByNameContainingIgnoreCase(String name);

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

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

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.

Modify pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>springdatacaseinsensitive</groupId>
	<artifactId>springdatacaseinsensitive</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springdatacaseinsensitive</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.3</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
		<plugins>

			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<fork>true</fork>
					<executable>C:\Program Files\Java\jdk1.8.0_131\bin\javac.exe</executable>
				</configuration>
			</plugin>


		</plugins>
	</build>
</project>

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

package com.javatute.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Student {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	String university;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

	public String getUniversity() {
		return university;
	}

	public void setUniversity(String university) {
		this.university = university;
	}

}

StudentController.java

package com.javatute.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.javatute.entity.Student;
import com.javatute.service.StudentService;

@RestController
@RequestMapping(value = "/student")
public class StudentController {

	@Autowired
	private StudentService studentService;

	@RequestMapping(value = "/saveall", method = RequestMethod.POST)
	@ResponseBody
	public List<Student> saveAll(@RequestBody List<Student> studentList) {
		List<Student> studentResponse = (List<Student>) studentService.saveAll(studentList);
		return studentResponse;
	}

	@RequestMapping(value = "/findbynameignorecase/{name}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getStudent(@PathVariable String name) {
		List<Student> studentResponse = (List<Student>) studentService.findByNameIgnoreCase(name);
		return studentResponse;
	}

	@RequestMapping(value = "/findbynamecontainingignorecase/{name}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> getListOfStudents(@PathVariable String name) {
		List<Student> studentResponse = (List<Student>) studentService.findByNameContainingIgnoreCase(name);
		return studentResponse;
	}

	@RequestMapping(value = "/findbynamelikeusingnamedparameter/{name}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByNameUsingNamedParameter(@PathVariable String name) {
		List<Student> studentResponse = (List<Student>) studentService.findByNameLikeUsingNamedParameter(name);
		return studentResponse;
	}

	@RequestMapping(value = "/findbynamelikeusingqueryannotation/{name}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByNameLikeUsingQueryAnnotation(@PathVariable String name) {
		List<Student> studentResponse = (List<Student>) studentService.findByNameLikeUsingQueryAnnotation(name);
		return studentResponse;
	}

}

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

StudentRepository.java – interface

package com.javatute.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.javatute.entity.Student;

@Repository
public interface StudentRepository extends JpaRepository<Student, Serializable> {

	public List<Student> findByNameIgnoreCase(String name);

	public List<Student> findByNameContainingIgnoreCase(String name);

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

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

StudentService.java – interface

package com.javatute.service;

import java.util.List;

import org.springframework.stereotype.Component;

import com.javatute.entity.Student;

@Component
public interface StudentService {
	public List<Student> saveAll(List<Student> student);

	public List<Student> findByNameContainingIgnoreCase(String name);

	public List<Student> findByNameIgnoreCase(String name);

	public List<Student> findByNameLikeUsingNamedParameter(String name);
	
	public List<Student> findByNameLikeUsingQueryAnnotation(String name);

}

StudentServiceImpl.java

package com.javatute.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.javatute.entity.Student;
import com.javatute.repository.StudentRepository;
import com.javatute.service.StudentService;

@Service("studentServiceImpl")
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@Transactional
	public List<Student> saveAll(List<Student> studentList) {
		List<Student> response = studentRepository.saveAll(studentList);
		return response;
	}

	@Transactional
	public List<Student> findByNameIgnoreCase(String name) {
		List<Student> studentResponse = studentRepository.findByNameIgnoreCase(name);
		return studentResponse;
	}

	@Transactional
	public List<Student> findByNameContainingIgnoreCase(String name) {
		List<Student> studentResponse = studentRepository.findByNameContainingIgnoreCase(name);
		return studentResponse;
	}

	@Transactional
	public List<Student> findByNameLikeUsingNamedParameter(String name) {
		List<Student> studentResponse = studentRepository.findByNameLikeUsingNamedParameter(name);
		return studentResponse;
	}

	@Transactional
	public List<Student> findByNameLikeUsingQueryAnnotation(String name) {
		List<Student> studentResponse = studentRepository.findByNameLikeUsingQueryAnnotation(name);
		return studentResponse;
	}

}

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

SpringMain.java

package com.javatute.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.*")
@EntityScan("com.javatute.entity")
public class SpringMain {
	public static void main(String[] args) {

		SpringApplication.run(SpringMain.class, args);
	}

}

See more details about @ComponentScan here.

JpaConfig.java

package com.javatute.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class JpaConfig {

}

See more details about @Configuration annotations here.

application.properties

# Connection url for the database
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=SYSTEM
spring.datasource.password=oracle2
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
# Show or not log for each sql query
spring.jpa.show-sql = true
 
 
spring.jpa.hibernate.ddl-auto =create
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
 
server.port = 9091

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.

[
    {
        "name": "John",
        "rollNumber": "0126CS01",
        "university":"rgtu"
    },
    {
        "name": "JohnhSmith",
        "rollNumber": "0126CS02",
        "university":"rgtu"
    },
    {
        "name": "mynameisjohnmichel",
        "rollNumber": "0126CS03",
        "university":"rgtu"
    },
    {
        "name": "JOHN",
        "rollNumber": "0126CS04",
        "university":"rgtu"
    }
]

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.

RELATED ARTICLESMORE FROM AUTHOR