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

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

User · Answer

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

User · Answer

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    lt property name  hibernateProperties  gt               lt value gt              hibernate jdbc batch size 25                  lt  -- Other parameter values here -- gt              hibernate show sql true              lt  value gt    lt  property gt    And your log4j file needs  log4j logger org hibernate SQL DEBUG

User · Answer

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.

Download P6Spy library

Get the “p6spy-install.jar“

Extract it

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

Add library dependency

Add p6spy.jar into your project library dependency

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>

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

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.

User · Answer

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.

User · Answer

In case of spring boot is being used   just config this   aplication yml logging    level      org hibernate SQL  DEBUG     org hibernate type  TRACE  aplication 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   HTH

User · Answer

for development with Wildfly   standalone xml   add those loggers    lt logger category  org hibernate SQL  gt      lt level name  DEBUG   gt   lt  logger gt   lt logger category  org hibernate type descriptor sql  gt      lt level name  TRACE   gt   lt  logger gt

User · Answer

It works for me with logging level org hibernate SQL  DEBUG logging level org hibernate type TRACE

User · Answer

In Java   Transform your query in TypedQuery if it s a CriteriaQuery  javax persistence    Then   query unwrap org hibernate Query class  getQueryString

User · Answer

Use Wireshark or something similar   None of the above mentioned answers will print sql with parameters properly or is a pain  I achieved this by using WireShark  which captures all sql commands being send from the application to Oracle Mysql etc with the queries

User · Answer

lt  -- A time date based rolling appender -- gt   lt appender name  FILE  class  org apache log4j RollingFileAppender  gt       lt param name  File  value  logs system log    gt       lt param name  Append  value  true    gt       lt param name  ImmediateFlush  value  true    gt       lt param name  MaxFileSize  value  200MB    gt       lt param name  MaxBackupIndex  value  100    gt        lt layout class  org apache log4j PatternLayout  gt           lt param name  ConversionPattern  value   d  d Z    t   -5p   F  L  -  m n    gt       lt  layout gt   lt  appender gt    lt appender name  journaldev-hibernate  class  org apache log4j RollingFileAppender  gt       lt param name  File  value  logs project log    gt       lt param name  Append  value  true    gt       lt param name  ImmediateFlush  value  true    gt       lt param name  MaxFileSize  value  200MB    gt       lt param name  MaxBackupIndex  value  50    gt        lt layout class  org apache log4j PatternLayout  gt           lt param name  ConversionPattern  value   d  d Z    t   -5p   F  L  -  m n    gt       lt  layout gt   lt  appender gt    lt logger name  com journaldev hibernate  additivity  false  gt       lt level value  DEBUG    gt       lt appender-ref ref  journaldev-hibernate    gt   lt  logger gt    lt logger name  org hibernate  additivity  false  gt       lt level value  INFO    gt       lt appender-ref ref  FILE    gt   lt  logger gt    lt logger name  org hibernate type  additivity  false  gt       lt level value  TRACE    gt       lt appender-ref ref  FILE    gt   lt  logger gt    lt root gt       lt priority value  INFO  gt  lt  priority gt       lt appender-ref ref  FILE    gt   lt  root gt

User · Answer

Change hibernate cfg xml to    lt property name  show sql  gt true lt  property gt   lt property name  format sql  gt true lt  property gt   lt property name  use sql comments  gt true lt  property gt    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

User · Answer

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    lt  -- A time date based rolling appender -- gt   lt appender name  FILE HIBERNATE  class  org jboss logging appender DailyRollingFileAppender  gt       lt errorHandler class  org jboss logging util OnlyOnceErrorHandler   gt       lt param name  File  value    jboss server log dir  hiber log   gt       lt param name  Append  value  false   gt       lt param name  Threshold  value  TRACE   gt       lt  -- Rollover at midnight each day -- gt       lt param name  DatePattern  value     yyyy-MM-dd   gt        lt layout class  org apache log4j PatternLayout  gt           lt  -- The default pattern  Date Priority  Category  Message n -- gt           lt param name  ConversionPattern  value   d  -5p   c   m n   gt       lt  layout gt        lt filter class  org apache log4j varia StringMatchFilter  gt           lt param name  StringToMatch  value  bind    gt           lt param name  AcceptOnMatch  value  true    gt       lt  filter gt       lt filter class  org apache log4j varia StringMatchFilter  gt           lt param name  StringToMatch  value  select    gt           lt param name  AcceptOnMatch  value  true    gt       lt  filter gt         lt filter class  org apache log4j varia DenyAllFilter   gt   lt  appender gt     lt category name  org hibernate type  gt     lt priority value  TRACE   gt   lt  category gt    lt logger name  org hibernate type  gt      lt level value  TRACE   gt       lt appender-ref ref  FILE HIBERNATE   gt   lt  logger gt    lt logger name  org hibernate SQL  gt      lt level value  TRACE   gt       lt appender-ref ref  FILE HIBERNATE   gt   lt  logger gt

User · Answer

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

User · Answer

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 ::
```
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 ::
  - 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]

User · Answer

You can add category lines to log4j xml    lt category name  org hibernate type  gt       lt priority value  TRACE   gt   lt  category gt    and add hibernate properties     lt property name  show sql  gt true lt  property gt   lt property name  format sql  gt true lt  property gt   lt property name  use sql comments  gt true lt  property gt

User · Answer

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

User · Answer

mysql jdbc driver has already provide a convenient to meet this requirement, you must at least the have the jar version >= mysql-connect-jar-5.1.6.jar

step 1: [configure your jdbc.url to add logger and custom logging]

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

now, it is using slf4j logging, if your default logging is log4j, you must add slf4j-api, slf4j-log4j12 dependencies to use slf4j logging

step 2: [write your custom logging]

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        this.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();
    }

}

User · Answer

If you want hibernate to print generated sql queries with real values instead of question marks      add following entry in hibernate cfg xml hibernate properties    show sql true format sql true use sql comments true    And add following entry 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

User · Answer

lt appender name  console  class  org apache log4j ConsoleAppender  gt       lt layout class  org apache log4j PatternLayout  gt       lt param name  ConversionPattern         value   d yyyy-MM-dd HH mm ss   -5p  c 1   L -  m n    gt       lt  layout gt   lt  appender gt    lt logger name  org hibernate  additivity  false  gt       lt level value  INFO    gt       lt appender-ref ref  console    gt   lt  logger gt    lt logger name  org hibernate type  additivity  false  gt       lt level value  TRACE    gt       lt appender-ref ref  console    gt   lt  logger gt

User · Answer

You need to enable logging for the the following categories    org hibernate SQL  nbsp  nbsp - 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

User · Answer

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

User · Answer

if you are using hibernate 3 2 xx use   log4j logger org hibernate SQL trace   instead of   log4j logger org hibernate SQL debug

User · Answer

Using Hibernate 4 and slf4j log4j2   I tried adding the following in my log4j2 xml configuration     lt Logger name  org hibernate type descriptor sql BasicBinder  level  trace  additivity  false  gt        lt AppenderRef ref  Console   gt    lt  Logger gt    lt Logger name  org hibernate type EnumType  level  trace  additivity  false  gt        lt AppenderRef ref  Console   gt   lt  Logger gt    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

User · Answer

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 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 set-up 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 Mevn project. This file has one line which is the below:

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

User · Answer

add following properties and values to your log4j or logback configuration   org hibernate sql DEBUG org hibernate type descriptor sql BasicBinder TRACE

User · Answer

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/

User · Answer

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

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

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.

User · Answer

Just for convenience  here is the same configuration example for Logback  SLF4J    lt appender name  SQLROLLINGFILE  gt    lt File gt  tmp sql log lt  File gt    lt rollingPolicy gt     lt FileNamePattern gt logFile  d yyyy-MM-dd  log lt  FileNamePattern gt    lt  rollingPolicy gt    lt layout gt     lt Pattern gt  -4date    msg  n lt  Pattern gt    lt  layout gt   lt  appender gt    lt logger name  org hibernate SQL  additivity  false   gt       lt level value  DEBUG    gt        lt appender-ref ref  SQLROLLINGFILE    gt   lt  logger gt    lt logger name  org hibernate type  additivity  false   gt    lt level value  TRACE    gt    lt appender-ref ref  SQLROLLINGFILE    gt   lt  logger gt    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  - MatchingStep com mypackage foo 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

User · Answer

hibernate-types has a utility method to log jpql and criteria queries. More details can be found in the autors'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);

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.

User · Answer

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.

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

The answer is

logging.level.org.hibernate.SQL=DEBUG

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

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

Update 2016

Examples related to java

Examples related to sql

Examples related to hibernate

Examples related to orm

Tags