Monitoring SQL queries that get generated and executed by ORM is critical for troubleshooting and performance optimization. By default, all the logs get logged to console using HIBERNATECONSOLE, which is actually a console appender.

You can monitor and log the queries by:

  • Defining logsql in ormsettings: This is a simple way to quickly enable SQL logging. The flag should be enabled in Application.cfc: this.ormsettings.logsql = true. This logs all SQL queries that are generated by Hibernate to the console and server's output log file.
  • Using log4J.properties: Hibernate uses log4j for its logging and you can completely control its logging, including SQL, by modifying the log4j.properties, which is present under /lib directory. You can also direct the logging to a log file using the FileAppender provided by log4j. See log4j for more details on Appenders. Following is a sample snippet from the log4j.properties file:

###--------------- Hibernate Log Settings ------ 
### Set Hibernate log 
log4j.logger.org.hibernate=ERROR, HIBERNATECONSOLE 

### log just the SQL 
#log4j.logger.org.hibernate.SQL=DEBUG, HIBERNATECONSOLE 
#log4j.additivity.org.hibernate.SQL=false 
### Also log the parameter binding to the prepared statements. 
#log4j.logger.org.hibernate.type=DEBUG 
### log schema export/update ### 
log4j.logger.org.hibernate.tool.hbm2ddl=DEBUG, HIBERNATECONSOLE 
### log cache activity ### 
log4j.logger.org.hibernate.cache=ERROR, HIBERNATECONSOLE 
# HibernateConsole is set to be a ColsoleAppender for Hibernate message using a PatternLayout. 
log4j.appender.HIBERNATECONSOLE=org.apache.log4j.ConsoleAppender 
log4j.appender.HIBERNATECONSOLE.layout=org.apache.log4j.PatternLayout 
log4j.appender.HIBERNATECONSOLE.layout.ConversionPattern=%d{MM/dd HH:mm:ss} [%t] HIBERNATE %-5p - %m%n%n 
#---------------------------------------------

The log4j.properties configuration controls the logging for the following:

  • SQL generated for entity operations
  • Parameter binding for the prepared statements
  • SQL generated for DDL
  • Secondary cache operations

With the default settings, all the logs get logged to console. You can also direct the logging to a log file using the FileAppender provided by log4j. See log4j for more details on Appenders.

Log4j Properties Description
log4j.logger.org.hibernate.SQL This controls when and how the SQL will be logged. DEBUG says all the SQL will be logged
log4j.logger.org.hibernate.type This logs the parameter binding to the prepared statement.
log4j.logger.org.hibernate.tool.hbm2ddl Logs SQL for DDL, that is, schema export.
log4j.logger.org.hibernate.cache Logs secondary cache information.

Note:

You can use the ormsettings and log4J.properties logging methods simultaneously.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy