I have thousands of statements to be executed as batches, and if one of them fails due to a UNIQUE constraints, then how do I detect them separately (one with success and the rest with failures) and handle the validation accordingly? Will all statements’ status be marked as failed?
0
The below code helped me debug that issue:
try {
stmt.addBatch("INSERT INTO my_table (column1, column2) VALUES ('val1', 'val2')");
stmt.addBatch("INSERT INTO my_table (column1, column2) VALUES ('val3', 'val4')");
int[] updateCounts = stmt.executeBatch();
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] == -2) {
// Statement encountered a unique constraint violation
System.out.println("Statement[" + (i + 1) + "] failed due to unique constraint violation.");
} else {
System.out.println("Statement[" + (i + 1) + "] succeeded.");
}
}
} catch (SQLException e) {
// Handle the exception
}
Will all statements’ status be marked as failed?
No. They will be run separately.
You have a couple of options to deal with errors:
-
You get an array “of thousands” of
int(one value per statement), indicating the count of modified rows. You can inspect them to decide which ones succeeded and which one failed. See executeBatch. -
You’ll get all the SQLExceptions for the failed queries.
executeBatchwill throw a SQLException for the first exception. Then you can useSQLException.getNextException()to iterate over all of them.