10. 批量插入
10. 批量插入
前言
上一章节，我们使用 PreparedStatement 操作了 BLOB 字段，下面我们再来看看批量插入的操作。
批量插入
1. 批量执行SQL语句
当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
JDBC的批量处理语句包括下面三个方法：
-
addBatch(String)：添加需要批量处理的SQL语句或是参数；
-
executeBatch()：执行批量处理语句；
-
clearBatch(): 清空缓存的数据
通常我们会遇到两种批量执行SQL语句的情况：
-
多条SQL语句的批量处理；
-
一个SQL语句的批量传参；
2. 高效的批量插入
举例：向数据表中插入20000条数据
-
数据库中提供一个goods表。创建如下：
CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
-
测试插入数据
mysql> insert into goods(name) values('test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into goods(name) values('test1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from goods;
+----+-------+
| id | NAME |
+----+-------+
| 1 | test |
| 2 | test1 |
+----+-------+
2 rows in set (0.00 sec)
mysql>
2.1 实现层次一：使用Statement插入20000条数据
//实现层次一：使用Statement
//向数据表中插入20000条数据
@Test
public void test01() throws Exception {
//1.获取连接
Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
//2.执行插入数据
// String sql = "insert into goods(name) values('test1')";
// st.executeUpdate(sql);
//向数据表中插入20000条数据
for (int i = 1; i <= 20000; i++) {
String sql = "insert into goods(name) values('test " + i +"')";
st.executeUpdate(sql);
}
}
测试执行如下：
执行之后的mysql数据：
mysql> select * from goods limit 10;
+----+--------+
| id | NAME |
+----+--------+
| 3 | test1 |
| 4 | test1 |
| 5 | test 1 |
| 6 | test 2 |
| 7 | test 3 |
| 8 | test 4 |
| 9 | test 5 |
| 10 | test 6 |
| 11 | test 7 |
| 12 | test 8 |
+----+--------+
10 rows in set (0.00 sec)
mysql> select count(1) from goods limit 10;
+----------+
| count(1) |
+----------+
| 20002 |
+----------+
1 row in set (0.00 sec)
2.2 实现层次二：使用PreparedStatement插入20000条数据
在上面可以看到耗时挺久的，下面我们改用 PreparedStatement 来插入数据看看。
//实现层次二：使用PreparedStatement插入20000条数据
@Test
public void test02() throws Exception {
//1.记录执行开始时间
long start = System.currentTimeMillis();
//2.获取数据库连接
Connection conn = JDBCUtils.getConnection();
//3.预编译SQL
String sql = "insert into goods(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
//4.设置插入数据以及执行
for (int i = 1; i <= 20000; i++) {
ps.setString(1, "test" + i);
ps.executeUpdate();
}
//5.计算结束时间
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//
//6.关闭资源
JDBCUtils.closeResource(conn, ps);
}
测试执行如下：
2.3 实现层次三：优化效率，将逐条SQL执行 改为 批处理执行
-
配置mysql启用批处理执行 rewriteBatchedStatements=true
user=root
password=*****
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
driverClass=com.mysql.cj.jdbc.Driver
-
测试代码
/**
* 实现层次三：使用PreparedStatement插入20000条数据
* 修改1：使用 addBatch() / executeBatch() / clearBatch()
* 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
* ?rewriteBatchedStatements=true 写在配置文件的url后面
*
*
* @throws Exception
*/
@Test
public void test03() throws Exception {
//1.记录执行开始时间
long start = System.currentTimeMillis();
//2.获取数据库连接
Connection conn = JDBCUtils.getConnection();
//3.预编译SQL
String sql = "insert into goods(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
//4.设置插入数据以及执行
for (int i = 1; i <= 20000; i++) {
ps.setString(1, "test" + i);
//执行批处理
//1.“攒”sql
ps.addBatch();
if(i % 500 == 0){ // 当达到500，则开始批处理
//2.执行
ps.executeBatch();
//3.清空
ps.clearBatch();
}
}
//5.计算结束时间
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//
//6.关闭资源
JDBCUtils.closeResource(conn, ps);
}
执行测试如下：
2.4 实现层次四：优化效率，将批处理的多次提交 设置为 最后统一 commit() 提交
/**
* 实现层次四：优化效率，将批处理的多次提交 设置为 最后统一 commit() 提交
* 使用Connection 的 setAutoCommit(false) / commit()
*
* @throws Exception
*/
@Test
public void test04() throws Exception {
//1.记录执行开始时间
long start = System.currentTimeMillis();
//2.获取数据库连接
Connection conn = JDBCUtils.getConnection();
//1.设置为不自动提交数据
conn.setAutoCommit(false);
//3.预编译SQL
String sql = "insert into goods(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
//4.设置插入数据以及执行
for (int i = 1; i <= 20000; i++) {
ps.setString(1, "test" + i);
//执行批处理
//1.“攒”sql
ps.addBatch();
if(i % 500 == 0){ // 当达到500，则开始批处理
//2.执行
ps.executeBatch();
//3.清空
ps.clearBatch();
}
}
//2.提交数据
conn.commit();
//5.计算结束时间
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//
//6.关闭资源
JDBCUtils.closeResource(conn, ps);
}
测试执行如下：
