Integrate P6Spy with Spring

P6Spy is a tool to debug JDBC interaction with a database. It's a wrapper
of all JDBC elements ( Connection, PreparedStatement, ResultSet... ) and
it has a powerful feature in order to log all informations about those
interactions.

JDBC wrapper is described in the "Java Performance Tuning" book of Jack Shirazi ( O'Reilly) in the chapter 16.


All elements wrappers have a constructor with the element to wrap. We can use it but
there is another mode for datasource. This last mode must be used to wrap a datasource
defined in an application server for example.

However since we can manage our objects ( beans ) in Spring ( and datasources
too ), it's possible to add a P6Spy datasource and link it with the real datasource
with constructor injection.

<?xml version="1.0" encoding="ISO-8859-1"?>



<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"

               "http://www.springframework.org/dtd/spring-beans.dtd">



<beans>

<bean id="myDataSourceTarget"

      class="org.apache.commons.dbcp.BasicDataSource"

      destroy-method="close">

    <property name="driverClassName">

        <value>org.hsqldb.jdbcDriver</value>

    </property>

    <property name="url">

        <value>jdbc:hsqldb:hsql://localhost:9001</value>

    </property>

    <property name="username"><value>sa</value></property>

    <property name="password"><value></value></property>

</bean>



<bean id="myDataSource" class="com.p6spy.engine.spy.P6DataSource"

                                             destroy-method="close">

    <constructor-arg>

        <ref local="myDataSourceTarget"/>

    </constructor-arg>

</bean>



</beans>

Moreover a p6spy.log file must be add to the classpath of the application
to specify the location of the log file.

module.log=com.p6spy.engine.logging.P6LogFactory



executionthreshold=

outagedetection=false

outagedetectioninterval=

filter=false

include     =

exclude     =

sqlexpression =

autoflush   = true

dateformat=

includecategories=

excludecategories=info,debug,result,batch



stringmatcher=

stacktraceclass=



reloadproperties=false

reloadpropertiesinterval=60



useprefix=false



appender=com.p6spy.engine.logging.appender.FileLogger

logfile     = c:/spy.log



append=true



log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender

log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout

log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n



log4j.logger.p6spy=INFO,STDOUT

The output can be graphically view with a tool like Iron Track SQL.



Advantages of this solution are the following:

It isn't necessary to modify the configuration of the application
server datasource in order to integrate P6Spy.

The use of P6Spy is much easier. No deep knowledge of the format
of the P6Spy configuration file is required ( only specify the location
of log file ).