CODEWITHSUNDEEP
javajdbctry-catchtry-with-resources
JaskeyLam
JaskeyLam

Reputation: 15785

Is it a good practice to put ResultSet into a nested try-with-resources statement after Java7?

According to doc of http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#close() ,

When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

But accoring to Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards? , it is seems to be a good practice to explicitly close Connection Statement and ResultSet .

If we still need to close ResultSet, we may need to have a nested try-with-resources statement since we may probably set parameter for Statement like this:

try (Connection conn = connectionProvider.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql) {//resources of conn and pst

     setPrepareStatementParameter(pstmt, kvs);//need to set parameters, so I have to put ResultSet into another try-with-resources statement

     try (ResultSet res = pstmt.executeQuery()) {
                ..............

     }
}

Question:

Does put the ResultSet into a separate try-with-resources statement worth anything since the doc states that closing Statement will close the ResultSet

Upvotes: 9

Views: 4772

Answers (2)

Lyoneel
Lyoneel

Reputation: 428

Yes, you should close or put a try-resources for result set.

Why?

I quote what I've read from other answer that makes a lot of sense for me.

  • In theory closing the statement closes the result set.
  • In practice, some faulty JDBC driver implementations failed to do so.

Check the full answer here: https://stackoverflow.com/a/45133734/401529

Upvotes: 0

imperfectgrist
imperfectgrist

Reputation: 661

Your example covers too limited a range of the interactions between Connections, Statements, and ResultSets. Consider the following:

try (Connection conn = connectionProvider.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql);) {

     for (int i = 0; i < kvs.length; i++) {
         setPrepareStatementParameter(pstmt, kvs[i]);

         // do other stuff

         // Place the ResultSet in another try with resources
         // to ensure the previous iteration's ResultSet
         // is closed when the next iteration begins
         try (ResultSet res = pstmt.executeQuery()) {
             ..............

         }
     }
 }

In the above example, the PreparedStatement is parametrized and executed a kvs.length number of times within the for-loop. Imagine a case in which the parametrization process, for any reason, took a significant length of time. Note that closing the PreparedStatement would do us no good since we want to reuse the compiled SQL statement at every iteration of the for-loop. Then surely nesting the ResultSet into its own try-with-resources block---thus ensuring the prior iteration's ResultSet is closed but the PreparedStatement remains open---is a worthwhile effort.

Upvotes: 4

Related Questions