Spring Framework
  1. Spring Framework
  2. SPR-1836

Add batchUpdate method to JDBCTemplate with support for returning generated keys

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 2.0 M3
    • Fix Version/s: None
    • Component/s: Data
    • Labels:
      None
    • Last commented by a User:
      true

      Description

      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.

        Issue Links

          Activity

          Hide
          Arthur Branham added a comment -

          Sample implementation of batchUpdate method.

          Show
          Arthur Branham added a comment - Sample implementation of batchUpdate method.
          Hide
          Tobias Mattsson added a comment -

          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)

          Show
          Tobias Mattsson added a comment - 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)
          Hide
          Tobias Mattsson added a comment -

          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.

          Show
          Tobias Mattsson added a comment - 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.
          Hide
          Arthur added a comment -

          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.

          Show
          Arthur added a comment - 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.
          Hide
          Thomas Risberg added a comment -

          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.

          Show
          Thomas Risberg added a comment - 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.
          Hide
          Sid added a comment -

          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.

          Show
          Sid added a comment - 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.

            People

            • Assignee:
              Thomas Risberg
              Reporter:
              Arthur Branham
              Last updater:
              Trevor Marshall
            • Votes:
              3 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                1 year, 46 weeks ago