使用JDBC进行批处理机制

配置文件代码： 

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);
        }
    }

}