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

Solution 1:

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();
                }
            });

    }
}

Solution 2:

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();
            }
        });
    }
}

Solution 3:

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

 }