提高大数据量Sql插入方法(批处理指令)

首先随便建一个user表

 测试最低效的插入方法,直接一条一条的执行

/**
 * @Description:
 * @Author CHEN
 * @Date 2022/11/17 0017
 */
public class test1 {
    private final String url ="jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai";
    private final String user = "root";
    private final String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        String driver = "com.mysql.cj.jdbc.Driver";
        Class.forName(driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void close(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test(){
        long start = System.currentTimeMillis();
        String sql = "insert into user(name,pwd) VALUES (?,222)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= 10000; i++) {
                ps.setObject(1, i);
                ps.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println("一万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
}

此时插入一万条数据的消耗:

 2 加入批处理语句后

/**
 * @Description:
 * @Author CHEN
 * @Date 2022/11/17 0017
 */
public class test1 {
    private final String url ="jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&&rewriteBatchedStatements=true";
    private final String user = "root";
    private final String password = "root";
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public void coon() throws Exception{
        String driver = "com.mysql.cj.jdbc.Driver";
        Class.forName(driver);
        connection = DriverManager.getConnection(url,user,password);
    }
    public void close(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (ps!=null){
                ps.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test(){
        long start = System.currentTimeMillis();
        String sql = "insert into user(name,pwd) VALUES (?,222)";
        try {
            coon();
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= 10000; i++) {
                ps.setObject(1, i);
                ps.addBatch();
                if (i%100==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        System.out.println("一万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }}

此时的处理效率得到大大提高:

 此处注意在使用批处理指令时在连接串后面加上 rewriteBatchedStatements=true,代表允许重写批处理指令提交,以及此时的sql语句一定不能有分号，否则有【BatchUpdateException】异常.

     *      ps.addBatch();      将sql语句打包到一个容器中
     *      ps.executeBatch();  将容器中的sql语句提交
     *      ps.clearBatch();    清空容器，为下一次打包做准备