Closing database connections in Java

Question

I am getting a little confused. I was reading the below from 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 application 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

When you are 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 this:

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 this:

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

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

Answer 2

It is always better to close the database/resource objects after usage. Better close the connection, resultset and statement objects in the finally block.

Until Java 7, all these resources need to be closed using a finally block. If you are using Java 7, then for closing the resources, you can do as follows.

try(Connection con = getConnection(url, username, password, "org.postgresql.Driver");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
) {

    // Statements
}
catch(....){}

Now, the con, stmt and rs objects become part of try block and Java automatically closes these resources after use.

Answer 3

It is enough to close just Statement and Connection. There is no need to explicitly close the ResultSet object.

Java documentation says about java.sql.ResultSet:

A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.

---

Thanks BalusC for comments: "I wouldn't rely on that. Some JDBC drivers fail on that."

Answer 4

Actually, it is best if you use a try-with-resources block and Java will close all of the connections for you when you exit the try block.

You should do this with any object that implements AutoClosable.

try (Connection connection = getDatabaseConnection(); Statement statement = connection.createStatement()) {
    String sqlToExecute = "SELECT * FROM persons";
    try (ResultSet resultSet = statement.execute(sqlToExecute)) {
        if (resultSet.next()) {
            System.out.println(resultSet.getString("name");
        }
    }
} catch (SQLException e) {
    System.out.println("Failed to select persons.");
}

The call to getDatabaseConnection is just made up. Replace it with a call that gets you a JDBC SQL connection or a connection from a pool.

Answer 5

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 6

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

Answer 7

Even better would be to use a Try With Resources block

try (Connection connection = DriverManager.getConnection(connectionStr, username, password)) {
    try (PreparedStatement statement = connection.prepareStatement(query)) {
        statement.setFetchSize(100);
        try (ResultSet resultSet = statement.executeQuery()) {
            List<String> results = new ArrayList<>();
            while (resultSet.next()) {
                String value = resultSet.getString(1);
                results.add(value);
                System.out.println(value);
            }
            return results;
        }
    }
}