把最终测试效率最高的方式记录一下,在mysql5.6下下面这种方式比batchUpdate的方式要快N倍
:
package com.jdbc.batch;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BufferQuery {
private static Connection mysqlConn = null;
private static ResultSet rs = null;
// 总条数
private static int allCount = 10000;
// 分批条数
private static int preCount = 1000;
// 计数器
private static int count = 0;
private static String insertSQL = "insert into users(firstname, lastname, age) values(?, ?, ?)";
private static PreparedStatement mysqlPs = null;
public static void main(String[] args) throws SQLException {
try {
StringBuilder sb = new StringBuilder();
sb.append("insert into users(firstname, lastname, age) values");
mysqlConn = DBUtils.getMySqlConn();
mysqlPs = mysqlConn.prepareStatement(insertSQL);
mysqlConn.setAutoCommit(false);
long start = System.currentTimeMillis();
for (int i = 1; i <= allCount; i++) {
if(i > 1) sb.append(",");
sb.append("('aa"+ i +"','bb',23)");
if(i % preCount == 0){
System.out.println("导入进行===>" + (++count * preCount) + "条");
}
}
mysqlPs.executeUpdate(sb.toString());
long end = System.currentTimeMillis();
System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms");
} catch (Exception e) {
mysqlConn.rollback();
System.out.println("数据出错,已进行回滚");
throw new RuntimeException();
} finally {
mysqlConn.commit();
DBUtils.free(rs, mysqlPs, mysqlConn);
}
}
}