Spring BatchSqlUpdate vs NamedParameterJdbcTemplate using named parameters
batch-processing, jdbc, spring, sql-update
Solution
After doing some research, I reached the following conclusions.
First of all, I realized that the `NamedParameterJdbcTemplate` class is the only one accepting named parameters for batch updates. The method `batchUpdate(String sql,Map[] batchValues)` was added in Spring 3 to achieve this.
The `BatchSqlUpdate` class contains an overridden `update(Object... params)` method that adds the given statement parameters to the queue rather than executing them immediately, as stated in the javadoc. This means that the statements will be executed only when the `flush()` method is called or the batch size exceeded the maximum. This classed doesn't support named parameters, although it contains a `updateByNamedParam()` method inherited from `SqlUpdate`. This is unfortunate since this method allows reuse of the same map for the named parameters, whereas the `NamedParameterJdbcTemplate.batchUpdate()` method requires an array of maps, with the associated overhead, code bloating and complications in reusing the array of maps if the batch size is variable.
I think it would be useful to have an overridden version of `updateByNamedParam(Map paramMap)` in `BatchSqlUpdate` to behave just like `update(Object... params)` but with added support for named parameters.
Problem
I have been using the `BatchSqlUpdate` class successfully for a while. The only annoyance using it is that named parameters need to be registered before running any query using the `declareParameter` or `setParameter` methods. This means that the type of the parameters has to be declared as well. However, Spring also provides a `NamedParameterJdbcTemplate` class which has a very convenient `batchUpdate` method that takes named parameters as input (an array of maps or `SqlParameterSource` objects) without the need of declaring them first. On top of that, this class can be reused easily and I also believe it's thread-safe. So I have a couple of questions about this: - What's the recommended way to perform (multiple) batch updates? - Why is this feature duplicated across two different classes that also behave differently? - Why does `BatchSqlUpdate` require declared parameters if `NamedParameterJdbcTemplate` does not? Thanks for the thoughts! Giovanni