使用批处理之前
向表中添加5000条数据所花的时间
public void noBatch() throws Exception{
Connection connection= JDBCUtils.getConnection();
String sql="insert into admin2 values(null,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//添加5000条记录
System.out.println("开始执行");
long start=System.currentTimeMillis();
for(int i=1;i<=5000;i++){
preparedStatement.setString(1,"jack"+i);
preparedStatement.setString(2,"666");
preparedStatement.executeUpdate();
}
long end=System.currentTimeMillis();
System.out.println("耗时="+(end-start));//耗时=7208
//关闭连接
JDBCUtils.close(null, preparedStatement,connection);
}
使用批处理之后
public void batch() throws Exception{
Connection connection= JDBCUtils.getConnection();
String sql="insert into admin2 values(null,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//添加5000条记录
System.out.println("开始执行");
long start=System.currentTimeMillis();
for(int i=1;i<=5000;i++){
preparedStatement.setString(1,"jack"+i);
preparedStatement.setString(2,"666");
//将sql语句加入到批处理包中
preparedStatement.addBatch();
//当有1000条记录时再批量执行
if(i%1000==0){
preparedStatement.executeBatch();
//清空
preparedStatement.clearBatch();
}
}
long end=System.currentTimeMillis();
System.out.println("批量处理耗时="+(end-start));//批量处理耗时=70
//关闭连接
JDBCUtils.close(null, preparedStatement,connection);
}
PS:需要修改properties
在url后加上?rewriteBatchedStatements=true
如图所示