[JAVA] The content of the return value of executeBatch is different between 11g and 12c

Execution environment

The main software versions used in writing this article are as follows. Docker and Oracle Official Docker Image are used to build the Oracle Database.

Event summary

When upgrading the database of Java application from Oracle Database 11c to Oracle Database 12g, the content of the return value (int type array) of PreparedStatement :: executeBatch was slightly different, and the time was infinitely melted, so leave a note. I will leave it.

The source code below uses batch update to insert 3 data into table ʻUSERS`. All connection information to the database will be received as run-time arguments.

Main.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class Main {
    public static void main(String[] args) {
        String url = args[0];
        String user = args[1];
        String password = args[2];

        try (Connection c = DriverManager.getConnection(url, user, password);
             PreparedStatement ps = c.prepareStatement("INSERT INTO USERS (ID, NAME) VALUES(?, ?)")) {
            ps.setInt(1, 1);
            ps.setString(2, "Alice");
            ps.addBatch();

            ps.setInt(1, 2);
            ps.setString(2, "Bob");
            ps.addBatch();

            ps.setInt(1, 3);
            ps.setString(2, "Carol");
            ps.addBatch();

            int[] updateCounts = ps.executeBatch();
            System.out.println(Arrays.toString(updateCounts));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

After compiling this code, first execute it with the connection destination directed to 11g, then change the connection destination to 12c and execute it as follows.

$ java -cp ../lib/ojdbc6.jar:. Main jdbc:oracle:thin:@192.168.99.100:1511:xe user1 password
[-2, -2, -2]
$ java -cp ../lib/ojdbc8.jar:. Main jdbc:oracle:thin:@192.168.99.100:1512/ORCLPDB1 user1 password
[1, 1, 1]

It was confirmed that the contents of ʻupdateCounts, which is the return value of PreparedStatement :: executeBatch`, are different between 11g and 12c.

For 12c, the content is clear, and the number of updates is stored in an int type array. On the other hand, 11g ʻexecuteBatch returns an array containingStatement.SUCCESS_NO_INFO`.

Conclusion: Read the docs properly

In fact, this difference in behavior is well documented in the Oracle Database documentation.

First of all, 11g document:

If the statement batch is processed successfully, the integer array returned by the executeBatch call of the statement, that is, the update count array, always contains one element for each batch operation. In the Oracle implementation of standard batch updates, the values of the array elements are as follows: For batches of precompiled SQL statements, the number of database rows affected by the individual statements contained in the batch is unknown. Therefore, all array element values are -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful, but the number of lines affected is unknown.

On the other hand, the 12c document clearly states that the behavior is different from 11g,

Starting with Oracle Database 12c Release 1 (12.1), the executeBatch method has been improved to return an int array the same size as the number of records in the batch. Each item in the return array is the number of rows in the database table affected by the corresponding record in the batch.

By the way, I haven't actually tried it, but Documentation of Oracle Database 18c Has exactly the same wording as that of 12c above. In other words, it seems that there is no difference in the behavior of PreparedStatement :: executeBatch between 12c and 18c.