java - ORACLE JDBC 批处理执行不返回受影响行的实际计数

Question

我正在开发一个使用 JDBC 和 Oracle11 的应用程序。

我的表 tbltest 中有数十万条记录，我正在通过 JDBC 批处理执行更新这些记录。因此，将其视为一个 id = 一个查询。

我的要求:我想跟踪哪些 id 已成功更新，哪些不存在于数据库中。

以下是我的代码:

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");

我的记录已根据提供的 idList 成功更新。根据this Javadoc，在 affectedRecords 中，我应该得到实际编号。每个更新查询更新的记录数，因为我没有收到任何异常。相反，我得到的数组 affectedRecords 只填充了 -2。所以如果 idList 包含 5 个元素:

实际输出:

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

预期输出:

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

我在互联网上搜索了这个问题，发现了一些像这样的帖子: https://community.oracle.com/thread/3691652?start=0&tstart=0 https://community.oracle.com/thread/1046798?tstart=0

但是这些帖子中提供的解决方案也对我没有帮助，因为我已经只使用了 ojdbc6.jar。

那么这个问题有什么解决方案或替代方案吗？

Answer 1

我终于找到了。问题与 PreparedStatement 有关。

根据 Oracle 的 Javadoc here ，当我使用 PreparedStatement 时，我无法获得受每个查询影响的记录数。因此，当我将代码转换为 Statement 时，它运行良好。

引用说明:

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.