Wednesday, April 25, 2012

Print query string in hibernate with parameter values


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 its not possible with hibernate api?


Source: Tips4all
Post by On
Tags , , , ,

5 comments:

  1. UserApril 25, 2012 at 7:23 PM

    You need to enable logging for the the following categories at debug and trace levels respectively:


    org.hibernate.SQL   - Log all SQL DML statements as they are executed
    org.hibernate.type - Log all JDBC parameters


    So a log4j configuration could look like:

    # logs the SQL statements
    log4j.logger.org.hibernate.SQL=debug

    # Logs the JDBC parameters passed to a query
    log4j.logger.org.hibernate.type=trace


    The first is equivalent to hibernate.show_sql=true legacy property, the second prints the bound parameters among other things.

    Another solution (non hibernate based) would be to use a JDBC proxy driver like P6Spy.

    ReplyDelete
  2. UserApril 25, 2012 at 7:23 PM

    turn on the org.hibernate.type Logger to see how the actual parameters are bind to the question marks.

    ReplyDelete
  3. UserApril 25, 2012 at 7:23 PM

    if you are using hibernate 3.2.xx
    use

    log4j.logger.org.hibernate.SQL=trace


    instead of

    log4j.logger.org.hibernate.SQL=debug

    ReplyDelete
  4. UserApril 25, 2012 at 7:23 PM

    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>

    ReplyDelete
  5. UserApril 25, 2012 at 7:23 PM

    Add the following line in the hibernate configuration xml file.

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

    ReplyDelete

Subscribe to: Post Comments (Atom)