关闭ResultSet但不关闭PreparedStatement [英] Closing ResultSet but not closing PreparedStatement

问题描述

如果ResultSet被关闭，但是PreparedStatement不是，我可以期待哪种类型的资源泄漏.非常可疑会导致打开游标问题...

What type of resource leak can I expect if ResultSet is closed, but PreparedStatement not. It is very suspicious that it can cause an open cursor issue...

   PreparedStatement p = connection.prepareStatement(...);
    try {
      ResultSet r = p.executeQuery();
      try {
        while (r.next()) {
          ....
        }
      } finally {
        try {
          r.close();
        } catch (SQLException e) {
        // log this or something -- prevent these from masking original exception
        }
      }
    }

它是Oracle 11g，jdbc 11.2.0.3

It is Oracle 11g, jdbc 11.2.0.3

谢谢

请尝试回答我的问题，而不是专注于解决问题

Plz try to answer my question and not focusing on the fix

推荐答案

如果ResultSet关闭，我会期望哪种类型的资源泄漏，但是 PreparedStatement不是吗?

What type of resource leak can I expect if ResultSet is closed, but PreparedStatement not ?

泄漏将是最大的打开游标问题.

The leak will be the maximum open-cursor issue.

ORA-01000: maximum open cursors exceeded

如果超过了打开的最大游标数，则数据库将变得不可用，除了已保留的游标.但是，在大多数情况下，甚至没有使用保持的光标(在您的问题中就是这种情况).

If the maximum open cursors is exceeded, the database will become unusable, except for the cursors that are already held. However, most of the times when this happen the held cursors are not even used (which would be the case in your question).

从Java 7开始，处理此问题的最佳方法是使用尝试使用资源. ResultSet和PreparedStatement都实现了AutoCloseable接口，这意味着它们将在不再需要时关闭.

Since java 7, the best way to handle this is to use a try with ressources. Both ResultSet and PreparedStatement implements AutoCloseable interface which mean they will be closed when not needed anymore.

如果您没有Java 7，则需要在finally块中进行处理，但是请确保在关闭之前验证空值，否则如果从未初始化ressource，则可能会遇到NPE.

If you don't have java 7, then you need to handle this in the finally block, but make sure you verify for null values before closing else you may encounter an NPE if ressource was never initialized.

请注意，反向操作不会引起任何问题，因为关闭语句会自动关闭结果集.

Note that the reverse won't cause any problem as closing a statement will automatically close it result set.

请注意，您可以轻松地在应用程序中允许更多的游标，以减少超出最大值的机会.

Note that you can easily allow more cursors to your application for less chance of exceeding the maximum.

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

但是，在大多数情况下，如果遇到最大打开的游标，则这不是解决方案，因为它只会隐藏真正的问题.

However, most of the time if you encounter maximum open cursors this should not be a solution as it would only hide the real problem.

这篇关于关闭ResultSet但不关闭PreparedStatement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！