原文转自:https://blog.csdn.net/qy20115549/article/details/52699724
目录
使用JDBC连接MySQL数据库进行数据插入的时候,经常会遇到数据量较大,插入数据库较慢,如何进行优化,加快导入数据库的速度,批量处理呢?
以下程序提供了两种方法。
程序结构
连接数据程序
package db;
import java.sql.Connection;
import java.sql.DriverManager;
/*
* 合肥工业大学 管理学院 qianyang 1563178220@qq.com
*/
public class MySQLConnections {
private String driver = "";
private String dbURL = "";
private String user = "";
private String password = "";
private static MySQLConnections connection = null;
private MySQLConnections() throws Exception {
driver = "com.mysql.jdbc.Driver";
dbURL = "jdbc:mysql://127.0.0.1:3306/test";
user = "root";
password = "112233";
System.out.println("dbURL:" + dbURL);
}
public static Connection getConnection() {
Connection conn = null;
if (connection == null) {
try {
connection = new MySQLConnections();
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
try {
Class.forName(connection.driver);
conn = DriverManager.getConnection(connection.dbURL,
connection.user, connection.password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
批量处理的两种方式
第一种方式,是每5000条记录放入数据库,一次。也就是每次提交的记录都有5000条,当然最后一次可能不是。另外,一种方式,是使用默认的提交方式。两者的处理速度都不错。50000条记录,大概15秒左右。
package db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* 合肥工业大学 管理学院 qianyang 1563178220@qq.com
*/
public class MySQLUpdate {
static Connection con = MySQLConnections.getConnection();
static PreparedStatement stmt = null;
public static int executeInsert() throws SQLException {
int i = 0;
//设置批量处理的数量
int batchSize = 5000;
stmt = con.prepareStatement("insert into mysqltest (id,name) "
+ "values (?,?)");
// 关闭事务自动提交 ,这一行必须加上
con.setAutoCommit(false);
for (int j = 0; j < 50005; j++){
++i;
stmt.setInt(1, j);
stmt.setString(2, "name");
stmt.addBatch();
if ( i % batchSize == 0 ) {
stmt.executeBatch();
con.commit();
}
}
if ( i % batchSize != 0 ) {
stmt.executeBatch();
con.commit();
}
return i;
}
public static void executeInsert2() throws SQLException {
// 关闭事务自动提交 ,这一行必须加上
con.setAutoCommit(false);
stmt = con.prepareStatement("insert into mysqltest1 (id,name) "
+ "values (?,?)");
for (int j = 0; j < 50002; j++){
stmt.setInt(1, j);
stmt.setString(2, "name");
stmt.addBatch();
}
stmt.executeBatch();
con.commit();
stmt.close();
con.close();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
main方法
package main;
import java.sql.SQLException;
import db.MySQLUpdate;
/*
* 合肥工业大学 管理学院 qianyang 1563178220@qq.com
*/
public class Test {
public static void main(String[] args) throws SQLException {
// long begin1 = System.currentTimeMillis();
// MySQLUpdate.executeInsert();
// long end1 = System.currentTimeMillis();
// System.out.println("程序运行时间为:"+(end1-begin1));
long begin2 = System.currentTimeMillis();
MySQLUpdate.executeInsert1();
long end2 = System.currentTimeMillis();
System.out.println("程序运行时间为:"+(end2-begin2));
}
}