Spring - JDBC Batch Update

A JDBC batch update is multiple updates using the same database session. That is, we don't have to open connections multiple times.

In our previous example, let's say we want to insert multiple Person objects in the database. Followings are the various ways to do that in Spring.

Using JdbcTemplate

batchUpdate(String... sql) : 

jdbcTemplate.batchUpdate(
"insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values ('Dana', 'Whitley', '464 Gorsuch Drive')",
"insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values ('Robin', 'Cash', '64 Zella Park')"
            );

batchUpdate(String sql, List<Object[]> batchArgs) : 

jdbcTemplate.batchUpdate(
        "insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values (?, ?, ?)",
        Arrays.asList(new Object[]{"Dana", "Whitley", "464 Gorsuch Drive"},
                      new Object[]{"Robin", "Cash", "64 Zella Park"})
 );

batchUpdate(String sql, List <Object[]> batchArgs, int[] argTypes) : 

jdbcTemplate.batchUpdate(
        "insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values (?, ?, ?)",
        Arrays.asList(new Object[]{"Dana", "Whitley", "464 Gorsuch Drive"},
                      new Object[]{"Robin", "Cash", "64 Zella Park"}),
                new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}
 );

batchUpdate(String sql, BatchPreparedStatementSetter pss) 

final List<Person> persons = Arrays.asList(
        Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
        Person.create("Robin", "Cash", "64 Zella Park")
);

String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";

int[] updateCounts = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        ps.setString(1, persons.get(i).getFirstName());
        ps.setString(2, persons.get(i).getLastName());
        ps.setString(3, persons.get(i).getAddress());
    }

    @Override
    public int getBatchSize() {
        return persons.size();
    }
});

batchUpdate(String sql, Collection<T> batchArgs, int batchSize, ParameterizedPreparedStatementSetter <T> pss) . This method can break the batch updates into serveral smaller batches specified by batchSize. 
 

final List<Person> persons = Arrays.asList(
        Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
        Person.create("Robin", "Cash", "64 Zella Park")
);

String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";

int[][] updateCounts = jdbcTemplate.batchUpdate(sql, persons, persons.size(),

        new ParameterizedPreparedStatementSetter<Person>() {
            @Override
            public void setValues(PreparedStatement ps, Person person) throws SQLException {
                ps.setString(1, person.getFirstName());
                ps.setString(2, person.getLastName());
                ps.setString(3, person.getAddress());
            }
        });

Using NamedParameterJdbcTemplate

batchUpdate(String sql, Map <String,?>[] batchValues)

List<Person> persons = Arrays.asList(
        Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
        Person.create("Robin", "Cash", "64 Zella Park")
);

String sql = "insert into Person (first_Name, Last_Name, Address) " +
        "values (:firstName, :lastName, :address)";

List<Map<String, Object>> batchValues = new ArrayList<>(persons.size());
for (Person person : persons) {
    batchValues.add(
            new MapSqlParameterSource("firstName", person.getFirstName())
                    .addValue("lastName", person.getLastName())
                    .addValue("address", person.getAddress())
                    .getValues());
}

int[] updateCounts = namedParamJdbcTemplate.batchUpdate(sql,
                           batchValues.toArray(new Map[persons.size()]));

batchUpdate(String sql, SqlParameterSource[] batchArgs) 

List<Person> persons = Arrays.asList(
        Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
        Person.create("Robin", "Cash", "64 Zella Park")
);

String sql = "insert into Person (first_Name, Last_Name, Address) " +
        "values (:firstName, :lastName, :address)";

SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(persons.toArray());
int[] updateCounts = namedParamJdbcTemplate.batchUpdate(sql, batch);

Using SimpleJdbcInsert

executeBatch(Map<String,?>... batch) 

List<Map<String, Object>> batchValues = new ArrayList<>(persons.size());
for (Person person : persons) {
 Map<String, Object> map = new HashMap<>();
  map.put("first_Name", person.getFirstName());
  map.put("last_Name", person.getLastName());
  map.put("address", person.getAddress());

 batchValues.add(map);
  }

 SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("PERSON");

 int[] ints = simpleJdbcInsert.executeBatch(batchValues.toArray(new Map[persons.size()]));

public int[] executeBatch(SqlParameterSource... batch).

We don't have to specify any column to bean field name mapping. It can figure out the differences like underscores in the table column names. For example bean#firstName is automatically mapped to column FIRST_NAME. 

List<Person> persons = Arrays.asList(
        Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
        Person.create("Robin", "Cash", "64 Zella Park")
);

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
                                          .withTableName("PERSON")
                                          .usingGeneratedKeyColumns("id");

SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(persons.toArray());
int[] ints = simpleJdbcInsert.executeBatch(batch);

Using BatchSqlUpdate

Batch Sql Update can be used as reusable compiled SQL object to perform batch updates. Please check out our related tutorial

Example Project

This example project uses ParameterizedPreparedStatementSetter for batch operation. We added a new method buildSave in one of our previous examples.

Dependencies and Technologies Used:

• Spring Context 4.2.3.RELEASE: Spring Context.
• Spring JDBC 4.2.3.RELEASE: Spring JDBC.
• H2 Database Engine 1.4.190: H2 Database Engine.
• JDK 1.8
• Maven 3.0.4

package com.logicbig.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class JdbcTemplatePersonDao implements PersonDao {

    @Autowired
    private DataSource dataSource;

    private JdbcTemplate jdbcTemplate;

    @PostConstruct
    private void postConstruct() {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public int[][] bulkSave(List<Person> newPersons) {

        final List<Person> persons = Arrays.asList(
                Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
                Person.create("Robin", "Cash", "64 Zella Park")
        );

        String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";

        return jdbcTemplate.batchUpdate(sql, persons, persons.size(),

                new ParameterizedPreparedStatementSetter<Person>() {
                    @Override
                    public void setValues(PreparedStatement ps, Person person) throws SQLException {
                        ps.setString(1, person.getFirstName());
                        ps.setString(2, person.getLastName());
                        ps.setString(3, person.getAddress());
                    }
                });
    }

    @Override
    public void save(Person person) {
        String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";
        jdbcTemplate.update(sql, person.getFirstName(), person.getLastName(),
                person.getAddress());
    }

    @Override
    public Person load(long id) {
        List<Person> persons = jdbcTemplate.query("select * from Person where id =?",
                new Object[]{id}, (resultSet, i) -> {
                    return toPerson(resultSet);
                });

        if (persons.size() == 1) {
            return persons.get(0);
        }
        return null;
    }

    @Override
    public void delete(long id) {
        jdbcTemplate.update("delete from PERSON where id = ?", id);
    }

    @Override
    public void update(Person person) {
        throw new UnsupportedOperationException();
    }

    @Override
    public void updateAddress(long id, String newAddress) {
        jdbcTemplate.update("update PERSON set ADDRESS = ? where ID = ?"
                , newAddress, id);
    }

    @Override
    public List<Person> loadAll() {
        return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
            return toPerson(resultSet);
        });
    }

    private Person toPerson(ResultSet resultSet) throws SQLException {
        Person person = new Person();
        person.setId(resultSet.getLong("ID"));
        person.setFirstName(resultSet.getString("FIRST_NAME"));
        person.setLastName(resultSet.getString("LAST_NAME"));
        person.setAddress(resultSet.getString("ADDRESS"));
        return person;
    }


    @Override
    public List<Person> findPersonsByLastName(String name) {
        return jdbcTemplate.query("select * from Person where LAST_NAME = ?",
                new Object[]{name}, new RowMapper<Person>() {
                    @Override
                    public Person mapRow(ResultSet resultSet, int i) throws SQLException {
                        return toPerson(resultSet);
                    }
                });
    }

    @Override
    public Long getPersonCount() {
        return jdbcTemplate.queryForObject("select count(*) from PERSON",
                Long.class);

    }
}

In this example too, we used embedded H2 database. This time, we wanted to enable logging for database communications. H2 requires to set logging level with the url (e.g. jdbc:h2:mem:testDB;TRACE_LEVEL_SYSTEM_OUT=3;). I didn't find any method to set a custom url on EmbeddedDatabaseBuilder, but I managed to do this: setName("exampleDB;TRACE_LEVEL_SYSTEM_OUT=3"). Other option could be to use H2 native classorg.h2.jdbcx.JdbcDataSource directly.

After running the app you will see there's only one connection opened for the three insert statements (batch update). If you do the same inserts in a loop (uncomment the loop code in AppController.java), you will see, a new connection is opened for each insert. That's the difference of using batchUpdate().