JDBC-Batch-Update

Statement batch update

参考: https://www.ibm.com/docs/en/i/7.1?topic=updates-statement-batch-update

To perform a Statement batch update, you must turn off auto-commit. In Java™ Database Connectivity (JDBC), auto-commit is on by default. Auto-commit means any updates to the database are committed after each SQL statement is processed. If you want to treat a group of statements being handed to the database as one functional group, you do not want the database committing each statement individually. If you do not turn off auto-commit and a statement in the middle of the batch fails, you cannot roll back the entire batch and try it again because half of the statements have been made final. Further, the additional work of committing each statement in a batch creates a lot of overhead.

See JDBC transactions for more details.

After turning off auto-commit, you can create a standard Statement object. Instead of processing statements with methods such as executeUpdate, you add them to the batch with the addBatch method. Once you have added all the statements you want to the batch, you can process all of them with the executeBatch method. You can empty the batch at anytime with the clearBatch method.

The following example shows how you can use these methods:

Example: Statement batch update

demo地址：https://gitee.com/ipwwp/jdbc-best-practise/tree/master/native-jdbc

package com.wp;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcBatchTest {

    private ObjectMapper objectMapper = new ObjectMapper();

    public static void main(String[] args) throws SQLException, ClassNotFoundException, JsonProcessingException {
        JdbcBatchTest jdbcBatchTest = new JdbcBatchTest();
        Connection conn = jdbcBatchTest.getConn();
        jdbcBatchTest.statementBatch(conn);
    }

    private Connection getConn() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://49.4.13.102:3306/test_wp?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8",
                "root",
                "password");
        return con;
    }

    public void statementBatch(Connection connection) throws SQLException, JsonProcessingException {
        connection.setAutoCommit(false);
        Statement statement = connection.createStatement();
        statement.addBatch("INSERT INTO user VALUES ('011','批量一',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");
        statement.addBatch("INSERT INTO user VALUES ('012','批量二',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");
        statement.addBatch("INSERT INTO user VALUES ('013','批量三',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");

        int [] updateCounts = statement.executeBatch();
        System.out.println(objectMapper.writeValueAsString(updateCounts));
        connection.commit();

    }

}

输出

[1,1,1]

In this example, an array of integers is returned from the executeBatch method. This array has one integer value for each statement that is processed in the batch. If values are being inserted into the database, the value for each statement is 1 (that is, assuming successful processing). However, some of the statements may be update statements that affect multiple rows. If you put any statements in the batch other than INSERT, UPDATE, or DELETE, an exception occurs.

PreparedStatement batch update

参考: https://www.ibm.com/docs/en/i/7.1?topic=updates-preparedstatement-batch-update

A preparedStatement batch is similar to the Statement batch; however, a preparedStatement batch always works off the same prepared statement, and you only change the parameters to that statement.

The following is an example that uses a preparedStatement batch.

connection.setAutoCommit(false);
PreparedStatement statement = 
  connection.prepareStatement("INSERT INTO TABLEX VALUES(?, ?)");
statement.setInt(1, 1);
statement.setString(2, "Cujo");
statement.addBatch();
statement.setInt(1, 2);
statement.setString(2, "Fred");
statement.addBatch();
statement.setInt(1, 3);
statement.setString(2, "Mark");
statement.addBatch();
int [] counts = statement.executeBatch();
connection.commit();

JDBC BatchUpdateException

参考: https://www.ibm.com/docs/en/i/7.1?topic=updates-jdbc-batchupdateexception

An important consideration of batch updates is what action to take when a call to the executeBatch method fails. In this case, a new type of exception, called BatchUpdateException, is thrown. The BatchUpdateException is a subclass of SQLException and it allows you to call all the same methods you have always called to receive the message, the SQLState, and vendor code.

BatchUpdateException also provides the getUpdateCounts method that returns an integer array. The integer array contains update counts from all the statements in the batch that were processed up to the point where the failure occurred. The array length tells you which statement in the batch failed. For example, if the array returned in the exception has a length of three, the fourth statement in the batch failed. Therefore, from the single BatchUpdateException object that is returned, you can determine the update counts for all the statements that were successful, which statement failed, and all the information about the failure.

The standard performance of processing batched updates is equivalent to the performance of processing each statement independently. You can refer to Blocked insert support for more information about optimized support for batch updates. You should still use the new model when coding and take advantage of future performance optimizations.

• Note: In the JDBC 2.1 specification, a different option is provided for how exception conditions for batch updates are handled. JDBC 2.1 introduces a model where the processing batch continues after a batch entry fails. A special update count is placed in the array of update count integers that is returned for each entry that fails. This allows large batches to continue processing even though one of their entries fails. See the JDBC 2.1 or JDBC 3.0 specification for details on these two modes of operation. By default, the native JDBC driver uses the JDBC 2.0 definition. The driver provides a Connection property that is used when using DriverManager to establish connections. The driver also provides a DataSource property that is used when using DataSources to establish connections. These properties allow applications to choose how they want batch operations to handle failures.

因为，我们当前使用的是，mysql 8.0.17，驱动也是8，所以，对应的应该是 JDBC 4.0版本

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.27</version>
</dependency>

这也就意味着，假设有3个statement，即使第二个statement出现fail，第三个statement仍然可以正常执行。以下是demo：

demo地址：https://gitee.com/ipwwp/jdbc-best-practise/tree/master/native-jdbc

public void statementBatch(Connection connection) throws JsonProcessingException, SQLException {
        connection.setAutoCommit(false);
        try {
            Statement statement = connection.createStatement();
            statement.addBatch("INSERT INTO user VALUES ('011','批量一',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");
            statement.addBatch("INSERT INTO aaa VALUES ('012','批量二',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");
            statement.addBatch("INSERT INTO user VALUES ('013','批量三',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");

            int[] updateCounts = statement.executeBatch();
            System.out.println(objectMapper.writeValueAsString(updateCounts));
        } catch (BatchUpdateException batchUpdateException) {
            printBatchUpdateException(batchUpdateException);
        }
        connection.commit();

    }

    public void printBatchUpdateException(BatchUpdateException b) {
        System.err.println("----BatchUpdateException----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
    }

输出

----BatchUpdateException----
SQLState:  42S02
Message:  Table 'test_wp.aaa' doesn't exist
Vendor:  1146
Update counts:  1   -3   1  

我们可以看到，updateCount中，返回了1 -3 1，表示第二个statement执行fail了，但是第三个statement仍然可以正常执行，并且成功了。

接下来，我们去数据库中查询，发现批量一 和 批量三 的确插入到了数据库中。批量二，没有插入到数据库中。

但是，假如，我们想实现另一种效果，3个statement中，只要有任何一个statement fail了，那么所有的statement都不执行。这个效果怎么实现呢？

其实很简单，我们只需要添加一行代码即可

public void statementBatch(Connection connection) throws JsonProcessingException, SQLException {
        connection.setAutoCommit(false);
        try {
            Statement statement = connection.createStatement();
            statement.addBatch("INSERT INTO user VALUES ('011','批量一',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");
            statement.addBatch("INSERT INTO aaa VALUES ('012','批量二',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");
            statement.addBatch("INSERT INTO user VALUES ('013','批量三',20,'111',1,'2022-12-07 15:37:27.376','2022-12-07 15:37:27.376')");

            int[] updateCounts = statement.executeBatch();
            System.out.println(objectMapper.writeValueAsString(updateCounts));
            connection.commit();
        } catch (BatchUpdateException batchUpdateException) {
            connection.rollback();
            printBatchUpdateException(batchUpdateException);
        }

    }

    public void printBatchUpdateException(BatchUpdateException b) {
        System.err.println("----BatchUpdateException----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
    }

可以看到，我们在catch中，添加了一行connection.rollback(); 。这个表示，将事务回滚，也即3个statement都fail了。

接下来，我们说个题外话，第2个statement 执行fail了，为什么返回的是-3呢？
其实，这个是jdbc定义的，一个special update count,是一个常量，位置是java.sql.Statement#EXECUTE_FAILED

    /**
     * The constant indicating that an error occurred while executing a
     * batch statement.
     *
     * @since 1.4
     */
    int EXECUTE_FAILED = -3;

关于JDBC BatchUpdateException，下面，还提供了，其他的参考资料。

Handling Failures during Execution
A JDBC driver may or may not continue processing the remaining commands in a batch once execution of a command fails. However, a JDBC driver must always provide the same behavior with a particular data source. For example, a driver cannot continue processing after a failure for one batch and not continue processing for another batch.

If a driver stops processing after the first failure, the array returned by the method BatchUpdateException.getUpdateCounts will always contain fewer entries than there were statements in the batch. Since statements are executed in the order that they are added to the batch, if the array contains N elements, this means that the first N elements in the batch were processed successfully when executeBatch was called.

JDBC 4.2 Specification • March 2014

When a driver continues processing in the presence of failures, the number of elements in the array returned by the method BatchUpdateException.getUpdateCounts always equals the number of
commands in the batch. When a BatchUpdateException object is thrown and the driver continues processing after a failure, the array of update counts will contain the following BatchUpdateException constant:
I Statement.EXECUTE_FAILED — the command failed to execute successfully.

This value is also returned for commands that could not be processed for some reason—such commands fail implicitly.

JDBC drivers that do not continue processing after a failure never return Statement.EXECUTE_FAILED in an update count array. Drivers of this type simply return a status array containing an entry for each command that was processed successfully.

A JDBC technology-based application can distinguish a JDBC driver that continues processing after a failure from one that does not by examining the size of the array returned by BatchUpdateException.getUpdateCounts. A JDBC driver that continues processing always returns an array containing one entry for each element in the batch. A JDBC driver that does not continue processing after a failure will always return an array whose number of entries is less than the number of commands in the batch.

Note – If your database supports returning an update count that may exceed Integer.MAX_VALUE, use the method BatchUpdateException.getLargeUpdateCounts.