批处理Batch-526互联

package com.jdbc.batch;

import com.JDBC_Utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Batch {
    @Test
    //传统加入数据方法
    public void noBatch() {
        Connection connection = null;
        String sql = "insert  into test values (?,?)";
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //开始时间
            System.out.println("开始执行~");
            long start = System.currentTimeMillis();
            for (int i = 1; i <= 5000; i++) {
                preparedStatement.setObject(1, "Mike" + i);
                preparedStatement.setObject(2, i);
                preparedStatement.executeUpdate();
            }
            //结束时间
            long end = System.currentTimeMillis();
            System.out.println("传统方式耗费时间: " + (end - start)); //277702毫秒
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(null, preparedStatement, connection);
        }
    }

    @Test
    public void useBatch() {
        使用批处理加入数据
        Connection connection = null;
        String sql = "insert  into test values (?,?)";
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();
            //取消连接的自动提交模式
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            //开始时间
            System.out.println("开始执行~");
            long start = System.currentTimeMillis();
            for (int i = 1; i <= 5000; i++) {
                preparedStatement.setObject(1, "Mike" + i);
                preparedStatement.setObject(2, i);
                preparedStatement.executeUpdate();
                //将sql语句 加入到 批处理包中
                preparedStatement.addBatch();
                //每有1000条数据， 就批量执行sql语句
                if (i % 1000 == 0) {
                    preparedStatement.executeBatch();
                    //装满1000之后就清空，准备装 下一波 的 1000个
                    preparedStatement.clearBatch();
                }
            }
            //结束时间
            long end = System.currentTimeMillis();
            System.out.println("批量方式耗费时间: " + (end - start));
            //提交事务  (不提交事务,则不会显示批量加入的数据)
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(null, preparedStatement, connection);
        }
    }
}