Hibernate logging too much information

I have following information in log4j.properties file..

log4j.appender.file = org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.File = C:\\app\\file.log
log4j.appender.file.Threshold = TRACE
log4j.appender.file.DatePattern = '.'yyy-MM-dd
log4j.appender.file.layout = org.apache.log4j.Pa开发者_运维百科tternLayout
log4j.appender.file.layout.ConversionPattern = %d %5p [%C:%M:%L] - %m%n
log4j.rootLogger = DEBUG, file
#HIbernate logging
log4j.logger.org.hibernate.SQL = DEBUG, file
log4j.logger.org.hibernate.type = TRACE, file
log4j.additivity.org.hibernate.SQL = false

Problem with this configuration is that it is writing too much of information to log file and the file is growing into MBs with in a short time. So I was wondering if there is anything I can do to stop hibernate messing up the log file and write only sql select statement and parameters and nothing else.

Instead of really verbose and not intuitive hibernate SQL logging you can use a Pass through Java JDBC driver that can log SQL and/or JDBC calls for other JDBC drivers.

Pass through JDBC driver have the advantage that the logged output, for prepared statements, have the bind arguments automatically inserted into the SQL output. This greatly Improves readability, debugging and the size of the log for many cases. SQL timing information can be generated when needed.

With hibernate's logging you have logging similar to :

 select bs0_.A_REF as A2_7_0_ from B bs0_ where  bs0_.other = 0 and bs0_.A_REF=?  
 TRACE 2011-08-  03 00:30:45,317 binding '123' to parameter: 1

With big query with a lot of parameter it can be painfull to replace all the bind parameter before executing the query manually on the database.

With Pass through JDBC driver you have logging similar to :

select bs0_.A_REF as A2_7_0_ from B bs0_ where  bs0_.other = 0 and bs0_.A_REF=123  

All the bind parameter are resolved and you just have to copy paste it to execute the query on the database.

You can check log4jdbc as good pass through JDBC driver.
To shut off hibernate logging be sure to set to false the hibernate.show_sql properties in the config file and all SQL related logger. See the Logging Configuration section of the reference documentation