是否可以确定哪些行受 SQL Server UPDATE 影响？

Question

我正在使用 Java 程序更新 SQL Server 2017 数据库。目前，在测试我的UPDATE语句时，我没有提交更改 ( connection.setAutoCommit(false))。

当我打印出受影响的行数时，计数远低于预期（UPDATE 语句应该更新每一行）。

下面是一些示例代码，演示了我用来发出UPDATE语句的循环：

    // List of users that need to be updated

    List<User> updatedUsers = new ArrayList<>();

    // Connect to database

    Connection connection = null;

    connection = DataFiles.getServerConnection();

    connection.setAutoCommit(false);

    PreparedStatement preparedStatement = null;

    int rowsAffected = 0;

    final int batchSize = 1000;

    int count = 0;

    // Create an UPDATE statement for each record to be updated

    for (User user : updatedUsers) {

        StringBuilder sql = new StringBuilder(

                "UPDATE USERS SET\n"

        );

        sql.append("USER_TYPE=?,\n")

                .append("FIRST_NAME=?,\n")

                .append("LAST_NAME=?,\n")

                .append("EMAIL_ADDRESS=?\n");

        sql.append("WHERE USER_ID=?");

        // Fill each ?

        preparedStatement = connection.prepareStatement(sql.toString());

        preparedStatement.setString(1, user.getUserTypeId());

        preparedStatement.setString(2, user.getFirstName());

        preparedStatement.setString(3, user.getLastName());

        preparedStatement.setString(4, user.getEmailAddress());

        preparedStatement.setString(5, user.getUserId());

        preparedStatement.addBatch();

        // Submit in batches of 1000

        if (++count % batchSize == 0) {

            rowsAffected += IntStream.of(preparedStatement.executeBatch()).sum();

            preparedStatement.clearBatch();

        }

    }

    System.out.println(count);

    rowsAffected += IntStream.of(preparedStatement.executeBatch()).sum();

    preparedStatement.clearBatch();

    preparedStatement.close();

    System.out.println(rowsAffected + " rows affected!");

我的WHERE语句应该为循环的每一次传递匹配一条记录，但我得到的33 rows affected不是我期望的 32,000。

有没有办法返回受影响的行？我想将匹配项与不匹配的项进行比较。

我已经尝试将我的循环调整为SELECT具有相同WHERE条件的语句并返回所有 32,000 条记录，因此我确信这些记录确实存在。

Answer 1

您正在循环的每次迭代中创建一个新的 PreparedStatement。您需要在调用 executeBatch 的同一个 PreparedStatement 对象上排队执行批处理。目前您只执行第 1000 个排队查询。StringBuilder sql = new StringBuilder(&nbsp; &nbsp; "UPDATE USERS SET\n");sql.append("USER_TYPE=?,\n")&nbsp; &nbsp; .append("FIRST_NAME=?,\n")&nbsp; &nbsp; .append("LAST_NAME=?,\n")&nbsp; &nbsp;.append("EMAIL_ADDRESS=?\n");sql.append("WHERE USER_ID=?");preparedStatement = connection.prepareStatement(sql.toString());for (User user : updatedUsers) {&nbsp; &nbsp; &nbsp; &nbsp; preparedStatement.setString(1, user.getUserTypeId());&nbsp; &nbsp; &nbsp; &nbsp; preparedStatement.setString(2, user.getFirstName());&nbsp; &nbsp; &nbsp; &nbsp; preparedStatement.setString(3, user.getLastName());&nbsp; &nbsp; &nbsp; &nbsp; preparedStatement.setString(4, user.getEmailAddress());&nbsp; &nbsp; &nbsp; &nbsp; preparedStatement.setString(5, user.getUserId());&nbsp; &nbsp; &nbsp; &nbsp; preparedStatement.addBatch();&nbsp; &nbsp; &nbsp; &nbsp; // Submit in batches of 1000&nbsp; &nbsp; &nbsp; &nbsp; if (++count % batchSize == 0) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rowsAffected += IntStream.of(preparedStatement.executeBatch()).sum();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; preparedStatement.clearBatch();&nbsp; &nbsp; &nbsp; &nbsp; }}为了确定受影响的行（在 sql server 2005+ 中），您可以在 SQL 中使用 OUTPUT 子句让数据库返回受影响的行数据。文章