I am inserting many records using JDBC batch inserts.
Is there any way to get the generated key for each record?
Can I use ps.getGeneratedKeys() with batch inserts?
I am using oracle.jdbc.OracleDriver
final String insert = "Insert into Student(RollNumber, Name, Age) values(StudentSEQ.nextval, ? , ?)";
final int BATCH_SIZE = 998;
int count = 0;
Connection con = null;
PreparedStatement ps = null;
try {
con = getConnection();
ps = con.prepareStatement(insert);
for (Student s : students) {
ps.setString(1, s.getName());
ps.setInt(2, s.getAge());
ps.addBatch();
count++;
if (count % BATCH_SIZE == 0) {
// Insert records in batches
ps.executeBatch();
}
}
// Insert remaining records
ps.executeBatch();
} finally {
if(ps != null)
ps.close();
release(con);
}
I am thinking of using ps.executeUpdate() along with ps.getGeneratedKeys() inside the loop to get the desired result. Any other solutions?
解决方案
It appears that Oracle 12c does not support combining auto-generated keys with batch update according to the following page:
See the subsection labeled "Limitations" under the section "Retrieval of Auto-Generated Keys"