Database Operation Time Logging

Everyday in software world we use code for accessing data from database or will use some other libraries for accessing data from database.

During development phase it is very rare to have performance issues mainly because of less volume of data. But when you move your code to production environment you can have performance hits. Consider a scenario, it looks like all database access is slow.

To analyse this situation you want to enable time logging. Like log connection obtaining time and query execution times.

Suppose you haven't added those log statements while writing code; then it is a going to be tedious job to add those log statements in all your data access objects. Especially if you are working on a big project with more than 100 DAO classes.

If you are using some other external library for data access, and if you don't have source code for that library; then adding log statements is not possible. In this kind of scenario we need to use some kind of profiling tools. But running profiling tools on production environment is not that easy.

I faced a similar situation in one of my projects. I found AOP programming very useful in this kind of scenario. In this blog I going to explain how I used AOP to solve this problem. I selected AspectJ as the AOP implementation. If you are new to Aspectj, please refer AspectJ documention.

I created an Aspect with two Around pointcuts.

• One pointcut to log connection time.
• One to log query execution time.

After developing aspect we need to instruct AspectJ to weave this aspect to our DAO classes. Weaving process can be done either at compile time or at load time. In this blog I will explain about Load Time Weaving. If you don't have DAO class source code available then Load Time Weaving is the only option for you. To do this, basically what we need is an XML file named aop.xml. We need to put this file inside META-INF folder of your jar file.

Here is a sample aop.xml file.

After that I configured my server(Websphere) to use Load Time Weaving feature of AspectJ to weave DatabaseTiming aspect to my DAO classes. Please refer to my previous blog for websphere configuration details.

After making above configuration changes, restart your server. Now you should be able to see those log statements (One for obtaining connection and one for executing query). See how we added those log statements to our DAO classes without touching the DAO class.