Logging the Hibernate SQL Queries In a File

Krishna406 · **Joined:** Fri Jul 27, 2007 12:57 am **Posts:** 4

Hi,
I would like to log the SQL queries in a text File.
I have made show_sql as true in config file. But, the SQL Queries are printed in the console.
I would like them to be printed in a local text file.
I am using Log4j for logging.
How can I do it?
and I have included log4j.properties in the class path. The File which the log messages are going contains all non sense stuff regarding connections etc etc. How can I disable them?
Please help.

MikePloed · **Posted:** Fri Jul 27, 2007 9:47 am

Hi,
you can configure log4j to only log org.hibernate.SQL

Please refer to Chapter 3.5 in the reference Documentation for further details:
http://www.hibernate.org/hib_docs/v3/re ... on-logging

Cheers,
Mike

user_who_lost_his_account · **Joined:** Fri Jul 27, 2007 4:39 am **Posts:** 7

Don't forget: in order to get the JDBC parameters the type category should be set to trace, as debug is not enough.

micmic2000 · **Joined:** Tue Jul 17, 2007 9:05 am **Posts:** 9

Speaking about JDBC parameters, p6spy is also an interesting tool. It automatically replaces the '?' inside queries by the real values of parameters.

Krishna406 · **Joined:** Fri Jul 27, 2007 12:57 am **Posts:** 4

# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE
log4j.rootCategory=INFO,LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

log4j.logger.org.hibernate=info

log4j.logger.org.hibernate.SQL=debug

### enable the following line if you want to track down connection ###
### leakages when using DriverManagerConnectionProvider ###
#log4j.logger.net.sf.hibernate.connection.DriverManagerConnectionProvider=trace

### log JDBC bind parameters ###
log4j.logger.org.hibernate.type=trace

### log prepared statement cache activity ###
log4j.logger.org.hibernate.ps.PreparedStatementCache=warn

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.Threshold=INFO
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=D:\\hibernate.log
log4j.appender.LOGFILE.Append=false
log4j.appender.LOGFILE.Threshold=INFO
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n

This is my Log4j.properties file. Now I want the SQL statements(Eg: select * from EMP) to be printed in hibernate.log file.
How can I do that?

pierre-henry · **Posted:** Mon Jul 30, 2007 3:18 am

Hi,

You can use the spy sql driver which allows to save in a log file.