getGeneratedKeys() does not return generated keys in case of a batch SQL INSERT

It is currently not possible to execute a batch insert and retrieve the generated keys.

Expected outcome:
After calling the MySQLPreparedStatement.executeBatch(), the MySQLPreparedStatement.getGeneratedKeys() method should return a ResultSet from which the keys generated in SQL INSERTs can be obtained.

Actual outcome:
The MySQLPreparedStatement.getGeneratedKeys() returns MySQLResultSet.EMPTY after calling MySQLPreparedStatement.executeBatch().

More details:

• The problem is reproducible e.g. by using QueryDSL-SQL. Its com.mysema.query.sql.dml.SQLInsertClause performs following operation:

  // Code simplified, see full implementation in https://github.com/mysema/querydsl/blob/master/querydsl-sql/src/main/java/com/mysema/query/sql/dml/SQLInsertClause.java:

  public ResultSet executeWithKeys() {

  final PreparedStatement stmt = createBatchStatement();

  stmt.executeBatch();

  return stmt.getGeneratedKeys();

  }

  private PreparedStatement createStatement(boolean withKeys) throws SQLException {

  SQLSerializer serializer = new SQLSerializer(configuration, true);

  serializer.serializeInsert(metadata, entity, batches.get(0).getColumns(), batches.get(0).getValues(), batches.get(0).getSubQuery());

  PreparedStatement stmt = prepareStatementAndSetParameters(serializer, withKeys);

  // add first batch

  stmt.addBatch();

  // add other batches

  for (int i = 1; i < batches.size(); i++) {

  SQLInsertBatch batch = batches.get(i);

  serializer = new SQLSerializer(configuration, true);

  serializer.serializeInsert(metadata, entity, batch.getColumns(), batch.getValues(), batch.getSubQuery());

  setParameters(stmt, serializer.getConstants(), serializer.getConstantPaths(), metadata.getParams());

  stmt.addBatch();

  }

  return stmt;

  }

• The above example code from QueryDSL-SQL works with mysql:mysql-connector-java:5.1.29. I.e. it returns the generated keys after batch INSERT.