SQL Zone is brought to you in partnership with:

Jakub is a Java EE developer since 2005 and occasionally a project manager, working currently with Iterate AS. He's highly interested in developer productivity (and tools like Maven and AOP/AspectJ), web frameworks, Java portals, testing and performance and works a lot with IBM technologies. A native to Czech Republic, he lives now in Oslo, Norway. Jakub is a DZone MVB and is not an employee of DZone and has posted 154 posts at DZone. You can read more from them at their website. View Full User Profile

JDBC: What Resources You Have to Close and When?

02.26.2013
| 5437 views |
  • submit to reddit

 

I was never sure what resources in JDBC must be explicitely closed and wasn’t able to find it anywhere explained. Finally my good colleague, Magne Mære, has explained it to me:

In JDBC there are several kinds of resources that ideally should be closed after use.  Even though every Statement and PreparedStatement is specified to be implicitly closed when the Connection object is closed, you can’t be guaranteed when (or if) this happens, especially if it’s used with connection pooling. You should explicitly close your Statement and PreparedStatement objects to be sure. ResultSet objects might also be an issue, but as they are guaranteed to be closed when the corresponding Statement/PreparedStatement object is closed, you can usually disregard it.

Summary: Always close PreparedStatement/Statement and Connection. (Of course, with Java 7+ you’d use the try-with-resources idiom to make it happen automatically.)

PS: I believe that the close() method on pooled connections doesn’t actually close them but just returns them to the pool.

A request to my dear users: References to any good resources would be appreciate.

Published at DZone with permission of Jakub Holý, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Gregor Kovač replied on Tue, 2013/02/26 - 3:20am

Hi!

You are right. When you are using connection pools, closing Statements is even more important, since the connection is not physical closes when you call close method on the connection. When you get a connection from connection pool, you get a wrapper around physical connection.

You can usualy gte physical connection from pooledconnection by calling unwrap method. At least on DB2 and SQL Server.

Best regards,
    Kovi

Maarten van Hul... replied on Tue, 2013/02/26 - 6:01am

Note that some containers (for example JBoss AS), can be configured to keep references to prepared statements. This is a performance optimization that prevents the database to having to compile the plan for the same query over and over again.

See also JBoss documentation about prepared statement cache

Regards,

Maarten

Xavier Dury replied on Tue, 2013/02/26 - 9:20am

There was this epic thread on javalobby which explains the different ways of closing JDBC resources.

Greg Brown replied on Tue, 2013/02/26 - 9:37am

Unless there is a compelling reason not to do so, I'd recommend closing each resource as soon as you are done with it. For example, if you use a statement to create multiple result sets, only the most recent result set will be closed with the statement. Closing each result set individually ensures that you don't leak resources.

 

matt inger replied on Tue, 2013/02/26 - 9:31pm in response to: Greg Brown

agreed, close everything, but this is where spring comes in handle, by doing all this for you, using the JdbcTemplate and ConnectionCallback, PreparedStatementCallback come in handy.  You can basically inject a DataSource, and ask the template to execute a callback within an open connection.  It will take care of opening and closing (depending on transaction semantics if you're using that too).

Doing all this manually is a pain.  It's much easier to do something like this in your code:

@Transactional

public List<Map<String,Object>> fetch() {

    return getJdbcTemplate().query(sql, args, argTypes, new ColumnMapRowMapper());

}

This create a prepared statement, sets the parameters as passed, and maps each row in the result setp to a Map<String, Object>

There's more low level methods allowing you to create prepared statements the way you want them, or even work direclty with the connection, but i find that the "query" or "update" methods are usually all i need, and never even touch connections and statements, except in the RowMapper implementation, where i'm given the result set already positioned on a row, and all i have to do is to extract values from it into domain object.


matt inger replied on Tue, 2013/02/26 - 9:35pm in response to: matt inger

Lund Wolfe replied on Sat, 2013/03/02 - 11:35pm

You can also just let an ORM like MyBatis or Hibernate do it for you.

// reuse prepared statements in MyBatis
session = sqlSessionFactory.openSession(ExecutorType.REUSE);

// when completely done with JDBC access
session.close();

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.