How to display hibernate sql parameter values – P6Spy


1. Download P6Spy library

Get the “p6spy-install.jar“, you can download it from

  1. P6Spy official website.
  2. P6Spy at Sourceforge.net

2. Extract it

Extract the p6spy-install.jar file, look for p6spy.jar and spy.properties

3. Add library dependency

Add p6spy.jar into your project library dependency along with commons-dbcp-1.4.jar

4. Modify P6Spy properties file

Modify your database configuration file. You need to replace your existing JDBC driver with P6Spy JDBC driver – ” com.p6spy.engine.spy.P6SpyDriver<!– SPY SETUP–>
<bean id=”dataSource” destroy-method=”close”>
<constructor-arg>
<ref local=”myDataSourceTarget”/>
</constructor-arg>
</bean>

<bean id=”myDataSourceTarget” destroy-method=”close”>

<property name=”driverClassName”>
<value>com.p6spy.engine.spy.P6SpyDriver</value>
</property>

<property name=”url”>
<value>jdbc:oracle:thin:@hostname:port:servicename</value>
</property>

<property name=”username”>
<value>username</value>
</property>

<property name=”password”>
<value>pw</value>
</property>

</bean>”

5. Modify P6Spy properties file

Modify the P6Spy properties file – “spy.properties# useful for debugging sql

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:\\temp\\spy.log

append=false

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=DEBUG,STDOUT

realdriver=oracle.jdbc.driver.OracleDriver”

Change the Log file location
Change the log file location in logfile property, all SQL statements will log into this file.

Windows

logfile     = c:/spy.log

*nix

logfile     = /srv/log/spy.log

6. Copy “spy.properties” to project classpath

Copy “spy.properties” to your project root folder, make sure your project can locate “spy.properties”, else it will prompt “spy.properties” file not found exception.

7. Done

Run your application and do some database transaction, you will notice all the SQL statements sent from application to database will be logged into a file you specified in “spy.properties”.

Sample log file as following.

insert into stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME)
values (?, ?, ?, ?, ?, ?)|
insert into stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME)
values (10.0, 1.1, '2009-12-30', 1.2, 11, 1000000)

Conclusion

P6Spy is really useful in reducing the developers’ debugging time. As long as your project is using JDBC driver for connection , P6Spy can fit into it and log all SQL statements and parameter values for you.

  1. #1 by Pablo on January 31, 2013 - 5:38 pm

    Did you achieve the JUnit logging with it? I’m trying without a success.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: