JDBC批处理

JDBC批处理

说明：

​ 当需要成批插入或者更新记录时，可以采用Java的批量更新机制， 这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率

批处理语句涉及的三个方法：

• addBatch(String):添加需要批量处理的SQL语句或是参数
• executeBatch():执行批量处理语句
• clearBatch():情况缓存的数据

测试代码

package com.imooc.jdbc.sample;

import com.imooc.jdbc.goodapp.common.DBUtils;

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

/**
 * @author CubeMonkey
 * @create 2020-10-12 13:03
 */
public class BatchSample {
    private static void tc1(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            long startTime = new Date().getTime();
            conn = DBUtils.getConnection();
            conn.setAutoCommit(false);//关闭自动提交
            String sql = "insert into employee(eno, ename, salary, dname) values(?, ?, ?, ?)";
            for (int i = 100000; i < 200000; i++){
//                if (i == 1005){
//                    throw new RuntimeException("插入失败");
//                }
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工"+i);
                pstmt.setFloat(3, 4000f);
                pstmt.setString(4, "市场部");
                pstmt.executeUpdate();
            }
            conn.commit();//提交数据
            long endTime = new Date().getTime();
            System.out.println("tc1()执行时长:"+(endTime-startTime));
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null && conn.isClosed() == false){
                    conn.rollback();//回滚数据
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DBUtils.closeConnection(null, pstmt, conn);
        }
    }
    //使用批处理若干数据
    private static void tc2(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            long startTime = new Date().getTime();
            conn = DBUtils.getConnection();
            conn.setAutoCommit(false);//关闭自动提交
            String sql = "insert into employee(eno, ename, salary, dname) values(?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            for (int i = 200000; i < 300000; i++){
                pstmt.setInt(1, i);
                pstmt.setString(2, "员工"+i);
                pstmt.setFloat(3, 4000f);
                pstmt.setString(4, "市场部");
//                pstmt.executeUpdate();
                pstmt.addBatch();//将参数加入批处理任务
            }
            pstmt.executeBatch();//执行批处理任务
            conn.commit();//提交数据
            long endTime = new Date().getTime();
            System.out.println("tc2()执行时长:"+(endTime-startTime));
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (conn != null && conn.isClosed() == false){
                    conn.rollback();//回滚数据
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DBUtils.closeConnection(null, pstmt, conn);
        }
    }
    public static void main(String[] args) {
        tc1();
        tc2();
    }
}

测试结果：