Java 1.6 java.sql try/catch/finally crazy coding

Question

I'm currently creating a lot of classes that will access database using a connection pool. So I get a connection, create a statement and get the result set. (I can't use Java 1.7 and the fantastic Automatic Resource Management) When finishing my method I must finish with a finally block:

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqle) {
                logger.error("Couldn't close result set", sqle);
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException sqle) {
                logger.error("Couldn't close statement", sqle);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException sqle) {
                logger.error("Couldn't close connection", sqle);
            }
        }

I'm already seeing the nightmare it will be for XX classes having 4/5 methods each.

Would it be good practice to make an helper class which would got a special close method for each object type like :

public static void closeResource(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException sqle) {
                logger.error("Couldn't close connection", sqle);
            }
        }

And then just doing my usual finally with xx.close(connection);xx.close(statement);xx.close(rs);

Or in the same thinking (I know at this point I'll shock some people as I myself find that a bit ackward), having a method like public static void closeResources(Object... obj) {} with an awful list of instanceof ?

Or in your experience, coding the whole thing everywhere is better ?

Perhaps have a look into using Aspects. You could potentially weave in this functionality using aspectJ. — mcfinnigan, Feb 29 '12 at 16:28
You might be interested in [Difference Between Spring JDBC Vs Plain JDBC?](http://stackoverflow.com/questions/9469643) — Tomasz Nurkiewicz, Feb 29 '12 at 16:29
@TomaszNurkiewicz Thanks for introducing me to Spring JDBC, I won't go for it for this project but I'll keep that in mind. — Michael Laffargue, Feb 29 '12 at 16:42

score 2 · Answer 1 · answered Feb 29 '12 at 16:28

Use overloading.

private void close(ResultSet rSet) throws SQLException {
    if (rSet != null) {
        rSet.close();
    }
}

private void close(Statement statement) throws SQLException {
    if (statement != null) {
        statement.close();
    }
}

private void close(Connection conn) throws SQLException {
    if (conn != null) {
        conn.close();
    }
}

Usage will be much cleaner now:

try {
    // do db stuff
} catch (Exception e) {
    logger.error("log it", e);
} finally {
    close(rs);
    close(cs);
    close(conn);
}

Yes I was thinking about that if I go on the method stuff. Thanks anyway — Michael Laffargue, Feb 29 '12 at 16:34

score 2 · Accepted Answer · answered Feb 29 '12 at 16:30

2

Use Apache commons project : http://commons.apache.org/dbutils/apidocs/org/apache/commons/dbutils/DbUtils.html

DbUtils.closeQuietly() is probably what you need

answered Feb 29 '12 at 16:30

chburd

4,131
28
33

Exactly what I was going to do. Thanks for pointing the lib. I used it a while ago and completely forgot those utilities – Michael Laffargue Feb 29 '12 at 16:49

score 1 · Answer 3 · answered Feb 29 '12 at 16:54

Just one more example. Suitable for simple small projects.

Object doRequest() throws SQLException {

    PreparedStatement ps = ... // initialize statement
    try {
        ResultSet rs = ps.executeQuery();
        try {

           // use ResultSet
           return someResult;

        } finally {
           rs.close();
        } 
    } finally {
        ps.close();
    }

}

Although it is not pretends to be complete solution (many nested try-finally are quite unreadable), there are several advantages:

Method itself not deals with exception handling. Often only caller may decide what to do with exception.
As follows, method always returns correct result or throws exception. No magic "error values" required.
Resources closed only if they were initialized. No need to check for null berode close().

Mister Smith · Answer 4 · 2012-02-29T17:18:24.710

You could also exploit the fact that for every class you want to close, the close method has no args, and make a reflective helper method like this:

    public static final void tryClose(Object o){
        if(o != null){
            Method[] m = o.getClass().getMethods();
            for (Method method : m) {
                if("close".equals(method.getName())){
                    if(!method.isAccessible()) method.setAccessible(true);
                    try {
                        method.invoke(o);
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                    break;
                }
            }
        }       
    }

EDIT: Tested with FileWriter, works fine in my machine.

That's a bit too much for what I was expecting ;). Thanks for sharing though. — Michael Laffargue, Feb 29 '12 at 17:10

Java 1.6 java.sql try/catch/finally crazy coding

4 Answers4