javajdbcjava-7try-with-resources

How should I use try-with-resources with JDBC?


I have a method for getting users from a database with JDBC:

public List<User> getUser(int userId) {
    String sql = "SELECT id, name FROM users WHERE id = ?";
    List<User> users = new ArrayList<User>();
    try {
        Connection con = DriverManager.getConnection(myConnectionURL);
        PreparedStatement ps = con.prepareStatement(sql); 
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();
        while(rs.next()) {
            users.add(new User(rs.getInt("id"), rs.getString("name")));
        }
        rs.close();
        ps.close();
        con.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return users;
}

How should I use Java 7 try-with-resources to improve this code?

I have tried with the code below, but it uses many try blocks, and doesn't improve the readability much. Should I use try-with-resources in another way?

public List<User> getUser(int userId) {
    String sql = "SELECT id, name FROM users WHERE id = ?";
    List<User> users = new ArrayList<>();
    try {
        try (Connection con = DriverManager.getConnection(myConnectionURL);
             PreparedStatement ps = con.prepareStatement(sql);) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery();) {
                while(rs.next()) {
                    users.add(new User(rs.getInt("id"), rs.getString("name")));
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return users;
}

Solution

  • There's no need for the outer try in your example, so you can at least go down from 3 to 2, and also you don't need closing ; at the end of the resource list. The advantage of using two try blocks is that all of your code is present up front so you don't have to refer to a separate method:

    public List<User> getUser(int userId) {
        String sql = "SELECT id, username FROM users WHERE id = ?";
        List<User> users = new ArrayList<>();
        try (Connection con = DriverManager.getConnection(myConnectionURL);
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while(rs.next()) {
                    users.add(new User(rs.getInt("id"), rs.getString("name")));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }