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.

JNDI Lookup for JDBC resources

Recently I came across an issue where the code to perform a JNDI lookup for a JDBC resource that worked fine in a tomcat container did not work when migrated to an OC$J container. Looking at the details of the code and the resource definitions explained why it did not work and the fix needed.

The following is code snippet that is used to get the datasource object by the application.

String dsName = "myDataSource";
initCtx = new InitialContext(); 
envCtx = (Context) initCtx.lookup("java:comp/env"); 
DataSource ds = (DataSource) envCtx.lookup(dsName);

In Tomcat the server.xml file contains the definition for the datasource "myDataSource". The resource ref mapping is done by an entry in the Catalina/hostname/warname.xml file. This setup works fine in a Tomcat container.


When ported to oc4j container, the datasource "myDataSource" is defined in the application's context (in this case it was the default application). However the code is when running within oc4j container, it failed to find the resource. Taking a closer look at the issue helped identify the solution.


The application is looking for the resource name "myDataSource" within its environment. In Tomcat this resource name was made available to it by the context definition within the Catalina/hostname/appname.xml file by having the following entry


In OC4J the datasources.xml file can provide this mapping. However, providing that mapping itself is not sufficient. The web.xml file needs to have a resource-ref definition that the name referenced within its environment is the globalname defined else where. Tomcat container was forgiving for some reason and it lookedup the resource withoout having the resource-ref definition. In order to fix the issue we had tow choice:

1) Change the code to lookup the resource using the global jndi name. ie

String dsName = "myDataSource";
initCtx = new InitialContext();
DataSource ds = (DataSource) initCtx.lookup(dsName);

or
2) Add the resource-ref to the web.xml file so the local name is mapped to the jndi global name.

<resource-ref>
        <res-ref-name>myDataSource
        <res-type >javax.sql.DataSource
        <res-auth >Container
        <res-sharing-scope >Shareable< /res-sharing-scope>
</resource-ref>

I personally prefer the later as it gives the option to change DataSource reference/names etc without having to change the code.