Executing two Java PreparedStatements with one con

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

If you look at it, you'll see that logic is somewhat different - first version will execute second query even if first query handling will fail. Second version will only proceed to executing second query, if handling first query+results will succeed.

Answer 2

I realise that this post is old, but if you are lucky enough to be running in a Java7 environment- I would suggest using the try-with-resource.

It will handle the connection closing for you- as the new versions of the class implement the Closable interface.

public static void viewTable(Connection con) throws SQLException {

String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";

try (Statement stmt = con.createStatement()) {
    ResultSet rs = stmt.executeQuery(query);

    while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");

        System.out.println(coffeeName + ", " + supplierID + ", " + 
                           price + ", " + sales + ", " + total);
    }
} catch (SQLException e) {
    JDBCTutorialUtilities.printSQLException(e);
}
}

Answer 3

The 2nd one is the way to go. The first one may fail to close the connection when an exception occurs in first try-block. When you just do printing to stderr in the catch-block then not, but you won't do that. Or should the 2nd statement be executed without respect to success/failure of first one?

Answer 4

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.