try with resources - parameterized PreparedStatements

Question

Edit The suggested answer is unacceptable, can't have declaring additional methods for each method I've, imagine?. There must be a nested try with resources solution.

How can you use try statement with a prepare statement taking parameters?

I've the error with following two lines.

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;
}

These two lines of code should be inside the try block. Not in the parentheses preceding the try block. — JB Nizet, Mar 28 '18 at 07:00
The idea of using try-with-resources is to be used when instantiating classes that implement Closeable. Move the code into the try block — Scary Wombat, Mar 28 '18 at 07:01
resultSet and preparedStatements are both closable, I want to utilize automatic closing with try resources, is there a way to use with inner outer try resources? — Developer11, Mar 28 '18 at 07:02
We both told you how to proceed. What is the problem? Did you try doing it? — JB Nizet, Mar 28 '18 at 07:05

score 9 · Accepted Answer · answered Mar 28 '18 at 09:32

Using inner try with resource, after the external try with resource, init the statement and then use the inner try with resource for the 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;
}

try with resources - parameterized PreparedStatements

1 Answers1

Linked