Discuss / Java / 为什么批处理时间更慢。。。

为什么批处理时间更慢。。。

Topic source

🌙

#1 Created at ... [Delete] [Delete and Lock User]

JDBC批量操作
*/
public class JDBCBatch {

/**
* JDBC连接的URL, 不同数据库有不同的格式:
*/
final String JDBC_URL = "jdbc:mysql://localhost/jdbc?useSSL=false&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8";
final String JDBC_USER = "root";
final String JDBC_PASSWORD = "root";

List<Student> list = new ArrayList<>();

//模拟插入数据
{
Student student = Student.builder().gender(1).score(100).name("和经济").grade(1).build();
Student student1 = Student.builder().gender(2).score(99).name("分解机").grade(2).build();
list.add(student);
list.add(student1);
for (int i = 0; i < 100; i++) {
list.add(student);
list.add(student1);
}
}

/**
* JDBC批量添加(循环)
*/
@Test
public void test() throws SQLException {

//获取连接
val connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);

//插入sql语句
String sql = "insert into students (grade, name, gender ,score) values(?,?,?,?)";
//创建预编译声明
val preparedStatement = connection.prepareStatement(sql);

long start = System.currentTimeMillis();
//批量插入
for (Student student : list) {
//给查询条件赋值 索引从1开始
preparedStatement.setObject(1, student.getGrade());
preparedStatement.setObject(2, student.getName());
preparedStatement.setObject(3, student.getGender());
preparedStatement.setObject(4, student.getScore());
//执行插入sql语句(不用传参数)
val result = preparedStatement.executeUpdate();
//打印返回数据(插入的记录数量)
System.out.println(result);
}
long end = System.currentTimeMillis();
System.out.println("花费时间:" + (end - start));
preparedStatement.close();
connection.close();
}

/**
* JDBC批量添加(批处理)
*/
@Test
public void test1() throws SQLException {

//获取连接
val connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);

//插入sql语句
String sql = "insert into students (grade, name, gender ,score) values(?,?,?,?)";
//创建预编译声明
val preparedStatement = connection.prepareStatement(sql);

long start = System.currentTimeMillis();
//批量插入
for (Student student : list) {
//给查询条件赋值 索引从1开始
preparedStatement.setObject(1, student.getGrade());
preparedStatement.setObject(2, student.getName());
preparedStatement.setObject(3, student.getGender());
preparedStatement.setObject(4, student.getScore());

// 添加到batch
preparedStatement.addBatch();
}
// 执行batch:
int[] ns = preparedStatement.executeBatch();
for (int n : ns) {
// batch中每个SQL执行的结果数量
System.out.println(n + " inserted.");
}

long end = System.currentTimeMillis();
System.out.println("花费时间:" + (end - start));

preparedStatement.close();
connection.close();
}

}

Joker.fu_95

#2 Created at ... [Delete] [Delete and Lock User]

我记得mysql批处理默认是关闭的,需要开启 在url上添加 

rewriteBatchedStatements=true

在编写批处理代码就很快,我的电脑一百万条数据,几十秒就插入了


  • 1

Reply