JDBC批量插入数据的操作

https://www.bilibili.com/video/BV1eJ411c7rf/?p=32

建表

create table goods(
	id int primary key auto_increment,
    name varchar(25)
);

批量插入的方式1：循环 statement.execute(sql)

Connection connection = JDBCUtils.getConnection();
Statement statement = connection.createStatement();

for (int i = 1; i <= 20000; i++) {
    String sql = "insert into goods(name) values('name_" + i + "')";
    statement.execute(sql);
}

批量插入的方式2：循环 preparedStatement.execute()

    //批量插入的方式二：使用 PreparedStatement
    @Test
    public void testInsert1() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                preparedStatement.setObject(1, "name_" + i);
                preparedStatement.execute();
            }
            long end = System.currentTimeMillis();
            System.out.printf("花费的时间为：%d\n", end - start);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

花费的时间为：19875

https://www.bilibili.com/video/BV1eJ411c7rf/?p=33

批量插入的方式3：preparedStatement.addBatch()、preparedStatement.executeBatch()、preparedStatement.clearBatch()

jdbc.properties

user=root
password=123456
url=jdbc:mysql://localhost:3306/jdbc_test?rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

    /**
     * 批量插入的方式三：
     * 1.addBatch()、executeBatch()、clearBatch()
     * 2.MySQL 服务器默认是关闭批处理的，我们需要通过一个参数，让 MySQL 开启批处理的支持。
     *  ?rewriteBatchedStatements=true 写在配置文件的 URL 后面
     * 3.使用更新的 MySQL 驱动：>= mysql-connector-java-5.1.37-bin.jar
     */
    @Test
    public void testInsert2() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                preparedStatement.setObject(1, "name_" + i);

                //1.“攒” SQL
                preparedStatement.addBatch();

                if (i % 500 == 0) {
                    //2.执行
                    preparedStatement.executeBatch();

                    //3.清空batch
                    preparedStatement.clearBatch();
                }
            }
            long end = System.currentTimeMillis();
            System.out.printf("花费的时间为：%d\n", end - start);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

清空数据

truncate table goods;

运行验证

花费的时间为：864

i<=100万时，花费的时间约为7秒

批量插入方式4：方式三的基础上 connection.setAutoCommit(false)、connection.commit()

    @Test
    public void testInsert4() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();

            //设置不允许自动提交
            connection.setAutoCommit(false);

            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                preparedStatement.setObject(1, "name_" + i);

                //1.“攒” SQL
                preparedStatement.addBatch();

                if (i % 500 == 0) {
                    //2.执行
                    preparedStatement.executeBatch();

                    //3.清空batch
                    preparedStatement.clearBatch();
                }
            }

            //提交数据
            connection.commit();

            long end = System.currentTimeMillis();
            System.out.printf("花费的时间为：%d\n", end - start);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

花费的时间为：5255

批量插入100万条记录时耗时5秒

https://github.com/jihch/jdbc/blob/main/src/main/java/io/github/jihch/batch/InsertTest.java