Close connection and statement finally

Question

Which is better for finally block:

finally {
        try {
            con.close();
            stat.close();
        } catch (SQLException sqlee) {
            sqlee.printStackTrace();
        }
    }

Or:

finally {
        try {
            if (con != null) {
                con.close();
            }
            if (stat != null) {
                stat.close();
            }
        } catch (SQLException sqlee) {
            sqlee.printStackTrace();
        }
    }

Answer 1

None of them are good enough. Use this:

public static void closeQuietly(AutoCloseable ... closeables) {
    for (AutoCloseable c : closeables) {
        if (c != null) {
            try {
                c.close();
            } catch (Exception e) {
                // log or ignore, we can't do anything about it really
            }
        }
    }
}

And call it like closeQuietly(stat, con);

Or use java 7's try-with-resource:

    List<String> results = new ArrayList<>();
    try (Statement statement = conn.createStatement();
         ResultSet rs = statement.executeQuery(query)) {

        int numberOfColumns = getColumnCount(rs);
        while (rs.next()) {
            int i = 1;
            while (i <= numberOfColumns) {
                results.add(rs.getString(i++));
            }
        }
    }

Answer 2

If there is a possibility either is null, you must check that. If the possibility does not exist, there is no valid reason to check for it.

Also, you can make your code slightly better readable by omitting some single-statement brackets:

finally {
    try {
        if (con != null)
            con.close();

        if (stat != null)
            stat.close();

    } catch (SQLException sqlee) {
        sqlee.printStackTrace();
    }
}

Answer 3

I would go with the second option, but adding a second nested finally block, just to make sure that both con and stat objects are marked for garbage collection:

finally {
    try {
        if(con != null)
            con.close();
        if(stat != null)
            stat.close();
    } catch(SQLException sqlee) {
        sqlee.printStackTrace();
    } finally {  // Just to make sure that both con and stat are "garbage collected"
        con = null;
        stat = null;
    }
}

Answer 4

As of Java 7, you don't need any more use the finallyl block to close a Connection or Statement object. Instead you can make use of the new features called 'try-with-resources'.

First you declare a Connection and Statament objects by using the new syntax for a try-catch block as follows:

try(Connection con =  DriverManager.getConnection(database-url, user, password); Statement st = conn.createStatement()) {

 //your stuffs here
} catch (SQLException e) {
   e.printStackTrace();
}    

Doing so, you won't need to worry to close explicitly the linkage with the database in a finally block because the jvm will do it for you.

Have nice coding....

Answer 5

Better way to use is the 2nd one, because if an exception is thrown while initializing con or stat, they won't be initialized, and might be left initialized to null. In that case, using the 1st code will throw NullPointerException.

Also, if you are already on Java 7, you should consider using try-with-resources, which automatically closes the resources. From the linked tutorial:

The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.