Closing database connections in Java

user_241852 0
Viewed 343986

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.

database-connection java
7 Answers

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.

user_706040

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.

Brian_Agnew_129600

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

user_76710

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