Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
3.4.0, 3.5.0
-
None
-
None
Description
I have a Spring Boot application that connects to an Amazon Aurora MySQL-compatible database. The setup uses the MariaDB JDBC driver, and HikariCP for connection pooling.
My code uses PreparedStatement.executeBatch() to perform batch inserts/updates. The problem is:
If I add multiple statements to the batch and call executeBatch(), it does not work (no records inserted/updated, or errors occur).
If I add exactly one statement to the batch and call executeBatch(), it works as expected.
Details:
Spring Boot version: 3.4.5
MariaDB JDBC driver version: 3.4.2
Aurora MySQL version: 2.7.12
HikariCP version: 5.1.0
Example JDBC URL: jdbc:aws-wrapper:mariadb://AuroraHost:AuroraPort/Schema?rewriteBatchedStatements=true&useSSL=false
// Some comments here
|
public Integer saveBulkDataInTableData(List<Schema> listOfCandidates) throws SQLException { |
Integer numberOfRecordsSaved = 0; |
int[] batchExecutionResult; |
if (!listOfCandidates.isEmpty()) { |
try(Connection conn = writeDataSource.getConnection(); PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO table01 (Id, isProcessed, lastModifiedDate) VALUES (?,?,?)")) { |
if (conn == null || conn.isClosed()) { |
LOGGER.error("Database connection is not available or is closed."); |
throw new SQLException("Database connection is not available or is closed."); |
}
|
conn.setAutoCommit(false); |
int batchSize = 2000; |
for (int i = 0; i < listOfCandidates.size(); i++) { |
conn.setAutoCommit(false); |
preparedStatement.setString(1, listOfCandidates.get(i).getId()); |
preparedStatement.setString(2, "N"); |
preparedStatement.setString(3, listOfCandidates.get(i).getActivityDate()); |
preparedStatement.addBatch();
|
if ((i + 1) % batchSize == 0) { |
LOGGER.debug("Saving batch at record number {}", i + 1); |
try { |
preparedStatement.executeBatch();
|
preparedStatement.clearBatch();
|
conn.commit();
|
} catch (Exception exception) { |
LOGGER.error("Batch with record number {} failed", i + 1, exception); |
conn.rollback();
|
}
|
}
|
}
|
// Execute remaining records in batch if any |
try { |
batchExecutionResult = preparedStatement.executeBatch();
|
numberOfRecordsSaved = numberOfRecordsSaved + batchExecutionResult.length;
|
conn.commit();
|
} catch (Exception exception) { |
LOGGER.error("Records remaining in the saveBulkDataInTableData batch failed", exception); |
conn.rollback();
|
} finally { |
conn.setAutoCommit(true); |
}
|
}
|
}
|
return numberOfRecordsSaved; |
}
|