java - Java7之后把ResultSet放到一个嵌套的try-with-resources语句中是个好习惯吗？

Question

根据 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.

但根据Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards? , 显式关闭 Connection Statement 和 ResultSet 似乎是一个好习惯。

如果我们仍然需要关闭 ResultSet，我们可能需要一个嵌套的 try-with-resources 语句，因为我们可能会像这样为 Statement 设置参数:

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()) {
                ..............

     }
}

---

问题:

是否将 ResultSet 放入一个单独的 try-with-resources 语句中，因为文档指出关闭 Statement 将关闭 ResultSet

Answer 1

您的示例涵盖的连接、语句和结果集之间的交互范围过于有限。请考虑以下事项:

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()) {
             ..............

         }
     }
 }

在上面的示例中，PreparedStatement 被参数化并在 for 循环中执行了 kvs.length 次。想象一下这样一种情况，在这种情况下，由于某种原因，参数化过程花费了很长的时间。请注意，关闭 PreparedStatement 对我们没有好处，因为我们希望在 for 循环的每次迭代中重用已编译的 SQL 语句。然后确保将 ResultSet 嵌套到它自己的 try-with-resources block 中——从而确保先前迭代的 ResultSet 关闭但 PreparedStatement 保持打开——这是一项值得的努力。