Intercept the JDBC statements to the database using P6Spy in a hibernate application

Question

I am developing a hibernate console application and refer to this article http://www.mkyong.com/hibernate/how-to-display-hibernate-sql-parameter-values-solution/ to setup the P6Spy in order to see what is the actual value of the "(?)" of the SQL generated by the Hibernate.

The setup should work fine as I can see info/debug messages output by P6Spy during the application starts up. However ,after that, P6Spy seems that it cannot intercept any JDBC statements. From the log generated by hibernate (eg: Hibernate: insert into Module (projectName, moduleName, projectId) values (?, ?, ?) ), it can be sure that the JDBC statements are already sent to the database.

The following is my setting:

hibernate.cfg.xml

• Already specified to use the P6Spy driver : <property name="hibernate.connection.driver_class">com.p6spy.engine.spy.P6SpyDriver</property>
• Already turned on the hibernate.show_sql property : <property name="hibernate.show_sql">true</property>

spy.properties

• Already changed to use my actual database JDBC driver , filter set to false , includecategories already explicitly set to log anything.

module.log=com.p6spy.engine.logging.P6LogFactory
realdriver=oracle.jdbc.driver.OracleDriver
filter=false
dateformat=yyyy.MM.dd HH:mm:ss
excludecategories=
includecategories=error, info, batch, debug,statement,commit,rollback,result
appender=com.p6spy.engine.logging.appender.StdoutLogger

Do I miss any important setting ??

Answer 1

The usual cause for this type of problem is that the JDBC driver is loaded before the P6SpyDriver. You can get around this problem by setting deregisterdrivers=true in spy.properties.

BTW - This issue was fixed in 2.0-alpha-1.

Answer 2

Finally , I give up P6Spy as it is the old library which the latest version is back to 2003. I resort to use another similar free open source called log4jdbc to intercept the JDBC statements , see this for more info if you are interested.

Answer 3

I've never used P6Spy, but you can get the bind variables to output to your log. Try putting this in your log4j.properties or xml file.

log4j.category.org.hibernate.type=DEBUG, CONSOLE