Always. You need to acquire and close them in the shortest possible scope to avoid resource leaking, transactional problems and exhausted connection pools. Not doing so would cause the DB to run out of resources sooner or later, resulting in exceptions like “Too many connections”.
The normal JDBC idiom is the following, whereby all resources are opened and closed in the very same try-with-resources block:
public List<Entity> list() throws SQLException {
List<Entity> entities = new ArrayList<Entity>();
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL_LIST);
ResultSet resultSet = statement.executeQuery();
) {
while (resultSet.next()) {
entities.add(map(resultSet));
}
}
return entities;
}
Or when you’re not on Java 7 yet:
public List<Entity> list() throws SQLException {
List<Entity> entities = new ArrayList<Entity>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = database.getConnection();
statement = connection.prepareStatement(SQL_LIST);
resultSet = statement.executeQuery();
while (resultSet.next()) {
entities.add(map(resultSet));
}
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
return entities;
}
Using PreparedStatement will give you the benefit of the DB caching of the statements (next to SQL injection prevention when used properly). Acquiring and closing the connection is the most expensive task, but there the connection pools are invented for. If you want to reuse the same statement to do bulk inserts/updates, then you can use batches.