Integerate P6spy with Liferay to format Hibernate SQL

When working on the customer's tickets, we may analysis the hibernate SQL to get some useful information, but it is really difficult to do that, the SQL file is disorganized, besides the most cursing problem is that there are so many “?” there, is there any way to make the hibernate SQL file be much more readable and legible ?

Introduction

P6Spy is a framework that enables database activity to be seamlessly intercepted and logged with no code changes to existing applications. It imitates a standard JDBC driver, for short, if adding the real driver to the p6spy configure file. then we can monitor the SQL processing steps and analyze the results.

IronTrack SQL is a software based on p6spy,but it is much more convenient to obserive the results.

Configration and Integeration

• P6spy

1. Download from http://sourceforge.net/projects/p6spy , “p6spy-install.jar”

2. Extract a clean Liferay Portal bundles,like “liferay-portal-tomcat-6.2-ee-sp7.zip”.

3. Extract “p6spy-install.jar”.

   copy “p6spy.jar” to “{server/tomcat}/lib/ext/”
   copy “spy.properties” to “{server/tomcat}//webapps/ROOT/WEB-INF/classes/”

4. Edit “spy.properties”

   set “logfile =” to you own log path:{$path$}/spy.log.
   set “realdriver =” to “realdriver=com.mysql.jdbc.Driver”
   set “deregisterdrivers =” to “deregisterdrivers = true”

5. Edit “portal-ext.properites”.

   set “jdbc.default.driverClassName =” value to ”com.p6spy.engine.spy.P6SpyDriver”

6. Edit “log4j.properties”.

   add “log4j.logger.p6spy=INFO,STDOUT”

7. Start up your portal server,the log has been written in the “spy.log”​

• IronTrack SQL

1. Download from http://www.ibm.com/developerworks/cn/java/j-lo-p6spy/IronTrackSQL.zip , “IronTrackSQL.zip”

2. Extract a clean Liferay Portal bundles,like “liferay-portal-tomcat-6.2-ee-sp7.zip”.

3. Extract “IronTrackSQL.zip”.

   copy “p6spy.jar, log4j-1.2.8.jar , irontracksql.jar” to “{server/tomcat}/lib/ext/”
   copy “spy.properties” to “{server/tomcat}/webapps/ROOT/WEB-INF/classes/”

4. Edit “spy.properties”

   set “logfile =” to you own log path:{$path$}/spy.log.
   set “realdriver =” to “realdriver=com.mysql.jdbc.Driver”
   set “deregisterdrivers =” to “deregisterdrivers = true”

5. Edit “portal-ext.properites”.

   set “jdbc.default.driverClassName =” value to ”com.p6spy.engine.spy.P6SpyDriver”

6. Edit “log4j.properties”.

   add “log4j.logger.p6spy=INFO,STDOUT”

7. Run command “java -jar irontracksql.jar” to start “IronTrack SQL”

8. Start up your portal server,the log has been written in the “spy.log” and logs have been show in “IronTrack SQL”.