Try / Try-with-resources and Connection, Statement and ResultSet

Question

I recently talked with my professor on how to handle the jdbc basic wiring diagram. Suppose we want to execute two queries, this is what it offers

public void doQueries() throws MyException{ Connection con = null; try { con = DriverManager.getConnection(dataSource); PreparedStatement s1 = con.prepareStatement(updateSqlQuery); PreparedStatement s2 = con.prepareStatement(selectSqlQuery); // Set the parameters of the PreparedStatements and maybe do other things s1.executeUpdate(); ResultSet rs = s2.executeQuery(); rs.close(); s2.close(); s1.close(); } catch (SQLException e) { throw new MyException(e); } finally { try { if (con != null) { con.close(); } } catch (SQLException e2) { // Can't really do anything } } } 

I do not like this approach, and I have two questions:

1.A) I think that if any exception is thrown when we do “other things” either on the rs.close() or s2.close() , then s1 will not be closed when the method completes. Am I right about that?

1.B) The professor asks me to explicitly close the ResultSet (even if the Statement documentation clearly states that it will close the ResultSet). She says Sun recommends him. Is there any reason for this?

Now this is what I consider the correct code for the same thing:

 public void doQueries() throws MyException{ Connection con = null; PreparedStatement s1 = null; PreparedStatement s2 = null; try { con = DriverManager.getConnection(dataSource); s1 = con.prepareStatement(updateSqlQuery); s2 = con.prepareStatement(selectSqlQuery); // Set the parameters of the PreparedStatements and maybe do other things s1.executeUpdate(); ResultSet rs = s2.executeQuery(); } catch (SQLException e) { throw new MyException(e); } finally { try { if (s2 != null) { s2.close(); } } catch (SQLException e3) { // Can't do nothing } try { if (s1 != null) { s1.close(); } } catch (SQLException e3) { // Can't do nothing } try { if (con != null) { con.close(); } } catch (SQLException e2) { // Can't do nothing } } } 

2.A) Is this code correct? (Is it guaranteed that everything will be closed at the end of the method?)

2.B) It is very large and verbose (and gets worse if there are more statements). Is there a shorter or more elegant way to do this without using try-in-resources?

Finally, this is the code I like best

 public void doQueries() throws MyException{ try (Connection con = DriverManager.getConnection(dataSource); PreparedStatement s1 = con.prepareStatement(updateSqlQuery); PreparedStatement s2 = con.prepareStatement(selectSqlQuery)) { // Set the parameters of the PreparedStatements and maybe do other things s1.executeUpdate(); ResultSet rs = s2.executeQuery(); } catch (SQLException e) { throw new MyException(e); } } 

3) Is this code correct? I think that my professor does not like this because there is no obvious closure of the ResultSet, but she told me that she is okay, as long as the documentation clearly says that everything is closed. Can you give any link to official documentation with a similar example or based on documentation showing that there is no problem with this code?

Answer 1

TL; dr

• In theory, closing a statement closes the result set.
• In practice, some erroneous implementations of the JDBC driver, as you know, could not do this. Therefore, the advice from your instructor, which she learned at the school of hard knocks. If you are not familiar with every implementation of every JDBC driver that can be deployed for your application, use try-with-resources to automatically close every level of your JDBC job, such as statements and result sets.

Use try-with-resources syntax

None of your codes make full use of try-with-resources . In the try-with-resources syntax, you declare and instantiate Connection , PreparedStatement and ResultSet in brackets in front of the brackets. See the tutorial for Oracle .

Although your ResultSet is not explicitly closed in your last code example, it should be closed indirectly when its statement is closed. But, as described below, it cannot be closed due to a faulty JDBC driver.

AutoCloseable

Any such objects that implement AutoCloseable will be automatically called by their close method. So there is no need for these finally clauses.

Yes, for the liberal arts professions reading this, yes, the Java team wrote with the error "shut down."

How do you know which objects are automatically closed and which are not? Look at the documentation for their class to see if it AutoCloseable as a superinterface. Conversely, see the JavaDoc page for AutoCloseable for a list of all related subinterfaces and implementing classes (actually dozens).

For example, to work with SQL, we see that Connection , Statement , PreparedStatement , ResultSet , and RowSet all automatically close, but there is no DataSource . This makes sense because the DataSource stores data about potential resources (connections to the database), but is not a resource itself. DataSource never "open", so there is no need to close it.

See Oracle Tutorial, Resource Statement .

Code example

Your last code sample comes close to good, but it should have wrapped the ResultSet in a try-with-resources statement to automatically close it.

Quote ResultSet JavaDoc:

The ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to obtain the next result from a sequence of multiple results.

As your teacher suggested, some JDBC drivers had serious flaws that did not meet the promise of the JDBC specification to close the ResultSet when its Statement or PreparedStatement closed. So many programmers have the habit of closing every ResultSet object explicitly.

This additional responsibility is made easier by the try-with-resources syntax. In real life, you will likely have to try all of your AutoCloseable objects, such as ResultSet . So my own opinion is this: why not try it with other resources? It will not hurt, makes your code more self-documented about your intentions, and it can help if your code ever encounters one of these faulty JDBC drivers. The only price is a pair of guys , provided that in any case you have the opportunity to try other things.

As indicated in the Oracle AutoCloseable , several AutoCloseable objects declared together will be closed in the reverse order , as we would like.

Tip: The try-with-resources syntax allows an optional semicolon in the last declared resource element. I use the semicolon as a habit, because it is easy to read by eye, consistent and easy to edit. I have included it in 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 assume that there is a more elegant syntax for this kind of work that could be invented in a future programming language. But for now, we have a try with resources, and I use it happily. Although the attempt to use resources is not entirely elegant, it is significantly better than the old syntax.

By the way, Oracle recommends using the DataSource implementation to get connections, rather than the DriverManager approach, which can be seen in your code. Using a DataSource throughout the code makes it easy to switch drivers or switch to a connection pool. See if your JDBC driver provides a DataSource implementation.

Update: Java 9

Now in Java 9 you can initialize resources before trying with resources. See this article . This flexibility may be useful in some scenarios.

Answer 2

The most interesting thing about JDBC code is that you code a specification where it is not always clear how compatible your implementation is. There are many different databases and drivers, and some drivers are better than others. This usually makes people err on the side of caution, recommending things like closing everything explicitly. You may be ok with closing only the connections here. Closing resultSet just to be safe is hard to argue with. You do not specify which database or driver you are using here, I would not like to hard code in the assumptions about the driver, which may be invalid for some implementation.

Closing in a sequence leaves you open to problems when an exception can be thrown and cause some of the closures to be skipped. You are right to worry about this.

Remember this is a toy example. The actual code uses a connection pool, where a call to close does not actually close the connection, instead it returns a connection to the pool. Thus, resources may not be closed after using the pool. If you want to change this code to use the connection pool, you will have to go back and close the instructions at a minimum.

Also, if you object to the verbosity of this question, the answer to this is to hide the code in reusable utilities that use strategies, resultSet converters, prepared reporting tools, etc. This was all done before, of course; you'll be on your way to rethinking Spring JDBC.

Speaking about that: Spring JDBC closes everything explicitly (perhaps because it needs to work with the maximum possible number of drivers and does not want to cause problems due to the fact that some driver does not behave well).

Answer 3

This is truly the main motivation for try-in-resources. See Java tutorials as a reference. Your professor is out of date. If you want to deal with a result set problem, you can always wrap it in another try-with-resources statement.

Answer 4

You can make a util class to handle the closure of these resources. those. FYI, I simply ignored SQLExceptions from trying to close resources in the util class, but you could register or collect and throw them very well as soon as you finish closing resources in the collection depending on your needs.

 public class DBUtil { public static void closeConnections(Connection ...connections){ if(connections != null ){ for(Connection conn : connections){ if(conn != null){ try { conn.close(); } catch (SQLException ignored) { //ignored } } } } } public static void closeResultSets(ResultSet ...resultSets){ if(resultSets != null ){ for(ResultSet rs: resultSets){ if(rs != null){ try { rs.close(); } catch (SQLException ignored) { //ignored } } } } } public static void closeStatements(Statement ...statements){ if(statements != null){ for(Statement statement : statements){ if(statement != null){ try { statement.close(); } catch (SQLException ignored) { //ignored } } } } } 

}

and then just call it from your method:

  public void doQueries() throws MyException { Connection con = null; try { con = DriverManager.getConnection(dataSource); PreparedStatement s1 = null; PreparedStatement s2 = null; try { s1 = con.prepareStatement(updateSqlQuery); s2 = con.prepareStatement(selectSqlQuery); // Set the parameters of the PreparedStatements and maybe do other things s1.executeUpdate(); ResultSet rs = null; try { rs = s2.executeQuery(); } finally { DBUtil.closeResultSets(rs); } } finally { DBUtil.closeStatements(s2, s1); } } catch (SQLException e) { throw new MyException(e); } finally { DBUtil.closeConnections(con); } } 

Answer 5

This is what I consider the best solution for processing resources such as JDBC. This method provides an immutable function by using the final variables and only declaring and assigning these variables if necessary, it is very efficient for the CPU and ensures in all cases that all resources that are assigned and opened are closed regardless of the state of the exceptions. The technique you use leaves gaps that can lead to resource leaks if they are not carefully implemented to solve all the scenarios. This method does not allow resource leakage, provided that the template is always respected:
1) assign a resource
2) try 3) use the resource
4) finally close the resource

 public void doQueries() throws MyException { try { final Connection con = DriverManager.getConnection(dataSource); try { final PreparedStatement s1 = con.prepareStatement(updateSqlQuery); try { // Set the parameters of the PreparedStatements and maybe do other things s1.executeUpdate(); } finally { try { s1.close(); } catch (SQLException e) {} } final PreparedStatement s2 = con.prepareStatement(selectSqlQuery); try { // Set the parameters of the PreparedStatements and maybe do other things final ResultSet rs = s2.executeQuery(); try { // Do something with rs } finally { try { rs.close(); } catch (SQLException e) {} } } finally { try { s2.close(); } catch (SQLException e) {} } } finally { try { con.close(); } catch (SQLException e) {} } } catch (SQLException e) { throw new MyException(e); } } 

With Java 7, you can use the new features of try-with-resources to simplify this: the new try-with-resources follows the above logical stream, as it ensures that all resources will be included in the resource block that is assigned to get closed. any exception thrown in the resource block will be thrown, but the assigned resources will still be closed. This code is greatly simplified and looks like this:

 public void doQueries() throws MyException { try ( final Connection con = DriverManager.getConnection(dataSource); final PreparedStatement s1 = con.prepareStatement(updateSqlQuery); final PreparedStatement s2 = con.prepareStatement(selectSqlQuery); final ResultSet rs = s2.executeQuery()) { s1.executeUpdate(); // Do something with rs } catch (SQLException e) { throw new MyException(e); } } 

[EDIT]: The assignment of rs to the resource block has been moved to show the simplest implementation. In practice, this simple solution does not really work, as it is inefficient. The connection should be reused, since establishing a connection is a very expensive operation. In addition, this simple example does not assign query parameters to a prepared statement. These scenarios need to be addressed because the resource block should include only assignment statements. To illustrate this, I also added another example.

  public void doQueries() throws MyException { final String updateSqlQuery = "select @@servername"; final String selecSqlQuery = "select * from mytable where col1 = ? and col2 > ?"; final Object[] queryParams = {"somevalue", 1}; try (final Connection con = DriverManager.getConnection(dataSource); final PreparedStatement s1 = newPreparedStatement(con, updateSqlQuery); final PreparedStatement s2 = newPreparedStatement(con, selectSqlQuery, queryParams); final ResultSet rs = s2.executeQuery()) { s1.executeUpdate(); while (!rs.next()) { // do something with the db record. } } catch (SQLException e) { throw new MyException(e); } } private static PreparedStatement newPreparedStatement(Connection con, String sql, Object... args) throws SQLException { final PreparedStatement stmt = con.prepareStatement(sql); for (int i = 0; i < args.length; i++) stmt.setObject(i, args[i]); return stmt; }