Spring Data JPA IN Clause Example with WHERE Condition

Introduction

In this example we will see how to work with IN clause with WHERE condition in SQL statement in Spring Data JPA. We will use MySQL server for this example. We will fetch selected rows or multiple rows selection as well as we will delete selected rows or multiple rows deletion from the table using IN operator with WHERE clause. We will use Spring transaction because we 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.

We will build the project using gradle as well as maven build tools.

Prerequisites

Eclipse 2019-12, Java at least 1.8, Gradle 6.1.1, Maven 3.6.3, MySQL 8.0.17, Spring Boot 2.2.6

MySQL Table

We will create a table called product under roytuts database in MySQL server.

We have also dumped 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);

Create Project

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:

<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>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.6.RELEASE</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<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>
			<version>8.0.17</version>
		</dependency>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>

    <build>
        <plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Configure Database

We 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 uncomment line #server.port=9999, where 9999 is the port on which you want to run Tomcat server.

#datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root

#server.port=9999

#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none

We also need to create transaction manager in order to apply transaction. We have used @EnableTransactionManagement to support Spring transaction.

We need to let Spring container know where our repository interfaces and entity classes.

package com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.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;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.repository")
public class DbConfig {

	@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);

		LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
		factory.setJpaVendorAdapter(vendorAdapter);
		factory.setPackagesToScan("com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.entity");
		factory.setDataSource(dataSource);
		factory.afterPropertiesSet();
		return factory.getObject();
	}

}

Entity Class

In order to map database table columns with Java object attributes, we need entity class.

package com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.entity;

import java.io.Serializable;

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

@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 we 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 we will create our own query using @Query annotation. For update or delete we need to use also @Modifying annotation along with @Query annotation.

Notice how we have used IN operator with WHERE condition.

package com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.repository;

import java.util.List;

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

import com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.entity.Product;

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 we are going to fetch or delete for selected products, so we need to pass input as JSON data. Therefore we need a POJO class for request body as an input.

package com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.dto;

import java.util.List;

public class Input {

	private List<Integer> ids;

	public List<Integer> getIds() {
		return ids;
	}

	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}

}

Service Class

We generally perform business logic on service layer. So we will interact with repository in service class.

In the below service class we 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.

package com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.service;

import java.util.List;

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

import com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.entity.Product;
import com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.repository.ProductRepository;

@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.

package com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.rest.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
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.table.multiple.rows.selection.deletion.dto.Input;
import com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.entity.Product;
import com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion.service.ProductService;

@RestController
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.

package com.roytuts.spring.data.jpa.table.multiple.rows.selection.deletion;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
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 we are getting the expected results or not.

The below youtube video shows how to test our application with the expected results.

Source Code

Download

Thanks for reading.

Leave a Comment