Problem with not closing db connection while debugging?

Question

I have a Java app that opens a connection to a database at the beginning, and closes it at the end. However, the program doesn't always finish, because an exception is thrown or I am debugging it and stop it halfway through.

Will this cause open connections to pile up and slow the database, or will it be cleaned up automatically?

Answer 1

A database Connection is owned and managed by the database, the class just gives you access to that database resource. If you don't close the connection then the Java class may be garbage collected, but the database may not be able to tell that the connection is no longer in use which may result in database resources being wasted (until a timeout on the database side) or even leak.

So, when you're done with using your Connection, you should be sure to explicitly close it by calling its close() method. This will allow the garbage collector to recollect memory as early as possible and, more important, it releases any other database resources (cursors, handles, etc) the connection may be holding on to.

The traditional way to do this in Java is to close your ResultSet, Statement, and Connection (in that order) in a finally block when you are done with them and the safe pattern looks 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.

In your case, this will solve the problem of the exception, but not the debugging one (and you will waste database resources until the timeout occur on the database side). So 1. don't debug your code using a production database 2. try to execute your debug session until the end.

Answer 2

Here's what Sun (err...Oracle?) says:

It is recommended that programmers explicitly close connections and statements they have created when they are no longer needed.

A programmer writing code in the Java programming language and not using any outside resources does not need to worry about memory management. The garbage collector automatically removes objects when they are no longer being used and frees the memory they were using. When memory is running low, it will recycle discarded objects, making the memory they currently occupy available for quick reuse.

However, if an application uses external resources, as it does when it accesses a DBMS with the JDBC API, the garbage collector has no way of knowing the status of those resources. It will still recycle discarded objects, but if there is lots of free memory in the Java heap, it may garbage collect infrequently, even though the (small) amount of Java garbage is holding open large amounts of expensive database resources. Therefore, it is recommended that programmers explicitly close all connections (with the method Connection.close) and statements (with the method Statement.close) as soon as they are no longer needed, thereby freeing DBMS resources as early as possible. This applies especially to applications that are intended to work with different DBMSs because of variations from one DBMS to another.

I would put the database access in a try block and make sure to close all statements and connections in a finally block.