MySQL Statement executeBatch - batchedGeneratedKeys Field
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();
}