How to do multiple inserts in database using spring JDBC Template batch?

Question

I need to insert thousands of records in the database at one go. I am using spring JDBC template in my application.

Below is the code I have written so far which executes all inserts at one go. So, if I ahve 10,000 users they are inserted at one go. But what I want is to execute them in batches say for example 500 records in one batch and so on.

@Override public void saveBatch(final List<Employee> employeeList) { final int batchSize = 500; getJdbcTemplate().batchUpdate(QUERY_SAVE, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Employee employee = employeeList.get(i); ps.setString(1, employee.getFirstname()); ps.setString(2, employee.getLastname()); ps.setString(3, employee.getEmployeeIdOnSourceSystem()); } @Override public int getBatchSize() { return employeeList.size(); } }); }

How do I change the above code so that instead of employeeList.size() as the batch size can we have batch size as say 500, execute them and then next 500 and so on?

Please help.

I know this question is quite old, but I have a question. Why didn't you just return 500 in the getBatchSize method? — Rahul Sharma
– Rahul Sharma, Commented Jun 24, 2016 at 14:11

adarshr · Accepted Answer · 2012-03-05 12:39:01Z

I am not sure if you can do that using JDBC template alone. Maybe you could invoke the batchUpdate method in steps, by slicing up the big list into batch-sized chunks.

Have a look here:

@Override public void saveBatch(final List<Employee> employeeList) { final int batchSize = 500; for (int j = 0; j < employeeList.size(); j += batchSize) { final List<Employee> batchList = employeeList.subList(j, j + batchSize > employeeList.size() ? employeeList.size() : j + batchSize); getJdbcTemplate().batchUpdate(QUERY_SAVE, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Employee employee = batchList.get(i); ps.setString(1, employee.getFirstname()); ps.setString(2, employee.getLastname()); ps.setString(3, employee.getEmployeeIdOnSourceSystem()); } @Override public int getBatchSize() { return batchList.size(); } }); } }

thanks for the answer but I have another question. What if there was 100 users to insert and batchSize given 500, it will insert same user's info multiple times ??
In case anyone wants to get the id's returned, you can do that via JdbcTemplate by accessing the PreparedStatement directly: stackoverflow.com/questions/7333524/…
j + batchSize > employeeList.size() ? employeeList.size() : j + batchSize can be replaced by Math.min(index + batchSize, employeeList.size())

Iulian Popescu · Accepted Answer · 2018-02-09 20:54:15Z

I know it's a bit late but you could do something similar to what @adarshr is doing, except use Google Guava Lists.partition to get the sublists.

public void saveBatch(final List<Employee> employeeList) { final int batchSize = 500; List<List<Employee>> batchLists = Lists.partition(employeeList, batchSize); for(List<Employee> batch : batchLists) { getJdbcTemplate().batchUpdate(QUERY_SAVE, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Employee employee = batch.get(i); ps.setString(1, employee.getFirstname()); ps.setString(2, employee.getLastname()); ps.setString(3, employee.getEmployeeIdOnSourceSystem()); } @Override public int getBatchSize() { return batch.size(); } }); } }

Navrattan Yadav · Accepted Answer · 2018-09-17 04:49:09Z

Spring provides Batch operations with multiple batches. In the example below, the batch size is 100.

 public class JdbcActorDao implements ActorDao { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public int[][] batchUpdate(final Collection<Actor> actors) { int[][] updateCounts = jdbcTemplate.batchUpdate( "update t_actor set first_name = ?, last_name = ? where id = ?", actors, 100, new ParameterizedPreparedStatementSetter<Actor>() { public void setValues(PreparedStatement ps, Actor argument) throws SQLException { ps.setString(1, argument.getFirstName()); ps.setString(2, argument.getLastName()); ps.setLong(3, argument.getId().longValue()); } }); return updateCounts; } // ... additional methods }

Thanks for your response. It looks much cleaner. I had few doubts: 1) what is meaning of 2D array as response of batchUpdate method 2) Can we crosscheck during running of application that dboperations is actually running in batches ?

Sathya · Accepted Answer · 2015-06-04 09:46:28Z

Still simplified way is modifying getBatchsize() method as in below works well

No need of partitioning or subset of list :),

@Override public void saveBatch(final List<Employee> employeeList) { final int batchSize = 500; getJdbcTemplate().batchUpdate(QUERY_SAVE, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Employee employee = employeeList.get(i); ps.setString(1, employee.getFirstname()); ps.setString(2, employee.getLastname()); ps.setString(3, employee.getEmployeeIdOnSourceSystem()); } @Override public int getBatchSize() { if (batchSize > employeeList.size()) { return employeeList.size(); } return batchSize; } }); }

This will not work, but only insert the very first 500 records into the DB.
explicitly call the batchUpdate method which takes the batchsize as parameter eg int[][] recordsUpdated = getJdbcTemplate().batchUpdate(QUERY_SAVE, employeeList, batchSize, ppss);

Collectives™ on Stack Overflow

How to do multiple inserts in database using spring JDBC Template batch?

4 Answers 4

3 Comments

Comments

1 Comment

2 Comments

Linked

Hot Network Questions