java中 描述try-with-resources的使用?,我应该如何在JDBC中使用try-with-resources？

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

public List getUser(int userId) {

String sql = "SELECT id, name FROM users WHERE id = ?";

List users = new ArrayList();

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, and doesn't improve the readability much. Should I use try-with-resources in another way?

public List getUser(int userId) {

String sql = "SELECT id, name FROM users WHERE id = ?";

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

}

解决方案

I realize this was long ago answered but want to suggest an additional approach that avoids the nested try-with-resources double block.

public List getUser(int userId) {

try (Connection con = DriverManager.getConnection(myConnectionURL);

PreparedStatement ps = createPreparedStatement(con, userId);

ResultSet rs = ps.executeQuery()) {

// process the resultset here, all resources will be cleaned up

} catch (SQLException e) {

e.printStackTrace();

}

}

private PreparedStatement createPreparedStatement(Connection con, int userId) throws SQLException {

String sql = "SELECT id, username FROM users WHERE id = ?";

PreparedStatement ps = con.prepareStatement(sql);

ps.setInt(1, userId);

return ps;

}