batchMode设置错误
问题现象
设置url参数batchMode=on且reWriteBatchedInserts=true,使用JDBC批量插入数据,抛出异常,提示绑定参数数量与语句需要的参数数量不一致:
bind message supplies * parameters, but prepared statement "" requires *
// conn是已经创建的Connection对象,创建该connection的url参数包含&batchMode=on&reWriteBatchedInserts=true // 批量绑定参数后执行,绑定参数数量会与改写后的insert语句的栏位数不匹配,抛出异常 // 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();
原因分析
将参数reWriteBatchedInserts设置为true时,批量语句会将多条SQL合并为一条,导致语句中预留参数栏位数发生变化,如果batchMode=on,会按照合并前的SQL绑定参数,导致绑定参数数量与语句需要的参数数量不一致。
处理方法
reWriteBatchedInserts设置为true时,将batchMode设置为off。