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:
- I’ve never seen such a database migration happen in more than 10 years
- 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 the Statement 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, callingConnection.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, callingConnection.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 the StatementFinalizer 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" />|
While you’re there, you can also check the |