Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Connection is a Leaky Abstraction

DZone's Guide to

Connection is a Leaky Abstraction

· Java Zone
Free Resource

The single app analytics solutions to take your web and mobile apps to the next level.  Try today!  Brought to you in partnership with CA Technologies

As junior Java developers, we learn very early in our career about the JDBC API. We learn it’s a very important abstraction because it allows to change the underlying database in a transparent manner. I’m afraid what appeared as a good idea is just over-engineering because:

  1. I’ve never seen such a database migration happen in more than 10 years
  2. Most of the time, the SQL written is not database independent

Still, there’ s no denying that JDBC is at the bottom of every database interaction in Java. However, I recently stumbled upon another trap hidden very deeply at the core of the javax.sql.Connection interface. Basically, you perhaps have been told to close theStatement returned by the Connection? And also to close the ResultSet returned by the Statement? But perhaps you also have been told that closing the Connection will close all underlying objects – Statement and ResultSet?

So, which one is true? Well, “it depends” and there’s the rub…

  • One one hand, if the connection is returned from the DriverManager, calling Connection.close() will close the physical connection to the database and all underlying objects.
  • On the other hand, if the connection is returned from a DataSource, calling Connection.close() will only return it to the pool and you’ll need to close statements yourself.

In the latter case, if you don’t close those underlying statements, database cursors will stay open, the RDBMS limit will be reached at some point and new statements won’t be executed. Conclusion: always close statement objects (as I already wrote about)! Note the result set will be closed when the statement is.

If you’re lucky to use Java 7 – and don’t use a data access framework, the code to use is the following:

try (PreparedStatement ps = connection.prepareStatement("Put SQL here")) {
    try (ResultSet rs = ps.executeQuery()) {
        // Do something with ResultSet
    }
} catch (SQLException e) {
    // Handle exception
    e.printStackTrace();
}

And if you want to make sure cursors will be closed even with faulty code, good old Tomcat provides theStatementFinalizer interceptor for that. Just configure it in the server.xml configuration file when you declare your Resource:

<Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
 jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" />

Note: while you’re there, you can also check the ResetAbandonedTimer interceptor. It can be used in conjunction with theremoveAbandonedTimeout attribute: this configures the time after which the connection will be returned back to the pool. If the attribute’s value is too low, connections in use might be returned. With the interceptor, each time the connection is used resets the timer.

CA App Experience Analytics, a whole new level of visibility. Learn more. Brought to you in partnership with CA Technologies.

Topics:

Published at DZone with permission of Nicolas Frankel, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}