关于JDBC的批量操作executeBatch()所引发sql语句异常 |
您所在的位置:网站首页 › jdbc批量提交异常 › 关于JDBC的批量操作executeBatch()所引发sql语句异常 |
java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('1512144017', 'quqiang01' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1162)
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1587)
at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1253)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('1512144017', 'quqiang01' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1548)
... 5 more
最近在使用JDBC的时候,一个比较坑的细节,就是关于他里面使用PreparedStatement或者Statement 的 addBatch()/executeBatch()的具体实现问题; 不要手贱在你传入的sql语句没末尾加上分号;
具体是这样: 1 //以一个批量提交的实现工具举例 2 // List lists: 所有数据的list 3 // List:单条数据"值"的list 4 5 public void batchExecutePstamt(String sql, List < List < Object >> lists) { 6 try { 7 connection.setAutoCommit(false); 8 pstmt = connection.prepareStatement(sql); 9 if (lists != null && !lists.isEmpty()) { 10 for (List < Object > cList: lists) { 11 if (cList == null || cList.isEmpty()) 12 continue; 13 14 for (int i = 0; i < cList.size(); i++) { 15 pstmt.setObject(i + 1, cList.get(i)); 16 } 17 pstmt.addBatch(); 18 } 19 log.info(pstmt.toString()); 20 pstmt.executeBatch(); 21 connection.commit(); 22 } 23 } catch (SQLException e) { 24 e.printStackTrace(); 25 } 26 }在这里,我们传入大量的需要插入的对象的List,里面的 List 就是某一条具体的记录的值;
如果你调用的时候, 在传入sql的时候, 传入了类似于 insert into `tablename` (name,age) values (?,?); 的sql语句那么他每次addBatch的时候就会在com.mysql.jdbc.PreparedStatement.addBatch()的创建com.mysql.jdbc.PreparedStatement.BatchParams 然后放进batchedArgs 静态列表里面
![]() ![]()
![]() ![]()
然后会在最后使用executeBatch()的时候处理 batchedArgs中的 数据 处理的步骤为: >> com.mysql.jdbc.StatementImpl.executeBatch() >> com.mysql.jdbc.PreparedStatement.executeBatchInternal()这里回去判断你设置的一些参数, 比如connection.getRewriteBatchedStatements(), 这个有助于批量数据处理的参数, 需要在jdbc.url连接中设置 rewriteBatchedStatements = true, 不过好像只针对于某个版本(5.2 ?不确定 )以后的数据操作性能有帮助.在这里会进入一个将多个PreparedStatement转化为 BLUK 模式的一条语句;(bluk模式不知道的请自行百度)
>> com.mysql.jdbc.PreparedStatement.executeBatchedInserts(int) 这里面有一段源码: 1 PreparedStatement batchedStatement = null; 2 3 int batchedParamIndex = 1; 4 long updateCountRunningTotal = 0; 5 int numberToExecuteAsMultiValue = 0; 6 int batchCounter = 0; 7 CancelTask timeoutTask = null; 8 SQLException sqlEx = null; 9 10 long[]updateCounts = new long[numBatchedArgs]; 11 //上面不多说 12 13 try { 14 //这句会把初始化我们传进来的sql 15 //我们传进来的是类似于 insert into tablename (columns…) values (?,?...)这种 16 //到这里相当于初始化成 INSERT INTO tablename` (`name`, `age`) values (** NOT SPECIFIED **, ** NOT SPECIFIED **); 17 //然后他会根据顺序传进我们上面所传进的多个已经赋值的PreparedStatement, 18 batchedStatement = /* FIXME -if we ever care about folks proxying our MySQLConnection */ 19 prepareBatchedInsertSQL(locallyScopedConn, numValuesPerBatch); 20 21 if (locallyScopedConn.getEnableQueryTimeouts() && batchTimeout != 0 && locallyScopedConn.versionMeetsMinimum(5, 0, 0)) { 22 timeoutTask = new CancelTask(batchedStatement); 23 locallyScopedConn.getCancelTimer().schedule(timeoutTask, batchTimeout); 24 } 25 26 if (numBatchedArgs |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |