2014-07-10

Example Use of Java Try-With-Resource For JDBC

Here is a nice nugget of example code for how to use a JDBC PreparedStatement with the try-with-resource feature in Java. This feature automatically closes resources even if any exceptions are thrown.

Note how for a PreparedStatement we must nest one try-with-resource inside another. Apparently an exception thrown by the inner one will be caught by the outer one.

This example is taken from this answer in StackOverflow. I am posting here for my copy-paste convenience.

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


Trailing Semicolon

And speaking of copy-paste, I can also share a small but important fact: Note the trailing semicolon on the second line of the outer "try". Two statements within the try, each terminated by a semicolon. Early drafts of this feature forbade the trailing semicolon and many folks mistakenly believe that is still the case. But in the final release the Java team realized the convenience of copy-pasting lines without having to remember to remove (or add) the trailing semicolon statement terminator. I suggest always including that optional semicolon.

1 comment: