Bug #3873 Statement.getGeneratedKeys method returns only 1 result for batched insertions
Submitted: 24 May 2004 16:23 Modified: 27 May 2004 19:52
Reporter: Ceki Gulcu Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.0.12-production OS:Microsoft Windows (Windows)
Assigned to: Mark Matthews CPU Architecture:Any

[24 May 2004 16:23] Ceki Gulcu
Description:
Hello,

After inserting several rows by using a prepared statement in a batch, retrieving the auto-generated keys by calling statement.getGeneratedKeys returns only the ID of the last insertion.

We have observed that inserting rows in batches results in a very significant boost in performance. Unfortunately, without the getGeneratedKeys method returning all the inserted rows, we cannot use batched statements.

Your help in shedding light on to the matter would be higly appreciated. Thank
you in advance,
 

How to repeat:
  try {
      // Assume we have a valid connection
      connection.setAutoCommit(false);

      // this is the simplified SQL for the purposes of this example
      StringBuffer sql = new StringBuffer();
      sql.append("INSERT INTO logging_event (");
      sql.append("sequence_number)");
      sql.append(" VALUES (??)");
      String insertSQL = sql.toString();

      PreparedStatement insertStatement =
        connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);

      // we have buffered events in a list called "eventsBuffer"
      for (int i = 0; i < eventsBuffer.size(); i++) {
        LoggingEvent event = (LoggingEvent) eventsBuffer.get(i);
                LogLog.info("*** event message is "+event.getRenderedMessage());
        insertStatement.setLong(1, event.getSequenceNumber());
        insertStatement.addBatch();
      }

      int[] r = insertStatement.executeBatch();

      // the correct number of insertions are made    
      if(r != null) {
        for(int x = 0; x < r.length; x++) {
          System.out.println("inserted "+r[x]);
        }
      }

      ResultSet keyRS = insertStatement.getGeneratedKeys();

      // unfortunately only one result is returned instead of 
      // eventsBuffer.size() results.  Actually, only the id of
      // the last inserted row is returned
      int i = 0;
      while (keyRS.next()) {
        int id = keyRS.getInt(1);
        System.out.println("**filling id ="+id);
        
        // eventIDArray is where we keep track of the IDs
        eventIDArray[i++] = id;;
      }

      connection.commit();
    } finally {
      // close the connection...
    }
[27 May 2004 19:52] Mark Matthews
This is fixed for 3.0.13, but only for prepared statements (it is a little outside the scope of the JDBC spec, as there is no corrolary executeBatch() method in java.sql.Statement that specifies that generated keys should be returned).

See a nightly build of 3.0.x after 00:00 GMT this evening from http://downloads.mysql.com/snapshots.php if you want to test it before the official 3.0.13 release.
[12 Apr 2007 7:28] Tahir Akhtar
Autogenerated Keys for Batch inserts tested with statement and prepared statement

Attachment: TestBatchInsertAutoGeneratedKeys.java (text/java), 2.78 KiB.

[12 Apr 2007 7:29] Tahir Akhtar
I tested the getGeneratedkey for batch inserts on mysql-connector-3.1.14. 

It appears that the feature works correctly through Statement but not through PreparedStatement (Above comment by Mark noted that it will work through PreparedStatement but not through Statement). 

Same behavior was observed on mysql-connector-5.0.5. 

See the attached JUnit test. <a href="http://bugs.mysql.com/file.php?id=6195"> http://bugs.mysql.com/file.php?id=6195 </a>