Spring BatchSqlUpdate vs NamedParameterJdbcTemplate using named parameters

Question

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

Answer 1

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.