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.

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

    }
}