JDBC 批量处理（13）

桑鱼

发布于 2020-03-17 17:16:13

7250

发布于 2020-03-17 17:16:13

文章被收录于专栏：学习笔记持续记录中...学习笔记持续记录中...

批量处理JDBC语句提高处理速度

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

2）JDBC的批量处理语句包括下面两个方法： addBatch(String)添加需要批量处理的SQL语句或参数 executeBatch()执行批量处理语句 clearBatch() 清空SQL

3）通常我们会遇到两种批量执行SQL语句的情况：1⃣️多条SQL语句的批量处理、2⃣️一个SQL语句的批量传参

多条SQL语句的批量处理

public class JDBCTest05 {

    // 使用Statement的addBatch()批处理
    public void testBatchWithStatement() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydb";
        String sql = null;
        Connection connection = DriverManager.getConnection(url,"user","password");
        Statement statement = connection.createStatement();
        long begin = System.currentTimeMillis();
        for(int i = 0;i < 100000;i++){
            sql = "INSERT INTO customers VALUES(" + (i + 1)
                    + ", ' name_" + i + "', '2010-Ô1-13')";
            statement.addBatch(sql); // statement的批量处理
        }
        long end = System.currentTimeMillis();
        System.out.println("Time: " + (end - begin));
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
    }

    // PreparedStatement()的executeUpdate分条处理
    public void testBatchWithPreparedStatement() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydb";
        String sql = "INSERT INTO customers VALUES(?,?,?)";
        Connection connection = DriverManager.getConnection(url,"user","password");
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        Date date = new Date(new java.util.Date().getTime());

        long begin = System.currentTimeMillis();
        for(int i = 0;i < 100000;i++){
            preparedStatement.setInt(1,i+1);
            preparedStatement.setString(2,"name_" + i);
            preparedStatement.setDate(3,date);
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("Time: " + (end - begin));
        preparedStatement.close();
        connection.close();
    }
    

    // PreparedStatement()的executeBatch批量处理
    public void testBatch() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydb";
        String sql = "INSERT INTO customers VALUES(?,?,?)";
        Connection connection = DriverManager.getConnection(url,"user","password");
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        Date date = new Date(new java.util.Date().getTime());

        long begin = System.currentTimeMillis();
        for(int i = 0;i < 100000;i++){
            preparedStatement.setInt(1,i+1);
            preparedStatement.setString(2,"name_ " + i);
            preparedStatement.setDate(3,date);
            preparedStatement.addBatch();
            
            if((i + 1) % 300 == 0){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        
        if(100000 % 300 != 0){
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        }

        long end = System.currentTimeMillis();
        System.out.println("Time: " + (end - begin));
        preparedStatement.close();
        connection.close();
    }
}

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

sql