Wednesday, November 24, 2004

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 ).
  • No comments: