If i have the following code would this be the correct way of closing the Connection, ResultSet, and Statement objects? I feel like all of the calls to close() should be in a finally block.
Connection con = null;
ResultSet rs = null;
Statement stmt = null;
try{
//Code before the while loop
con = DriveManager.getConnection("Stuff");
while(someBoolean){
stmt = con.createStatement();
rs = stmt.executeQuery("SQL query");
// do stuff with query results.
if( rs != null){
rs.close();
}
if( stmt != null){
stmt.close();
}
} //end while
if( con != null ){
con.close();
}
catch (Exception e){
//handle exception
}
You don't need to create the Statement in the loop: you can re-use it. Also with Java 7 try-with-resources (tutorial) you don't need to take care of the tedium of closing in the right order, etc.
You can do that like this:
try (
Connection con = DriverManager.getConnection("Stuff");
Statement stmt = con.createStatement();
){
while(someBoolean){
try (ResultSet rs = stmt.executeQuery("SQL query")) {
// do stuff with query results.
}
} //end while
} catch (Exception e){
//handle exception
}
As you can see it requires a lot less checks and statements than managing it yourself, while it ensures closing the resources in the right order, even if exceptions occur (even if that exception occurs when closing one of the other resources). For example your old code failed to close resources if any exception occurred.
Yes, closing resources should be in a finally block, because you should close all resources no matter where an exception might be thrown.
The standard pattern is:
Connection con = null;
ResultSet rs = null;
Statement stmt = null;
try {
con = DriveManager.getConnection("Stuff");
stmt = con.createStatement();
rs = stmt.executeQuery("SQL query");
// do stuff with query results
} catch (SQLException e) { // Don't catch Exception, catch what you expect
// handle exception
} finally {
// each close can itself explode, so wrap each in try catch
try {
if (rs != null)
rs.close();
} catch (SQLException ignore) {} // no point handling
try {
if (stmt != null)
stmt.close();
} catch (SQLException ignore) {} // no point handling
try {
if (con != null)
con.close();
} catch (SQLException ignore) {} // no point handling
}
Although a failure to close one of the resources probably means the others will explode too, it;'s still good practice to attempt to close each, hence the individual try-catches inside the finally block.
In Java 7 , you can use try-with-resource statement :
try(Connection con = getConnection(url, username, password, "drivername");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
//statements
}catch(....){}
In Java 6 , you can close the resources in finally block :
} finally {
try { rs.close(); } catch (Exception e) { }
try { ps.close(); } catch (Exception e) { }
try { conn.close(); } catch (Exception e) { }
}
You can even use helper class to close the connection . Apache Commons DbUtils has a DbUtils class.
} finally {
DbUtil.closeQuietly(rs);
DbUtil.closeQuietly(ps);
DbUtil.closeQuietly(conn);
}
I suggest having nested try/catch blocks, one for each resource:
final Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser,
jdbcPassword);
try {
final Statement stmnt = conn.createStatement();
try {
final ResultSet rs = stmnt
.executeQuery("select * from pg_user");
try {
while (rs.next()) {
System.out.println(rs.getObject(1));
}
} finally {
rs.close();
}
} finally {
stmnt.close();
}
} finally {
conn.close();
}
There is only one single line in each finally clause. Otherwise you have to wrap it in another try/catch, so that possible exceptions are handled correctly in a finally block.
Generally speaking, this pattern (try/catch - nested or not) to manage resources clutters up the code. One method to get rid of is to manage your resources centrally, and use a listener/reader.
So in a library you define a Reader and a with... method:
interface ResultSetReader {
void read(ResultSet rs) throws SQLException;
}
public static void withResultSet(final String query,
final ResultSetReader reader) throws Exception {
final Connection conn = getConnection();
try {
final Statement stmnt = conn.createStatement();
try {
final ResultSet rs = stmnt.executeQuery(query);
try {
while (rs.next()) {
reader.read(rs);
}
} finally {
rs.close();
}
} finally {
stmnt.close();
}
} finally {
conn.close();
}
}
to be used with an anonymous class at the calling site like this
withResultSet("select * from pg_user", new ResultSetReader() {
@Override
public void read(ResultSet rs) throws SQLException {
System.out.println(rs.getObject(1));
}
});
This way there is no try/catch at the usage site, and nobody forgets to to close resources (and nobody closes resources incorrectly).
If Java 8 is available, the ResultSetReader interface could by replaced by a closure.
