Batch processing|V2.4.5|OceanBase Connector/J

Multiple UPDATE or INSERT statements can be grouped into a batch, and the entire batch is sent to the database for processing. This process reduces the number of round trips to the database and improves the application performance.

OceanBase Connector/J uses PrepareStatement and Statements on the server as standards for communicating with the database for batch processing and manages batch processing by using the allowMultiQueries and rewriteBatchedStatements parameters. If allowMultiQueries or rewriteBatchedStatements is set to true, OceanBase Connector/J uses only the text protocol. PrepareStatement is handled by the driver on the client.

In an INSERT query, rewriteBatchedStatements is used to rewrite batchedStatement for execution in a single executeQuery. If you set this parameter set to true, useServerPrepStmts is set to false. When allowMultiQueries is set to true, multi-value queries are allowed.

OceanBase Connector/J provides different batch processing settings in Oracle and MySQL modes.

Batch processing in Oracle mode

PrepareStatement processing

useServerPrepStmts	rewriteBatchedStatements	allowMultiQueries	INSERT operation	UPDATE operation
true	true	true	The Prepare operation is performed again. The number of bracket pairs added after `values` in the SQL string is the same as the number of addBatch operations performed.	The `UPDATE` SQL statements are separated by semicolons (;).
true	true	false	The Prepare operation is performed again. The number of bracket pairs added after `values` in the SQL string is the same as the number of addBatch operations performed.	The `UPDATE` SQL statements are separated by semicolons (;).
true	false	true	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.
true	false	false	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.
false	true	true	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are separated by semicolons (;).
false	true	false	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are separated by semicolons (;).
false	false	true	Multiple `INSERT` statements are executed.	Multiple `UPDATE` statements are executed.
false	false	false	Multiple `INSERT` statements are executed.	Multiple `UPDATE` statements are executed.

Statement processing

useServerPrepStmts	rewriteBatchedStatements	allowMultiQueries	INSERT operation	UPDATE operation
true	true	true	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
true	true	false	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
true	false	true	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.
true	false	false	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.
false	true	true	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
false	true	false	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
false	false	true	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.
false	false	false	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.

Batch processing in MySQL mode

PrepareStatement processing

useServerPrepStmts	rewriteBatchedStatements	allowMultiQueries	INSERT operation	UPDATE operation
true	true	true	The Prepare operation is performed again. The number of bracket pairs added after `values` in the SQL string is the same as the number of addBatch operations performed.	The `UPDATE` SQL statements are separated by semicolons (;).
true	true	false	The Prepare operation is performed again. The number of bracket pairs added after `values` in the SQL string is the same as the number of addBatch operations performed.	The `UPDATE` SQL statements are separated by semicolons (;).
true	false	true	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.
true	false	false	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.	The prepared SQL string is in the normal format, and then parameters are set with multiple `EXECUTE` operations.
false	true	true	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are separated by semicolons (;).
false	true	false	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are separated by semicolons (;).
false	false	true	Multiple `INSERT` statements are executed.	Multiple `UPDATE` statements are executed.
false	false	false	Multiple `INSERT` statements are executed.	Multiple `UPDATE` statements are executed.

Statement processing

useServerPrepStmts	rewriteBatchedStatements	allowMultiQueries	INSERT operation	UPDATE operation
true	true	true	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
true	true	false	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
true	false	true	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.
true	false	false	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.
false	true	true	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
false	true	false	The `INSERT` SQL statements are in the text format and are concatenated by parentheses ().	The `UPDATE` SQL statements are all in the text format and are separated by semicolons (;).
false	false	true	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.
false	false	false	The `INSERT` statements are executed one by one.	The `UPDATE` statements are executed one by one.

Examples

public void test_execute_batch() {
    Connection conn = null;
    try {
        for (int q = 0; q < 2; q++) {
            for (int w = 0; w < 2; w++) {
                for (int e = 0; e < 2; e++) {

                    /**
                     *  useServerPrepStmts = true / false
                     *  rewriteBatchedStatements = true / false
                     *  allowMultiQueries = true / false
                     */
                    boolean uSPS = q == 0 ? false : true;
                    String rBS = w == 0 ? "false" : "true";
                    String aMQ = e == 0 ? "false" : "true";

                    String other_condition = "&rewriteBatchedStatements=" + rBS
                            + "&allowMultiQueries=" + aMQ;
                    conn = ConnectionUtils.getDefObOracleConnection(uSPS, true, other_condition);
                    Statement statement = conn.createStatement();

                    try {
                        statement.execute("DROP TABLE test_batch_t");
                    } catch (SQLException exp) {
                        //e.printStackTrace();
                    }

                    String sql = "create table test_batch_t(id int primary key, c1 varchar2(10))";
                    statement.execute(sql);

                    PreparedStatement ps = conn
                            .prepareStatement("insert into test_batch_t(id, c1) values (?, ?)");

                    for (int j = 0; j < 10; j++) {
                        ps.setInt(1, j);
                        ps.setString(2, j + "_test");
                        ps.addBatch();
                    }
                    ps.executeBatch();

                    ps = conn.prepareStatement("update test_batch_t set id = ? where c1 = ?");

                    for (int j = 0; j < 10; j++) {
                        ps.setInt(1, j);
                        ps.setString(2, j + "_test");
                        ps.addBatch();
                    }
                    ps.executeBatch();
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}