java - 尝试使用资源 - 参数化的PreparedStatements

Question

这个问题已经有答案了:

How should I use try-with-resources with JDBC? (5 个回答)

已关闭 5 年前。

编辑建议的答案是 Not Acceptable ，想象一下，不能为我的每个方法声明附加方法吗？必须有一个嵌套的资源尝试解决方案。

如何将 try 语句与带有参数的准备语句一起使用？

以下两行出现错误。

preparedStatement.setString(1, uuid);
preparedStatement.setString(2, emailAddress);

Error Declaration final or effectively final variable exepcted

public UserBean getUserDevices(final String emailAddress, final String uuid) throws SQLException {
    UserBean userBean = null;

    String query = "SELECT user.id, user.emailAddress, device.uuid, device.active, device.user_id FROM user " +
            "LEFT JOIN device ON user.id = device.user_id AND device.uuid = ? " +
            "WHERE user.emailAddress = ?";

    try (Connection connection = dataSource.getConnection();
         PreparedStatement preparedStatement = connection.prepareStatement(query);
         preparedStatement.setString(1, uuid);
         preparedStatement.setString(2, emailAddress);
         ResultSet resultSet = preparedStatement.executeQuery();

    ) {
        while(resultSet.next()) {
            if(userBean == null) { // initialize user once while iterating for devices
                userBean = new UserBean(resultSet.getInt("user.id"), resultSet.getString("user.emailAddress"), null);
            }
            if(resultSet.getString("device.uuid") != null) { // if device exists (it's a LEFT JOIN) then add
                userBean.getDevices().add(new DeviceBean(0, resultSet.getString("device.uuid"), resultSet.getBoolean("device.active"), resultSet.getInt("device.user_id")));
            }
        }
    }
    return userBean;
}

Answer 1

使用内部try with resources，在外部try with resource之后，初始化语句，然后使用内部try with resources作为resultSet。

public UserBean getUserDevices(final String emailAddress, final String uuid) throws SQLException {
    UserBean userBean = null;

    String query = "SELECT user.id, user.emailAddress, device.uuid, device.active, device.user_id FROM user " +
            "LEFT JOIN device ON user.id = device.user_id AND device.uuid = ? " +
            "WHERE user.emailAddress = ?";

    try (Connection connection = dataSource.getConnection();
         PreparedStatement preparedStatement = connection.prepareStatement(query);
    ) {
        preparedStatement.setString(1, uuid);
        preparedStatement.setString(2, emailAddress);

        try(ResultSet resultSet = preparedStatement.executeQuery();) {
            while(resultSet.next()) {
                if(userBean == null) { // initialize user once while iterating for devices
                    userBean = new UserBean(resultSet.getInt("user.id"), resultSet.getString("user.emailAddress"), null);
                }
                if(resultSet.getString("device.uuid") != null) { // if device exists (it's a LEFT JOIN) then add
                    userBean.getDevices().add(new DeviceBean(0, resultSet.getString("device.uuid"), resultSet.getBoolean("device.active"), resultSet.getInt("device.user_id")));
                }
            }
        }
    }
    return userBean;
}