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

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 2

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.