log4j hibernate show_sql to log file

Hibernate writes the SQL commands, which are logged via

<property name="hibernate.show_sql">true</property>

by default to console. In a tomcat envirement the default ist the catalina.out log file. To write your plain SQL logs to a seperate log file you have to configure a seperate logger for the SQL statements. You can do this by adding the following Logger in log4j.xml:

  <logger name="org.hibernate.SQL">
    <!-- level info logs -->
    <level  value="DEBUG" />
    <appender-ref ref="sqlAppender"/>    
  </logger>

And don’t forget to create a appender for it:

  <appender name="sqlAppender" class="org.apache.log4j.DailyRollingFileAppender">
    <param name="datePattern" value="'.'yyyy-MM-dd" />
    <param name="file" value="${log.path}/myproject-sql.log" />
    <param name="Append" value="true" />
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern"
             value="%d{ISO8601} %-5p [%t] %c: %m%n" />
    </layout>
  </appender>