Executing two Java PreparedStatements with one connection - style choice

Question

Okay, I've realized that I really have asked way too many questions without contributing back to the community, but I want your opinions on this. Say if I have

private void closeAll(ResultSet rs, PreparedStatement ps, Connection con) {
    if (rs != null)
        try {
            rs.close();
        } catch (SQLException e) {
        }
    if (ps != null)
        try {
            ps.close();
        } catch (SQLException e) {
        }
    if (con != null)
        try {
            con.close();
        } catch (SQLException e) {
        }
}

and I wanted to do several operations on my MySQL database using a single connection. Is it better to write

Connection con = ...;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    ps = con.prepareStatement(...);
    rs = ps.executeQuery();
    if (rs.next()) ...;
} catch (SQLException e) {
    System.err.println("Error: " + e);
} finally {
    closeAll(rs, ps, null);
}
try {
    ps = con.prepareStatement(...);
    rs = ps.executeQuery();
    if (rs.next()) ...;
} catch (SQLException e) {
    System.err.println("Error: " + e);
} finally {
    closeAll(rs, ps, con);
}

or

Connection con = ...;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    ps = con.prepareStatement(...);
    rs = ps.executeQuery();
    if (rs.next()) ...;
    rs.close();
    ps.close();

    ps = con.prepareStatement(...);
    rs = ps.executeQuery();
    if (rs.next()) ...;
} catch (SQLException e) {
    System.err.println("Error: " + e);
} finally {
    closeAll(rs, ps, con);
}

I consider better to mean either safer, clearer, more concise, or more robust. I'm not sure whether the latter will always close whichever prepared statements and result sets are open whenever it encounters an exception, while I believe it does look more concise. But the former looks nicer since it's more consistent, yet it puts more overhead since it uses more try finally blocks.

I realize that Java 7's automatic resource management part of Project Coin will force me to lean to the former since the resources used in the header are implicitly final in the body. However, I have quite some time before I have to worry about revising my code to adapt it to ARM and be able to remove the boilerplate code, so the question still stands: of the above two styles, which would be better practice? If they both do the expected behaviors, will the latter give me a noticeable performance boost that would excuse the "uglier" style?

Answer 1

It seems to me this is a case of personal preference. In my time I've written code that resembles both blocks. As regards performance I don't think there would be a particularly noticeable difference, only performance tests would tell.

It might be a case that the millisecond or so difference that one version delivers isn't half as important as the ten minutes or so that another person reading your code six months after you've written it would spend asking why.

My personal preference would be the second. You say you're holding a connection open to the database. With the first block of code if you get an exception thrown, that would be handled but then you'd drop down to the second try/catch and try again. You might not want that if the first one failed. With the second, an exception would cause you come out of the code and then close your connection.

I've programmed mainly in C#. It was about eight years ago when I last did any Java. But I think there is and have been plenty of C# programmers who've pondered this. I have at any rate.