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 {
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(); }
}
我记得mysql批处理默认是关闭的,需要开启 在url上添加
rewriteBatchedStatements=true
在编写批处理代码就很快,我的电脑一百万条数据,几十秒就插入了
Sign in to make a reply
🌙
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();
}
}