How to execute batch update using Spring JDBC

In this Spring JDBC tutorial, you will learn how to execute multiple SQL update statements in a batch, for improved performance over execution of each SQL statement individually. Compare to pure JDBC batch update, Spring simplifies batch update in a way that you focus only on providing SQL statements and input values.

Suppose that we have a JdbcTemplate object configured with a DataSource as follows:

DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/yourdbname");
dataSource.setUsername("root");
dataSource.setPassword("password");

JdbcTemplate template = new JdbcTemplate(dataSource);

Let’s see some code examples that executes batch updates using Spring JDBC.

1. Basic Batch Update Example using Spring JDBC

The following code example illustrates how to execute 3 SQL update statements in a batch using the JdbcTemplate class:

String sql1 = "INSERT INTO Users (email, pass, name) VALUES ('email0', 'pass0', 'name0')";
String sql2 = "UPDATE Users SET password='default' WHERE user_id < 10";
String sql3 = "DELETE FROM Users WHERE email = ''";
	
int[] updateCounts = template.batchUpdate(sql1, sql2, sql3);

As you can see, the batchUpdate() method takes an array of SQL statements as arguments, and returns an array of integer numbers indicating the number of rows affected by each query.

You can also send an array of SQL statements to be executed in a batch like this:

String[] sqlArray = {
		"INSERT INTO Users (email, pass, name) VALUES ('email0', 'pass0', 'name0')",
		"INSERT INTO Users (email, pass, name) VALUES ('email1', 'pass1', 'name1')",
		"INSERT INTO Users (email, pass, name) VALUES ('email2', 'pass2', 'name2')",
		"INSERT INTO Users (email, pass, name) VALUES ('email3', 'pass3', 'name3')",
		"INSERT INTO Users (email, pass, name) VALUES ('email4', 'pass4', 'name4')",
		"INSERT INTO Users (email, pass, name) VALUES ('email5', 'pass5', 'name5')",
		"INSERT INTO Users (email, pass, name) VALUES ('email6', 'pass6', 'name6')",
		"INSERT INTO Users (email, pass, name) VALUES ('email7', 'pass7', 'name7')",
		"INSERT INTO Users (email, pass, name) VALUES ('email8', 'pass8', 'name8')",
		"INSERT INTO Users (email, pass, name) VALUES ('email9', 'pass9', 'name9')",
};	

int[] updateCounts = template.batchUpdate(sqlArray);

2. Spring JDBC Batch Update with PreparedStatement

In case you need to execute bath update with queries containing parameters, write code as follows:

public void batchInsert(List<User> listUsers) {
	String sql = "INSERT INTO Users (email, pass, name) VALUES (?, ?, ?)";
		
	int[] updateCounts = template.batchUpdate(sql,
			new BatchPreparedStatementSetter() {
				
				@Override
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					User user = listUsers.get(i);
					ps.setString(1, user.getEmail());
					ps.setString(2, user.getPassword());
					ps.setString(3, user.getFullname());
				}
				
				@Override
				public int getBatchSize() {
					return 100;
				}
			});
		
}

In this example, the input data is a List of User objects. You need call another overload of the batchUpdate() method that requires a SQL statement and a BatchPreparedStatementSetter class that set values for the PreparedStatement used by the JdbcTemplate class.

Note that the getBatchSize() method must return a value that is equal to the number of times that the SQL statement is executed.

3. Execute Multiple Batches Update using Spring JDBC

For executing a large number of SQL update statements, it is recommended to issue multiple batches for improved performance. For example, if you need to execute 1,000 statements, execute a batch of 50 statements each time.

The following code example illustrates how to execute multiple SQL update batches using Spring JDBC:

String sql = "INSERT INTO Users (email, password, full_name) VALUES (?, ?, ?)";

List<User> batchArgs = new ArrayList<>();

for (int i = 1; i <= 1000; i++) {
	batchArgs.add(new User("email-" + i, "password-" + i, "fullname-" + i));
}

int batchSize = 50;

int[][] updateCounts = template.batchUpdate(sql, batchArgs, batchSize, 
		new ParameterizedPreparedStatementSetter<User>() {

			@Override
			public void setValues(PreparedStatement ps, User user) throws SQLException {
				ps.setString(1, user.getEmail());
				ps.setString(2, user.getPassword());
				ps.setString(3, user.getFullname());
			}
		});

Code of the User class:

public class User {
	private Integer id;
	private String email;
	private String password;
	private String fullname;
	
	public User(String email, String password, String fullname) {
		this.email = email;
		this.password = password;
		this.fullname = fullname;
	}
	
	// getters…
	// setters…
}

That’s how to execute SQL batch update using Spring JDBC. If you want to know how to integrate the code with a Spring MVC application, read this tutorial: Spring MVC with JdbcTemplate Tutorial.

NOTE: Although Spring makes it convenient to use its API for batch update, the performance is worse than using regular JDBC batch update. The following table shows the result when I tested batch update on my computer:

Related Spring and Database Tutorials:

• How to configure Spring MVC JdbcTemplate with JNDI Data Source in Tomcat
• Spring and Hibernate Integration Tutorial (XML Configuration)
• Understand Spring Data JPA with Simple Example
  
• Spring MVC + Spring Data JPA + Hibernate - CRUD Example

Other Spring Tutorials:

• Understand the core of Spring framework
• Understand Spring MVC
• Understand Spring AOP
• Spring MVC beginner tutorial with Spring Tool Suite IDE
• Spring MVC Form Handling Tutorial
• Spring MVC Form Validation Tutorial
• 14 Tips for Writing Spring MVC Controller
• Spring Web MVC Security Basic Example (XML Configuration)

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.