Java 连接关闭时 ResultSet 未关闭？

ResultSet not closed when connection closed?

提问by jb.

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.

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

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

当连接关闭时，您是否遇到过 ResultSets 和 Statements 没有关闭的问题？

I found only thisand 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 在关闭连接时关闭 ResultSet 有问题（我们使用 Oracle db，因此我更正）。java.sql.api 在 Connection.close() javadoc 中什么也没说。

采纳答案by Aaron

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()会将连接放回池中。此外，某些数据库在服务器上有一个游标资源，除非明确关闭，否则它不会被正确释放。

回答by neu242

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

我在 Oracle 中遇到了未关闭的 ResultSets 问题，即使连接已关闭。我得到的错误是

"ORA-01000: maximum open cursors exceeded"

So: Always close your ResultSet!

所以：总是关闭你的 ResultSet！

回答by John Gardner

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) 等，而不必担心关闭扫描可能抛出的异常.

回答by JavadocMD

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.

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

回答by Spencer Kormos

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 中，语句（而不是结果集）与 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 池化框架会池化 Statements 和 Connections，因此不关闭它们可能不会在池中将这些对象标记为空闲，并导致框架中出现性能问题。

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() 方法，则有理由调用它，而忽略它则后果自负。

回答by Horcrux7

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 驱动程序，那么关闭连接应该不会有任何问题。但是有2个问题：

• 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.

最好的做法是全部关闭。

回答by Stefan Schweizer

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) {
        }
    }
}

回答by Konrad

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. Server connects to DB 1
3. Server Selects on DB 1
4. Server "closes" connection to DB 1
5. Server connects to DB 2
6. Deadlocked!

1. 用户将请求页面
2. 服务器连接到 DB 1
3. 服务器在 DB 1 上选择
4. 服务器“关闭”与 DB 1 的连接
5. 服务器连接到 DB 2
6. 陷入僵局！

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 从未真正关闭连接，即使它们在 finally 中正确关闭。

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 设置为不可共享。

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>