springjdbc的批量操作
这里主要是看的官方文档,如何翻译:
NamedParameterJdbcTemplate
The NamedParameterJdbcTemplate
class adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ( '?'
) arguments. The NamedParameterJdbcTemplate
class wraps aJdbcTemplate
, and delegates to the wrapped JdbcTemplate
to do much of its work. This section describes only those areas of the NamedParameterJdbcTemplate
class that differ from the JdbcTemplate
itself; namely, programming JDBC statements using named parameters.
// some JDBC-backed DAO class... private NamedParameterJdbcTemplate namedParameterJdbcTemplate; public void setDataSource(DataSource dataSource) { this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource); } public int countOfActorsByFirstName(String firstName) { String sql = "select count(*) from T_ACTOR where first_name = :first_name"; SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName); return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class); }
Notice the use of the named parameter notation in the value assigned to the sql
variable, and the corresponding value that is plugged into the namedParameters
variable (of type MapSqlParameterSource
).
Alternatively, you can pass along named parameters and their corresponding values to a NamedParameterJdbcTemplate
instance by using the Map
-based style.The remaining methods exposed by the NamedParameterJdbcOperations
and implemented by theNamedParameterJdbcTemplate
class follow a similar pattern and are not covered here.
The following example shows the use of the Map
-based style.
private NamedParameterJdbcTemplate namedParameterJdbcTemplate; public void setDataSource(DataSource dataSource) { this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource); } public int countOfActorsByFirstName(String firstName) { String sql = "select count(*) from T_ACTOR where first_name = :first_name"; Map<String, String> namedParameters = Collections.singletonMap("first_name", firstName); return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class); }
Basic batch operations with the JdbcTemplate
You accomplish JdbcTemplate
batch processing by implementing two methods of a special interface, BatchPreparedStatementSetter
, and passing that in as the second parameter in your batchUpdate
method call. Use the getBatchSize
method to provide the size of the current batch. Use the setValues
method to set the values for the parameters of the prepared statement. This method will be called the number of times that you specified in the getBatchSize
call. The following example updates the actor table based on entries in a list. The entire list is used as the batch in this example:
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) { int[] updateCounts = jdbcTemplate.batchUpdate("update t_actor set first_name = ?, " + "last_name = ? where id = ?", new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, actors.get(i).getFirstName()); ps.setString(2, actors.get(i).getLastName()); ps.setLong(3, actors.get(i).getId().longValue()); } public int getBatchSize() { return actors.size(); } }); return updateCounts; } // ... additional methods }
If you are processing a stream of updates or reading from a file, then you might have a preferred batch size, but the last batch might not have that number of entries. In this case you can use the InterruptibleBatchPreparedStatementSetter
interface, which allows you to interrupt a batch once the input source is exhausted. The isBatchExhausted
method allows you to signal the end of the batch.
Batch operations with a List of objects
Both the JdbcTemplate
and the NamedParameterJdbcTemplate
provides an alternate way of providing the batch update. Instead of implementing a special batch interface, you provide all parameter values in the call as a list. The framework loops over these values and uses an internal prepared statement setter. The API varies depending on whether you use named parameters. For the named parameters you provide an array of SqlParameterSource
, one entry for each member of the batch. You can use theSqlParameterSource.createBatch
method to create this array, passing in either an array of JavaBeans or an array of Maps containing the parameter values.
This 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(final List<Actor> actors) { SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray()); int[] updateCounts = namedParameterJdbcTemplate.batchUpdate( "update t_actor set first_name = :firstName, last_name = :lastName where id = :id", batch); return updateCounts; } // ... additional methods }
For an SQL statement using the classic "?" placeholders, you pass in a list containing an object array with the update values. This object array must have one entry for each placeholder in the SQL statement, and they must be in the same order as they are defined in the SQL statement.
The same example using 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); } int[] updateCounts = jdbcTemplate.batchUpdate( "update t_actor set first_name = ?, last_name = ? where id = ?", batch); return updateCounts; } // ... additional methods }
All of the above batch update methods return an int array containing the number of affected rows for each batch entry. This count is reported by the JDBC driver. If the count is not available, the JDBC driver returns a -2 value.
Batch operations with multiple batches
The last example of a batch update deals with batches that are so large that you want to break them up into several smaller batches. You can of course do this with the methods mentioned above by making multiple calls to the batchUpdate
method, but there is now a more convenient method. This method takes, in addition to the SQL statement, a Collection of objects containing the parameters, the number of updates to make for each batch and a ParameterizedPreparedStatementSetter
to set the values for the parameters of the prepared statement. The framework loops over the provided values and breaks the update calls into batches of the size specified.
This example shows a batch update using 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, 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 }
The batch update methods for this call returns an array of int arrays containing an array entry for each batch with an array of the number of affected rows for each update. The top level array’s length indicates the number of batches executed and the second level array’s length indicates the number of updates in that batch. The number of updates in each batch should be the batch size provided for all batches except for the last one that might be less, depending on the total number of update objects provided. The update count for each update statement is the one reported by the JDBC driver. If the count is not available, the JDBC driver returns a -2 value.