Thursday, October 29, 2009

Toggle Hibernate "Show SQL" at Runtime

Okay, the title is a bit deceiving. As far as I know, you cannot modify the value of the configuration property "hibernate.show_sql." However, that does not prohibit you from toggling at runtime whether SQL statement are output to your log files.

Why would you want to do this?
Because turning on "show_sql" will flood your logs with all the SQL generated by hibernate, making it difficult to find a particular section you might be debugging or optimizing. Being able to toggle the output of SQL allows you to enable it just for the sections of code you are investigating.

First, the solution, then I'll get to why:


import org.apache.log4j.*;

...

Logger sqlLogger = Logger.getLogger("org.hibernate.SQL");
sqlLogger.setLevel(Level.DEBUG);

... do your stuff ...

sqlLogger.setLevel(Level.OFF);

So what's going on here?
The key is knowing what "show_sql" does and that it is not the only way to log the SQL statements generated by hibernate. Namely, "show_sql" sends all SQL to the standard output, but hibernate also uses Apache Commons Logging. So, as long as you configure commons logging to use an underlying logging framework that supports changing log levels at runtime, you can toggle the output of SQL by getting a hold of the "org.hibernate.SQL" logger and bending it to your will.