Grails SQL Logging part 2 – groovy.sql.Sql

I discussed options for logging Hibernate-generated SQL in an earlier post but today I was trying to figure out how to see the SQL from groovy.sql.Sql and didn’t have much luck at first. The core problem is that the Sql class uses a java.util.logging.Logger (JUL) while the rest of the world uses a Log4j logger (often with a Commons Logging or SLF4J wrapper). I assumed that since I am using the Grails support for JUL -> Log4j bridging (enabled with the grails.logging.jul.usebridge = true setting in Config.groovy) that all I needed to do was add the class name to my log4j DSL block:

log4j = {
   error 'org.codehaus.groovy.grails',
         'org.springframework',
         'org.hibernate',
         'net.sf.ehcache.hibernate'
   debug 'groovy.sql.Sql'
}

but that didn’t work. Some googling led to this mailing list discussion which has a solution involving a custom java.util.logging.Handler to pipe JUL log messages for the 'groovy.sql.Sql' logger to Log4j. That seemed like overkill to me since theoretically that’s exactly what grails.logging.jul.usebridge = true already does. I realized I had no idea how the bridging worked, so I started looking at the implementation of this feature.

It turns out that this is handled by the Grails “logging” plugin (org.codehaus.groovy.grails.plugins.log4j.LoggingGrailsPlugin) which calls org.slf4j.bridge.SLF4JBridgeHandler.install(). This essentially registers a listener that receives every JUL log message and pipes it to the corresponding SLF4J logger (typically wrapping a Log4j logger) with a sensible mapping of the different log levels (e.g. FINEST -> TRACE, FINER -> DEBUG, etc.)

So what’s the problem then? While grails.logging.jul.usebridge = true does configure message routing, it doesn’t apply level settings from the log4j block to the corresponding JUL loggers. So although I set the level of 'groovy.sql.Sql' to debug, the JUL logger level is still at the default level (INFO). So all I need to do is programmatically set the logger’s level to DEBUG (or TRACE to see everything) once, e.g. in BootStrap.groovy

import groovy.sql.Sql
import java.util.logging.Level

class BootStrap {

   def init = { servletContext ->
      Sql.LOG.level = Level.FINE
   }
}

I discussed options for logging Hibernate-generated SQL in an earlier post but today I was trying to figure out how to see the SQL from groovy.sql.Sql and didn’t have much...

This entry was posted on Wednesday, October 31st, 2012 at 10:25pm and is filed under database, grails, groovy, hibernate, java. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response (comments are moderated) or trackback from your own site.