OC4J JDBC Connection Pool issue with ResultSet.getStatement()

Recently one our customers migrated their application from Tomcat to OC4J container and noticed some interesting observations regarding JDBC connection pool. I thought I would share here as it will help others too.

The Issue:
After migrating the application to OC4J, the number of connections to the database held by the enterprise manager did not match with the actual connections held in the database. The customer had Interscope's Wily product and it reported a different number for the number of database connections.

The application's heap usage also goes up significantly as it the conection objects are not released.

This lead us to evaluate the application's code on how they are getting the connection and releasing it. The way they were getting the connection was in line with the typical usage. However, when they are releasing the connection, they are going it by getting the connection object through the reference from the resultset object. They were using a utility method sibilar to the one below.

   protected synchronized Connection getConn() {
     Connection conn = null;
     try {
         Context initContext = new InitialContext();
         
         DataSource ds = (DataSource)initContext.looku("dataSourceName");
          conn = ds.getConnection();
          
     } catch (Exception e) {
      Logger.error("Error in getting a JDBC " + "connection " + e.getMessage());      
     }     
     return conn;
    }

 public synchronized void closeResultSetAndItsAssoc(ResultSet rs) {
     if (rs != null) {
      try {
 
       Statement stmt = rs.getStatement();
       Connection conn = stmt.getConnection();
       
       if (rs != null) {
        try { rs.close(); } catch (SQLException ignore) {}             
       }     
       if (stmt != null) {
        try { stmt.close(); } catch (SQLException ignore) {}
       }     
       if (conn != null) {
        try { conn.close(); } catch (SQLException ignore) {}
       }
      }  catch (SQLException ignore) {
       appLogger.warn(ignore.getMessage());     
      }
     }
     
    }

This code snippet worked well with Tomcat container. However, with the OC4J container, the actual connection object obtained by the getConn() method above is different from the one obtained by the stmt.getConnection() method. So, it appeared to the connection pool manager that the applicatio never closed the connection. Interestingly the one obtained by the stmt.getConnection() method is the actual physical connection and we would notice the connection is closed at the database rather than going back to inactive state.

Luckily, they were using the getConn() method and the closeResultSetAndItsAssoc() methods from the same object. We were able to make a quick fix to the closeResultSetAndItsAssoc method by closing the connection reference obtained using the getConn() method.