Java Database Connectivity (JDBC)

Hi everyone.

As noted in the Oracle Database JDBC Developer’s Guide 12c Release 1 (version 12.1), chapter 21:

Note: Starting from Oracle Database 12c Release 1 (12.1), Oracle update batching is deprecated. Oracle recommends that you use standard JDBC batching instead of Oracle update batching.

We tested the standard JDBC batching using Oracle jdbc driver version 12.1.0.1 (ojdbc7-12.1.0.1.jar) and Oracle Database 11g Enterprise Edition Release version 11.2.0.3.0. The test fails in the asserts regarding the row counts returned by the method java.sql.Statement.executeBatch(), i.e., all the items in the returned array are -2 instead of 1 or 0. We suspect this has to do with the version of the database we are using. Should the test pass if the version 12.1 of the database were used instead? In other words, does the standard JDBC batching require Oracle jdbc driver version 12.1 and Oracle Database 12.1 to return the actual update counts in PreparedStatement.executeBatch()?

Following is the test case we used, based on Junit 4.

Hope I've made myself clear.

public class StandardJDBCBatchingTest {
  static String DB_URL = "jdbc:oracle:thin:@db-server:1530:db-port";
  static String DB_USER = "xxx";
  static String DB_PASSWORD = "yyy";

  @Test
  public void test() throws SQLException {
    String tableName = "ORA_JDBC_BATCHING";
    String createTableSql = "create table " + tableName + "( ID integer, NAME varchar(100) )";
    String dropTableSql = "drop table " + tableName + " cascade constraints";
    String insertSql = "insert into " + tableName + "( ID, NAME ) values ( ?, ? )";
    String updateSql = "update " + tableName + " set NAME=? where ID=?";

    Connection conn = null;
    PreparedStatement insertStmt = null;
    PreparedStatement updateStmt = null;
    try {
      conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
      DatabaseMetaData dbInfo = conn.getMetaData();
      System.out.println("Product version: " + dbInfo.getDatabaseProductVersion());
      conn.setAutoCommit(false);
      try {
        conn.prepareStatement(dropTableSql).executeUpdate();
      } catch (SQLException e) {
        // ignore exception assuming it was thrown because the table doesn't exist
      }
      conn.prepareStatement(createTableSql).executeUpdate();

      insertStmt = conn.prepareStatement(insertSql);

      insertStmt.setLong(1, 1);
      insertStmt.setString(2, "one");
      insertStmt.addBatch();
      insertStmt.setLong(1, 2);
      insertStmt.setString(2, "two");
      insertStmt.addBatch();
      int[] rowCount = insertStmt.executeBatch();
      assertEquals(2, rowCount.length);
      assertEquals(1, rowCount[0]);
      assertEquals(1, rowCount[1]);

      updateStmt = conn.prepareStatement(updateSql);

      updateStmt.setLong(2, 1);
      updateStmt.setString(1, "one.one");
      updateStmt.addBatch();
      updateStmt.setLong(2, 3);
      updateStmt.setString(1, "two.two");
      updateStmt.addBatch();


      rowCount = updateStmt.executeBatch();
      assertEquals(2, rowCount.length);
      assertEquals(1, rowCount[1]);
      assertEquals(0, rowCount[1]);
    } finally {
      if (insertStmt != null)
        insertStmt.close();
      if (updateStmt != null)
        updateStmt.close();
      if (conn != null) {
        conn.commit();
        conn.close();
      }
    }
  }
}