Reply

Hibernate: Log SQL Statements

At 11:06 PM on Aug 21, 2005, R.J. Lorimer wrote:

Hibernate, in the end, is all about dispatching SQL statements. SQL is at the heart of communication with an RDBMS system, and it is extremely important when struggling through performance problems or other bugs, that you know what is going on. After all, knowing the executed SQL allows you to determine the number of queries to complete an O/R mapping task, not to mention that seeing SQL queries is critical to understanding how to optimize the queries via indices and other database settings.

There are two ways (due to the legacy of Hibernate) to enable SQL logging. The first is to simply enable SQL logging in the Hibernate configuration properties by setting the hibernate.show_sql property to true:

SessionFactory sf = new Configuration()
.setProperty("hibernate.show_sql", "true")
// ...
.buildSessionFactory();

This is a nice quick and dirty solution, but it is relatively inflexible. SQL statements are always logged to System.out when that property is enabled, and on some servers, System.out isn't accessible by the average developer; or is cluttered with a million other log statements.

Alternatively, Hibernate uses the Apache-Jakarta Commons Logging package, which means that it will utilize log4j , java.util.logging , or potentially another logging framework. Typically, log messages are sent to commons logging, and then they are dispatched to one of these logging frameworks. Then, those logging frameworks determine where the message should be sent (log files, sockets, emails, database records, system out, system err, etc). It is easy to see how using these log frameworks will increase the flexibility of log statements. With Hibernate, simply setting the org.hibernate.SQL logger to 'DEBUG' or 'ALL' will ensure that all SQL statements are logged to the console. Here is an example of the log4j configuration for this:

log4j.logger.org.hibernate.SQL=DEBUG, SQL_APPENDER
log4j.additivity.org.hibernate.SQL=false

Note that in this case I have turned 'additivity' off for the org.hibernate.SQL logger. I like using this setting because it ensures that log messages aren't bubbled up to parent handlers - it is, of course, optional depending on your logging preference.

Until next time,

R.J. Lorimer
Contributing Editor - rj -at- javalobby.org
Author              - http://www.coffee-bytes.com
Software Consultant - http://www.crosslogic.com