How to use IN Clause in JPA Repository

Last updated on October 5th, 2024

The ‘IN’ clause is a power SQL feature that allows us to specify multiple values in a ‘WHERE’ clause to a single query. We can use the ‘IN’ clause in JPA Repository through the query method with the In keyword, JPQL and Native SQL query. Creating a repository interface extends the JpaRepository interface of Spring Data JPA where we can add a query method with the In keyword and add custom methods annotated with @Query annotation to define a query with IN clause through JPQL and Native SQL. In this topic, we will learn how to use the IN clause in the JPA Repository through various methods.

Using the IN Clause in JPA Repository

Using Query Method with In keyword

To use a query method with the In keyword in our repository interface. We must add a query method to it with findBy keywords then field name suffix In keyword and add parameter[findByField_NameIn(Parameter param)].  

Example

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
public interface OrderRepository extends JpaRepository<OrderDetail, Long> {

List<OrderDetail> findByCustomerIdIn(List<Long> ids);
}

Using JPQL with IN

To use JPQL with an IN clause in our repository interface we must add a custom method in it. We need to use @Query annotation to this added method to define the IN clause query.

Example

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query("SELECT od FROM OrderDetail od WHERE od.customerId IN(:custId)")
List<OrderDetail> findOrdersByCustomerIds(List<Long> custId);
}

Using Native Query with IN

Add a custom method in the repository interface annotated with @Query annotation and use its nativeQuery attribute to define the query with the IN clause through the native query.

Example

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query(value = "SELECT * FROM order_detail WHERE customer_id IN(:custId)", nativeQuery = true)
List<OrderDetail> findOrdersWithCustomerIds(List<Long> custId);
}

Step-by-step to Guide the use of IN clause in JPA Repository

Let’s make a Spring Boot Application step-by-step guide to use the IN clause to fetch records from the database table using the JPA Repository. We will create an example to provide all orders for a given list of customer IDs to the client through the API endpoints through the methods mentioned above.  

These are the following steps:

1. Create a Spring Boot Project
2. Setup in the IDE
3. Configure H2 Database
4. Create a JPA Entity
5. Create a Repository Interface
6. Create a Service Interface
7. Implement the Service Interface
8. Create a Controller
9. Run the Spring Boot Application

1. Create a Spring Boot Project

We are creating a Spring Boot Project using the web tool Spring Initializr. By Providing details for the project and select the following Maven dependencies:

• Spring Web
• Spring Data JPA
• H2 Database
• Lombok

2. Setup in the IDE

We use Eclipse IDE to set up and configure the created Spring Boot Project. You can use other IDE to set up and configure the Spring Boot project.

Project Structure of Spring Boot

This image shows the Spring Boot project structure in Eclipse IDE.

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement methods to use IN Clause in JPA Repository.

pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.3.0</version>
		<relativePath/>
		<!-- lookup parent from repository -->
	</parent>
	<groupId>com.springjava</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

3. Configure H2 Database

We are going to configure the H2 database connection in the application.properties file.

application.properties

#H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true

4. Create a JPA Entity

Let’s create a JPA Entity class. For example, consider an OrderDetail entity and use Lombok for generating setter and getter methods, a constructor, etc.

OrderDetail.java

package com.springjava.entity;

import java.sql.Date;
import org.hibernate.annotations.CurrentTimestamp;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Data
@Entity
public class OrderDetail {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private Long customerId;
private Double orderAmt;
@CurrentTimestamp
private Date orderDate;
}

5. Create a Repository Interface

Create a repository interface for the OrderDetail JPA Entity class that interface extends the JpaRepository interface to perform persistence operations on the order_detail database table.

OrderRepository.java

package com.springjava.repository;

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.springjava.entity.OrderDetail;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
List<OrderDetail> findByCustomerIdIn(List<Long> ids);
@Query("SELECT od FROM Order od WHERE od.customerId IN: ids")
List<OrderDetail> findOrdersByCustomerIds(List<Long> ids);
@Query(value = "SELECT * FROM order_detail WHERE customer_id IN(ids)", nativeQuery = true)
List<OrderDetail> findOrdersWithCustomerIds(List<Long> ids);
}

6. Create a Service Interface

Create a Service interface OrderService with some method declaration.

OrderService.java

package com.springjava.service;

import java.util.List;
import com.springjava.entity.OrderDetail;

public interface OrderService {
void saveAll(List<OrderDetail> orderList);
List<OrderDetail> getOrdersByCustomerIds(List<Long> ids);
List<OrderDetail> getOrdersByCustomerIdsWithJPQL(List<Long> ids);
List<OrderDetail> getOrdersByCustomerIdsWithNative(List<Long> ids);
}

7. Implement the Service Interface

Implement the OrderService interface in the OrderServiceImpl class. This class is annotated with @Service annotation, where we inject OrderRepository to call all its methods.

OrderServiceImpl.java

package com.springjava.service;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.springjava.entity.OrderDetail;
import com.springjava.repository.OrderRepository;

@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderRepository orderRepo;

@Override
public void saveAll(List<OrderDetail> orderList) {
orderRepo.saveAll(orderList);
}

@Override
public List<OrderDetail> getOrdersByCustomerIds(List<Long> ids) {
return orderRepo.findByCustomerIdIn(ids);
}

@Override
public List<OrderDetail> getOrdersByCustomerIdsWithJPQL(List<Long> ids) {
return orderRepo.findOrdersByCustomerIds(ids);
}

@Override
public List<OrderDetail> getOrdersByCustomerIdsWithNative(List<Long> ids) {
return orderRepo.findOrdersWithCustomerIds(ids);
      }
}

8. Create a Controller

Create a controller class OrderController. This is annotated with @RestController to make this class a RestController.

OrderController.java

package com.springjava.controller;

import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
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.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.springjava.entity.OrderDetail;
import com.springjava.service.OrderService;

@RestController
@RequestMapping("/api/order")
public class OrderController {
@Autowired
private OrderService orderService;

@PostMapping("/save-all")
public ResponseEntity<?> save(@RequestBody List<OrderDetail> orderList) {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
orderService.saveAll(orderList);
respOrder.put("status", 1);
respOrder.put("message", "Record is Saved Successfully!");
return new ResponseEntity<>(respOrder, HttpStatus.CREATED);
}

@GetMapping("/in-with-query-method")
public ResponseEntity<?> getOrdersByCustIds() {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
List < Long > ids = Arrays.asList(1L, 2L, 3L);
List<OrderDetail> orderList= orderService.getOrdersByCustomerIds(ids);
if (!orderList.isEmpty()) {
respOrder.put("status", 1);
respOrder.put("data", orderList);
return new ResponseEntity<>(respOrder, HttpStatus.OK);
} else {
respOrder.clear();
respOrder.put("status", 0);
respOrder.put("message", "Data is not found");
return new ResponseEntity<>(respOrder, HttpStatus.NOT_FOUND);
   }
}

@GetMapping("/in-with-jpql")
public ResponseEntity<?> getOrdersByCustIdsWithJPQL() {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
List < Long > ids = Arrays.asList(1L, 2L, 3L);
List<OrderDetail> orderList= orderService.getOrdersByCustomerIdsWithJPQL(ids);
if (!orderList.isEmpty()) {
respOrder.put("status", 1);
respOrder.put("data", orderList);
return new ResponseEntity<>(respOrder, HttpStatus.OK);
} else {
respOrder.clear();
respOrder.put("status", 0);
respOrder.put("message", "Data is not found");
return new ResponseEntity<>(respOrder, HttpStatus.NOT_FOUND);
   }
}

@GetMapping("/in-with-native")
public ResponseEntity<?> getOrdersByCustIdsWithNative() {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
List < Long > ids = Arrays.asList(1L, 2L, 3L);
List<OrderDetail> orderList= orderService.getOrdersByCustomerIdsWithNative(ids);
if (!orderList.isEmpty()) {
respOrder.put("status", 1);
respOrder.put("data", orderList);
return new ResponseEntity<>(respOrder, HttpStatus.OK);
} else {
respOrder.clear();
respOrder.put("status", 0);
respOrder.put("message", "Data is not found");
return new ResponseEntity<>(respOrder, HttpStatus.NOT_FOUND);
   }
}
}

9. Run the Spring Boot Application

Right-click this Spring Boot application on the DemoApplication.java, then click Run As and select Java Application.

H2 Database Console

Browse this URL “http://localhost:8080/h2-console”  on the browser to check H2 database.

JSON Array

We are creating a sample JSON Array to test the API http://localhost:8080/api/order/save-all.

[
  {
    "customerId": 1,
    "orderAmt": 700
  },
  {
    "customerId": 2,
    "orderAmt": 900
  },
  {
    "customerId": 3,
    "orderAmt": 800
  }
]

Test the APIs on the Postman Tool

POST: http://localhost:8080/api/order/save-all

GET: http://localhost:8080/api/order/in-with-query-method

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select od1_0.id,od1_0.customer_id,od1_0.order_amt,od1_0.order_date from order_detail od1_0 where od1_0.customer_id in (?,?,?)

GET: http://localhost:8080/api/order/in-with-jpql

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select od1_0.id,od1_0.customer_id,od1_0.order_amt,od1_0.order_date from order_detail od1_0 where od1_0.customer_id in (?,?,?)

GET: http://localhost:8080/api/order/in-with-native

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: SELECT * FROM order_detail WHERE customer_id IN(?,?,?)

Conclusion

In clause is used in the JPA Repository through query method with In keyword, JPQL and Native SQL query.