Closing Database Connections in Java

Question

I am getting a little confused, I was reading the below from http://en.wikipedia.org/wiki/Java_Database_Connectivity

Connection conn = DriverManager.getConnection(
     "jdbc:somejdbcvendor:other data needed by some jdbc vendor",
     "myLogin",
     "myPassword" );

Statement stmt = conn.createStatement();
try {
    stmt.executeUpdate( "INSERT INTO MyTable( name ) VALUES ( 'my name' ) " );
} finally {
    //It's important to close the statement when you are done with it
    stmt.close();
}

Do you not need to close the conn Connection? What is really happening if the conn.close() doesn't occur?

I have a private web app I'm maintaining that doesn't currently close either form, but is the important one really the stmt one, the conn one, or both?

The site keeps going down intermittently but the server keeps saying it's a database connection issue, my suspicion is that it's not being closed, but I don't know which if any to close.

Answer 1

Yes, you need to close the Connection. Otherwise, the database client will typically keep the socket connection and other resources open.

Answer 2

Yes. You need to close the resultset, the statement and the connection. If the connection has come from a pool, closing it actually sends it back to the pool for reuse.

You typically have to do this in a finally{} block, such that if an exception is thrown, you still get the chance to close this.

Many frameworks will look after this resource allocation/deallocation issue for you. e.g. Spring's JdbcTemplate. Apache DbUtils has methods to look after closing the resultset/statement/connection whether null or not (and catching exceptions upon closing), which may also help.

Answer 3

When you're done with using your Connection, you need to explicitly close it by calling its close() method in order to release any other database resources (cursors, handles, etc) the connection may be holding on to.

Actually, the safe pattern in Java is to close your ResultSet, Statement, and Connection (in that order) in a finally block when you are done with them, something like that:

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

try {
    // Do stuff
    ...

} catch (SQLException ex) {
    // Exception handling stuff
    ...
} finally {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) { /* ignored */}
    }
    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException e) { /* ignored */}
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) { /* ignored */}
    }
}

The finally block can be slightly improved into (to avoid the null check):

} finally {
    try { rs.close(); } catch (Exception e) { /* ignored */ }
    try { ps.close(); } catch (Exception e) { /* ignored */ }
    try { conn.close(); } catch (Exception e) { /* ignored */ }
}

But, still, this is extremely verbose so you generally end up using an helper class to close the objects in null-safe helper methods and the finally block becomes something like that:

} finally {
    DbUtil.closeQuietly(rs);
    DbUtil.closeQuietly(ps);
    DbUtil.closeQuietly(conn);
}

And, actually, the Apache Commons DbUtils has a DbUtils class which is precisely doing that so there is no need to write your own.