How to print a query string with parameter values when using Hibernate

For development with Wildfly (standalone.xml), add these loggers:

<logger category="org.hibernate.SQL">
   <level name="DEBUG"/>
</logger>
<logger category="org.hibernate.type.descriptor.sql">
   <level name="TRACE"/>
</logger>

The solution is correct but logs also all bindings for the result objects. To prevent this it's possible 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>

Hibernate shows query and their parameter values in different lines.

If you are using application.properties in Spring Boot and you can use below highlighted parameter in application.properties.

• org.hibernate.SQL will show queries:

  logging.level.org.hibernate.SQL=DEBUG
  

• org.hibernate.type will show all parameter values, which will map with select, insert and update queries.

  logging.level.org.hibernate.type=TRACE
  

• org.hibernate.type.EnumType will show enum type parameter value:

  logging.level.org.hibernate.type.EnumType=TRACE
  

  Example output:

  2018-06-14 11:06:28,217 TRACE [main] [EnumType.java : 321] Binding [active] to parameter: [1]
  

• sql.BasicBinder will show integer, varchar, boolean type parameter value

  logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
  

  Example output:

  * 2018-06-14 11:28:29,750 TRACE [http-nio-9891-exec-2] [BasicBinder.java : 65] binding parameter [1] as [BOOLEAN] - [true]
  * 2018-06-14 11:28:29,751 TRACE [http-nio-9891-exec-2] [BasicBinder.java : 65] binding parameter [2] as [INTEGER] - [1]
  * 2018-06-14 11:28:29,752 TRACE [http-nio-9891-exec-2] [BasicBinder.java : 65] binding parameter [3] as [VARCHAR] - [public]
  

Using a YAML property:

logging.level.org.hibernate:
    SQL: DEBUG
    type.descriptor.sql.BasicBinder: TRACE

In Java:

Transform your query in TypedQuery if it's a CriteriaQuery (javax.persistence).

Then:

query.unwrap(org.hibernate.Query.class).getQueryString();

The simplest solution for me is implementing a regular string replacement to replace parameter inputs with parameter values (treating all parameters as string, for simplicity):

String debuggedSql = sql;
// then, for each named parameter
debuggedSql = debuggedSql.replaceAll(":"+key, "'"+value.toString()+"'");
// and finally
System.out.println(debuggedSql);

Or something similar for positional parameters (?).

Take care of null values and specific value types like date, if you want a run ready SQL to be logged.

I had problems with all of the answers here. None of them actually gave me parameters for the Spring Data JPA query that was being passed an enum as the PK.

For Hibernate 5.3:

    <!-- silence the noise -->
    <Logger name="org.hibernate.search.engine.metadata.impl" additivity="false"/>
    <Logger name="org.hibernate.boot.internal" additivity="false"/>
    <Logger name="org.hibernate.engine.internal" additivity="false"/>
    <Logger name="org.hibernate.engine.jdbc" additivity="false"/>
    <Logger name="org.hibernate.engine.transaction" additivity="false"/>
    <Logger name="org.hibernate.engine.loading.internal" additivity="false"/>
    <Logger name="org.hibernate.engine.spi.CollectionEntry" additivity="false"/>
    <Logger name="org.hibernate.engine.query.spi.HQLQueryPlan" additivity="false"/>
    <Logger name="org.hibernate.engine.query.spi.QueryPlanCache" additivity="false"/>
    <Logger name="org.hibernate.engine.spi.IdentifierValue" additivity="false"/>
    <Logger name="org.hibernate.engine.spi.CascadingActions" additivity="false"/>
    <Logger name="org.hibernate.engine.spi.ActionQueue" additivity="false"/>
    <Logger name="org.jboss.logging"/>
    
    <Logger name="org.hibernate.SQL" level="debug" additivity="false">
      <AppenderRef ref="Console"/>
    </Logger>
    <Logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="trace" additivity="false">
      <AppenderRef ref="Console"/>
    </Logger>
    <Logger name="org.hibernate.engine" level="trace" additivity="false">
      <AppenderRef ref="Console"/>
    </Logger>

With hibernate.format_sql set to true for pretty print, this is a sample of my output:

17:00:00,664 [TRACE] Named parameters: {1=DE} [main] org.hibernate.engine.spi.QueryParameters.traceParameters(QueryParameters.java:325) 
17:00:00,671 [DEBUG] 
    select
        countrysub0_.code as code1_23_,
        countrysub0_1_.country_subdivision as country_1_61_ 
    from
        country_subdivision countrysub0_ 
    left outer join
        jurisdiction_country_subdivision countrysub0_1_ 
            on countrysub0_.code=countrysub0_1_.jurisdiction 
    where
        countrysub0_.code=? [main] org.hibernate.engine.jdbc.spi.SqlStatementLogger.logStatement(SqlStatementLogger.java:103) 

I tried simply setting org.hibernate.engine.spi.QueryParameters to trace, but for some reason the named parameters kept getting silenced, so I silenced everything else that was logging instead. It doesn't seem to log all parameters though, so I still needed the BasicBinding log as well.

you have to configure as below:

# Hibernate logging options (INFO only shows startup messages)
log4j.logger.org.hibernate=INFO

# Log JDBC bind parameter runtime arguments
log4j.logger.org.hibernate.type=trace

If you are using spring-boot you can use log4jdbc-spring-boot-starter dependency, which is a fork of org.bgee.log4jdbc-log4j2 .

In pom.xml I used the following dependency:

   <dependency>
        <groupId>com.integralblue</groupId>
        <artifactId>log4jdbc-spring-boot-starter</artifactId>
        <version>2.0.0</version>
    </dependency>

In documentation it's mentioned that if you just include this dependency SQL logging will not work, we have to specify following property in application.properties

logging.level.jdbc.sqlonly=INFO

but with

2.0.0 version, All the logging properties are set to info by default . at time of writing this answer the usage count for 2.0.0 is less compared to 1.0.2

So with 2.0.0 even if you don't specify any properties for logging sql following properties are set by default

logging.level.jdbc.sqlonly=INFO
logging.level.jdbc.resultset=INFO
logging.level.jdbc.connection=INFO
logging.level.jdbc.resultsettable=INFO
logging.level.jdbc.audit=INFO
logging.level.jdbc.sqltiming=INFO

So if you want only SQL queries printed with ? replaced with actual values and avoid unnecessary log from result set these properties explicitly to

logging.level.jdbc.sqlonly=INFO
logging.level.jdbc.resultset=OFF
logging.level.jdbc.connection=OFF
logging.level.jdbc.resultsettable=OFF
logging.level.jdbc.audit=OFF
logging.level.jdbc.sqltiming=OFF