Logging SQL with Log4j - problem

wild_oscar · **Joined:** Wed May 23, 2007 1:07 pm **Posts:** 28

My current log4j properties file has the following input regarding Hibernate:

Code:

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

log4j.rootLogger=warn, stdout
#log4j.logger.org.hibernate=info
#log4j.logger.org.hibernate=DEBUG
log4j.logger.org.hibernate=debug, stdout 
log4j.logger.org.hibernate.SQL=debug, stdout
log4j.logger.org.hibernate.type=info, stdout
log4j.logger.org.hibernate.engine.QueryParameters=debug 

and the file is in the lib folder of my web application.

Unfortunately, I cannot get any Hibernate logs on my tomcat logging file unless I explicitly set the attribute

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

on my hibernate.cfg.xml file. (and I can't get my queryparameters, on which I am interested in).

Can anyone help me understand what am I doing wrong for the hibernate not being logged?

tima · **Joined:** Wed Oct 04, 2006 2:47 am **Posts:** 12

Try moving the log4j.properties file to WEB-INF/classes/

wild_oscar · **Joined:** Wed May 23, 2007 1:07 pm **Posts:** 28

tima wrote:

Try moving the log4j.properties file to WEB-INF/classes/

Produces no effect. Removing the
<property name="show_sql">true</property>

proprety turns logging off, even though I have all the log4j.logger.org.hibernate settings in trace level!

Isn't the show_sql property supposed to be optional if you have the log4j property set?

And why can't I log the query parameters, no matter what option I use?

tima · **Joined:** Wed Oct 04, 2006 2:47 am **Posts:** 12

The show_sql property just enables logging to System.out.
The logging equivalent is the org.hibernate.SQL category.

Do you have the log4j in your WEB-INF/lib dir?

If you are still having problems, and are using tomcat, try placing a copy of log4j.properties in common/classes or shared/classes.
You can also get diagnostic output from log4j by specifying -Dlog4j.debug=true on the webserver command line.

wild_oscar · **Joined:** Wed May 23, 2007 1:07 pm **Posts:** 28

tima wrote:

The show_sql property just enables logging to System.out.
The logging equivalent is the org.hibernate.SQL category.

Do you have the log4j in your WEB-INF/lib dir?

If you are still having problems, and are using tomcat, try placing a copy of log4j.properties in common/classes or shared/classes.
You can also get diagnostic output from log4j by specifying -Dlog4j.debug=true on the webserver command line.

I am using tomcat 6.

I have tried placing the log4j.properties file in both the /lib and /classes directory of my web application, without success. Logging of other packages works regardless of the directory the file is in, because when I move it I update its location in my servlet description in web.xml:

Code:

   <servlet>
      <servlet-name>LogServelet</servlet-name>
      <servlet-class>aux.servlet.LogServlet</servlet-class>
      <init-param>
         <param-name>log4jconfig</param-name>
         <param-value>/WEB-INF/classes/log4j.properties</param-value>
      </init-param>
      <load-on-startup>1</load-on-startup>
   </servlet>

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

So, let me see if I get this straight:

- If the property "show_sql" is set to false or not set, you cannot get SQL logged anywhere (not to the console, not to any logging file) regardless of whatever log4j settings you use.

- If the property "show_sql" is set to true, you have the ability to log it (or not) using log4j configuration, but you can't avoid the SQL to show in your console, regardless of your log4j configuration.

Are the 2 statements above correct? It is what my personal experience shows.

wild_oscar · **Joined:** Wed May 23, 2007 1:07 pm **Posts:** 28

gonzao_diaz wrote:

So, let me see if I get this straight:

- If the property "show_sql" is set to false or not set, you cannot get SQL logged anywhere (not to the console, not to any logging file) regardless of whatever log4j settings you use.

- If the property "show_sql" is set to true, you have the ability to log it (or not) using log4j configuration, but you can't avoid the SQL to show in your console, regardless of your log4j configuration.

Are the 2 statements above correct? It is what my personal experience shows.

Yes, this is, as far as I remember, what happens.

After several hours trying to make it work, I eventually gave up on it.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Actually, I was wrong.
Probably I had some classes/libraries problem that prevented things from working correctly.

What tima said was correct, hibernate.show_sql just forces the SQL output to the console (indeed annoying of you don't want that), but want to retrieve the SQL by some other means.

Follow this checklist and you will be able to control what you see in terms of SQL code.

1) set the hibernate.show_sql to false in your connections
2) in your web.xml, add the following

Code:

    <context-param>
      <param-name>log4jConfigLocation</param-name>
      <param-value>/WEB-INF/classes/log4j.xml</param-value>
    </context-param>

and, if you are using Spring, this other fragment

Code:

<listener>
  <listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
</listener>

3) add the following file log4j.xml under the WEB-INF/classes directory of your deployed program (forget about the web server's directories)

Code:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

   <appender name="appenderConsole" class="org.apache.log4j.ConsoleAppender">

     <layout class="org.apache.log4j.SimpleLayout"/>
   </appender>


   <appender name="appenderFile" class="org.apache.log4j.RollingFileAppender">
     <param name="File" value="/logs/pubh3.javalog"/>
     <param name="Append" value="true"/>
     <param name="MaxFileSize" value="500KB"/>
     <param name="MaxBackupIndex" value="1"/>
     <layout class="org.apache.log4j.PatternLayout">
       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
     </layout>
   </appender>
   
   <appender name="appenderFileDBCon" class="org.apache.log4j.RollingFileAppender">
     <param name="File" value="/logs/pubh3.dbcon"/>
     <param name="Append" value="true"/>
     <param name="MaxFileSize" value="500KB"/>
     <param name="MaxBackupIndex" value="1"/>
     <layout class="org.apache.log4j.PatternLayout">
       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
     </layout>
   </appender> 
    
   
    <logger name="org.hibernate.SQL" additivity="false">
      <level value="debug" />
      <appender-ref ref="appenderFile" />
    </logger>
   
   <root>
        <level value="info"/>
       <appender-ref ref="appenderConsole" />
       <appender-ref ref="appenderFile" />
   </root>

</log4j:configuration>

As you can see, by taking in and out those appenders in <root>, you can decide what shows or not in the console and in the log file. You could also easily create a new file appender, just for the Hibernate SQL.

4) For good measure, these are my libraries (they should be under WEB-INF/lib of your deployed application, independently of what you have on your web server:

commons-logging-1.1.1.jar
log4j-1.2.15.jar