Connection Not Closed Errors in JBoss when setting Oracle End To End Metrics

amy

pany.instance.Service.service.ejb.session.TestDao.queryDatabaseForAccountNums(TestDao.java:30)       at com.company.instance.Service.service.ejb.session.ServiceBean.chrisTestOp(ServiceBean.java:119)       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)       at java.lang.reflect.Method.invoke(Method.java:585)       at org.jboss.invocation.Invocation.performCall(Invocation.java:359)       at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)
.....
09:27:55,379 INFO  [CachedConnectionManager] Closing a connection for you.  Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@f3c7bb
java.lang.Throwable: STACKTRACE       at org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:290)       at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:417)       at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)       at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)       at com.company.instance.Service.service.ejb.session.TestDao.queryDatabaseForAccountNums(TestDao.java:30)       at com.company.instance.Service.service.ejb.session.ServiceBean.chrisTestOp(ServiceBean.java:119)       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)       at java.lang.reflect.Method.invoke(Method.java:585)       at org.jboss.invocation.Invocation.performCall(Invocation.java:359)       at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)       at org.jboss.wsf.container.jboss42.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:59)       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:158)
......The Java code that causes the error in the container but works in the standalone test harness.  In the real world it extends an interface per springs standards, but the interface didn't make a difference to the error so I left it out for brevity's sake.   In the real world I'm also doing the instrumentation in an Aspect triggered by an annotation, but again, didn't affect the error so I wrote a simpler example here.   

Note the two lines called out in the comments where the oracle connection object is manipulated.  If I take those lines of code out, the exceptions go away.  
Code:
public class TestDao extends JdbcDaoSupport  
{   //~ Instance fields ----------------------------------------------------------------------------

   //~ Methods ------------------------------------------------------------------------------------
   public boolean queryDatabaseForAccountNums() throws Throwable   {       System.out.println(quot;Impl start DatabaseDaoAspectImplquot;);               String[] metrics1 = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];       metrics1[OracleConnection.END_TO_END_ACTION_INDEX] = quot;EHquot;;       metrics1[OracleConnection.END_TO_END_MODULE_INDEX] = quot;EPPquot;;              System.out.println(quot;Starting get connectionquot;);
OracleConnection conn = (OracleConnection)getJdbcTemplate().getDataSource().getConnection().getMetaData().getConnection();       conn.setEndToEndMetrics(metrics1, (short) 0);        System.out.println(quot;modified connectionquot;);              System.out.println(quot;querying dbquot;);       getJdbcTemplate().execute(quot;select * from channelquot;);
       System.out.println(quot;Done querying dbquot;);       return true;   }
}

The working test harness data source configuration:

Code:
lt;bean id=quot;jndiDataSourceRewritequot; class=quot;org..jdbc.datasource.SingleConnectionDataSourcequot;gt;lt;property name=quot;driverClassNamequot;gt;
lt;valuegt;oracle.jdbc.OracleDriverlt;/valuegt;
lt;/propertygt;
lt;property name=quot;uclquot;gt;
lt;valuegt;jdbcracle:thinserver:1521:dblt;/valuegt;
lt;/propertygt;
lt;property name=quot;usernamequot;gt;
lt;valuegt;java_userlt;/valuegt;
lt;/propertygt;
lt;property name=quot;passwordquot;gt;
lt;valuegt;java_userlt;/valuegt;
lt;/propertygt; lt;/beangt;

Not working container deployed applicationContext reference:
Application Context Data Source Declaration:

Code:
lt;bean id=quot;jndiDataSourceRewritequot; class=quot;org..jndi.JndiObjectFactoryBeanquot;gt;lt;property name=quot;jndiNamequot;gt;lt;valuegt;java:company/ds/ClientNotification_oracle_DSlt;/valuegt;lt;/propertygt; lt;/beangt;
Oracle Data Source XML file deployed in jboss server instance

Code:

lt;datasourcesgt; lt;local-tx-datasourcegt; lt;jndi-namegt;company/ds/ClientNotification_oracle_DSlt;/jndi-namegt; lt;connection-uclgt;jdbcracle:thindatabase:1531:TSTlt;/connection-uclgt; lt;driver-classgt;oracle.jdbc.OracleDriverlt;/driver-classgt; lt;user-namegt;java_userlt;/user-namegt; lt;passwordgt;java_userlt;/passwordgt; lt;min-pool-sizegt;10lt;/min-pool-sizegt; lt;max-pool-sizegt;10lt;/max-pool-sizegt; lt;blocking-timeout-millisgt;5000lt;/blocking-timeout-millisgt; lt;idle-timeout-minutesgt;15lt;/idle-timeout-minutesgt; lt;!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool --gt; lt;valid-connection-checker-class-namegt;org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionCheckerlt;/valid-connection-checker-class-namegt;  lt;!-- Checks the Oracle error codes and messages for fatal errors --gt;lt;exception-sorter-class-namegt;org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorterlt;/exception-sorter-class-namegt;
lt;/local-tx-datasourcegt;
lt;/datasourcesgt;

Does anyone have any hints/clues what might be going on here?  

Thank you!

The problem is you are creating connections yourself and probably not closing/releasing them properly. One question why?!Code:

OracleConnection conn = (OracleConnection)getJdbcTemplate().getDataSource().getConnection().getMetaData().getConnection();
conn.setEndToEndMetrics(metrics1, (short) 0);
That piece of uglyness (IMHO ofcourse  ) creates a new database connection, whereas you probably expect it to be the connection retrieved/used by the JdbcTemplate.Code:
      System.out.println(quot;Starting get connectionquot;);
OracleConnection conn = (OracleConnection)getJdbcTemplate().getDataSource().getConnection().getMetaData().getConnection();       conn.setEndToEndMetrics(metrics1, (short) 0);        System.out.println(quot;modified connectionquot;);              System.out.println(quot;querying dbquot;);       getJdbcTemplate().execute(quot;select * from channelquot;);

This code uses 2 connections you are messing around with the datasource yourself getting a new connection.

2 things you need to do to get this working

1) Configure transactions
2) Use a ConnectionCallback to modify your connection that way you will get a managed connection from spring and those 2 queries will use the same connection (instead of each using a single connection).

Hi Marten,

I did some research yesterday and now I think I follow what you mean. That makes sense.  And that would be why quot;SingleConnectionquot; worked, even though the code wasn't right.

(And yes I think that code IS ugly, which was why in the original version its hidden hidden hidden away in an aspect triggered by an annotation...  Hopefully I can figure out a similar trick for doing it with Callable Statements.)

Thank you!