Spring JdbcTemplate.batchUpdate()

By Arvind Rai, August 08, 2022

On this page we will learn using Spring JdbcTemplate.batchUpdate() method. The batchUpdate() method issues multiple SQL using batching. The batchUpdate() accepts arguments in following ways.
1.

int[] batchUpdate(String... sql) throws DataAccessException

Issue multiple SQL updates on a single JDBC Statement using batching. If JDBC driver does not support batch updates, the method will fall back to separate updates on a single statement.
2.

int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException

Issue multiple update statements on a single PreparedStatement, using batch updates and a BatchPreparedStatementSetter to set values. If JDBC driver does not support batch updates, the method will fall back to separate updates on a single PreparedStatement.
3.

<T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize, ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException

Execute multiple batches using the supplied SQL statement with the collect of supplied arguments. Each batch should be of size indicated in 'batchSize'.
4.

int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException

Execute a batch using the supplied SQL statement with the batch of supplied arguments.
5.

int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException

Execute a batch using the supplied SQL statement with the batch of supplied arguments.

Parameters:
The sql defines SQL query used in batch operation.
The pss is the object to set parameters.
The batchArgs is the list of Object arrays containing the batch of arguments for the query.
The batchSize is the batch size.

Returns:
The method returns an array of the number of rows affected by each statement.

Throws:
If there is any problem issuing the update, the method throws DataAccessException.

Example using Spring Boot

PersonDAO.java

package com.concretepage;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.stereotype.Repository;

@Repository
public class PersonDAO {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  // batchUpdate(String... sql)
  public void addPersonsUsingBatch1(List<Person> list) {
	String sql1 = "insert into person (name, age) values ('" + list.get(0).getName() + "', " + list.get(0).getAge() + ")";
	String sql2 = "insert into person (name, age) values ('" + list.get(1).getName() + "', " + list.get(1).getAge()	+ ")";
	int[] insertedRows = jdbcTemplate.batchUpdate(sql1, sql2);
  }

  // batchUpdate(String sql, BatchPreparedStatementSetter pss)
  public void addPersonsUsingBatch2(List<Person> list) {
	String sql = "insert into person (name, age) values (?, ?)";
	int[] insertedRows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

	  @Override
	  public void setValues(PreparedStatement ps, int i) throws SQLException {
		ps.setString(1, list.get(i).getName());
		ps.setInt(2, list.get(i).getAge());
	  }

	  @Override
	  public int getBatchSize() {
		return list.size();
	  }
	});
  }

  // batchUpdate(String sql, Collection<T> batchArgs, int batchSize, ParameterizedPreparedStatementSetter<T> pss)
  public void addPersonsUsingBatch3(List<Person> list) {
	String sql = "insert into person (name, age) values (?, ?)";
	int[][] insertedRows = jdbcTemplate.batchUpdate(sql, list, 2,
		new ParameterizedPreparedStatementSetter<Person>() {
		  @Override
		  public void setValues(PreparedStatement ps, Person p) throws SQLException {
			ps.setString(1, p.getName());
			ps.setInt(2, p.getAge());
		  }
		});
  }

  // batchUpdate(String sql, List<Object[]> batchArgs)
  public void addPersonsUsingBatch4(List<Person> list) {
	String sql = "insert into person (name, age) values (?, ?)";
	Object[] ob1 = { list.get(0).getName(), list.get(0).getAge() };
	Object[] ob2 = { list.get(1).getName(), list.get(1).getAge() };
	int[] insertedRows = jdbcTemplate.batchUpdate(sql, Arrays.asList(ob1, ob2));
  }

  // batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes)
  public void addPersonsUsingBatch5(List<Person> list) {
	String sql = "insert into person (name, age) values (?, ?)";
	Object[] ob1 = { list.get(0).getName(), list.get(0).getAge() };
	Object[] ob2 = { list.get(1).getName(), list.get(1).getAge() };
	int[] argTypes = { java.sql.Types.CHAR, java.sql.Types.INTEGER };
	int[] insertedRows = jdbcTemplate.batchUpdate(sql, Arrays.asList(ob1, ob2), argTypes);
  }
}

Person.java

package com.concretepage;
public class Person {
    private int id;
	private String name;
	private int age;
	public Person(String name, int age) {
	  this.name = name;
	  this.age = age;
	}
	public int getId() {
	  return id;
	}
	public void setId(int id) {
	  this.id = id;
	}
	public String getName() {
	  return name;
	}
	public int getAge() {
	  return age;
	}
	@Override
	public String toString() {
	  return id + " - " + name + " - " + age;
	}
}

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
spring.datasource.username=root
spring.datasource.password=cp
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

MySQL Table: person

CREATE TABLE `person` (
	`id` INT(5) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`age` INT(3) NOT NULL,
	PRIMARY KEY (`id`)
)

pom.xml

<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-jdbc</artifactId>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>6.0.5</version>
	</dependency>
</dependencies>

MyApplication.java

package com.concretepage;
import java.util.Arrays;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

@SpringBootApplication
public class MyApplication {
  public static void main(String[] args) {
	ApplicationContext ctx = SpringApplication.run(MyApplication.class, args);
	PersonDAO personDAO = ctx.getBean(PersonDAO.class);
	personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 1", 21), new Person("Shiva 1", 31)));
	personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 2", 22), new Person("Shiva 2", 32)));
	personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 3", 23), new Person("Shiva 3", 33)));
	personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 4", 24), new Person("Shiva 4", 34)));
	personDAO.addPersonsUsingBatch1(Arrays.asList(new Person("Mohan 5", 25), new Person("Shiva 5", 35)));	
  }
}

Following rows are inserted in database.