Oracle 连接未在 Java 应用程序中关闭

Oracle connection not closing in Java Application

提问by jeff

I have a connection leak in some older Java web applications which do not utilize connection pooling. Trying to find the leak is hard because IT will not grant me access to v$session SELECT Count(*) FROM v$session;

我在一些不使用连接池的旧 Java Web 应用程序中存在连接泄漏。试图找到泄漏点很困难，因为 IT 不会授予我访问 v$session 的权限SELECT Count(*) FROM v$session;

So instead I am trying to debug with System.out statements. Even after closing the connection conn.close();when I print conn to the System log file it gives me the connection object name.

因此，我尝试使用 System.out 语句进行调试。即使在conn.close();我将 conn 打印到系统日志文件时关闭连接后，它也会为我提供连接对象名称。

try { 
    Connection conn;
    conn.close() 
    } 
catch (SQLException e) { }
finally { 
    if (conn != null) {
        try {
           System.out.println("Closing the connection"); 
           conn.close();
           }
        catch (Exception ex) 
            {
            System.out.println("Exception is " + ex); 
            }
     }
 }
// I then check conn and it is not null and I can print the object name.
    if (conn != null) {
            System.out.println("Connection is still open and is " + conn); 
    }

however if I also add conn = null;below the conn.close();statement the connection now seems closed. So my question is does conn.close(); actually release my connection or do I also have to make it null to really release my connection. Like I said it is really hard for me to determine if the connection is actually released without being able to query v$session. Is there snippet of java code which can give me my open connections??

但是，如果我还在语句conn = null;下方添加，conn.close();则连接现在似乎已关闭。所以我的问题是 conn.close(); 实际释放我的连接还是我也必须让它为空才能真正释放我的连接。就像我说的那样，在无法查询 v$session 的情况下，我很难确定连接是否真的被释放了。是否有 Java 代码片段可以为我提供打开的连接？

It's probably educational at this point because I plan to refactor these applications to use connection pooling but I'm looking for a quick bandaid for now.

在这一点上它可能具有教育意义，因为我计划重构这些应用程序以使用连接池，但我现在正在寻找一个快速的创可贴。

回答by duffymo

The important part of the close is what's happening on the database side. It's the RDBMS that has to close that connection. Calling the close() method is what communicates the message to the database to close the connection.

关闭的重要部分是数据库端发生的事情。RDBMS 必须关闭该连接。调用 close() 方法是将消息传递给数据库以关闭连接。

Setting the connection to null doesn't instruct RDBMS to do anything.

将连接设置为 null 不会指示 RDBMS 执行任何操作。

Same logic applies to ResultSet, which is a cursor on the database side, and Statement. You need to close those in individual try/catch blocks in the finally block of the method that created them, in reverse order of creation. Otherwise you'll see errors about "Max cursors exceeded".

相同的逻辑适用于 ResultSet（数据库端的游标）和 Statement。您需要在创建它们的方法的 finally 块中以与创建相反的顺序关闭单个 try/catch 块中的那些。否则，您将看到有关“超出最大游标数”的错误。

回答by Peter Tillemans

Setting the conn to null only breaks the reference link to the connection object, and has no influence on the connection being open or not. If the connection is still open then the connection will still be referred to from inside the JDBC driver/connection pool etc...

将 conn 设置为 null 只会断开连接对象的引用链接，对连接是否打开没有影响。如果连接仍然打开，那么连接仍然会从 JDBC 驱动程序/连接池等内部引用......

Setting a variable to null is more telling the garbage collector that it is ok to clean up the original object when it wants to than anything else.

将变量设置为 null 更能告诉垃圾收集器，它可以在需要时清理原始对象，而不是其他任何事情。

回答by atk

As others are saying, you've got two different concepts here: closing the connecting and tracking the connection in a variable.

正如其他人所说，这里有两个不同的概念：关闭连接和跟踪变量中的连接。

To close the connection, call conn.close(). This will notset the variable conn to null. You can test if the connection is open with conn.isClosed().

要关闭连接，请调用conn.close()。这不会将变量 conn 设置为 null。您可以使用 测试连接是否打开conn.isClosed()。

If you don't care to track the connection in your code any more, you can conn = null. This does notimmediately close the connection. I believe the connection will be automatically closed, based on the JDBC documentation:

如果您不想再跟踪代码中的连接，您可以conn = null. 这不会立即关闭连接。我相信连接会根据 JDBC文档自动关闭：

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

立即释放此 Connection 对象的数据库和 JDBC 资源，而不是等待它们自动释放。

If you choose to go this route, be aware that the garbage collector may not close your connection as quickly as you want, and you may have what appears to be a resource leak; reserved database locks won't be released until the connection is garbage collected. Certain drivers (I don't know if oracle is one) impose maximum limit to the number of connections that may exist at one time, so leaving open connections can also cause failures to connect, later in the program.

如果您选择走这条路线，请注意垃圾收集器可能不会像您希望的那样迅速关闭您的连接，并且您可能会遇到资源泄漏；在连接被垃圾收集之前，不会释放保留的数据库锁。某些驱动程序（我不知道 oracle 是不是一个）对一次可能存在的连接数施加了最大限制，因此保持打开的连接也会导致连接失败，稍后在程序中。

回答by Jay

Connection leaks are a best. I think a good strategy is to wrap the getting and releasing of connections in a couple of functions and then always get and release your connections through those functions. Then you can have those functions maintain a list of all open connections, and do a stack trace on the caller of the allocate function. Then have a screen that shows a list of all open connections and where they came from. Run this in a test environment, run around using a bunch of screens, then exit them all so all the connections SHOULD close, then bring up the screen that shows open connectoins, and the villain should be revealed.

连接泄漏是最好的。我认为一个好的策略是将连接的获取和释放包装在几个函数中，然后始终通过这些函数获取和释放你的连接。然后，您可以让这些函数维护所有打开连接的列表，并对分配函数的调用者进行堆栈跟踪。然后有一个屏幕，显示所有打开的连接及其来源的列表。在测试环境中运行它，使用一堆屏幕跑来跑去，然后将它们全部退出，以便所有连接都应该关闭，然后调出显示打开连接的屏幕，反派应该被揭露。

回答by sjt

Is there snippet of Java code which can give me my open connections?

是否有 Java 代码片段可以为我提供打开的连接？

Statement smt = null;
    ResultSet rs = null;
    try { 
        // Create Statement from connection
        smt = conn.createStatement();
        // Execute Query in statement 
        rs = stmt.executeQuery("SELECT 1 FROM Dual");

        if (rs.next()) {
            return true; // connection is valid
        }
        catch (SQLException e) {
            // Some sort of logging
            return false;
        }
        finally {
            if (smt != null) smt.close();
            if (rs != null) rs.close();
        }

Just a quick guess, assuming you are using Oracle. Sugession: Why don't you install jboss and set up connection pooling through there?

只是一个快速的猜测，假设您使用的是 Oracle。建议：你为什么不安装 jboss 并通过那里设置连接池？

回答by CoolBeans

My explanation here is an educated guess.

我在这里的解释是有根据的猜测。

As a practice I have always set conn=null after the close. I believe when you do conn.close() you are telling the garbage collector that it's ready to be garbage collected. However, it will be up to the garbage collection process to determine when to do so.

作为一种做法，我总是在关闭后设置 conn=null。我相信当您执行 conn.close() 时，您是在告诉垃圾收集器它已准备好进行垃圾收集。但是，由垃圾收集过程决定何时这样做。

Also you can change your

你也可以改变你的

if(conn!=null)

if(conn!=null)

to

到

if (conn.isClosed())

如果（conn.isClosed（））

..

..