Bug #80497 MySQL Statement executeBatch - batchedGeneratedKeys Field not exposed

• Submitted: 24 Feb 2016 14:57
• Modified: 3 Mar 2016 3:11
• Reporter: Shriprasad Marathe
• Status: Not a Bug
• Category: Connector / J
• Severity: S4 (Feature request)
• Version: 5.0.4
• OS: Any
• CPU Architecture: Any
• Tags: batchedGeneratedKeys, executeBatch

[24 Feb 2016 14:57] Shriprasad Marathe

Description:
I am using MySQL 5.6 with Spring-mybatis. for one of my module we are using batch processing.
But in case of batch insert autogenerated ids are not obtained.

I have just check the source code of com.mysql.jdbc.Statement. It contains the field
batchedGeneratedKeys.

After debuging the code using statment.executeBatch(), I found that batchedGeneratedKeys is populated with actual generated ids inside getBatchedGeneratedKeys() method, which is called by executeBatch itself.

Question : Why this class not exposed the field
batchedGeneratedKeys using getGeneratedIds(). This will be useful for calling module. It will reduce one select Query to determine generated ids.

Following is the codesnippet is tried. Used Java reflection to get this ids.

@Test
public void testBatch() throws SQLException, NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
String sqlInsert = "INSERT INTO AdSize (name,height,width,type,created_at,updated_at) VALUES ";
StringBuilder sqlBatchBuilder = new StringBuilder(sqlInsert);

int width = 1000;
int height = 1000;
for (int i = 0; i < 10; i++) {
if(!sqlBatchBuilder.toString().equals(sqlInsert)) {
sqlBatchBuilder.append(",");
}
String name = width + " x " + height + " (Custom)";
sqlBatchBuilder
.append("('")
.append(name)
.append("',")
.append(height)
.append(",")
.append(width)
.append(",")
.append("'custom'")
.append(",")
.append("now()")
.append(",")
.append("now())");
width++;
height++;
}
// sqlBatchBuilder.append(")");

System.out.println(sqlBatchBuilder.toString());

Statement stmt = null;
Connection connection = getDataSource().getConnection();
stmt = connection.createStatement();
stmt.addBatch(sqlBatchBuilder.toString());
stmt.executeBatch();
Field batchedGeneratedKeys = com.mysql.jdbc.Statement.class.getDeclaredField("batchedGeneratedKeys");
batchedGeneratedKeys.setAccessible(true);
// get underline statement object _stmt
Field _stmt = stmt.getClass().getDeclaredField("_stmt");
_stmt.setAccessible(true);
com.mysql.jdbc.Statement statement = (com.mysql.jdbc.Statement) _stmt.get(stmt);
ArrayList batchedGeneratedKeysList = (ArrayList) batchedGeneratedKeys.get(statement);
for (Object generatedKey : batchedGeneratedKeysList) {
byte[][] keyArray = (byte[][]) generatedKey;
for (byte[] bs : keyArray) {
System.out.println(new String(bs));
}
// System.out.println();
}
stmt.close();
connection.close();
}

How to repeat:
Please refer to above code

Suggested fix:
Please provide the mechanism such that Statement object will be able to return list of generated ids in case of executeBatch

[1 Mar 2016 9:34] Filipe Silva

Hi Shriprasad,

Thank you for this bug report.

There is already such mechanism. The JDBC specification states that the generated keys can be retrieved by consulting the ResultSet you obtain by calling Statement.getGeneratedKeys(). Most times you have to enjoin the Statement that you desire this information by using the flag Statement.RETURN_GENERATED_KEYS. In batch statements, however, there's no such option so the Connector/J driver, by default, turns it on for you. The JDBC specification doesn't enforce this, but doesn't denies it too, so you get it for free.

You suggestion of getting this information as a List of ids probably won't ever happen because this depends on the JDBC specification that we don't control.

I hope that helps.

If you feed that is something missing or failing still, please reopen this bug or report a new one.

Thank you,

[3 Mar 2016 3:11] Shriprasad Marathe

Thanks Filip for your reply !

Please refer to http://www.docjar.com/html/api/com/mysql/jdbc/StatementImpl.java.html.

If you check line #2594 and #2613 there is a mechanism that this class populates the autogeneratedkeys inside batchedGeneratedKeys (the array list object).

The method at #2613 is used by executeBatch at line #1038, just afer calling executeUpdate.

I debug this code and found that generated keys are set inside batchedGeneratedKeys  object by these methods.

Thanks & Regards,
Shriprasad