配置文件代码:
user=root
password=chen
url=jdbc:mysql://localhost:3306/mn_db03?rewriteBatchedStatements=true
driver=com.mysql.jdbc.Driver
工具类:
package day38.utils;
/**
* @Author:monian
* @Wo yi wu ta,wei shou shu er!
* @Date:2023/11/15 21:16
*/
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtils {
private static String user;
private static String password;
private static String url;
private static String driver;
public JDBCUtils() {
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException var1) {
throw new RuntimeException();
}
}
public static void close(ResultSet set, Statement statement, Connection connection) {
try {
if (set != null) {
set.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException var4) {
throw new RuntimeException(var4);
}
}
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("E:\\桌面\\demo\\Java\\com.hanshunping\\src\\mysql.properties"));
user = properties.getProperty("user");
url = properties.getProperty("url");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
} catch (IOException var1) {
throw new RuntimeException(var1);
}
}
}
未加入批处理,与加入批处理的比比较:
package day39.batch_;
import day38.utils.JDBCUtils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Author:monian
* @Wo yi wu ta,wei shou shu er!
* @Date:2023/11/16 8:24
*/
public class Batch_ {
@SuppressWarnings({"all"})
//使用传统方法,添加5000条记录
@Test
public void noBatch() throws Exception {
//建立连接
Connection connection = null;
//获得预处理语句,执行静态sql语句
PreparedStatement preparedStatement = null;
ResultSet set = null;
String resSQl = "select * from admin";
try {
connection = JDBCUtils.getConnection();
//现在进行插入语句
String insertsql = "insert into admin values(null, ?, ?)";
//循环操作进行多次插入
long start = System.currentTimeMillis();
for (int i = 1; i < 5000; i++) {
preparedStatement = connection.prepareStatement(insertsql);
preparedStatement.setString(1, "王 二" + i);
preparedStatement.setString(2, "wang123");
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("5000条插入数据运行时间:" + (end - start));
preparedStatement = connection.prepareStatement(resSQl);
set = preparedStatement.executeQuery();
//打印数据
while (set.next()) {
String username = set.getString("username");
String password = set.getString("password");
int id = set.getInt("id");
System.out.println(id + "\t" + username + "\t" + password);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(set, preparedStatement, connection);
}
}
//使用Batch方法,添加5000条记录
@Test
public void Batch() throws Exception {
//建立连接
Connection connection = null;
//获得预处理语句,执行静态sql语句
PreparedStatement preparedStatement = null;
ResultSet set = null;
String resSQl = "select * from admin";
try {
connection = JDBCUtils.getConnection();
//现在进行插入语句
String insertsql = "insert into admin values(null, ?, ?)";
//循环操作进行多次插入
long start = System.currentTimeMillis();
for (int i = 1; i < 5000; i++) {
preparedStatement = connection.prepareStatement(insertsql);
preparedStatement.setString(1, "王 二" + i);
preparedStatement.setString(2, "wang123");
preparedStatement.executeUpdate();
//将sql 语句加入到批处理包中 -> 看源码
preparedStatement.addBatch();
//当有1000条记录时,在批量执行
if ((i + 1) % 1000 == 0) {//满1000条sql
preparedStatement.executeBatch();
}
//清除Batch
preparedStatement.clearBatch();
}
long end = System.currentTimeMillis();
System.out.println("5000条插入数据运行时间:" + (end - start));
preparedStatement = connection.prepareStatement(resSQl);
set = preparedStatement.executeQuery();
//打印数据
while (set.next()) {
String username = set.getString("username");
String password = set.getString("password");
int id = set.getInt("id");
System.out.println(id + "\t" + username + "\t" + password);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtils.close(set, preparedStatement, connection);
}
}
}