Java使用JDBC连接数据库逐条插入数据、批量插入数据、以及通过SQL语句批量导入数据的效率对比

测试用的示例java代码：

package com.zifeiy.test.normal;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;import org.apache.commons.exec.ExecuteException;public class Test20181120 {public static void main(String[] args) throws ExecuteException, IOException, ClassNotFoundException, SQLException {// 生成1万条测试数据
List<TestObject> testObjectList = new ArrayList<TestObject>();
for (int i = 0; i < 10000; i ++) {
testObjectList.add(new TestObject());
}// 生成CSV文件
File csvFile = new File("D:\\test.csv");
FileOutputStream fos = new FileOutputStream(csvFile);
OutputStreamWriter osw = new OutputStreamWriter(fos, "UTF-8");
String content = "";
for (TestObject e : testObjectList) {
content += e.toCsvLine();
}
osw.write(content);
osw.flush(); // MySQL依次执行1万条Insert的SQL
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF8&rewriteBatchedStatements=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&useSSL=false", "root", "password");
Statement statement = connection.createStatement(); // drop table的SQL
String dropTableSQL = "drop table if exists test_table";
// create table的SQL
String createTableSQL = "create table test_table ( id integer, name varchar(20), age integer, brief varchar(100) )"; long beginTime, endTime;
// 使用JDBC一次插入
statement.execute(dropTableSQL);
statement.execute(createTableSQL);
beginTime = System.currentTimeMillis();
for (TestObject e : testObjectList) {
statement.execute(e.toInsertSQL());
}
endTime = System.currentTimeMillis();
System.out.println("timer 1 : " + (endTime - beginTime) + " ms"); // 使用JDBC批量插入
statement.execute(dropTableSQL);
statement.execute(createTableSQL);
beginTime = System.currentTimeMillis();
for (TestObject e : testObjectList) {
statement.addBatch(e.toInsertSQL());
}
statement.executeBatch();
endTime = System.currentTimeMillis();
System.out.println("timer 2 : " + (endTime - beginTime) + " ms"); // 使用SQL批量导入CSV文件内容
statement.execute(dropTableSQL);
statement.execute(createTableSQL);
beginTime = System.currentTimeMillis();
statement.execute(
"load data local infile 'd:\\\\test.csv' \r\n" +
"into table testdb.test_table character set utf8\r\n" +
"fields terminated by ',' optionally enclosed by '\"' escaped by '\"' \r\n" +
"lines terminated by '\\r\\n'"
);
statement.executeBatch();
endTime = System.currentTimeMillis();
System.out.println("timer 3 : " + (endTime - beginTime) + " ms");
}static class TestObject {
private Integer id;
private String name;
private Integer age;
private String brief;
public TestObject() {
this.id = (int) ( Math.random() * 1e9);
this.name = (id % 4 == 0) ? "刘德华" : ( (id % 4 == 1) ? "周杰伦" : ( (id % 4 == 2) ? "麦哲伦" : "范晓萱" ) );
this.age = (int) (Math.random() * 100 );
this.brief = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
}
public String toInsertSQL() {
return String.format("insert into test_table (id, name, age, brief) values (%d, '%s', %d, '%s')", id, name, age, brief);
}
public String toCsvLine() {
return String.format("%d,\"%s\",%d,\"%s\"\r\n", id, name, age, brief);
}
}}

其中，我们首先创造了1万条随机数据，然后生成这1万条数据对应的CSV文件，

然后我们通过以下三种方式进行对这1万条数据：

• 使用JDBC逐条插入；
• 使用JDBC批量插入；
• 使用load dataSQL语句导入CSV文件。

执行的结果如下：

结果1

timer 1 : 31417 ms
timer 2 : 27559 ms
timer 3 : 239 ms


结果2

timer 1 : 31428 ms
timer 2 : 28009 ms
timer 3 : 223 ms


结果3

timer 1 : 30779 ms
timer 2 : 30969 ms
timer 3 : 441 ms


可以发现，使用SQL批量导入文本文件的方法明显比JDBC插入快不止一个数量级。