CODEWITHSUNDEEP
javajdbc
TCCV
TCCV

Reputation: 3182

Reusing a Connection Object Within a Single Method

I have come across this pattern. Is it OK to reuse a Connection object within a single method when you need multiple SQL statements to execute?

My initial thought is to close all of the resources before moving on:

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    conn = ConnectionFactory.getConnection();
    ps = conn.prepareStatement("SELECT * FROM MYTABLE WHERE COL=?");
    ps.setString(1, "val");
    rs = ps.executeQuery();
    // get values used to determine the next statement type
} catch (SQLException e) {
    LOG.error("SQL failed.", e);
} finally {
    if(rs != null){rs.close();}
    if(ps != null){ps.close();}
    if(conn != null){conn.close();}
}
// Then another SQL statement is needed (either an UPDATE or INSERT).
// Repeat the same pattern to open, use and close the connection

Is it just as safe to do the following? And if it is safe, is there a real benefit?

//... boilerplate
try {
    conn = ConnectionFactory.getConnection();
    ps = conn.prepareStatement("SELECT * FROM MYTABLE WHERE COL=?");
    ps.setString(1, "val");
    rs = ps.executeQuery();
    // ... more

    ps = conn.prepareStatement("UPDATE MYTABLE SET COL=?")
    // ... etc
} finally {
    if(rs != null){rs.close();}
    if(ps != null){ps.close();}
    if(conn != null){conn.close();}
}

Upvotes: 0

Views: 536

Answers (2)

Nathan Hughes
Nathan Hughes

Reputation: 96454

Reusing a connection is not an anti-pattern, it's entirely ok. Reusing the connection is the only way for both statements to get executed within the same local JDBC transaction. If you are writing applications that access relational databases you should learn about transactions.

The way you are implementing your exception handling is error-prone because if an exception is thrown when closing any resource the later resources don't get closed. If closing the preparedStatement throws an exception then the connection doesn't get closed. try-with-resources would be an improvement, but the way try-with-resources handles the edge cases makes me avoid it for JDBC in favor of nested try-finally blocks.

If there is an antipattern here it is using JDBC directly, it is very low-level, involves a lot of cut-and-paste, and doesn't make it easy to use transactions or connection pools. Using Spring would handle details like demarcating database transactions, using connection pools, and closing resources for you.

Upvotes: 1

Andreas
Andreas

Reputation: 159215

What you should do, is use try-with-resources:

//... boilerplate
try (Connection conn = ConnectionFactory.getConnection()) {
    try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM MYTABLE WHERE COL=?")) {
        ps.setString(1, "val");
        try (ResultSet rs = ps.executeQuery()) {
            // ... more
        }
    }

    try (PreparedStatement ps = conn.prepareStatement("UPDATE MYTABLE SET COL=?")) {
        // ... etc
    }
}

Upvotes: 1

Related Questions