NamedParameterJdbcTemplate.batchUpdate does not individually apply the SQL type from each SqlParameterSource argument

[silviomo]

The class NamedParameterJdbcTemplate has following method:

@Nonnull
@Override
public int[] batchUpdate(@Nonnull String sql, @Nonnull SqlParameterSource[] batchArgs) {
// implementation
}

The method signature suggests fine-grained control over the SQL type hints, so that these could be customised for each element in the array, however, this is not true.

As a matter of fact, only the SQL hints for the first element in the array are processed, they are then applied to all the elements in the batch by PreparedStatementCreatorFactory.

This can lead to exceptions within the JDBC driver, for example when using MySQL/MariaDB. Consider the following table definition and inserts:

CREATE TABLE `names` (
`first_name` VARCHAR(30) NOT NULL,
`nick_name` VARCHAR(30) NULL
);

INSERT INTO `names`(`first_name`, `nick_name`) VALUES(:first_name, :nick_name);

Now consider this Java:

MapSqlParameterSource sqlParameterSource1 = new MapSqlParameterSource();
sqlParameterSource2.addValue("first_name", "Jose", Types.VARCHAR);
sqlParameterSource2.addValue("nick_name", null, Types.NULL);

MapSqlParameterSource sqlParameterSource2 = new MapSqlParameterSource();
sqlParameterSource1.addValue("first_name", "Silvio", Types.VARCHAR);
sqlParameterSource1.addValue("nick_name", "Silv", Types.VARCHAR);

SqlParameterSource[] batch = new SqlParameterSource[] {
        sqlParameterSource1,
        sqlParameterSource2
};
namedParameterJdbcTemplate.batchUpdate(sql, batch);

This will throw an exception in the MySQL driver because Types.NULL from the first element will be applied to the second element as well.

Cannot convert class java.lang.String to SQL type requested due to com.mysql.cj.exceptions.WrongArgumentException - Conversion from java.lang.String to NULL is not supported.

[jhoeller]

Thanks for raising this! Looks like a regression caused by #21935 (about two years ago). We used to derive named parameter replacement from the first batch arguments (which is still necessary), but since then we're also taking the SQL type indications from the first argument entry only. I guess most batches use the same types anyway, otherwise we'd have noticed earlier. In any case, this is easy enough to address through SqlParameterValue wrapping, hopefully also easy enough to backport.

[jhoeller]

This is available in the latest snapshots now: 5.3.2, 5.2.12, 5.1.20 (see https://repo.spring.io/snapshot/org/springframework/spring-framework-bom/). It'd be great if you could give the fix an early try, we're going to release all of the above in early December.

It turned out that it was indeed a regression in 5.1.x. I've backported the test to 5.0.x and 4.3.x as well where it passes right away.