mysql jdbc multi update返回错误的受影响行

Question

    String sql = "update t_test set createDate = now() where id = 1; update t_test set createDate = now() where id = 101;";
    String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false";
    Connection connection = DriverManager.getConnection(url, "username", "password");
    connection.setAutoCommit(false);
    final PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.execute();
    //return 1, but 2 rows actually updated
    System.out.println("updated rows=" + preparedStatement.getUpdateCount());
    connection.commit();
    connection.close();

表t_test包含两列：id和createDate，我在jdbc url中设置了allowMultiQueries=true。在上面的sql中，更新了两行，但preparedStatement.getUpdateCount()返回1。MySQL服务器版本是5.7，我已经使用JDBC5.1.40、6.0.6和8.0.21版本进行了测试，它们都返回相同的结果:1

EDIT：正如@Massimo所说，我反复使用getMoreResults()和getUpdateCount()来获取更新的计数，并对它们求和，直到getUpdateCount()返回-1。

代码如下：

    System.out.println("updated rows=" + preparedStatement.getUpdateCount());
    int totalCount = preparedStatement.getUpdateCount();
    while (true){
        preparedStatement.getMoreResults();
        final int updateCount = preparedStatement.getUpdateCount();
        if(updateCount != -1){
            System.out.println("updated rows=" + preparedStatement.getUpdateCount());
            totalCount += preparedStatement.getUpdateCount();
        }else{
            break;
        }
    }
    System.out.println("total updated rows=" + totalCount);

Answer 1

无法对其进行测试。但是从理论上讲，当您执行多条语句时，preparedStatement会返回每条语句的updateCount。所以它给出了第一条语句的更新计数。要获得这一秒的更新计数，您应该尝试调用preparedStatement.getMoreResults()，然后再次调用preparedStatement.getUpdateCount()。

请参阅文档

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getUpdateCount()

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getMoreResults()