How to use NOT IN operator in JPA Repository

by

Last updated on December 16th, 2024

 The “NOT IN” operator is a power feature of SQL that allows us to specify multiple values with the “WHERE” clause in a single query to filter the database records based on the provided values. This operator is used in a query when fetching records from the database table that excludes the list’s values. We can use the NOT IN operator in JPA Repository using a query method with the NotIn keyword, JPQL with the NOT IN operator, and Native Query with the NOT IN operator. This topic will demonstrate how to use NOT IN operator in JPA Repository using Spring Boot application in various ways. 

Use the NOT IN Operator in JPA Repository

Use NOT IN Operator in JPA Repository

These are the following ways to use NOT IN operator in JPA Repository:

1. Using Query Method with NotIn Keyword

Adding a custom query method in the repository interface that starts with the findBy keyword followed by the field name (property_name of the JPA Entity class), then adds the NotIn keyword and adds the parameter to the method [findByFieldNameNotIn(Parmater param)].

Example

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
List<OrderDetail> findByStatusNotIn(List<String> statusList);
}
  • findByStatusNotIn(List<String> statusList): It provides the list of orders whose status is not in the given list.

2. Using JPQL with NOT IN Operator

Use @Query annotation to specify the query with NOT IN operator using JPQL by adding a custom method in the repository interface.

Example

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query("SELECT o FROM OrderDetail o WHERE o.status NOT IN:statusList")
List<OrderDetail> findOrdersByStatusNotInJPQL(List<String> statusList); 
}
  • @Query: It Defines a custom query.
  • NOT IN: It excludes orders where the status is in the provided list.

3. Using Native Query with NOT IN Operator

Use @Query annotation with its attribute nativeQuery= “true” to specify the query with NOT IN operator using Native Query by adding a custom method in the repository interface.

Example

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query(value = "SELECT * FROM order_detail WHERE status NOT IN (?1)", nativeQuery = true)
List<OrderDetail> findOrdersByStatusNotInNative(List<String> statusList);
}
  • nativeQuery = true: It indicates that the query is a native SQL query.
  • NOT IN: It excludes records based on the status field values.

Use NOT IN operator with Other Conditions

We can combine the NOT IN operator with additional conditions.

Example

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
List<OrderDetail>findByStatusNotInAndPriceGreaterThan(List<String> statusList, double price);
}
  • The above query method we used NotIn, And, and GreaterThan keywords for NOT IN operator with AND and Greater Than(>) operators.

Why Use the NOT IN Operator in JPA Repository?

  • It excludes specific records based on a list of values.
  • It simplifies the exclusion logic in complex queries.
  • It reduces the need for multiple OR conditions when filtering out multiple values.

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

Let’s make a Spring Boot Application step-by-step guide to use the NOT IN operator to fetch records from the database table using the JPA Repository. We will create an example which provides a list of orders excluding status values given list to the client through the API endpoints in the ways mentioned above(NotIn keyword, JPQL and Native Query).  

These are the following steps:

  1. Create 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 Spring Boot Project

 We are creating a Spring Boot Project from 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 project structure of Spring Boot in Eclipse IDE.

Use the NOT IN Operator in JPA Repository

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement the NOT IN operator 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 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 String product;
private double price;
private String status;  // e.g., "PENDING", "COMPLETED", "CANCELLED"

}

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> {
//Find orders whose status is NOT in the provided list
List<OrderDetail> findByStatusNotIn(List<String> statusList);
@Query("SELECT o FROM OrderDetail o WHERE o.status NOT IN:statusList")
List<OrderDetail> findOrdersByStatusNotInJPQL(List<String> statusList);
@Query(value = "SELECT * FROM order_detail WHERE status NOT IN (?1)", nativeQuery = true)
List<OrderDetail> findOrdersByStatusNotInNative(List<String> statuses);
//Find orders whose status is NOT in the provided list and whose price is greater than the given price
List<OrderDetail>findByStatusNotInAndPriceGreaterThan(List<String> statusList, double price);
}

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> getOrdersByStatusNotIn(List<String> statusList);
List<OrderDetail> getOrderByStatusNotInJPQL(List<String> statusList);
List<OrderDetail> getOrderByStatusNotInNative(List<String> statusList);
List<OrderDetail> getOrderByStatusNotInAndPriceGreaterThan(List<String> statusList, double price);
}

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> getOrdersByStatusNotIn(List<String> statusList) {
return orderRepo.findByStatusNotIn(statusList);
}

@Override
public List<OrderDetail> getOrderByStatusNotInJPQL(List<String> statusList) {
return orderRepo.findOrdersByStatusNotInJPQL(statusList);
}

@Override
public List<OrderDetail> getOrderByStatusNotInNative(List<String> statusList) {
return orderRepo.findOrdersByStatusNotInNative(statusList);
}

@Override
public List<OrderDetail> getOrderByStatusNotInAndPriceGreaterThan(List<String> statusList, double price) {
return orderRepo.findByStatusNotInAndPriceGreaterThan(statusList, price);
  }
}

8. Create a Controller

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

OrderController.java

package com.springjava.controller;
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("/not-in-with-query-method")
public ResponseEntity<?> getOrdersByStatusNotIn() {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
List<OrderDetail> orderList= orderService.getOrdersByStatusNotIn(List.of("PENDING"));
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("/not-in-with-jpql")
public ResponseEntity<?> getOrdersByStatusNotInWithJPQL() {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
List<OrderDetail> orderList= orderService.getOrderByStatusNotInJPQL(List.of("CANCELLED"));
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("/not-in-with-native")
public ResponseEntity<?> getOrdersByStatusNotInWithNative() {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
List<OrderDetail> orderList= orderService.getOrderByStatusNotInNative(List.of("PENDING", "CANCELLED"));
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("/not-in-with-condition")
public ResponseEntity<?> getOrdersByStatusNotInWithPrice() {
Map<String, Object> respOrder = new LinkedHashMap<String, Object>();
List<OrderDetail> orderList= orderService.getOrderByStatusNotInAndPriceGreaterThan(List.of("PENDING"), 1200);
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

To check the H2 database console we need to browse the URL “http://localhost:[server_port]/h2-console” on the browser. 

JSON Array

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

[
    {
     "product": "Laptop",
      "price": 2000,
       "status":"PENDING"
     },
    {
    "product": "Mobile",
     "price": 1800,
      "status":"COMPLETED"
     },
     {
     "product": "Mobile",
      "price": 1600,
       "status":"CANCELLED"
    }
]

Test the APIs on the Postman Tool

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

Use the NOT IN Operator in JPA Repository

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

Use the NOT IN Operator in JPA Repository

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.price,od1_0.product,od1_0.status from order_detail od1_0 where od1_0.status not in (?)

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

Use the NOT IN Operator in JPA Repository

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.price,od1_0.product,od1_0.status from order_detail od1_0 where od1_0.status not in (?)

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

Use the NOT IN Operator in JPA Repository

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 status NOT IN (?,?)

GET: http://localhost:8080/api/order/not-in-with-condition

Use the NOT IN Operator in JPA Repository

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.price,od1_0.product,od1_0.status from order_detail od1_0 where od1_0.status not in (?) and od1_0.price>?

Conclusion

The NOTIN operator is used in the JPA Repository through the query method with the NotIn keyword, JPQL, and Native Query.

Leave a Comment