java mysql 批量插入效率_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 testObjectList = new ArrayList();

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插入快不止一个数量级。