1. 三种方法的介绍(互为迭代升级的关系)
2. 代码演示
Connection conn = Utils_01.getConnection();
Utils_01.closeResource(conn, ps);
(1) 利用for循环批量插入
@Test
public void insert1() throws Exception {
int cnt = 200000;
long start = new Date().getTime();
Connection conn = Utils_01.getConnection();
String sql = "insert into goods (name) values (?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 1; i <= cnt; i ++ ) {
ps.setObject(1, "name_" + i);
ps.execute();
}
Utils_01.closeResource(conn, ps);
long end = new Date().getTime();
System.out.println(end - start);
}
(2) 开启MySQL的批量插入
@Test
public void insert2() throws Exception {
int cnt = 2000000;
long start = new Date().getTime();
Connection conn = Utils_01.getConnection();
String sql = "insert into goods (name) values (?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 1; i <= cnt; i ++ ) {
ps.setObject(1, "name_" + i);
ps.addBatch();
if (i % 500 == 0 || i == cnt) {
ps.executeBatch();
ps.clearBatch();
}
}
Utils_01.closeResource(conn, ps);
long end = new Date().getTime();
System.out.println(end - start);
}
(3) 取消自动提交
@Test
public void insert_3() throws Exception {
int cnt = 2000000;
long start = new Date().getTime();
Connection conn = Utils_01.getConnection();
conn.setAutoCommit(false);
String sql = "insert into goods (name) values (?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 1; i <= cnt; i ++ ) {
ps.setObject(1, "name_" + i);
ps.addBatch();
if (i % 500 == 0 || i == cnt) {
ps.executeBatch();
ps.clearBatch();
}
}
conn.commit();
Utils_01.closeResource(conn, ps);
long end = new Date().getTime();
System.out.println(end - start);
}