Bulk Update Using Spring JPA CriteriaUpdate

Criteria Update

The new feature bulk update operation in Criteria API was added in JPA 2.1. I will show you an example how to perform bulk update using Spring JPA CriteriaUpdate. The CriteriaUpdate interface can be used to implement bulk update operations.

Update operations performed through CriteriaUpdate interface are directly mapped to database update operations. Hence the persistent context is not synchronized with the result and optimistic lock is not available for the involved entities. Therefore, if you are using optimistic lock then you need to update the version column as part of your update statement.

Related Post:

Bulk Delete using Spring JPA CriteriaDelete

Prerequisites

Java 1.8+/19, Maven 3.8.2/3.8.5, Spring Boot & Spring Data JPA 2.5.6/3.1.3, MySQL 8.0.26/8.1.0

Project Setup

You can create a maven-based project in your favorite IDE or tool. The name of the project is spring-data-jpa-criteriaupdate.

You can use the following pom.xml file for your maven project. The following build file has the required dependencies.

For spring boot 3.1.3, you can use the following 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-criteriaupdate</artifactId>
	<version>0.0.1-SNAPSHOT</version>

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

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

For spring boot 2.5.6, you can use the following 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-criteriaupdate</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>8</maven.compiler.source>
		<maven.compiler.target>8</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</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>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Table

For this example I am going to create a table called product under roytuts database. So create a table product under database roytuts in the MySQL server with the below structure.

CREATE TABLE `product` (
	`id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
	`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
	`price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sales_count` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_date` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am also going to store sample data into the product table to test the application right away.

insert into product(id,name,price,sale_price,sales_count,sale_date) values(1, 'Desktop','30000','35000','55','02-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(2, 'Desktop','30300','37030','43','03-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(3, 'Tablet','39010','48700','145','10-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(4, 'Phone','15000','17505','251','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(5, 'Phone','18000','22080','178','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(6, 'Tablet','30500','34100','58','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(7, 'Adapter','2000','2500','68','06-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(8, 'TV','45871','55894','165','07-10-2021');

application.properties

The following datasource configuration is done in the src/main/resources/application.properties file:

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

spring.jpa.hibernate.ddl-auto=none

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type.descriptor.sql=trace

I have set the following properties so that Hibernate does not create the product table because I already have created the table in MySQL server.

spring.jpa.hibernate.ddl-auto=none

I 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=trace

Entity Class

The entity class maps the Java object to the table in database.

@Entity
@Table(name = "product")
public class Product {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;
	@Column
	private String name;
	@Column
	private double price;
	@Column
	private double salePrice;
	@Column
	private int salesCount;
	@Column
	private String saleDate;

        //Getters and Setters
}

Service Class

The service class is responsible for processing the business of the application.

I have included the EntityManager instance from the persistent context which will be used for building CriteriaUpdate query.

An example for bulk update may be, you want to give 15% hike to all employees. In this example, I am going to update the price of a product. Though this is a simple example and in your real project you may have thousands or lakhs of records for which you want to perform bulk update operations.

@Service
public class UpdateService {

	@PersistenceContext
	private EntityManager entityManager;

	@Transactional
	public void updateBulkProducts(final String productName, final double price) {
		CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

		CriteriaUpdate<Product> criteriaUpdate = criteriaBuilder.createCriteriaUpdate(Product.class);

		Root<Product> root = criteriaUpdate.from(Product.class);

		criteriaUpdate.set("price", price);
		criteriaUpdate.where(criteriaBuilder.equal(root.get("name"), productName));

		entityManager.createQuery(criteriaUpdate).executeUpdate();
	}

}

I am updating the product’s price based on product’s name.

@Transactional annotation from Spring framework is required otherwise you may get the following exception:

javax.persistence.TransactionRequiredException: Executing an update/delete query

Spring Boot Main Class

Spring Boot application needs a class with main method and @SpringBootApplication annotation that will deploy the application in embedded Tomcat server.

I don’t want to use the web feature, so I am implementing CommandLineRunner interface. I also included the base package for entity class so that Spring picks it up when required.

@SpringBootApplication
@EntityScan(basePackages = "com.roytuts.spring.data.jpa.criteriaupdate.entity")
public class CriteriaUpdateApp implements CommandLineRunner {

	@Autowired
	private UpdateService updateService;

	public static void main(String[] args) {
		SpringApplication.run(CriteriaUpdateApp.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		System.out.println("CriteriaUpdate::Bulk Update Started...");
		updateService.updateBulkProducts("Desktop", 31000.50);
		System.out.println("CriteriaUpdate::Bulk Update Done.");
	}

}

Testing the CriteriaUpdate Application

Before update operation the records you will find in the product table are given below:

Run the above main class as Java Application or Spring Boot Application. Your product Desktop’s price will be updated.

After update operation you will find the updated price for Desktop product:

You will also find the following log in the console for the SQL statement.

CriteriaUpdate::Bulk Update Started...
Hibernate: 
    update
        product 
    set
        price=31000.5 
    where
        name=?
o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Desktop]
CriteriaUpdate::Bulk Update Done.

Hope you got idea how to use CriteriaUpdate API with Spring Boot to update multiple records in database.

Source Code

Download

Roy Tutorials