Oracle connection not closing in Java Application

Question

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;

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.

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??

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.

Answer 1

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

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.

Answer 2

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.

Also you can change your

if(conn!=null)

to

if (conn.isClosed())

..

Answer 3

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.

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

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

Answer 4

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 not set the variable conn to null. You can test if the connection is open with conn.isClosed().

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

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

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.

Answer 5

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.

Answer 6

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

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.