MySQL Statement executeBatch - batchedGeneratedKeys Field
Posted by: Shriprasad Marathe
Date: February 24, 2016 05:11AM

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();
}

Options: ReplyQuote


Subject
Written By
Posted
MySQL Statement executeBatch - batchedGeneratedKeys Field
February 24, 2016 05:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.