Getting the connection (with some AOP too!)

Abstract

If you need the raw OracleConnection so you can use End-to-End Metrics and you're using a JNDI data source from WebSphere, you need to jump through a few hoops.

The Details

Some up-front info: the project I'm on is running some old software so I'm not sure how relevant any of this is to anyone but me. We are using Spring 2.0.6, Hibernate 3.2.5, WebSphere 6.x and Oracle 10g...

Anyways, we wanted to implement Oracle's End-to-End metrics so that we could have a better way to see what each user is doing on the system and where potential problems might lie in relation to the database. We also wanted the code to be as non-intrusive as possible - adding new code everywhere would be a total pain.

I decided to use AOP which allow us the ability to attach code anytime a method on a DAO is called. It works quite well, even with Spring 2.0.

Back to the original idea, here's the code to get the OracleConnection:

import org.hibernate.jdbc.ConnectionWrapper;
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;
import oracle.jdbc.OracleConnection;

Connection conn = session.connection();
org.hibernate.jdbc.ConnectionWrapper cw = (ConnectionWrapper) conn;
WSJdbcConnection connws = (WSJdbcConnection) cw.getWrappedConnection();
OracleConnection oc = (OracleConnection) WSJdbcUtil.getNativeConnection(connws);


With session being the Hibernate session (I added the imports so you wouldn't have to look for them later).

Now that you have the OracleConnection, you can easily add the code for the metrics:

String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = module;
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = action;
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = userName;
oc.setEndToEndMetrics(metrics, (short) 0);


Now that the hard stuff is out of the way, let's throw in some AOP. Using Spring's AOP XMLNS makes this quite easy. Basically we will define our Aspect bean and then configure it as a before-type aspect.

<bean class="com.djs.OracleMetricsAspect" id="metricsAdvice" />
<aop:config>
<aop:aspect ref="metricsAdvice">
<aop:before method="addMetrics" pointcut="execution(* com.djs..*DAOImpl*.*(..))" />
</aop:aspect>
</aop:config>


This will run the metricsAdvice before any method that's executed in any class that's contains DAOImpl in it's name.

So now that we have that finished, here's the addMetrics method that's run in the OracleMetricsAspect class:

public void addMetrics(final JoinPoint joinPoint) {
  try {
    if (joinPoint.getTarget() instanceof HibernateDaoSupport) {
      HibernateTemplate ht = ((HibernateDaoSupport) joinPoint.getTarget()).getHibernateTemplate();
      ht.execute(new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
          String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
          String className = joinPoint.getTarget().getClass().getName();
          String module = className.substring(className.lastIndexOf(".") + 1);
          if (module.length() > 48) {
            metrics[OracleConnection.END_TO_END_MODULE_INDEX] = module.substring(0, 48);
          } else {
            metrics[OracleConnection.END_TO_END_MODULE_INDEX] = module;
          }
          String action = joinPoint.getSignature().toShortString();
          if (action.length() > 29) {
            metrics[OracleConnection.END_TO_END_ACTION_INDEX] = action.substring(0, 29) + "(" + joinPoint.getArgs().length + ")";
          } else {
            metrics[OracleConnection.END_TO_END_ACTION_INDEX] = action + "(" + joinPoint.getArgs().length + ")";
          }
          metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = SecurityUtils.getUserName(); // this is implementation specific
          try {
            Connection conn = session.connection();
            ConnectionWrapper cw = (ConnectionWrapper) conn;
            WSJdbcConnection connws = (WSJdbcConnection) cw.getWrappedConnection();
            OracleConnection oc = (OracleConnection) WSJdbcUtil.getNativeConnection(connws);
            oc.setEndToEndMetrics(metrics, (short) 0);
          } catch (Exception e) {
            logger.error(e.getMessage(), e);
          }
          return null;
        }
      });
    }
  } catch (Exception e) {
    logger.error(e.getMessage(), e);
  }
}

Since all of our DAOs extend Spring's HibernateDaoSupport, we have access to the HibernateTemplate. And from there we can get the Hibernate session. And from the JoinPoint, we have access to the class name and method that's being called so we can use that for the module and action for the Oracle metrics. Below is a screenshot of what the DBAs see in OIM:

Hope I didn't miss anything.

Have fun coding!