JDBC的批处理

JDBC使用MySQL处理大数据的时候,自然而然的想到要使用批处理，

普通的执行过程是：每处理一条数据，就访问一次数据库；

而批处理是：累积到一定数量，再一次性提交到数据库，减少了与数据库的交互次数，所以效率会大大提高。

注意在mysql中:数据库连接中的参数rewriteBatchedStatements相当重要，如果不开启rewriteBatchedStatements=true，那么jdbc会把批量插入当做一行行的单条处理，也即没有达到批量插入的效果。该值在mysql中的默认值是false，mysql8.0版本。其中还有一个属性allowMultiQueries，该属性是在查询的时候，允许在一个查询语句中，使用';'号来分隔多个查询，但是要注意，它不影响addBatch()和executeBatch()方法，这两个方法依赖于rewritebatchstatement属性。

mysql连接参数的文档：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

1.JDBC实现批处理，预编译和非预编译

1、使用Statement对象添加要批量执行SQL语句，如下：

Statement.addBatch(sql1);
Statement.addBatch(sql2);
Statement.addBatch(sql3);
Statement.executeBatch();
Statement.clearBatch();

优点：可以向数据库发送多条不同的ＳＱＬ语句。
缺点：SQL语句没有预编译，当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。

2.使用PrepareStatement

    @Test
    public void testJdbcBatchHandleByPrepareStatement(){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        Instant start = Instant.now();
        try{
            conn = JdbcUtil.getConnection();
            String sql = "insert into testbatch(id,name) values(?,?)";
            st = conn.prepareStatement(sql);
            for(int i=1;i<10000;i++){
                st.setInt(1, i);
                st.setString(2, UUID.randomUUID().toString());
                st.addBatch();
                // 这种主要是为了避免内存不足
                // if(i%1000==0){
                //    st.executeBatch();
                //    st.clearBatch();
                // }
            }
            st.executeBatch();
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.release(conn, st, rs);
        }
        Instant end = Instant.now();
        System.out.println("花费时间:"+ Duration.between(start, end).toMillis());
    }

优点：发送的是预编译后的SQL语句，执行效率高。
缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

2.PreparedStatement不使用批处理

PreparedStatement preparedStatement = conn.prepareStatement("insert into t(name,price,score) values(?,?,?)");

for(int i=0; i<10000; i++){
    preparedStatement.setString(1, randomStr());
    preparedStatement.setDouble(2, randomPrice());
    preparedStatement.setInt(3, randomScore());
    pstmt.execute();
}

最终用时28秒

3.prepareStatement使用批处理

PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i=0;i<10000;i++){
    preparedStatement.setString(1, randomStr());
    preparedStatement.setDouble(2, randomPrice());
    preparedStatement.setInt(3, randomScore());
    preparedStatement.addBatch();
}
preparedStatement.executeBatch();
preparedStatement.clearBatch();

最终用时0.3秒，效率差距啊。

4.prepareStatement开启事务，不开启批处理

connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i=0;i<10000;i++){
    preparedStatement.setString(1, randomStr());
    preparedStatement.setDouble(2, randomPrice());
    preparedStatement.setInt(3, randomScore());
    preparedStatement.execute();
}
connection.commit();

最终用时3秒，还不错。

5.prepareStatement开启事务，开启批处理

connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i=0;i<10000;i++){
      preparedStatement.setString(1, randomStr());
      preparedStatement.setDouble(2, randomPrice());
      preparedStatement.setInt(3, randomScore());
      preparedStatement.addBatch();
}
preparedStatement.executeBatch();
connection.commit();
preparedStatement.clearBatch();

最终用时0.2秒，终极方法。 所以建议在处理大批量的数据时，同时使用批处理和事务。