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, Jun 24 '16 at 14:11

adarshr · Accepted Answer · 2012-03-05T12:39:01.900

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 ?? — muhammed ozbilici, Jul 18 '20 at 08:58
In case anyone wants to get the id's returned, you can do that via JdbcTemplate by accessing the PreparedStatement directly: https://stackoverflow.com/questions/7333524/how-can-i-insert-many-rows-into-a-mysql-table-and-return-the-new-ids#69365101 — Curtis Yallop, Sep 28 '21 at 16:27
`j + batchSize > employeeList.size() ? employeeList.size() : j + batchSize` can be replaced by `Math.min(index + batchSize, employeeList.size())` — deep, Mar 24 '22 at 14:11

score 20 · Answer 2 · edited Feb 09 '18 at 20:54

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 · Answer 3 · 2018-09-17T04:49:09.103

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 ? — Ankit, Mar 09 '17 at 05:51

score -1 · Answer 4 · answered Jun 04 '15 at 09:46

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. — ngeek, Jun 12 '17 at 15:24
explicitly call the batchUpdate method which takes the batchsize as parameter eg int[][] recordsUpdated = getJdbcTemplate().batchUpdate(QUERY_SAVE, employeeList, batchSize, ppss); — Ajith Kannan, Jul 22 '19 at 17:26

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

4 Answers4

Linked