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
.
In such a scenario, when automatically setting the values to underlying
PreparedStatement
, the corresponding JDBC type for each value must be derived from the given Java type.
While this usually works well, there is a potential for problems (for example, with null
values
contained in the Map). In such a case, Spring calls ParameterMetaData.getParameterType
by default,
which can be expensive when using your JDBC driver. You should use the latest driver and consider setting the spring.jdbc.getParameterType.ignore
property to true
(as a JVM system property or via the SpringProperties
) if you are experiencing a performance issue (as reported in Oracle 12c, JBoss and PostgreSQL).
Also, one might consider explicitly specifying the appropriate JDBC types either through a BatchPreparedStatementSetter
(as shown earlier), or through an explicit array of types passed to the call based on
List<Object[]>
, or through a call to registerSqlType
on a custom instance of
MapSqlParameterSource
, or through BeanPropertySqlParameterSource
, which infers the SQL
type from the property type declared in Java, even for an empty value.
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
.