Incorrect batchMode Settings
Symptom
Set the URL parameters batchMode to on and reWriteBatchedInserts to true. After JDBC is used to insert data in batches, a message is displayed, indicating that the number of binding parameters is inconsistent with the number of parameters required by the statement.
bind message supplies * parameters, but prepared statement "" requires *
// conn is a created connection object. The URL parameters for creating the connection contain batchMode=on&reWriteBatchedInserts=true.
// Bind parameters in batches and then execute the statement. The number of bound parameters does not match the number of columns in the rewritten INSERT statement. As a result, an exception is reported.
// java.sql.BatchUpdateException: bind message supplies 3 parameters, but prepared statement "" requires 6
PreparedStatement stmt = conn.prepareStatement("insert into test_tbl values (?, ?, ?)");
stmt.setInt(1, 1);
stmt.setString(2, "aaa");
stmt.setString(3, "bbbb");
stmt.addBatch();
stmt.setInt(1, 1);
stmt.setString(2, "aaa");
stmt.setString(3, "bbbb");
stmt.addBatch();
stmt.setInt(1, 1);
stmt.setString(2, "aaa");
stmt.setString(3, "bbbb");
stmt.addBatch();
stmt.executeBatch(); Cause Analysis
When reWriteBatchedInserts is set to true, the batch statement combines multiple SQL statements into one. As a result, the number of reserved parameter columns in the statement changes. If batchMode is set to on, parameters are bound based on the SQL statements before combination. As a result, the number of bound parameters is inconsistent with the number of parameters required by the statement.
Solution
If reWriteBatchedInserts is set to true, set batchMode to off.