连接关闭时ResultSet未关闭？

I've been doing code review (mostly using tools like FindBugs) of one of our pet projects and FindBugs marked following code as erroneous (pseudocode):

我一直在做我们的宠物项目之一的代码审查（主要使用像FindBugs这样的工具）和FindBugs标记下面的代码是错误的（伪代码）：

Connection conn = dataSource.getConnection();

try{
    PreparedStatement stmt = conn.prepareStatement();
    //initialize the statement
    stmt.execute();
    ResultSet rs =  stmt.getResultSet();
    //get data
}finally{
    conn.close();
}

The error was that this code might not release resources. I figured out that the ResultSet and Statement were not closed, so I closed them in finally:

错误是此代码可能不会释放资源。我发现ResultSet和Statement没有关闭，所以我最后关闭了它们：

finally{
    try{
        rs.close()
    }catch(SqlException se){
        //log it
    }
    try{
        stmt.close();
    }catch(SqlException se){
        //log it
    }
    conn.close();
}

But I encountered the above pattern in many projects (from quite a few companies), and no one was closing ResultSets or Statements.

但是我在许多项目中遇到了上述模式（来自不少公司），没有人关闭ResultSet或Statements。

Did you have troubles with ResultSets and Statements not being closed when the Connection is closed?

在Connection关闭时，您是否遇到没有关闭ResultSet和Statements的麻烦？

I found only this and it refers to Oracle having problems with closing ResultSets when closing Connections (we use Oracle db, hence my corrections). java.sql.api says nothing in Connection.close() javadoc.

我发现只有这一点，它指的是Oracle在关闭Connections时关闭ResultSet的问题（我们使用Oracle db，因此我的更正）。 java.sql.api在Connection.close（）javadoc中什么也没说。

8 个解决方案

#1

One problem with ONLY closing the connection and not the result set, is that if your connection management code is using connection pooling, the connection.close() would just put the connection back in the pool. Additionally, some database have a cursor resource on the server that will not be freed properly unless it is explicitly closed.

仅关闭连接而不是结果集的一个问题是，如果您的连接管理代码使用连接池，则connection.close（）只会将连接放回池中。此外，某些数据库在服务器上有一个游标资源，除非明确关闭，否则将无法正确释放。

#2

I've had problems with unclosed ResultSets in Oracle, even though the connection was closed. The error I got was

即使连接已关闭，我在Oracle中也没有使用未关闭的ResultSet。我得到的错误是

"ORA-01000: maximum open cursors exceeded"

So: Always close your ResultSet!

所以：总是关闭你的ResultSet！

#3

You should always close all JDBC resources explicitly. As Aaron and John already said, closing a connection will often only return it to a pool and not all JDBC drivers are implemented exact the same way.

您应该始终显式关闭所有JDBC资源。正如Aaron和John已经说过的那样，关闭连接通常只会将其返回到池中，而且并非所有JDBC驱动程序都以完全相同的方式实现。

Here is a utility method that can be used from a finally block:

这是一个可以在finally块中使用的实用方法：

public static void closeEverything(ResultSet rs, Statement stmt,
        Connection con) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
        }
    }
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
        }
    }
    if (con != null) {
        try {
            con.close();
        } catch (SQLException e) {
        }
    }
}

#4

Oracle will give you errors about open cursors in this case.

在这种情况下，Oracle会向您提供有关打开游标的错误。

According to: http://java.sun.com/javase/6/docs/api/java/sql/Statement.html

根据：http：//java.sun.com/javase/6/docs/api/java/sql/Statement.html

it looks like reusing a statement will close any open resultsets, and closing a statement will close any resultsets, but i don't see anything about closing a connection will close any of the resources it created.

看起来重用一个语句将关闭所有打开的结果集，关闭一个语句将关闭任何结果集，但我没有看到关闭连接的任何内容将关闭它创建的任何资源。

All of those details are left to the JDBC driver provider.

所有这些细节都留给了JDBC驱动程序提供程序。

Its always safest to close everything explicitly. We wrote a util class that wraps everything with try{ xxx } catch (Throwable {} so that you can just call Utils.close(rs) and Utils.close(stmt), etc without having to worry about exceptions that close scan supposedly throw.

明确地关闭所有内容总是最安全的。我们写了一个util类，用try {xxx} catch（Throwable {}包装所有东西，这样你就可以调用Utils.close（rs）和Utils.close（stmt）等，而不必担心关闭扫描的异常会抛出。

#5

The ODBC Bridge can produce a memory leak with some ODBC drivers.

ODBC桥可以使用一些ODBC驱动程序产生内存泄漏。

If you use a good JDBC driver then you should does not have any problems with closing the connection. But there are 2 problems:

如果您使用一个好的JDBC驱动程序，那么关闭连接应该没有任何问题。但是有两个问题：

• Does you know if you have a good driver?
• 你知道你是否有一个好司机？
• Will you use other JDBC drivers in the future?
• 将来你会使用其他JDBC驱动程序吗？

That the best practice is to close it all.

最好的做法是关闭它。

#6

I work in a large J2EE web environment. We have several databases that may be connected to in a single request. We began getting logical deadlocks in some of our applications. The issue was that as follows:

我在一个大型的J2EE Web环境中工作。我们有几个可以在单个请求中连接的数据库。我们开始在一些应用程序中遇到逻辑死锁。问题是如下：

1. User would request page
2. 用户会请求页面
3. Server connects to DB 1
4. 服务器连接到DB 1
5. Server Selects on DB 1
6. 服务器在DB 1上选择
7. Server "closes" connection to DB 1
8. 服务器“关闭”与DB 1的连接
9. Server connects to DB 2
10. 服务器连接到DB 2
11. Deadlocked!
12. 僵持！

This occurred for 2 reasons, we were experiencing far higher volume of traffic than normal and the J2EE Spec by default does not actually close your connection until the thread finishes execution. So, in the above example step 4 never actually closed the connection even though they were closed properly in finally .

出现这种情况有两个原因，我们遇到的流量远远高于正常情况，默认情况下，J2EE规范实际上并没有关闭您的连接，直到线程完成执行。因此，在上面的示例中，步骤4实际上从未实际关闭连接，即使它们最终正确关闭。

To fix this, you you have to use resource references in the web.xml for your Database Connections and you have to set the res-sharing-scope to unsharable.

要解决此问题，您必须在web.xml中使用资源引用作为数据库连接，并且必须将res-sharing-scope设置为unharable。

Example:

例：

<resource-ref>
    <description>My Database</description>
    <res-ref-name>jdbc/jndi/pathtodatasource</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Unshareable</res-sharing-scope>
</resource-ref>

#7

I've definitely seen problems with unclosed ResultSets, and what can it hurt to close them all the time, right? The unreliability of needing to remembering to do this is one of the best reasons to move to frameworks that manage these details for you. It might not be feasible in your development environment, but I've had great luck using Spring to manage JPA transactions. The messy details of opening connections, statements, result sets, and writing over-complicated try/catch/finally blocks (with try/catch blocks in the finally block!) to close them again just disappears, leaving you to actually get some work done. I'd highly recommend migrating to that kind of a solution.

我肯定已经看到了未封闭的ResultSet的问题，并且一直关闭它们会有什么害处，对吧？需要记住这样做的不可靠性是转移到为您管理这些细节的框架的最佳理由之一。它可能在您的开发环境中不可行，但我很幸运使用Spring来管理JPA事务。打开连接，语句，结果集以及编写过于复杂的try / catch / finally块（在finally块中使用try / catch块！）的混乱细节再次关闭它们就会消失，让你真正完成一些工作。我强烈建议迁移到那种解决方案。

#8

In Java, Statements (not Resultsets) correlate to Cursors in Oracle. It is best to close the resources that you open as unexpected behavior can occur in regards to the JVM and system resources.

在Java中，语句（而非Resultsets）与Oracle中的游标相关联。最好关闭打开的资源，因为JVM和系统资源可能会发生意外行为。

Additionally, some JDBC pooling frameworks pool Statements and Connections, so not closing them might not mark those objects as free in the pool, and cause performance issues in the framework.

此外，一些JDBC池框架池语句和连接，因此不关闭它们可能不会将这些对象标记为池中的空闲，并导致框架中的性能问题。

In general, if there is a close() or destroy() method on an object, there's a reason to call it, and to ignore it is done so at your own peril.

通常，如果对象上有close（）或destroy（）方法，则有理由对其进行调用，并且忽略它是由您自己承担的。