Spring Data JPA Specification
Here I will create an example on Spring Data JPA Specification Criteria Query with IN Clause. Spring Data JPA Specification Criteria Query generally used for search functionality on a page where users want to search by providing some inputs or by selecting several dropdown values. In this example I will show you how to use collection or list of input parameters into Criteria Query to fetch data from database.
Problem Scenario
Suppose you have a web page where you give your users option to search based on various input parameters. Let’s say you have input fields on the search page, which may have dropdown or input box and users want to search on this page by providing some inputs according to their choice or requirements. It is possible that users try to search the results by giving only one input field’s value or they may not want to provide any input value and click on search or submit button.
So in this situation you do not know for which field(s) you need to build a query for fetching data from database table. Therefore Criteria query comes to rescue here. Spring Data JPA Specification Criteria Query is somehow a little different from Hibernate Criteria Query API.
Here in this Spring Data JPA Specification Criteria Query with IN Clause example, you will see how to provide search results based on users input parameter.
I will not only fetch data based on a single value but I will also fetch data when users provide a collection or list of input values.
Let’s say your search page has a multi-select dropdown and obviously users will be able to select multiple values from dropdown. So in this case you have to use IN clause to fetch data from database.
Therefore you will see how to pass a list or collection of input parameters in IN clause using Spring Data JPA Specification Criteria Query IN Clause.
Recommended Reading:
Prerequisites
Spring Data JPA
JDK 8 or 12, Gradle 4 or 5.6, Spring Boot 1.5.9 or 2.1.8, Oracle 12 or MySQL 8.0.17
Java 19, Maven 3.8.5, Spring Data JPA 3.2.0, MySQL 8.1.0
MySQL table
Create tables called region, country and cse (case) in the MySQL database. The table name case conflicts in MySQL server so I have put as cse.
Also put some sample data into the tables as shown in below:
CREATE TABLE `region` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into `region`(`name`)
values ('Asia'), ('US'), ('UK');
CREATE TABLE `country` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into `country`(`name`)
values ('India'), ('Mexico'), ('Canada'), ('England'), ('Scotland');
CREATE TABLE `cse` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`region_id` int unsigned NOT NULL,
`country_id` int unsigned NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (region_id)
REFERENCES region(id),
FOREIGN KEY (country_id)
REFERENCES country(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into `cse`(`region_id`, `country_id`)
values (1, 1), (2, 3), (2, 2), (3, 4);Let’s move on to the example Spring Data JPA Specification Criteria Query with IN Clause…
Project Setup
Create a gradle or maven based Spring Boot project in your favorite IDE or tool. The name of the project is spring-data-jpa-specification-criteria-in-clause.
For spring boot 3, you can use the following pom.xml file for your project reference:
<?xml version="1.0" encoding="UTF-8"?>
<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>com.roytuts</groupId>
<artifactId>spring-data-jpa-left-right-inner-cross-join</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>19</maven.compiler.source>
<maven.compiler.target>19</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.1.5</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.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
For Spring Boot 2.1.8, use the following build.gradle script:
I have added MySQL dependency in this script to use MySQL database.
buildscript {
ext {
springBootVersion = '2.1.8.RELEASE'
}
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
apply plugin: 'java'
apply plugin: 'org.springframework.boot'
sourceCompatibility = 12
targetCompatibility = 12
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
runtime('mysql:mysql-connector-java:8.0.17')
}For Spring Boot 1.5.9, use the following build.gradle script:
I have added Oracle dependency in this script to use Oracle database.
buildscript {
ext {
springBootVersion = '1.5.9.RELEASE'
}
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
apply plugin: 'java'
apply plugin: 'org.springframework.boot'
sourceCompatibility = 1.8
targetCompatibility = 1.8
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
compile('org.springframework.boot:spring-boot-starter-web')
compile("org.springframework.boot:spring-boot-starter-data-jpa")
runtime("com.oracle.jdbc:ojdbc7:12.1.0.2")
}After updating the build file, try to build the blank project.
If you face any issue related to main class then you can create the class with main method in order to make the build successful.
Configuring Data Source
Define the datasource in application.properties file under classpath directory src/main/resources.
Please make sure to change the database credentials and connection details in the application.properties file.
We do not want to start our server on default port, so we changing the port as 9999 on which our embedded tomcat server will be started up.
In the below application.properties file I have added datasource for both Oracle as well as MySQL. So you can use any one of them according to your requirements.
#datasource for Oracle
spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver
spring.datasource.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
spring.datasource.url=jdbc:Oracle:thin:@//<host>:<port>/<service name>
spring.datasource.username=<username>
spring.datasource.password=<password>
#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none
#datasource for MySQL
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root
#show sql query
logging.level.org.hibernate.SQL=DEBUG
#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none
#server port
server.port=9999Spring Configuration
The following configuration class is not required for spring boot 3.
Create below Spring configuration class in order to create Spring DataSource and EntityManagerFactory beans.
Notice I have also let Spring know where our Entity classes and repository interfaces are.
If you are using Oracle database then you can uncomment the line that uses Oracle database type and comment the line that uses MySQL database type.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.config;
@Configuration
@EnableJpaRepositories(basePackages = "com.roytuts.spring.data.jpa.specification.criteria.in.clause.repository")
public class DatabaseConfig {
@Autowired
private Environment environment;
@Bean
public DataSource dataSource() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driverClassName"));
ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));
ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));
ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));
return ds;
}
@Bean
public EntityManagerFactory entityManagerFactory(DataSource dataSource) {
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setDatabase(Database.MYSQL);
//vendorAdapter.setDatabase(Database.ORACLE);
LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
factory.setJpaVendorAdapter(vendorAdapter);
factory.setPackagesToScan("com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity");
factory.setDataSource(dataSource);
factory.afterPropertiesSet();
return factory.getObject();
}
}Entity Classes
Let’s say you have below entity classes for corresponding tables in Oracle or MySQL database.
You have basically case, country and region tables in database. Let’s say a case is file for a country that belongs to a region.
I have removed all the getters and setters from the below entity classes. I will use these entity classes to fetch results based on search criteria.
Table – Region
This is a simple entity class that has only id and name. For example, regions may be Asia, USA, UK etc.
A region may have multiple cases. So I have used annotation @OneToMany.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity;
@Entity
@Table(name = "REGION")
public class Region implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID")
private Integer id;
@Column(name = "NAME")
private String name;
@OneToMany(cascade = CascadeType.MERGE, mappedBy = "region")
private Set<Case> cases;
//getters and setters
}Table – Country
This entity class contains id and name of the country. For example, India, Canada, Mexico, England etc.
A country may have multiple cases. So I have used annotation @OneToMany.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity;
@Entity
@Table(name = "COUNTRY")
public class Country implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID")
private Integer id;
@Column(name = "NAME")
private String name;
@OneToMany(cascade = CascadeType.MERGE, mappedBy = "country")
private Set<Case> cases;
//getters and setters
}Table – CASE
In the below entity class we have @ManyToOne relationship with Region and Country entity classes.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity;
@Entity
@Table(name = "CSE")
public class Case implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID")
private Integer id;
@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name = "REGION_ID", nullable = false)
private Region region;
@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name = "COUNTRY_ID", nullable = false)
private Country country;
//getter and setters
}Search Class
Now let’s say you want to search cases based on country or region or regions or both country and region(s).
Therefore you need to create a POJO class that you will use as input parameters for fetching data from database tables.
In the below, the class has two attributes – country and list of regionIds.
I will pass single id value to the country attribute and one or more id values to the regionIds.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.criteria;
public class CaseSearchCriteria implements Serializable {
private static final long serialVersionUID = 1L;
private String country;
private List<Integer> regionIds;
//getters and setters
}Repositories
You need to create below Spring Data JPA Repositories in order to fetch data from database tables.
I will be using Spring Data JPA Specification Criteria Query to fetch data and I want to search cases based on country or regions or both. So I have applied JpaSpecificationExecutor on Case Repository.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.repository;
public interface CaseRepo extends JpaRepository<Case, Integer>, JpaSpecificationExecutor<Case> {
}Below two repositories are optional for this example but may be required for other purpose.
public interface RegionRepo extends JpaRepository<Region, Integer> { }
public interface CountryRepo extends JpaRepository<Country, Integer> { }Spring Service Class
Create below Spring Service class in order to perform the business logic or here to fetch the from database tables.
The important part here is CaseSearchSpecificaton class that does the required things for you.
Notice how I am building Predicates using CriteriaQuery API to build our query for fetching data.
I first check whether there is any input parameter passed for fetching data otherwise I won’t fetch if no criteria is built from the input parameter(s).
The values in double quotation for building CriteriaQuery are entity attributes based on which I want to fetch data from database
For regionIds we are building CriteriaQuery IN clause because I may have multiple region ids, whereas for country I am building CriteriaQuery equal clause because I will have only single country id.
Now I build this search Specification and pass it to repository findAll() method to fetch data.
Ideally you should not return entity class object as a return type from Spring Service class method. You should always wrap to DTO or Value Object and return that object to Controller class.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.service;
@Service
public class SearchCaseService {
@Autowired
private CaseRepo caseRepo;
@Transactional
public List<Case> searchCases(final CaseSearchCriteria searchCriteria) {
List<Case> cases = caseRepo.findAll(CaseSearchSpecificaton.findByCriteria(searchCriteria));
return cases;
}
private static class CaseSearchSpecificaton {
private static Specification<Case> findByCriteria(final CaseSearchCriteria searchCriteria) {
return new Specification<Case>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Case> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<>();
if (searchCriteria.getRegionIds() != null && !searchCriteria.getRegionIds().isEmpty()) {
Join<Case, Region> regions = root.join("region");
predicates.add(regions.in(searchCriteria.getRegionIds()));
}
if (null != searchCriteria.getCountry()) {
Join<Case, Country> country = root.join("country");
predicates.add(cb.equal(country.get("id"), searchCriteria.getCountry()));
}
return cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
};
}
}
}VO Class
As I mentioned in the above step that you should not return the entity class to clients. So I will create a VO or DTO class that will be used in the below controller class though I had to use in the service class.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.vo;
public class CaseVo {
private int caseId;
private String region;
private String country;
//getters and setters
}REST Controller
Create below Spring REST Controller class in order to fetch data from service layer and make available to other client APIs.
Here is the below POST method that takes CaseSearchCriteria object as a parameter in the request body.
Now when you pass values for country or regions, then you will get the expected data (if available for respective country or regions) from database.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.rest.controller;
@RestController
public class CaseSearchController {
@Autowired
private SearchCaseService searchCaseService;
@PostMapping("/search/cases")
public ResponseEntity<List<CaseVo>> searchCases(@RequestBody CaseSearchCriteria caseSearchCriteria) {
List<Case> cases = searchCaseService.searchCases(caseSearchCriteria);
List<CaseVo> list = cases.stream().map(c -> {
CaseVo vo = new CaseVo();
vo.setCaseId(c.getId());
vo.setRegion(c.getRegion().getName());
vo.setCountry(c.getCountry().getName());
return vo;
}).collect(Collectors.toList());
return new ResponseEntity<List<CaseVo>>(list, HttpStatus.OK);
}
}Main Class
A class with at least main method is enough to run the Spring Boot application.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause;
@SpringBootApplication(scanBasePackages = "com.roytuts.spring.data.jpa.specification.criteria.in.clause")
public class SpringDataJpaSpecificationCriteriaInClauseApp {
public static void main(String[] args) {
SpringApplication.run(SpringDataJpaSpecificationCriteriaInClauseApp.class, args);
}
}Testing the Spring Data JPA Specification
Now you are done with coding…
Let’s test the application. Use similar type of request body for POST request with the REST end-point http://localhost:9999/search/cases.
{
"country":1,
"regionIds":[1,2]
}Note the above request body parameters are in JSON format. Once you hit the request you should get desired data as shown below:
[
{
"caseId": 1,
"region": "Asia",
"country": "India"
}
]You may also want to pass only country or regionIds to fetch the data.