Introduction
Here we 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 giving some inputs or by selecting several dropdown values. In this example we will use 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 giving 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 we do not know which field(s) we 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, we will see how to provide search results based on users input parameter.
We will not only fetch data based on a single value but we 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 we 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
Knowledge of Spring Data JPA
JDK 8 or 12, Eclipse 4.12, Gradle 4 or 5.6, Spring Boot 1.5.9 or 2.1.8, Oracle 12 or MySQL 8.0.17
Creating 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);Example with Source Code
Let’s move on to the example Spring Data JPA Specification Criteria Query with IN Clause…
Creating Project
Create a gradle based Spring Boot project in Eclipse. The name of the project is spring-data-jpa-specification-criteria-in-clause.
Once project gets successfully created in Eclipse, delete generated classes from source and test directories and update the build.gradle script with the below content.
For Spring Boot 2.1.8
We 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
We 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 Datasource
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=9999Creating Spring Configuration Class
Create below Spring configuration class in order to create Spring DataSource and EntityManagerFactory beans.
Notice we 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;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
@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();
}
}Creating Entity Classes
Let’s say we have below entity classes for corresponding tables in Oracle or MySQL database.
We 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. We 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 we have used annotation @OneToMany.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity;
import java.io.Serializable;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@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 we have used annotation @OneToMany.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity;
import java.io.Serializable;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@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;
import java.io.Serializable;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
@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
}Creating Search Class
Now let’s say we want to search cases based on country or region or regions or both country and region(s).
Therefore we need to create a POJO class that we will use as input parameters for fetching data from database tables.
In the below class we have two attributes – country and list of regionIds.
We 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;
import java.io.Serializable;
import java.util.List;
public class CaseSearchCriteria implements Serializable {
private static final long serialVersionUID = 1L;
private String country;
private List<Integer> regionIds;
//getters and setters
}Creating Repositories
You need to create below Spring Data JPA Repositories in order to fetch data from database tables.
We will be using Spring Data JPA Specification Criteria Query to fetch data and we want to search cases based on country or regions or both. So we have applied JpaSpecificationExecutor on Case Repository.
package com.roytuts.spring.data.jpa.specification.criteria.in.clause.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity.Case;
public interface CaseRepo extends JpaRepository<Case, Integer>, JpaSpecificationExecutor<Case> {
}Below two repositories are option for our example but may be required for other purpose.
public interface RegionRepo extends JpaRepository<Region, Integer> { }
public interface CountryRepo extends JpaRepository<Country, Integer> { }Creating 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 thing for us.
Notice how we are building Predicates using CriteriaQuery API to build our query for fetching data.
We first check whether there is any input parameter passed for fetching data otherwise we 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 we want to fetch data from database
For regionIds we are building CriteriaQuery IN clause because we may have multiple region ids, whereas for country we are building CriteriaQuery equal clause because we will have only single country id.
Now we 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;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.criteria.CaseSearchCriteria;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity.Case;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity.Country;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity.Region;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.repository.CaseRepo;
@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()]));
}
};
}
}
}Creating VO Class
As I mentioned in the above step that you should not return the entity class to clients. So we will create a VO or DTO class that will be used in the below controller class though we 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
}Creating 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;
import java.util.List;
import java.util.stream.Collectors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.criteria.CaseSearchCriteria;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.entity.Case;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.service.SearchCaseService;
import com.roytuts.spring.data.jpa.specification.criteria.in.clause.vo.CaseVo;
@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);
}
}Creating 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;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@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 Application
Now we are done with coding…
Let’s test out 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": 5,
"region": "Asia",
"country": "India"
}
]You may also want to pass only country or regionIds to fetch the data.
Recommended Reading:
Source Code
Thanks for reading.