Add batchUpdate method to JDBCTemplate with support for returning generated keys [SPR-1836]

[spring-projects-issues]

Arthur Branham opened SPR-1836 and commented

I realize that generated keys are not returned in a batchUpdate even if multiple insert statements are executed for a table with an autoincrement column. Currently in JDBCTemplate the only way to get a generated key is to execute statements one at a time using the following method signature:
public in update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder)
I have implemented a batchUpdate method with the following method signature:
public int[] batchUpdate(final String sql,final BatchPreparedStatementSetter pss,final KeyHolder generatedKeyHolder) throws DataAccessException;

This method is a little more performant and convenient because all statements are executed by one PreparedStatement. Parameter values for each statement are set via the BatchPreparedStatementSetter. After each batch is executed, any generated keys are collected and aggregated in the KeyHolder. Updated row counts are returned for each batch statement. The returned keys can then be used to fetch in bulk, the rows that were created via a batchQuery as described in http://opensource.atlassian.com/projects/spring/browse/SPR-1835

I will attach an implementation and sample usage.

---

Affects: 2.0 M3

Attachments:

• JDBCTemplateBatchUpdateMethodImpl.txt (1.82 kB)
• JDCBTemplateBatchUpdateWithGeneratedKeys.txt (2.11 kB)

Issue Links:

• Add batchQuery methods to JDBCTemplate [SPR-1835] #6529 Add batchQuery methods to JDBCTemplate

Referenced from: commits spring-projects/spring-data-relational@21490b8, spring-projects/spring-data-relational@a390cf7, spring-projects/spring-data-relational@3efb2a4

3 votes, 5 watchers

[spring-projects-issues]

Arthur Branham commented

Sample implementation of batchUpdate method.

[spring-projects-issues]

Tobias Mattsson commented

I agree, there should be a method like this. Unlike Arthur I think PreparedStatementCreate should be passed in as an argument.

Im attaching an example implementation based on
public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)

[spring-projects-issues]

Tobias Mattsson commented

After testing this a bit more and consulting the spec i realize that my implementation is flawed. The JDBC spec clearly says:

'It is implementation-defined as to whether getGeneratedKeys will return generated values after invoking the executeBatch method.'

My implementation therefore is flawed, please ignore it.

[spring-projects-issues]

Arthur commented

I came across this as well. DB2 for instance does not return the generated keys when using executeBatch. I believe this is why I originally looped through the batches, set the parameters for that batch, and executed the update as a prepared statement update call, and aggregated the returned keys.

[spring-projects-issues]

Thomas Risberg commented

This issue has been around for a while and as noted there is no good solution since the spec doesn't guarantee that the generated keys will be made available. I'm closing this now.

[spring-projects-issues]

Sid commented

I was able to provide an implementation for the returning db generated keys for batch, how should I get the code reviewed and if approved commit it.