[jdbc-v2] Critical: executeBatch reuse causes duplicate data insertion after 0.8.6 #2548
Description
A critical regression was introduced in version 0.8.6 (54f35eb#diff-b3fdfecf6eb5032594e47bfb9c6c4b28d54438e401263c6ff10bb068c407487fL294).
The PreparedStatement can no longer be safely reused for multiple executeBatch() calls.
Current Behavior:
When a PreparedStatement is reused to call executeBatch() multiple times, it re-inserts the data from previous batches. This happens because the internal batch state is not cleared after execution, due to the removal of the cleanBatch() logic.
Expected Behavior:
The executeBatch() method should only insert the data that was added to the batch since the last execution. The PreparedStatement should be reusable without leaking state from previous operations.
Impact:
This causes severe data corruption by creating duplicate entries in the database and violates the standard contract of a PreparedStatement.
Suggested Fix
The batch should be cleaned automatically after execution. A reliable approach, used by implementations like the MySQL connector, is to perform this cleanup in a finally block to guarantee it happens even if an exception is thrown.
Proposed code structure for the executeBatch() method:
try {
// ... original execute logic ...
} finally {
cleanBatch(); // Crucial: Reset the internal batch state
}Minimal test case:
<clickhouse-java.version>0.9.1</clickhouse-java.version>
examples/jdbc Basic, replaceinsertDateWithPreparedStatementwith
static void insertDateWithPreparedStatement(String url, Properties properties) throws SQLException {
try (Connection conn = DriverManager.getConnection(url, properties)) {//Grab a connection using the jdbc DriverManager
try (PreparedStatement pstmt = conn.prepareStatement("INSERT INTO " + TABLE_NAME + " VALUES(?, ?, ?, ?)")) {//Create a prepared statement
pstmt.setDate(1, Date.valueOf("2025-01-01"));//Set the first parameter to '2025-01-01' (using java.sql.Date)
pstmt.setInt(2, 1);//Set the second parameter to 1
pstmt.setString(3, "Alice");//Set the third parameter to "Alice"
pstmt.setObject(4, Collections.singletonMap("key1", "value1"));
pstmt.addBatch();//Add the current parameters to the batch
pstmt.executeBatch();//Execute the batch
pstmt.setObject(1, ZonedDateTime.now());
pstmt.setInt(2, 2);//Set the second parameter to 2
pstmt.setString(3, "Bob");//Set the third parameter to "Bob"
pstmt.setObject(4, Collections.singletonMap("key2", "value2"));
pstmt.addBatch();//Add the current parameters to the batch
pstmt.executeBatch();//Execute the batch
}
}
}
log:
[main] INFO com.clickhouse.examples.jdbc.Basic - DateTime: 2025-01-01T00:00+08:00[Asia/Shanghai], Int: 1, String: Alice, Object: {key1=value1}
[main] INFO com.clickhouse.examples.jdbc.Basic - DateTime: 2025-01-01T00:00+08:00[Asia/Shanghai], Int: 1, String: Alice, Object: {key1=value1}
[main] INFO com.clickhouse.examples.jdbc.Basic - DateTime: 2025-08-20T16:42:44.883+08:00[Asia/Shanghai], Int: 2, String: Bob, Object: {key2=value2}