How to print a query string with parameter values

Question

Is it possible in Hibernate to print generated SQL queries with real values instead of question marks?

How would you suggest to print queries with real values if it is not possible with Hibernate API?

Answer 1

You can add category lines to log4j.xml:

<category name="org.hibernate.type">
    <priority value="TRACE"/>
</category>

and add hibernate properties:

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

Answer 2

<!-- A time/date based rolling appender -->
<appender name="FILE" class="org.apache.log4j.RollingFileAppender">
    <param name="File" value="logs/system.log" />
    <param name="Append" value="true" />
    <param name="ImmediateFlush" value="true" />
    <param name="MaxFileSize" value="200MB" />
    <param name="MaxBackupIndex" value="100" />

    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%d %d{Z} [%t] %-5p (%F:%L) - %m%n" />
    </layout>
</appender>

<appender name="journaldev-hibernate" class="org.apache.log4j.RollingFileAppender">
    <param name="File" value="logs/project.log" />
    <param name="Append" value="true" />
    <param name="ImmediateFlush" value="true" />
    <param name="MaxFileSize" value="200MB" />
    <param name="MaxBackupIndex" value="50" />

    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%d %d{Z} [%t] %-5p (%F:%L) - %m%n" />
    </layout>
</appender>

<logger name="com.journaldev.hibernate" additivity="false">
    <level value="DEBUG" />
    <appender-ref ref="journaldev-hibernate" />
</logger>

<logger name="org.hibernate" additivity="false">
    <level value="INFO" />
    <appender-ref ref="FILE" />
</logger>

<logger name="org.hibernate.type" additivity="false">
    <level value="TRACE" />
    <appender-ref ref="FILE" />
</logger>

<root>
    <priority value="INFO"></priority>
    <appender-ref ref="FILE" />
</root>

Answer 3

Here is what worked for me, set below property in the log4j.file:

log4j.logger.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Hibernate properties settings :

hibernate.show_sql=true

Answer 4

Logging works but not exactly you want or i wanted some time ago, but P6Spy does work perfectly,

here is the simple tutorial to implement as well MKYONG tutorial for P6Spy.

for me it worked like charm.

1. Download P6Spy library

Get the “p6spy-install.jar“

2. Extract it

Extract the p6spy-install.jar file, look for p6spy.jar and spy.properties

3. Add library dependency

Add p6spy.jar into your project library dependency

4. Modify P6Spy properties file

Modify your database configuration file. You need to replace your existing JDBC driver with P6Spy JDBC driver – com.p6spy.engine.spy.P6SpyDriver

Original is MySQL JDBC driver – com.mysql.jdbc.Driver

<session-factory>
  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="show_sql">true</property>
</session-factory>

Changed it to P6Spy JDBC driver – com.p6spy.engine.spy.P6SpyDriver

<session-factory>
  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
  <property name="hibernate.connection.driver_class">com.p6spy.engine.spy.P6SpyDriver
  </property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="show_sql">true</property>
</session-factory>

5. Modify P6Spy properties file Modify the P6Spy properties file – spy.properties

Replace the real driver with your existing MySQL JDBC driver

realdriver=com.mysql.jdbc.Driver

#specifies another driver to use
realdriver2=
#specifies a third driver to use
realdriver3=

Change the Log file location Change the log file location in logfile property, all SQL statements will log into this file.

Windows

logfile     = c:/spy.log

*nix

logfile     = /srv/log/spy.log

6. Copy “spy.properties” to project classpath

Copy “spy.properties” to your project root folder, make sure your project can locate “spy.properties”, else it will prompt “spy.properties” file not found exception.

Answer 5

The solution is correct but logs also all bindings for the result objects. To prevent this it's possibile to create a separate appender and enable filtering, for example:

<!-- A time/date based rolling appender -->
<appender name="FILE_HIBERNATE" class="org.jboss.logging.appender.DailyRollingFileAppender">
    <errorHandler class="org.jboss.logging.util.OnlyOnceErrorHandler"/>
    <param name="File" value="${jboss.server.log.dir}/hiber.log"/>
    <param name="Append" value="false"/>
    <param name="Threshold" value="TRACE"/>
    <!-- Rollover at midnight each day -->
    <param name="DatePattern" value="'.'yyyy-MM-dd"/>

    <layout class="org.apache.log4j.PatternLayout">
        <!-- The default pattern: Date Priority [Category] Message\n -->
        <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
    </layout>

    <filter class="org.apache.log4j.varia.StringMatchFilter">
        <param name="StringToMatch" value="bind" />
        <param name="AcceptOnMatch" value="true" />
    </filter>
    <filter class="org.apache.log4j.varia.StringMatchFilter">
        <param name="StringToMatch" value="select" />
        <param name="AcceptOnMatch" value="true" />
    </filter>  
    <filter class="org.apache.log4j.varia.DenyAllFilter"/>
</appender> 

<category name="org.hibernate.type">
  <priority value="TRACE"/>
</category>

<logger name="org.hibernate.type">
   <level value="TRACE"/> 
   <appender-ref ref="FILE_HIBERNATE"/>
</logger>

<logger name="org.hibernate.SQL">
   <level value="TRACE"/> 
   <appender-ref ref="FILE_HIBERNATE"/>
</logger>

Answer 6

if you are using hibernate 3.2.xx use

log4j.logger.org.hibernate.SQL=trace

instead of

log4j.logger.org.hibernate.SQL=debug