tl;dr
- In theory closing the statement closes the result set.
- In practice, some faulty JDBC driver implementations failed to do so, notoriously. Thus the advice from your instructor that she learned from the School Of Hard Knocks. Unless you are familiar with every implementation of every JDBC driver that might be deployed for your app, use try-with-resources to auto-close every level of your JDBC work such as statements and result sets.
Use try-with-resources syntax
None of your code is fully using try-with-resources. In try-with-resources syntax, you declare and instantiate your Connection
, PreparedStatement
, and ResultSet
in parentheses, before the braces. See Tutorial by Oracle.
While your ResultSet
is not being explicitly closed in your last code example, it should be closed indirectly when its statement is closed. But as discussed below, it might not be closed because of faulty JDBC driver.
AutoCloseable
Any such objects implementing AutoCloseable
will automatically have their close
method invoked. So no need for those finally
clauses.
For the Humanities-majors reading this, yes, the Java team misspelled “closable”.
How do you know which objects are auto-closable and which are not? Look at their class documentation to see if it declares AutoCloseable
as a super-interface. Conversely, see the JavaDoc page for AutoCloseable
for a list of all the bundled sub-interfaces and implementing classes (dozens actually).
For example, for SQL work, we see that Connection
, Statement
, PreparedStatement
, ResultSet
, and RowSet
are all auto-closable but DataSource
is not. This makes sense, as DataSource
stores data about potential resources (database connections) but is not itself a resource. A DataSource
is never “open” so no need to close.
See Oracle Tutorial, The try-with-resources Statement.
Code example
Your last code example is getting close to good, but should have wrapped ResultSet
in a try-with-resources statement to get automatically closed.
To quote ResultSet
JavaDoc:
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
As your teacher has been suggesting, there have been serious flaws in some JDBC drivers that failed to live up to the promise of the JDBC spec to close the ResultSet
when its Statement
or PreparedStatement
is closed. So many programmers make a habit of closing each ResultSet
object explicitly.
This extra duty is easier now with the try-with-resources syntax. In real work you’ll likely have a try-else around all your AutoCloseable
objects such as ResultSet
anyways. So my own opinion is: Why not make it a try-with-resources + else? Does not hurt, makes your code more self-documenting about your intentions, and it might help if your code ever encounters one of those faulty JDBC drivers. The only cost is a pair of parens, assuming you’d have a try-catch-else in place anyways.
As stated in the Oracle Tutorial, multiple AutoCloseable
objects declared together will be closed in reverse order, just as we would want.
Tip: The try-with-resources syntax allows an optional semicolon on the last declared resource item. I include the semicolon as a habit because it reads well to my eye, is consistent, and facilitates cut-and-paste editing. I include it on your PreparedStatement s2
line.
public void doQueries() throws MyException{
// First try-with-resources.
try ( Connection con = DriverManager.getConnection( dataSource ) ;
PreparedStatement s1 = con.prepareStatement( updateSqlQuery ) ;
PreparedStatement s2 = con.prepareStatement( selectSqlQuery ) ;
) {
… Set parameters of PreparedStatements, etc.
s1.executeUpdate() ;
// Second try-with-resources, nested within first.
try (
ResultSet rs = s2.executeQuery() ;
) {
… process ResultSet
} catch ( SQLException e2 ) {
… handle exception related to ResultSet.
}
} catch ( SQLException e ) {
… handle exception related to Connection or PreparedStatements.
}
}
I suppose there is a more elegant syntax for this kind of work that might be invented in a future programming language. But for now, we have try-with-resources, and I do use it happily. While try-with-resources is not perfectly elegant, it is a big improvement over the older syntax.
By the way, Oracle recommends using a DataSource
implementation for getting connections rather than the DriverManager
approach seen in your code. Using DataSource
throughout your code makes it easier to switch drivers or switch to a connection pool. See if your JDBC driver provides an implementation of DataSource
.
Update: Java 9
Now in Java 9 you can initialize the resources before the try-with-resources. See this article. This flexibility may be useful in some scenarios.