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.

Posy

It's always a best practice to close the connections on your own, without depending on other drivers and templates to handle closing. Failure of closing the connection will result in the sockets and resources open forever until a crash(no more resource scenario) or restart. – Concert

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.

Anglican

Awesome help, thank you! I didn't catch or think about the conn != null statements. – Posy

@Posy Yes, rs, ps, conn may be null depending on where the code breaks. That's why this is known as the "safe" pattern. – Anglican

@Pascal Thivent: Actually we don't need to close all of them. "Core Java Volume two - Advanced Features" book has wrote: The close method of a Statement object automatically closes the associated ResultSet if the statement has an open result set. Similarly, the close method of the Connection class closes all Statements of the Connection. – Lottielotto

@Majid: Unless it's a pooled connection. The statements would then leak away. – Adlib

@BalusC: Can u please explain what happens when a pooled connection is closed by using connection.close() method – Magallanes

@Krnsa: usually, it's released back to the pool who in turn worry under the covers about testing/reaping the connections. – Adlib

FWIW, DbUtils is not exactly the same as the code above. DbUtils only catches SQLException. If the close() method threw any RuntimeException, DbUtils would allow that exception to escape, which is usually bad if called in a finally block. Of course most JDBC drivers should only throw SQLException. But I've been caught out by closeQuietly() methods not actually being "quiet" under all circumstances before. – Kosher

what happens when, the program terminated and you didint call the close function()? – Dialyse

What if I need to throw the SQL Exception instead? – Barefaced

Most of these answers are of course out of date. In the last few versions of Java you should use a try-with-resources block. See answer that explains this down below. – Spirituality

@Adlib you are wrong, pooling has no effect on closing, close on pooling connection will clean up THEN return to the pool. This is mandated by JDBC specs – Hamrah

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.

Languid

What if my statement is implicit, i.e. ResultSet rs = conn.createStatement().executeQuery(sql); inside the try block? – Heartrending

You will not be able to reference them in the finally {} block for closure. If an exception is thrown, the close () method of the ResultSet will never be invoked – Tabanid

What happens if I do not close them? – Neuralgia

if you dont close them, then memory leaks can occur. – Languid

Answer 3

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.

Spirituality

So you do not have to manually close connection in this case? – Analemma

Correct. You do not have to explicitly close the connection. It will be closed when the end of the try code block is reached. – Spirituality

What is the Java version requirement for this to work? – Honorable

Java 7, if I am not mistaken. – Canal

Answer 4

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."

Mohammedanism

I wouldn't rely on that. Some JDBC drivers fail on that. E.g. Oracle with "Maximum open cursors exceeded", etc. Just explicitly close all opened resources, no excuses. – Adlib

I would rather not use drivers that do no conform to the specs then – Hamrah

As BalusC points out, it is good defensive programming to explicitly close the connection instead of hardwiring a dependency on a particular provider. – Panek

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.

Object

When I insert a "finally" eclipse likes to highlight it telling me it's wrong. should this go after the catch blocks? – Posy

Yes. try{}catch{}finally{}. The catch{} is optional, btw. Just like the finally{} – Object

I moved the "close" statements to the finally, but they're just saying "sqlexception", any suggestions? – Posy

close() throws a SQLException. You have to handle that. See DbUtils.closeQuietly() to handle this silently. – Object

> What is really happening if the conn.close() doesn't occur? – Neuralgia

Re "You typically have to do this in a finally{} block": Doesn't that happen way too late? Don't the close's need to be done directly? – Honorable

Not necessarily. It depends on what else goes on in your method. The reason why it should happen in a finally block is to ensure the connection is closed regardless of what’s happened. – Object

Answer 6

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

Nondescript

... until it exits. This ties down various finite resources on the client and server side. If a client does this kind of thing too much, it can cause problems for the client itself, the database service, and possibly even for other applications running on client or server machine. – Ulpian

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;
        }
    }
}

Cud

i dont this additional burden is required you can check my comment below if you are using java9 and above we have easy way to close connections – Stannfield

Answer 8

Since Java 9 it is possible to use already declared variables:

FileOutputStream fos = new FileOutputStream("filename");
XMLEncoder xEnc = new XMLEncoder(fos);
try (fos; xEnc) {
    xEnc.writeObject(object);
} catch (IOException ex) {
    Logger.getLogger(Serializer.class.getName()).log(Level.SEVERE, null, ex);
}

even you can read more here https://bugs.openjdk.org/browse/JDK-7196163

Stannfield