Logging SQL queries and parameter values from Hibernate in JBoss EAP

Environment

• Red Hat JBoss Enterprise Application Platform (EAP)
  • 8
  • 7
  • 6
  • 5
  • 4
• Hibernate
  • 6
  • 5
  • 4
  • 3

Issue

• How do I find out what SQL queries are being sent to the database?
• I need to print the parameter values for the prepared statements.

Resolution

Configuring Hibernate SQL Logging in JBoss EAP

• For JBoss EAP 8 or later, Hibernate SQL logging can be enabled by addint the following logger categories in the logging subsystem:

  • org.hibernate.SQL : DEBUG
  • org.hibernate.type : TRACE
  • org.hibernate.metamodel.model : TRACE
  • org.hibernate.orm.jdbc.bind : TRACE
  • org.hibernate.orm.results : DEBUG
  • org.hibernate.internal.util.EntityPrinter : DEBUG

• For JBoss EAP 6 or JBoss EAP 7, Hibernate SQL logging can be enabled by adding the following logger categories in the logging subsystem:

  • org.hibernate.SQL : DEBUG
  • org.hibernate.type : TRACE
  • org.hibernate.metamodel.model : TRACE
  • If bindings are not seen, use TRACE for the top level category org.hibernate

  See Change logging levels using the CLI command in JBoss EAP 6 or 7 for more information.

  In many cases, it may be useful to enable Spy logging in addition (or as an alternative) to using Hibernate SQL logging. A difference is that Spy logging will capture all use of a given datasource (not merely use of the datasource by Hibernate).

• For JBoss EAP 5 or earlier, edit $JBOSS_HOME/server/$PROFILE/conf/jboss-log4j.xml:

    <!-- Log SQL sent to the database -->
    <category name="org.hibernate.SQL">
        <priority value="DEBUG"/>
    </category>
    <!-- Log values bound to SQL statements and values retrieved from query result sets -->
    <category name="org.hibernate.type">
        <priority value="TRACE"/>
    </category>
  

Format SQL

• To pretty-print the SQL log messages set the option "hibernate.format_sql" to true
  • This option may be used whether SQL logging is enabled by the JBoss logging configuration or via the show_sql property.
  • format_sql may be configured in the hibernate configuration file (persistence.xml, hibernate.cfg.xml, hibernate.properties, etc.).
  • NOTE: Analysis of logs (e.g. when piping to command line tools like grep, etc.), is often easier with this option disabled (set to false).

Hibernate SQL Logging to Standard Out

• To log SQL to the console (STDOUT) set the option "hibernate.show_sql" to true
  • This option will override any changes made to JBoss logging configuration and write directly to standard out when show_sql = true.
  • If you wish to control logging (enable/disable) via the JBoss logging configuration, do not set show_sql = true.
  • show_sql may be configured in the hibernate configuration file (persistence.xml, hibernate.cfg.xml, hibernate.properties, etc.)

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.