使用try-with-resources/在“finally”子句中关闭此“preparedstatement”[duplicate]

Question

我正在Sonarqube上运行我的JDBC代码。我的代码有问题。

try (final Connection connection = DatabaseConnectionProvider.createConnection(configuration)) {
        PreparedStatement statement = connection.prepareStatement(
                "SELECT 1 FROM `todo_items` WHERE `id` = ? LIMIT 1;");
        statement.setLong(1, id);
        ResultSet resultSet = statement.executeQuery();
        if (!resultSet.next()) {
            return false;
        }
        statement = connection.prepareStatement("UPDATE `todo_items` SET `checked` = ? WHERE id = ?;");
        statement.setBoolean(1, checked);
        statement.setLong(2, id);
        statement.executeUpdate();
        return true;
    }

上面说3号线和9号线有阻塞问题。使用try-with-resources或在“finally”子句中关闭此“PreparedStatement”。我不明白这一点。我不知道如何解决这个问题

Answer 1

您可以使用内部的try-with-resources，因为您在内部有两个不同的prepare语句：

try (final Connection connection = DatabaseConnectionProvider.createConnection(configuration)) {
    try(PreparedStatement statement = connection.prepareStatement("SELECT 1 FROM `todo_items` WHERE `id` = ? LIMIT 1;")) {
        statement.setLong(1, id);
        ResultSet resultSet = statement.executeQuery();
        if (!resultSet.next()) {
            return false;
        }
    }
    try(PreparedStatement statement = connection.prepareStatement("UPDATE `todo_items` SET `checked` = ? WHERE id = ?;")) {
        statement.setBoolean(1, checked);
        statement.setLong(2, id);
        statement.executeUpdate();
        return true;
    }
}

小的优化是可能的（注意，try(.....)中有2个autoclobable:

try (final Connection connection = DatabaseConnectionProvider.createConnection(configuration);
        PreparedStatement statement = connection.prepareStatement("UPDATE `todo_items` SET `checked` = ? WHERE id = ?;")) {
    statement.setBoolean(1, checked);
    statement.setLong(2, id);
    return statement.executeUpdate() > 0;
}

您不必查询DB中是否存在行，因为更新的行数通常由executeUpdate()返回。实际返回值可能依赖于JDBC驱动程序实现（尽管JDBC规范对此非常清楚）。