ORACLE JDBC Batch execution doesn't return actual count of affected rows

Question

I'm working on an application where I'm using JDBC and Oracle11.

I have hundreds of thousands of records in my table tbltest which I'm updating via JDBC batch execution. So consider it as one id = one query.

My Requirement: I want to track which ids got successfully updated and which do not exist in db.

Following is my code:

String sql = "UPDATE TBLTEST SET STATUS = 'CANCEL' WHERE ID = ?";
PreparedStatement preparedStatement = null;
Connection connection = getConnection(); // I'm getting this connection properly
preparedStatement = connection.prepareStatement(sql);

for (String id : idList) { // idList is a List of String being passed to my method
    preparedStatement.setString(1, id);
    preparedStatement.addBatch();
}
int[] affectedRecords = preparedStatement.executeBatch();

System.out.println("Records affected:"+Arrays.toString(affectedRecords));
int success = preparedStatement.getUpdateCount();
System.out.println(success + " Total updated");

My records are successfully being updated as per the idList provided. According to this Javadoc, in affectedRecords, I should get actual no. of records updated by each update query because I'm not getting any exception. Instead I get the array affectedRecords filled with -2 only. So if idList contains 5 elements:

Actual output:

Records affected: [-2, -2, -2, -2, -2]
5 Total updated

Expected Output:

Records affected: [1, 1, 1, 1, 1]
5 Total updated

I searched this issue on internet and came across some posts like these: https://community.oracle.com/thread/3691652?start=0&tstart=0 https://community.oracle.com/thread/1046798?tstart=0

But the the solutions provided in those posts also don't help me as I'm already using ojdbc6.jar only.

So is there any solution or alternate to this issue?

Answer 1

I finally found it. The problem was related to PreparedStatement.

According to Javadoc by Oracle here, I cannot get the count of records affected by each query when I use PreparedStatement. So when I converted my code to Statement it worked perfectly.

Reference Note:

Update Counts in the Oracle Implementation of Standard Batching If a statement batch is processed successfully, then the integer array, or update counts array, returned by the statement executeBatch call will always have one element for each operation in the batch. In the Oracle implementation of standard update batching, the values of the array elements are as follows:

For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.

For a generic statement batch, the array contains the actual update counts indicating the number of rows affected by each operation. The actual update counts can be provided only in the case of generic statements in the Oracle implementation of standard batching.

For a callable statement batch, the server always returns the value 1 as the update count, irrespective of the number rows affected by each operation.