JDBC对于数据进行批处理

package com.xmaven;

import com.xmaven.utils.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

/**
 * @ClassName BatchSample
 * @Description TODO JDBC批处理
 * @Author Ambition
 * @Date 2021/2/5 12:22
 * @Version 1.0.0
 **/
public class BatchSample {

    //标准方式插入若干数据
    public static void test1() {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            long startTime = System.currentTimeMillis();
            conn = DbUtils.getConnection();
            // JDBC默认使用自动提交模式
            conn.setAutoCommit(false);
            String sql = "insert into user(id,username,password,salary,dname) values(?,?,?,?,?)";
            for (int i = 100000; i < 200000; i++) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工" + i);
                pstmt.setString(3, "a123456");
                pstmt.setFloat(4, 4000f);
                pstmt.setString(5, "市场部");
                pstmt.executeUpdate();
            }
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println("test1标准插入:" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }


    //使用批处理插入若干数据
    public static void test2() {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            long startTime = System.currentTimeMillis();
            conn = DbUtils.getConnection();
            // JDBC默认使用自动提交模式
            conn.setAutoCommit(false);
            String sql = "insert into user(id,username,password,salary,dname) values(?,?,?,?,?)";
            for (int i = 200000; i < 300000; i++) {
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工" + i);
                pstmt.setString(3, "a123456");
                pstmt.setFloat(4, 4000f);
                pstmt.setString(5, "市场部");
//                pstmt.executeUpdate();
                pstmt.addBatch(); //将参数加入批处理
            }
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println("test2批处理执行时长:" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null, pstmt, conn);
        }
    }


    public static void main(String[] args) {
        test1();
        test2();
    }

}

执行结果:

test1标准插入:19255
test2批处理执行时长:6018

相同10w条数据批处理效果更好