Introduction
In this example you will see how to work with IN clause with WHERE condition in SQL statement in Spring Data JPA. I will use MySQL server for this example. I will fetch selected rows or multiple rows selection as well as I will delete selected rows or multiple rows deletion from the table using IN operator with WHERE clause. I will use Spring transaction because I want to rollback our changes from database if any issue occurs during row deletion.
The IN operator allows us to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. You can specify either IN or NOT IN with your SQL query statement to fetch data from database table.
I will build the project using gradle as well as maven build tools.
Prerequisites
Java 1.8+ (11 – 16), Gradle 6.1.1, Maven 3.6.3 – 3.8.2, MySQL 8.0.17 – 8.0.26, Spring Boot 2.2.6 – 2.5.6
MySQL Table
You may create a table called product under roytuts database in MySQL server. It’s not mandatory that you need to create table in MySQL server manually. You can also create table through entity class and here I am going to show you how to create table through entity class and load data from SQL script.
You may also want to dump some data to get you started with the testing once application coding is over.
CREATE TABLE IF NOT EXISTS `product` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`code` varchar(255) NOT NULL,
`price` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `product` (`id`, `name`, `code`, `price`) VALUES
(1, 'American Tourist', 'AMTR01', 12000),
(2, 'EXP Portable Hard Drive', 'USB02', 5000),
(3, 'Shoes', 'SH03', 1000),
(4, 'XP 1155 Intel Core Laptop', 'LPN4', 80000),
(5, 'FinePix Pro2 3D Camera', '3DCAM01', 150000),
(6, 'Simple Mobile', 'MB06', 3000),
(7, 'Luxury Ultra thin Wrist Watch', 'WristWear03', 3000),
(8, 'Headphone', 'HD08', 400),
(9, 'Test 1', 'test1', 10),
(10, 'Test 2', 'test2', 11),
(11, 'Test 3', 'test3', 12);Project Setup
You can create either gradle based or maven based project in Eclipse. The name of the project is spring-data-jpa-in-clause-where-table-multiple-rows-selection-deletion.
If you are creating gradle based project then use below build.gradle script:
buildscript {
ext {
springBootVersion = '2.2.6.RELEASE'
}
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
plugins {
id 'java-library'
id 'org.springframework.boot' version "${springBootVersion}"
}
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}")
implementation('mysql:mysql-connector-java:8.0.17')
//required for jdk 9 or above
runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}If you are creating maven based project then you can use below pom.xml file:
<?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-in-clause-where-table-multiple-rows-selection-deletion</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>11 - 16</maven.compiler.source>
<maven.compiler.target>11 - 16</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.6</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>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--required only if jdk 9 or higher version is used -->
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>Configure Database
I will put database settings into classpath file application.properties under src/main/resources directory.
I am running the server on default port 8080, if you want to run on different port then you can use server.port=9999, where 9999 is the port on which you want to run Tomcat server.
#Spring Datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root
#ignore null fields in json
spring.jackson.default-property-inclusion=NON_NULL
#SQL related
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type.descriptor.sql=trace
#schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=create
#load initial data from data.sql
spring.datasource.initialization-mode=always
spring.jpa.defer-datasource-initialization=true
I am including only non-null fields in JSON response from REST APIs.
I have set the following properties so that Hibernate create the product table in MySQL server. If you want that your entity class should not create table in database then change the value from create to none.
spring.jpa.hibernate.ddl-auto=createI am logging SQL statement and formatting them for better reading in the console using the following key/value pairs:
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type.descriptor.sql=traceThe following properties are required to load initial data from the SQL script. The first property needs to load data from SQL file. The secod property needs to defer the loading process, i.e., once the table is created then only load data.
spring.datasource.initialization-mode=always
spring.jpa.defer-datasource-initialization=trueThe following data.sql file is kept under class path folder src/main/resources.
INSERT INTO `product` (`id`, `name`, `code`, `price`) VALUES
(1, 'American Tourist', 'AMTR01', 12000),
(2, 'EXP Portable Hard Drive', 'USB02', 5000),
(3, 'Shoes', 'SH03', 1000),
(4, 'XP 1155 Intel Core Laptop', 'LPN4', 80000),
(5, 'FinePix Pro2 3D Camera', '3DCAM01', 150000),
(6, 'Simple Mobile', 'MB06', 3000),
(7, 'Luxury Ultra thin Wrist Watch', 'WristWear03', 3000),
(8, 'Headphone', 'HD08', 400),
(9, 'Test 1', 'test1', 10),
(10, 'Test 2', 'test2', 11),
(11, 'Test 3', 'test3', 12);
Entity Class
In order to map database table columns with Java object attributes, I need entity class.
@Entity
@Table(name = "product")
public class Product implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "name")
private String name;
@Column(name = "code")
private String code;
@Column(name = "price")
private Double price;
//getters and setters
}Repository Interface
In order to perform database operations through Spring Data JPA I need JPARepository or CrudRepository provided by Spring Data JPA API.
These repository interfaces already provide the required basic CRUD operations out of the box.
But for our case the built-in methods do not meet the requirements. Therefore I will create our own query using @Query annotation. For update or delete I need to use also @Modifying annotation along with @Query annotation.
Notice how I have used IN operator with WHERE condition.
public interface ProductRepository extends JpaRepository<Product, Integer> {
@Query("SELECT p FROM Product p WHERE p.id IN (:ids)")
public List<Product> findProductsByIds(@Param("ids") List<Integer> ids);
@Modifying
@Query("DELETE FROM Product p WHERE p.id IN (:ids)")
public void deleteProductsByIds(@Param("ids") List<Integer> ids);
}DTO Class
As I am going to fetch or delete for selected products, so I need to pass input as JSON data. Therefore I need a POJO class for request body as an input.
public class Input {
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}Service Class
I generally perform business logic on service layer. So I will interact with repository in service class.
In the below service class I have three methods. The first first method uses the built-in method from Spring Data JPA to fetch all rows from the database table.
The second method uses custom query from repository interface to fetch selected products or rows from the database table.
Similarly the third method uses custom query to delete the selected products from the table.
@Service
public class ProductService {
@Autowired
private ProductRepository repository;
public List<Product> getProducts() {
return repository.findAll();
}
public List<Product> getProductsByIds(List<Integer> ids) {
return repository.findProductsByIds(ids);
}
@Transactional
public void deleteProducts(List<Integer> ids) {
repository.deleteProductsByIds(ids);
}
}REST Controller
The below REST controller methods expose different endpoints to the end users for consuming the service.
@RestController
@CrossOrigin(value = http://localhost:4200)
public class ProductRestController {
@Autowired
private ProductService service;
@GetMapping("/products")
public ResponseEntity<List<Product>> getAllProducts() {
return new ResponseEntity<List<Product>>(service.getProducts(), HttpStatus.OK);
}
@PostMapping("/products")
public ResponseEntity<List<Product>> getProductsByIds(@RequestBody Input input) {
return new ResponseEntity<List<Product>>(service.getProductsByIds(input.getIds()), HttpStatus.OK);
}
@PostMapping("/delete/products")
public ResponseEntity<Void> deleteProducts(@RequestBody Input input) {
service.deleteProducts(input.getIds());
return new ResponseEntity<Void>(HttpStatus.OK);
}
}Main Class
A class having main method with @SpringBootApplication annotation is enough to deploy the application into Tomcat server.
@SpringBootApplication
@EnableTransactionManagement
@EntityScan(basePackages = "com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.entity")
@EnableJpaRepositories(basePackages = "com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.repository")
public class SpringJpaTableMultiRowsSelectionDeletionApp {
public static void main(String[] args) {
SpringApplication.run(SpringJpaTableMultiRowsSelectionDeletionApp.class, args);
}
}Testing the Application
Now it’s time to verify our application whether you are getting the expected results or not.
Records before and after deletion in your database table.
The below youtube video shows how to test our application with the expected results.