As your Grails application grows in size and popularity you will find yourself with performance and concurrency problems to overcome. As part of this process you will likely find yourself debugging the raw sql sent to the database. Hibernate comes with functionality to do this, but it is not very most user friendly. The most popular tool for sql debugging is P6spy, but an up and coming alternative is Log4jdbc. Log4jdbc works similar to P6Spy in that you replace your usual sql driver with one provided by the tool.
//driverClassName = "org.h2.Driver" driverClassName = "net.sf.log4jdbc.DriverSpy"You then have to make a change to the database connection url. You must add log4jdbc to the protocol component. Log4jdbc should then be able to detect for which driver it is proxying for.
jdbc:log4jdbc:mysql://localhost/mydatabaseNext you need to add appropriately to the logging configuration of your Grails app. Log4jdbc provides lots of options. I like to use jdbcsqltiming; it captures the sql statements, and their duration:
info 'jdbcsqltiming'If you try the above in your development environment, it should work well. Now we move to trying to add it to a production / staging environment which uses Tomcat7 and MySql. If you simply try to apply the same settings as above, you might see the following error:
ClassNotFoundException net.sf.log4jdbc.DriverSpyThe reason for this is due to how Tomcat7 requires jdbc drivers to be placed in a common lib folder such as $TOMCAT_HOME/lib. So like your typical database driver, log4jdbc's must also be placed in the common lib folder. Unfortunately the log4jdbc driver has a number of logging related dependencies, so you must also place these in the common lib folder. Here is what I added to $TOMCAT_HOME/lib :
commons-logging-1.1.1.jar jcl-over-slf4j-1.6.2.jar jul-to-slf4j-1.6.2.jar log4j-1.2.16.jar log4jdbc4-1.2.jar slf-api-1.6.4.jar slf4j-log4j12-1.6.4.jarYou might see some warnings about your path containing multiple SLF4J bindings, but regardless your application should now be working with sql statements flying into your logs.