首先在资源文件jdbc.properties文件中url后添加: ?rewriteBatchedStatements=true 功能为允许MySQL批量插入
测试方法:
@Test
public void testInsert3() throws SQLException {
ConnectionClass connectionClass = new ConnectionClass();
Connection connection = connectionClass.getConnection();
//设置不允许自动提交数据,因为提交数据也会耗费时间,等到最后sql缓存完了一次性提交
connection.setAutoCommit(false);
String sql = "insert into goods(name) values(?)";
/*预编译sql,会被缓存下来,以后每次使用编译后的sql,减少重复编译的时间 比statement快速*/
PreparedStatement preparedStatement =
connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i = 1;i<=1000000;i++){
preparedStatement.setObject(1,"name_"+i);
preparedStatement.addBatch();
if(i % 500 == 0){
/*缓存sql语句,攒到一定量的sql才提交*/
/*最开始为每一句sql都提交一次 非常耗费时间*/
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
/*缓存完了再统一提交*/
connection.commit();
long end = System.currentTimeMillis();
System.out.println(end - start);
}