How to log parameters of hibernate queries

Every development needs a debugging.

Sometimes it's quite hard to figure out what's the problem in our Hibernate-powered JPQL queries with default logging setup.
If we need to see some meaningful data instead of annoying "?" signs in the logs, just use the following quick approach for the local Tomcat/TomEE installation:

First of all, make sure, the SQL-logging is enabled in your META-INF/persistence.xml:

<properties>
[SKIPPED]
 <property name="hibernate.show_sql" value="true" />
 <property name="hibernate.format_sql" value="true" />
 <property name="hibernate.use_sql_comments" value="true" />
</properties>

In the LIB directory of your local TomEE, e.g. C:\Java\apache-tomee-webprofile-1.6.0.2\lib place 2 following files:

• log4j-1.2.14.jar, and
• log4j.xml

Below is the sample log4j.xml contents:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration PUBLIC "-//LOGGER" "http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
    <appender name="stdout" class="org.apache.log4j.ConsoleAppender">
        <layout class="org.apache.log4j.PatternLayout">
            <!-- The default pattern: Date Priority [Category] Message\n -->
            <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] [%c{3}]\: %m%n" />
        </layout>
    </appender>
 
    <appender name="hibernateLog" class="org.apache.log4j.RollingFileAppender">
        <param name="file" value="hibernate_log4j.log"/>
        <param name="MaxFileSize" value="10MB"/>
        <param name="MaxBackupIndex" value="4"/>
        <param name="Append" value="true"/>
 
        <filter class="org.apache.log4j.varia.StringMatchFilter">
            <param name="StringToMatch" value="ORA-00054" />
            <param name="AcceptOnMatch" value="false" />
        </filter>
 
        <filter class="org.apache.log4j.varia.StringMatchFilter">
            <param name="StringToMatch" value="SQL Error: 54" />
            <param name="AcceptOnMatch" value="false" />
        </filter>
 
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] [%c{3}]\: %m%n" />
        </layout>
    </appender>
 
 
    <logger name="org.hibernate.SQL" additivity="false">
        <level value="trace" />
        <appender-ref ref="hibernateLog" />
    </logger>
 
    <logger name="org.hibernate.type" additivity="false">
        <level value="trace" />
        <appender-ref ref="hibernateLog" />
    </logger>
 
    <logger name="org.hibernate" additivity="false">
        <level value="info" />
        <appender-ref ref="hibernateLog" />
    </logger>
 
    <root>
        <priority value="info"/>
        <appender-ref ref="stdout"/>
    </root>
 
</log4j:configuration>

Restart your java web container.

In the current configuration the new log file will be created in C:\Java\apache-tomee-webprofile-1.6.0.2\bin\ hibernate_log4j.log

The sample logger output could look like this:

2014-11-05 09:22:12.151 DEBUG [http-bio-8080-exec-1] [org.hibernate.SQL]:
    /* load com.db.fatca.manual.model.User */ select
        user0_.WEBSSOID as WEBSSOID1_27_3_,
        [SKIPPED…]
    from
        USER_PROFILE user0_
    left outer join
        USER_PROCESSING_COUNTRY assignedco1_
        [SKIPPED…]
    where
        user0_.WEBSSOID=?
    order by
        country2_.COUNTRY_NAME asc
2014-11-05 09:22:12.201 TRACE [http-bio-8080-exec-1] [descriptor.sql.BasicBinder]: binding parameter [1] as [VARCHAR] - qa

eof