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.
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);
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; } }); }
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:
Number of statements | JDBC batch update | Spring Batch update |
100 | 71 ms | 1,454 ms |
1000 | 403 ms | 4,027 ms |
10,000 | 2,475 ms | 30,075 ms |
100,000 | 18,951 ms | 265,771 ms |