在此笔记里,我们将看到我们如何可以使用像Statement和PreparedStatement JDBC API来批量在任何数据库中插入数据。此外,我们将努力探索一些场景,如在内存不足时正常运行,以及如何优化批量操作。
首先,使用Java JDBC基本的API批量插入数据到数据库中。
Simple Batch - 简单批处理
我把它叫做简单批处理。要求很简单,执行批量插入列表,而不是为每个INSERT语句每次提交数据库,我们将使用JDBC批处理操作和优化性能。
想想一下下面的代码:
Bad Code
String [] queries = {
"insert into employee (name, city, phone) values ('A', 'X', '123')",
"insert into employee (name, city, phone) values ('B', 'Y', '234')",
"insert into employee (name, city, phone) values ('C', 'Z', '345')",
};
Connection connection = new getConnection();
Statement statemenet = connection.createStatement();
for (String query : queries) {
statemenet.execute(query);
}
statemenet.close();
connection.close();
这是糟糕的代码。它单独执行每个查询,每个INSERT语句的都提交一次数据库。考虑一下,如果你要插入1000条记录呢?这是不是一个好主意。
下面是执行批量插入的基本代码。来看看:
Good Code
Connection connection = new getConnection(); Statement statemenet = connection.createStatement(); for (String query : queries) { statemenet.addBatch(query); } statemenet.executeBatch(); statemenet.close(); connection.close();
请注意我们如何使用addBatch()方法,而不是直接执行查询。然后,加入所有的查询,我们使用statement.executeBatch()方法一次执行他们。没有什么花哨,只是一个简单的批量插入。
请注意,我们已经从一个String数组构建了查询。现在,你可能会想,使其动态化。例如:
import java.sql.Connection; import java.sql.Statement; //... Connection connection = new getConnection(); Statement statemenet = connection.createStatement(); for (Employee employee: employees) { String query = "insert into employee (name, city) values('" + employee.getName() + "','" + employee.getCity + "')"; statemenet.addBatch(query); } statemenet.executeBatch(); statemenet.close(); connection.close();
请注意我们是如何从Employee对象中的数据动态创建查询并在批处理中添加,插入一气呵成。完美!是不是?
等等......你必须思考什么关于SQL注入?这样动态创建的查询SQL注入是很容易的。并且每个插入查询每次都被编译。
为什么不使用PreparedStatement而不是简单的声明。是的,这是个解决方案。下面是SQL注入安全批处理。
SQL Injection Safe Batch - SQL注入安全批处理
思考一下下面代码:
import java.sql.Connection; import java.sql.PreparedStatement; //... String sql = "insert into employee (name, city, phone) values (?, ?, ?)"; Connection connection = new getConnection(); PreparedStatement ps = connection.prepareStatement(sql); for (Employee employee: employees) { ps.setString(1, employee.getName()); ps.setString(2, employee.getCity()); ps.setString(3, employee.getPhone()); ps.addBatch(); } ps.executeBatch(); ps.close(); connection.close();
看看上面的代码。漂亮。我们使用的java.sql.PreparedStatement和在批处理中添加INSERT查询。这是你必须实现批量插入逻辑的解决方案,而不是上述Statement那个。
这一解决方案仍然存在一个问题。考虑这样一个场景,在您想要插入到数据库使用批处理上万条记录。嗯,可能产生的OutOfMemoryError:
java.lang.OutOfMemoryError: Java heap space
com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.<init>(ServerPreparedStatement.java:72)
com.mysql.jdbc.ServerPreparedStatement.addBatch(ServerPreparedStatement.java:330)
org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:171)
这是因为你试图在一个批次添加所有语句,并一次插入。最好的办法是将执行分批次。看看下面的解决方案
Smart Insert: Batch within Batch - 智能插入:将整批分批
这是一个简单的解决方案。考虑批量大小为1000,每1000个查询语句为一批插入提交。
String sql = "insert into employee (name, city, phone) values (?, ?, ?)"; Connection connection = new getConnection(); PreparedStatement ps = connection.prepareStatement(sql); final int batchSize = 1000; int count = 0; for (Employee employee: employees) { ps.setString(1, employee.getName()); ps.setString(2, employee.getCity()); ps.setString(3, employee.getPhone()); ps.addBatch(); if(++count % batchSize == 0) { ps.executeBatch(); } } ps.executeBatch(); // insert remaining records ps.close(); connection.close();
这才是理想的解决方案,它避免了SQL注入和内存不足的问题。看看我们如何递增计数器计数,一旦BATCHSIZE 达到 1000,我们调用executeBatch()提交。
今天在做一个将excel数据导入数据库的程序时,由于数据量大,准备采用jdbc的批量插入。于是用了preparedStatement.addBatch();当加入1w条数据时,再执行插入操作,preparedStatement.executeBatch()。我原以为这样会很快,结果插入65536条数据一共花30多分钟,完全出乎我的意料。于是问了一下同事,他们在处理这种大批量数据导入的时候是如何处理的,发现他们也是用的jdbc批量插入处理,但与我不同是:他们使用了con.setAutoCommit(false);然后再preparedStatement.executeBatch()之后,再执行con.commit();于是再试,什么叫奇迹?就是刚刚导入这些数据花了半小时,而加了这两句话之后,现在只用了15秒钟就完成了。于是去查查了原因,在网上发现了如下一段说明:
* When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that
requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with
SET autocommit and COMMIT statements:
SET autocommit=0;
... SQL import statements ...
COMMIT;
第一次,正是因为没有setAutoCommit(false);那么对于每一条insert语句,都会产生一条log写入磁盘,所以虽然设置了批量插入,但其效果就像单条插入一样,导致插入速度十分缓慢。
部分代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
String sql =
"insert into table *****"
;
con.setAutoCommit(
false
);
ps = con.prepareStatement(sql);
for
(
int
i=
1
; i<
65536
; i++){
ps.addBatch();
// 1w条记录插入一次
if
(i %
10000
==
0
){
ps.executeBatch();
con.commit();
}
}
// 最后插入不足1w条的数据
ps.executeBatch();
con.commit();
|