关于JDBC支不支持批量操作,网上的答案各有各的说法,经过一翻测试,发现MySQL驱动JDBC确实不支持批量操作,如果需要其支持批量操作,需要在MySQL的连接url中加上rewriteBatchedStatements=true,加上后,无论MySQL是循环插入还是批量插入,均有改善
测试环境win7 32位系统 mysql5 JDBC驱动为mysql-connector-java-5.1.16-bin.jar,数据库表user为InnorDB
public static void test_mysql(){ String url="jdbc:mysql://localhost:3306/test"; String userName="root"; String password="123456"; Connection conn=null; try { Class.forName("com.mysql.jdbc.Driver"); conn = (Connection)DriverManager.getConnection(url, userName, password); conn.setAutoCommit(false); String sql = "insert into user(username,password) values(?,?)"; PreparedStatement prest = (PreparedStatement)conn.prepareStatement(sql); long a=System.currentTimeMillis(); for(int x = 0; x < 10000; x++){ prest.setString(1, "username"+x); prest.setString(2, "password"+x); prest.execute(); } conn.commit(); long b=System.currentTimeMillis(); System.out.println("MySql not batch"+ (b-a)+" ms"); } catch (Exception ex) { ex.printStackTrace(); }finally{ try { if(conn!=null)conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void test_mysql_batch(){ String url="jdbc:mysql://localhost:3306/test"; String userName="root"; String password="123456"; Connection conn=null; try { Class.forName("com.mysql.jdbc.Driver"); conn = (Connection)DriverManager.getConnection(url, userName, password); conn.setAutoCommit(false); String sql = "insert into user(username,password) values(?,?)"; PreparedStatement prest = (PreparedStatement)conn.prepareStatement(sql); long a=System.currentTimeMillis(); for(int x = 0; x < 100000; x++){ prest.setString(1, "username"+x); prest.setString(2, "password"+x); prest.addBatch(); } prest.executeBatch(); conn.commit(); long b=System.currentTimeMillis(); System.out.println("MySql batch"+ (b-a)+" ms"); } catch (Exception ex) { ex.printStackTrace(); }finally{ try { if(conn!=null)conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
打印结果如下
MySql not batch 13712 ms MySql batch 13922 ms
而当MySQL连接参数均加上rewriteBatchedStatements=true时,无论是单条execute执行还是executeBatch执行,均有改善,打印如下
MySql not batch 1470 ms MySql batch 1930 ms
这似乎说明,JDBC的批量操作似乎并没有本质上的效率提高,要想其支持批量操作,还是得从MySQL客户端入手,为何会如此呢?看了一下JDBC源码后发现,addBatch就相当于将sql保存至列表中,executeBatch没有加rewriteBatchedStatements=true参数时,executeBatch还是将列表中的sql一条一条executeUpdate,为何要这样呢?
exechteBatch部分代码如下:
try { clearWarnings(); if (!this.batchHasPlainStatements && this.connection.getRewriteBatchedStatements()) { if (canRewriteAsMultiValueInsertAtSqlLevel()) { return executeBatchedInserts(batchTimeout); } if (this.connection.versionMeetsMinimum(4, 1, 0) && !this.batchHasPlainStatements && this.batchedArgs != null && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) { return executePreparedBatchAsMultiStatement(batchTimeout); } } return executeBatchSerially(batchTimeout); } finally { clearBatch(); }
当连接MySQL的url设置了rewriteBatchedStatements=true参数时,则会返回executeBatchedInserts(batchTimeout);而在此方法中,sql会编译成批量插入的形式处理
insert into user(username,password) values(?,?),(?,?)...
而如果没有加rewriteBatchedStatements=true,则会返回executeBatchSerially(batchTimeout);而在此方法中,是将此列表循环一条一条的executeUpdate,
for (batchCommandIndex = 0; batchCommandIndex < nbrCommands; batchCommandIndex++) { Object arg = this.batchedArgs.get(batchCommandIndex); if (arg instanceof String) { updateCounts[batchCommandIndex] = executeUpdate((String) arg); } else { BatchParams paramArg = (BatchParams) arg; try { updateCounts[batchCommandIndex] = executeUpdate( paramArg.parameterStrings, paramArg.parameterStreams, paramArg.isStream, paramArg.streamLengths, paramArg.isNull, true); ....
这是何用意呢?看来以后用JDBC得多加注意