Spring JDBC Batch Operations

Most JDBC drivers provide performance improvements when batching multiple calls to the same compiled statement. By grouping updates into batches, you limit the number of roundtrips to the database.

Basic batch operations using JdbcTemplate

You batch process JdbcTemplate by implementing two methods of the special interface BatchPreparedStatementSetter and passing this implementation as the second parameter in the call to the batchUpdate method. The getBatchSize method can be used to set the values of compiled statement parameters. The setValues method can be used to set the values of parameters of a prepared statement. This method is called the number of times specified in the getBatchSize call. The following example updates the t_actor table based on entries in a list, using the entire list as the batch:

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

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    fun batchUpdate(actors: List<Actor>): IntArray {
        return jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                object: BatchPreparedStatementSetter {
                    override fun setValues(ps: PreparedStatement, i: Int) {
                        ps.setString(1, actors[i].firstName)
                        ps.setString(2, actors[i].lastName)
                        ps.setLong(3, actors[i].id)
                    }
                    override fun getBatchSize() = actors.size
                })
    }
    // ... additional methods
}

If you are handling an update stream or reading from a file, you You may already have a preferred batch size, but the latest batch may not have that many records. In this case, you can use the InterruptibleBatchPreparedStatementSetter interface, which allows you to interrupt the batch after the input data source is exhausted. The isBatchExhausted method allows you to signal the end of a batch.

Batch operations using a list of objects

Template JdbcTemplate and NamedParameterJdbcTemplate provide an alternative way to provide batch updates. Instead of implementing a specialized batch interface, you provide all the parameter values in the call as a list. The framework iterates through these values and uses the internal setter of the compiled statements. The API differs depending on whether you use named parameters. For named parameters, you specify an array of SqlParameterSource, one entry for each group member. You can use the SqlParameterSourceUtils.createBatch helper methods to create this array by passing an array of bean-based objects (using getters corresponding to the parameters), Map instances with String-key (containing the corresponding parameters as values), or a combination of both.

The following example shows a batch update using named parameters:

public class JdbcActorDao implements ActorDao {
    private NamedParameterTemplate namedParameterJdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }
    public int[] batchUpdate(List<Actor>actors) {
        return this.namedParameterJdbcTemplate.batchUpdate(
                "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
                SqlParameterSourceUtils.createBatch(actors));
    }
    // ... additional methods
}
        

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
    fun batchUpdate(actors: List<Actor>): IntArray {
        return this.namedParameterJdbcTemplate.batchUpdate(
                "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
                SqlParameterSourceUtils.createBatch(actors));
    }
        // ... additional methods
}

For a SQL statement using classic placeholders ?, you pass a list containing an array of objects with update values. This array of objects must contain one element for each placeholder in the SQL statement, and they must be in the same order in which they are defined in the SQL statement.

The following example is similar to the previous one, except that it uses classic JDBC placeholders ?:

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public int[] batchUpdate(final List<<Actor> actors) {
        List<Object[]> batch = new ArrayList<Object[]>();
        for (Actor actor : actors) {
            Object[] values = new Object[] {
                    actor.getFirstName(), actor.getLastName(), actor.getId()};
            batch.add(values);
        }
        return this.jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                batch);
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    fun batchUpdate(actors: List<Actor>): IntArray {
        val batch = mutableListOf<Array<Any>>()
        for (actor in actors) {
            batch.add(arrayOf(actor.firstName, actor.lastName, actor.id))
        }
        return jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?", batch)
    }
    // ... additional methods
}
        

All of the batch update methods we described earlier return an int array containing the number of affected rows for each batch entry. This counter is reported by the JDBC driver. If the counter is not available, the JDBC driver returns the value -2.

Batch operations using multiple packages

The previous batch update example dealt with packages that are so large that you need to break them into several smaller packages. You can do this using the methods mentioned earlier by making several calls to the batchUpdate method, but now there is a more convenient method. This method accepts, in addition to the SQL statement, a Collection of objects containing parameters, the number of updates for each package and a ParameterizedPreparedStatementSetter for setting the values of the compiled statement parameters. The framework iterates through the provided values and breaks the update calls into batches of the specified size.

The following example shows a batch update that uses a batch size of 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,
                (PreparedStatement ps, Actor actor) -> {
                    ps.setString(1, actor.getFirstName());
                    ps.setString(2, actor.getLastName());
                    ps.setLong(3, actor.getId().longValue());
                });
        return updateCounts;
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    fun batchUpdate(actors: List<Actor>): Array<IntArray> {
        return jdbcTemplate.batchUpdate(
                    "update t_actor set first_name = ?, last_name = ? where id = ?",
                    actors, 100) { ps, argument ->
            ps.setString(1, argument.firstName)
            ps.setString(2, argument.lastName)
            ps.setLong(3, argument.id)
        }
    }
    // ... additional methods
}

The batch update method for this call returns an array of int arrays that contains an array entry for each batch with an array of the number of rows affected for each update. The length of the top-level array indicates the number of packages running, and the length of the second-level array indicates the number of updates in that package. The number of updates in each package must match the package size specified for all packages (except the last one, which may be smaller), depending on the total number of upgradeable objects specified. The update count for each update statement is the count reported by the JDBC driver. If the count is not available, the JDBC driver returns -2.