[CONJ-1299] PreparedStatement Batch Insert (rewriteBatchedStatements=true) Fails with "Column Count Mismatch" Due to Hardcoded VALUES Literals (original) (raw)

When using PreparedStatement for batch insertion in Java (with MariaDB JDBC driver), a SQLIntegrityConstraintViolationException is thrown. The root cause is that the INSERT statement contains a hardcoded literal value (-1) instead of a placeholder , which leads to a mismatch between the number of table columns and the number of values provided.
Code Sample
public class BatchInsertErrorDemo {
public static void main(String[] args) {
String url = "jdbc:mariadb://localhost:3306/your_database?characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&rewriteBatchedStatements=true";
String user = "your_username";
String password = "your_password";

try (Connection conn = DriverManager.getConnection(url, user, password);
// Note: The SQL contains 9 columns in the column list,
// and 8 placeholders + 1 hardcoded literal (-1) in the value list
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO t_org_user_job (F_ID, F_ORG_ID, F_USER_ID, F_JOB_ID, F_JOB_DICT_ID, F_STATUS, F_IS_ENABLED, F_tenant_id, f_sort) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, -1)" // `-1` is a non-placeholder literal, not a parameter placeholder
))

{ // First batch entry pstmt.setString(1, "1"); pstmt.setString(2, "1"); pstmt.setString(3, "1"); pstmt.setString(4, "1"); pstmt.setString(5, "1"); pstmt.setString(6, "1"); pstmt.setString(7, "1"); pstmt.setString(8, "10000"); pstmt.addBatch(); // Second batch entry pstmt.setString(1, "2"); pstmt.setString(2, "2"); pstmt.setString(3, "2"); pstmt.setString(4, "2"); pstmt.setString(5, "2"); pstmt.setString(6, "2"); pstmt.setString(7, "2"); pstmt.setString(8, "10000"); pstmt.addBatch(); // Execute batch (error occurs here) int[] results = pstmt.executeBatch(); System.out.println("Batch insertion succeeded."); }

catch (SQLException e)

{ e.printStackTrace(); }

}
}
Error Stack Trace
Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn=1388) Column count doesn't match value count at row 2
at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:318)
at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:403)
at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:196)
at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:1411)
at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:1350)
at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:1269)
at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:1193)
at org.mariadb.jdbc.ClientPreparedStatement.executeBatchRewrite(ClientPreparedStatement.java:197)
... 4 more