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...
    }

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.

Autogenerated Keys for Batch inserts tested with statement and prepared statement

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

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>