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

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?

You need to enable logging for the the following categories:

• org.hibernate.SQL   - set to debug to log all SQL DML statements as they are executed
• org.hibernate.type - set to trace to 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.

Just for convenience, here is the same configuration example for Logback (SLF4J)

<appender name="SQLROLLINGFILE">
 <File>/tmp/sql.log</File>
 <rollingPolicy>
  <FileNamePattern>logFile.%d{yyyy-MM-dd}.log</FileNamePattern>
 </rollingPolicy>
 <layout>
  <Pattern>%-4date | %msg %n</Pattern>
 </layout>
</appender>

<logger name="org.hibernate.SQL" additivity="false" >	
 <level value="DEBUG" />	
 <appender-ref ref="SQLROLLINGFILE" />
</logger>

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

The output in your sql.log (example) then looks like this:

2013-08-30 18:01:15,083 | update stepprovider set created_at=?, lastupdated_at=?, version=?, bundlelocation=?, category_id=?, customer_id=?, description=?, icon_file_id=?, name=?, shareStatus=?, spversion=?, status=?, title=?, type=?, num_used=? where id=?
2013-08-30 18:01:15,084 | binding parameter [1] as [TIMESTAMP] - 2012-07-11 09:57:32.0
2013-08-30 18:01:15,085 | binding parameter [2] as [TIMESTAMP] - Fri Aug 30 18:01:15 CEST 2013
2013-08-30 18:01:15,086 | binding parameter [3] as [INTEGER] -
2013-08-30 18:01:15,086 | binding parameter [4] as [VARCHAR] - com.mypackage.foo
2013-08-30 18:01:15,087 | binding parameter [5] as [VARCHAR] -
2013-08-30 18:01:15,087 | binding parameter [6] as [VARCHAR] -
2013-08-30 18:01:15,087 | binding parameter [7] as [VARCHAR] - TODO
2013-08-30 18:01:15,087 | binding parameter [8] as [VARCHAR] -
2013-08-30 18:01:15,088 | binding parameter [9] as [VARCHAR] - [email protected]
2013-08-30 18:01:15,088 | binding parameter [10] as [VARCHAR] - PRIVATE
2013-08-30 18:01:15,088 | binding parameter [11] as [VARCHAR] - 1.0
2013-08-30 18:01:15,088 | binding parameter [12] as [VARCHAR] - 32
2013-08-30 18:01:15,088 | binding parameter [13] as [VARCHAR] - MatchingStep
2013-08-30 18:01:15,089 | binding parameter [14] as [VARCHAR] -
2013-08-30 18:01:15,089 | binding parameter [15] as [INTEGER] - 0
2013-08-30 18:01:15,089 | binding parameter [16] as [VARCHAR] - 053c2e65-5d51-4c09-85f3-2281a1024f64

If you are using Spring Boot, just config this:

application.yml

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type: TRACE

application.properties

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

and nothing more.

Your log will be something like this:

2020-12-07 | DEBUG | o.h.SQL:127 - insert into Employee (id, name, title, id) values (?, ?, ?, ?)
2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:64 - binding parameter [1] as [VARCHAR] - [001]
2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:64 - binding parameter [2] as [VARCHAR] - [John Smith]
2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:52 - binding parameter [3] as [VARCHAR] - [null]
2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:64 - binding parameter [4] as [BIGINT] - [1]

Change hibernate.cfg.xml to:

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

Include log4j and below entries in "log4j.properties":

log4j.logger.org.hibernate=INFO, hb
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE

log4j.appender.hb=org.apache.log4j.ConsoleAppender
log4j.appender.hb.layout=org.apache.log4j.PatternLayout

Log4JDBC is a nice solution which prints the exact SQL going to the database with parameters in place, rather than the most popular answer here which does not do this. One major convenience of this is that you can copy the SQL straight to your DB front-end and execute it as-is.

• http://log4jdbc.sourceforge.net/
• https://code.google.com/p/log4jdbc-remix/

The latter also outputs a tabular representation of query results.

Sample Output showing generated SQL with params in place together with result set table from query:

5. insert into ENQUIRY_APPLICANT_DETAILS (ID, INCLUDED_IN_QUOTE, APPLICANT_ID, TERRITORY_ID, ENQUIRY_ID, ELIGIBLE_FOR_COVER) values (7, 1, 11, 1, 2, 0) 

10 Oct 2013 16:21:22 4953 [main] INFO  jdbc.resultsettable  - |---|--------|--------|-----------|----------|---------|-------|
10 Oct 2013 16:21:22 4953 [main] INFO  jdbc.resultsettable  - |ID |CREATED |DELETED |CODESET_ID |NAME      |POSITION |PREFIX |
10 Oct 2013 16:21:22 4953 [main] INFO  jdbc.resultsettable  - |---|--------|--------|-----------|----------|---------|-------|
10 Oct 2013 16:21:22 4953 [main] INFO  jdbc.resultsettable  - |2  |null    |null    |1          |Country 2 |1        |60     |
10 Oct 2013 16:21:22 4953 [main] INFO  jdbc.resultsettable  - |---|--------|--------|-----------|----------|---------|-------|

---

Update 2016

Most recently I have now been using log4jdbc-log4j2 (https://code.google.com/archive/p/log4jdbc-log4j2/ ) with SLF4j and logback. Maven dependencies required for my setup are as below:

<dependency>
	<groupId>org.bgee.log4jdbc-log4j2</groupId>
	<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
	<version>1.16</version>
</dependency>
<dependency>
	<groupId>org.slf4j</groupId>
	<artifactId>slf4j-api</artifactId>
    <version>${slf4j.version}</version>
</dependency>
<dependency>
	<groupId>ch.qos.logback</groupId>
	<artifactId>logback-core</artifactId>
    <version>${logback.version}</version>
</dependency>
<dependency>
	<groupId>ch.qos.logback</groupId>
	<artifactId>logback-classic</artifactId>
    <version>$logback.version}</version>
</dependency>

The driver and DB URLs then look like:

database.driver.class=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
database.url=jdbc:log4jdbc:hsqldb:mem:db_name #Hsql
#database.url=jdbc:log4jdbc:mysql://localhost:3306/db_name 

My logback.xml configuration file looks like the below: this outputs all SQL statements with parameters plus the resultset tables for all queries.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

	<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
		<encoder>
			<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n
			</pattern>
		</encoder>
	</appender>

	<logger name="jdbc.audit" level="ERROR" />
	<logger name="jdbc.connection" level="ERROR" />
	<logger name="jdbc.sqltiming" level="ERROR" />
	<logger name="jdbc.resultset" level="ERROR" />
	
    <!-- UNCOMMENT THE BELOW TO HIDE THE RESULT SET TABLE OUTPUT -->
    <!--<logger name="jdbc.resultsettable" level="ERROR" /> -->

	<root level="debug">
		<appender-ref ref="STDOUT" />
	</root>
</configuration>

Finally, I had to create a file named log4jdbc.log4j2.properties at the root of the classpath e.g. src/test/resources or src/main/resources in a Maven project. This file has one line:

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

The above will depend on your logging library. See the docs at https://code.google.com/archive/p/log4jdbc-log4j2 for more info.

Sample Output:

10:44:29.400 [main] DEBUG jdbc.sqlonly -  org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
5. select memberrole0_.member_id as member_i2_12_0_, memberrole0_.id as id1_12_0_, memberrole0_.id 
as id1_12_1_, memberrole0_.member_id as member_i2_12_1_, memberrole0_.role_id as role_id3_12_1_, 
role1_.id as id1_17_2_, role1_.name as name2_17_2_ from member_roles memberrole0_ left outer 
join roles role1_ on memberrole0_.role_id=role1_.id where memberrole0_.member_id=104 

10:44:29.402 [main] INFO  jdbc.resultsettable - 
|----------|---|---|----------|--------|---|-----|
|member_id |id |id |member_id |role_id |id |name |
|----------|---|---|----------|--------|---|-----|
|----------|---|---|----------|--------|---|-----|

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>

Add the following to your log4j or logback configuration:

org.hibernate.sql=DEBUG
org.hibernate.type.descriptor.sql.BasicBinder=TRACE

You can do it using the datasource-proxy, as I described in this post.

Assuming your application expects a dataSource bean (e.g. via @Resource), this is how you can configure datasource-proxy:

<bean id="actualDataSource" class="bitronix.tm.resource.jdbc.PoolingDataSource" init-method="init"
  destroy-method="close">
	<property name="className" value="bitronix.tm.resource.jdbc.lrc.LrcXADataSource"/>
	<property name="uniqueName" value="actualDataSource"/>
	<property name="minPoolSize" value="0"/>
	<property name="maxPoolSize" value="5"/>
	<property name="allowLocalTransactions" value="false" />
	<property name="driverProperties">
    	<props>
        	<prop key="user">${jdbc.username}</prop>
        	<prop key="password">${jdbc.password}</prop>
        	<prop key="url">${jdbc.url}</prop>
        	<prop key="driverClassName">${jdbc.driverClassName}</prop>
    	</props>
	</property>
</bean>

<bean id="proxyDataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
	<property name="dataSource" ref="testDataSource"/>
	<property name="listener">
    	<bean class="net.ttddyy.dsproxy.listener.ChainListener">
        	<property name="listeners">
            	<list>
                	<bean class="net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener">
                    	<property name="logLevel" value="INFO"/>
                	</bean>
                	<bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
            	</list>
        	</property>
    	</bean>
	</property>
</bean>

<alias name="proxyDataSource" alias="dataSource"/>

Now the Hibernate output vs. datasource-proxy:

INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:1, Num:1, Query:{[select company0_.id as id1_6_, company0_.name as name2_6_ from Company company0_][]}
INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into WarehouseProductInfo (id, quantity) values (default, ?)][19]}
INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into Product (id, code, company_id, importer_id, name, version) values (default, ?, ?, ?, ?, ?)][phoneCode,1,-5,Phone,0]}

The datasource-proxy queries contain parameter values and you can even add custom JDBC statement interceptors so that you can catch N+1 query issues right from your integration tests.

Turn on the org.hibernate.type logger to see how the actual parameters are bind to the question marks.

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>

http://jakarta.apache.org/log4j/" debug="false">

<!-- 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>

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>

This answer is a little variance for the question. Sometimes, we only need the sql only for debug purposes in runtime. In that case, there are a more easy way, using debug on editors.

• Put a breakpoint on org.hibernate.loader.Loader.loadEntityBatch (or navigate on the stack until there);
• When execution is suspended, look the value of variable this.sql

This is for Hibernate 3. I'm not sure that this work on other versions.

The MySQL JDBC driver already provides a convenient feature to meet this requirement. You must at least the have the JAR version greater than or equal to 5.1.6 (e.g. mysql-connect-jar-5.1.6.jar)

1. Configure jdbc.url to add logger your logger and custom logging:

   jdbc.url=jdbc:mysql://host:port/your_db?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true&profilerEventHandler=com.xxx.CustomLoggingProfilerEventHandler
   

It is using slf4j logging, if your default logging is log4j, you must add slf4j-api and slf4j-log4j12 as dependencies to use slf4j logging:

2. Write your custom logging code:

   package com.xxx;
   import java.sql.SQLException;
   import java.util.Properties;
   
   import com.mysql.jdbc.Connection;
   import com.mysql.jdbc.log.Log;
   
   public class CustomLoggingProfilerEventHandler implements ProfilerEventHandler {
       private Log log;
   
       public LoggingProfilerEventHandler() {
       }
   
       public void consumeEvent(ProfilerEvent evt) {
           /**
               * you can only print the sql as his.log.logInfo(evt.getMessage())
               * you can adjust your sql print log level with: DEBUG,INFO
               * you can also handle the message to meet your requirement
               */ 
           this.log.logInfo(evt);
       }
   
       public void destroy() {
           this.log = null;
       }
   
       public void init(Connection conn, Properties props) throws SQLException {
           this.log = conn.getLog();
       }
   }
   

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

I like this for log4j:

log4j.logger.org.hibernate.SQL=trace
log4j.logger.org.hibernate.engine.query=trace
log4j.logger.org.hibernate.type=trace
log4j.logger.org.hibernate.jdbc=trace
log4j.logger.org.hibernate.type.descriptor.sql.BasicExtractor=error 
log4j.logger.org.hibernate.type.CollectionType=error 

Log4Jdbc plugin would be best for your requirement. It shows following-

1. Complete SQL query being hit to the db
2. Parameter values being passed to the query
3. Execution time taken by each query

Refer below link to configure Log4Jdbc-

https://code.google.com/p/log4jdbc/

If you want Hibernate to print generated SQL queries with real values instead of question marks, add following entries to hibernate.cfg.xml/hibernate.properties:

show_sql=true
format_sql=true
use_sql_comments=true

And add following entries to log4j.properties:

log4j.logger.org.hibernate=INFO, hb
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.appender.hb=org.apache.log4j.ConsoleAppender
log4j.appender.hb.layout=org.apache.log4j.PatternLayout

Logging works but not exactly what you want and I wanted some time ago, but P6Spy does work perfectly.

Here is simple tutorial to implement as well. For me it worked like charm.

1. Download the P6Spy library: Get 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 the 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>  <!-- note the difference -->
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/myuser</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>  <!-- note the difference -->
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/myuser</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: spy.properties

Replace the realdriver 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 logfile location in the logfile property. All SQL statements will be logged into this file.

Windows:

logfile=c:/spy.log

UNIX:

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, or else a spy.properties file not found exception will be thrown.

http://jakarta.apache.org/log4j/" debug="false">

<appender name="console" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
	<param name="ConversionPattern" 
	  value="%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n" />
    </layout>
</appender>

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

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

Using Hibernate 4 and slf4j/log4j2, I tried adding the following to my log4j2.xml configuration:

<Logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="trace" additivity="false"> 
    <AppenderRef ref="Console"/> 
</Logger> 
<Logger name="org.hibernate.type.EnumType" level="trace" additivity="false"> 
    <AppenderRef ref="Console"/>
</Logger>

but without success.

I found out through this thread that the jboss-logging framework used by Hibernate needed to be configured in order to log through slf4j. I added the following argument to the VM arguments of the application:

-Dorg.jboss.logging.provider=slf4j

And it worked like a charm.

If you are using hibernate 3.2.X use this:

log4j.logger.org.hibernate.SQL=trace

instead of this:

log4j.logger.org.hibernate.SQL=debug 

All of the answers here are helpful, but if you're using a Spring application context XML to setup your session factory, setting the log4j SQL level variable only gets you part of the way there, you also have to set the hibernate.show_sql variable in the app context itself to get Hibernate to start actually showing the values.

ApplicationContext.xml has:

<property name="hibernateProperties">
  		    <value>
  			hibernate.jdbc.batch_size=25
  			... <!-- Other parameter values here -->
			hibernate.show_sql=true
  		    </value>
 </property>

And your log4j file needs

log4j.logger.org.hibernate.SQL=DEBUG

In Java:

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

Then:

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

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

Use Wireshark or something similar:

None of the above mentioned answers will print SQL with parameters properly, or it is a pain to get it working with them. I achieved this by using WireShark, which captures all SQL/commands being send from the application to Oracle/MySQL etc. with the queries.

You can log this: net.sf.hibernate.hql.QueryTranslator

Output example:

2013-10-31 14:56:19,029 DEBUG [net.sf.hibernate.hql.QueryTranslator] HQL: select noti.id, noti.idmicrosite, noti.fcaducidad, noti.fpublicacion, noti.tipo, noti.imagen, noti.visible, trad.titulo, trad.subtitulo, trad.laurl, trad.urlnom, trad.fuente, trad.texto  from org.ibit.rol.sac.micromodel.Noticia noti join noti.traducciones trad where index(trad)='ca' and noti.visible='S' and noti.idmicrosite=985 and noti.tipo=3446       
2013-10-31 14:56:19,029 DEBUG [net.sf.hibernate.hql.QueryTranslator] SQL: select noticia0_.NOT_CODI as x0_0_, noticia0_.NOT_MICCOD as x1_0_, noticia0_.NOT_CADUCA as x2_0_, noticia0_.NOT_PUBLIC as x3_0_, noticia0_.NOT_TIPO as x4_0_, noticia0_.NOT_IMAGEN as x5_0_, noticia0_.NOT_VISIB as x6_0_, traduccion1_.NID_TITULO as x7_0_, traduccion1_.NID_SUBTIT as x8_0_, traduccion1_.NID_URL as x9_0_, traduccion1_.NID_URLNOM as x10_0_, traduccion1_.NID_FUENTE as x11_0_, traduccion1_.NID_TEXTO as x12_0_ from GUS_NOTICS noticia0_ inner join GUS_NOTIDI traduccion1_ on noticia0_.NOT_CODI=traduccion1_.NID_NOTCOD where (traduccion1_.NID_CODIDI='ca' )and(noticia0_.NOT_VISIB='S' )and(noticia0_.NOT_MICCOD=985 )and(noticia0_.NOT_TIPO=3446 )

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.

hibernate-types has a utility method to log JPQL and criteria queries. More details can be found in the authors's blog post. Here is an example using JPQL:

Query jpql = entityManager.createQuery("""
    select
       YEAR(p.createdOn) as year,
       count(p) as postCount
    from
       Post p
    group by
       YEAR(p.createdOn)
    """, Tuple.class
);
String sql = SQLExtractor.from(jpql);

The sql variable has the following value:

SELECT
    extract(YEAR FROM sqlextract0_.created_on) AS col_0_0_,
    count(sqlextract0_.id) AS col_1_0_
FROM
    post p
GROUP BY
    extract(YEAR FROM p.created_on)

It can be copy-pasted into the DB console as-is.

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