Which should I close first, the PreparedStatement or the Connection?

Question

When using a PreparedStatement in JDBC, should I close the PreparedStatement first or the Connection first? I just saw a code sample in which the Connection is closed first, but it seems to me more logical to close the PreparedStatement first.

Is there a standard, accepted way to do this? Does it matter? Does closing the Connection also cause the PreparedStatement to be closed, since the PreparedStatement is directly related to the Connection object?

Although according to the spec the statement should be closed when the connection is closed, JDBC drivers have been seen to have issues with this, so it is considered good practice to explicitly close the statement (and the result set). — Yishai, Mar 02 '10 at 15:00
Close things in the inverse order you opened them. All things. — user207421, May 02 '14 at 01:59

Brian Agnew · Accepted Answer · 2013-01-08T10:02:28.077

45

The statement. I would expect you to close (in order)

the result set
the statement
the connection

(and check for nulls along the way!)

i.e. close in reverse order to the opening sequence.

If you use Spring JdbcTemplate (or similar) then that will look after this for you. Alternatively you can use Apache Commons DbUtils and DbUtils.close() or DbUtils.closeQuietly().

edited Jan 08 '13 at 10:02

answered Mar 02 '10 at 14:32

Brian Agnew

268,207
37
334
440

2

Indeed. Some JDBC drivers will throw an exception on closing a result set or statement after the connection is closed. – Yishai Mar 02 '10 at 14:39
14

That's correct. To the point: close the *resources* in **reversed order** as you acquired them. – BalusC Mar 02 '10 at 14:50

score 7 · Answer 2 · answered Mar 02 '10 at 14:40

The following procedures should be done (in order)

The ResultSet
The PreparedStatement
The Connection.

Also, it's advisable to close all JDBC related objects in the finally close to guarantee closure.

//Do the following when dealing with JDBC. This is how I've implemented my JDBC transactions through DAO....

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
  conn = ....
  ps = conn.prepareStatement(...);

  //Populate PreparedStatement
  rs = ps.executeQuery();

} catch (/*All relevant exceptions such as SQLException*/Exception e) {
  logger.error("Damn, stupid exception: " , e);
} finally {
if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                logger.error(e.getMessage(), e.fillInStackTrace());
            }
        }

        if (ps != null) {
            try {
                ps.close();
                ps = null;
            } catch (SQLException e) {
                logger.error(e.getMessage(), e.fillInStackTrace());
            }
        }

        try {
            if (conn!= null && !conn.isClosed()){
                if (!conn.getAutoCommit()) {
                    conn.commit();
                    conn.setAutoCommit(true);
                }
                conn.close();
                conn= null;
            }
        } catch (SQLException sqle) {
            logger.error(sqle.getMessage(), sqle.fillInStackTrace());
        }
}

You can see I've checked if my objects are null and for connection, check first if the connection is not autocommited. Many people fail to check it and realise that the transaction hasn't been committed to DB.

All of that finally boilerplate should be condensed into a utility method (for example `DBUtils.close(rs, ps, conn);`). Also the advice about autocommit depends on the situation. Sometimes when there is an exception you do not want to commit at all. Also, the effort of explicitly setting the reference to null is almost always unneeded, because it will get dereferenced when the method exits, which is hopefully very soon after this, otherwise the method is likely too long. — Yishai, Mar 02 '10 at 14:58
@Yishai, yes, I forgot to mention that if there are exceptions and autocommit is off, you can do a rollback...Thanks for showing this. — Buhake Sindi, Mar 02 '10 at 21:14

Which should I close first, the PreparedStatement or the Connection?

2 Answers2

Linked