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?

See also http://stackoverflow.com/questions/2536829/hibernate-show-real-sql — Petr Gladkikh, Aug 16 '12 at 05:23

score 474 · Accepted Answer · edited Apr 25 '17 at 13:53

474

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.

edited Apr 25 '17 at 13:53

Abdull

23,005
22
116
159

answered Nov 11 '09 at 06:40

Pascal Thivent

535,937
127
1,027
1,106

15

This is useful. But this does not show me the real SQL queries. – Nicolas Barbulesco Sep 03 '13 at 08:26
6

@Nicolas that's true, however inmediately after the query it dispays the binded parameters. – Xtreme Biker Sep 19 '13 at 06:47
3

I am using grails 2.4.4 and hibernate 4. Changing log4j configuration did not work for me but p6spy worked! – Champ Jan 13 '15 at 07:03
Adding the `org.hibernate.type` category to my appender didn't work for me, but adding the `org.hibernate.loader.hql` category instead did work. – Emil Lundberg Feb 13 '15 at 16:04
the logging of JDBC params doesn't work in hibernate 4 – Gordon Jun 25 '15 at 22:59
You may need to tell Hibernate what logging manager you use (log4j, slf4j), see [how](http://stackoverflow.com/questions/11639997/how-do-you-configure-logging-in-hibernate-4-to-use-slf4j). – Vlastimil Ovčáčík Aug 09 '15 at 18:31
11

In Hibernate 5 we can use `org.hibernate.type.descriptor.sql.BasicBinder` logger. Enabling logging on `org.hibernate.type` printed too much useless info for me... – csharpfolk Aug 08 '16 at 18:15
5

`org.hibernate.type` and `org.hibernate.loader.hql` not work for me to show the parameters – Dherik Oct 06 '16 at 15:54
Where should I put those configurations? – Gavin Haynes Nov 19 '16 at 00:15
So, if you already have `hibernate.show_sql=true`, you can skip the first part and just add the `log4j.logger.org.hibernate.type=trace`. Works for me – Nestor Milyaev Mar 22 '18 at 13:31
Is there a reason why Hibernate does not show full sql query? – Shamal Karunarathne May 23 '18 at 08:56
"Enabling logging on org.hibernate.type printed too much useless info for me..." Me too. TRACE [,] [org.hibernate.type.descriptor.sql.BasicExtractor] found [...] as column [...] System crashed when a varchar(max) column containing a very large text was logged. – Géza Dec 20 '18 at 17:25
I am not getting info on enum parameters. How can I get that? I.e if a parameter is an enum, it is simply not printed. – dabicho Sep 17 '19 at 23:45
it works for me with: logging.level.org.hibernate.SQL= DEBUG \n logging.level.org.hibernate.type=trace – tiennv Aug 20 '20 at 12:39

score 78 · Answer 2 · answered Aug 31 '13 at 22:33

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

This is not answering the OPs question. – ShadowGames Oct 15 '19 at 08:52 — ShadowGames, Oct 15 '19 at 08:52

JRichardsz · Answer 3 · 2020-07-22T13:16:05.853

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

score 35 · Answer 4 · edited May 11 '17 at 17:47

35

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

edited May 11 '17 at 17:47

Dave Jarvis

28,853
37
164
291

answered Jan 25 '13 at 10:16

Hari

351
3
2

Thanks, has worked for me really well. Those settings will add under the sql query a parameter like `binding parameter [1] as [VARCHAR] - [1]`. – G. Ciardini May 07 '19 at 08:34

Alan Hay · Answer 5 · 2018-11-29T12:20:43.310

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

Hey....This seems cool.... just what the doctor ordered for me :) ... but does it also support CLOB/BLOB?? Also, is it possible to only display the query and not the result set. - Thanks :) — dev ray, Aug 27 '14 at 10:29
could you give me an example of configuration how to do that? — grep, Dec 08 '14 at 15:33
Actually, the *latter* outputs a tabluar representation of query results ... i.e. one needs log4jdbc-remix for that nice feature. — meriton, Jun 02 '16 at 16:56
This solution was best for my situation where I needed to see the row number values Hibernate generates for paging result sets. The trace logging only shows query parameter values. — Oliver Hernandez, Jan 09 '17 at 18:54
This is very cool and also works for spring boot. Also much easier to integrate there. See [this other stackoverflow question](https://stackoverflow.com/questions/52376778/can-log4jdbc-be-used-with-spring-boot/59771403#59771403) — judos, Jan 16 '20 at 14:08

Michal Z m u d a · Answer 6 · 2013-03-19T18:52:17.397

9

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>

edited Mar 19 '13 at 18:52

answered Mar 19 '13 at 18:34

Michal Z m u d a

4,882
3
40
36

Vlad Mihalcea · Answer 7 · 2018-01-04T12:23:06.837

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.

score 7 · Answer 8 · edited Feb 28 '14 at 14:12

7

add following properties and values to your log4j or logback configuration:

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

edited Feb 28 '14 at 14:12

L-Ray

1,629
1
15
26

answered Dec 14 '13 at 12:34

Ravshan Samandarov

664
9
16

6

The `org.hibernate.type.descriptor.sql.BasicBinder` category doesn't include all parameters, eg enum types. So if you want everything, you really need `TRACE` for the entire `org.hibernate.type` group. – seanf Apr 23 '14 at 05:32
For me it works in Hibernate 4.3! Plus, I wouldn't TRACE the full org.hibernate.type, because that's way too much output. In most cases, this solution will do. – cslotty Mar 16 '16 at 12:43
Note that org.hibernate.type.descriptor.sql.BasicExtractor logs the result sets. So having large size entry can even crash the apllication when logging to console in Eclipse, and I suppose it is also not ideal for logging into file. That is why I prefer this solution, it works also in Hibernate 3. For those who are interested in enum types, please try out the exact class that logs them when org.hibernate.type=TRACE. Then set org.hibernate.type.xyz.TheClassThatLogsEnumParams=TRACE. – Géza Dec 21 '18 at 09:29

score 5 · Answer 9 · answered Nov 10 '09 at 19:21

5

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

answered Nov 10 '09 at 19:21

flybywire

232,954
184
384
491

score 4 · Answer 10 · answered Mar 25 '16 at 04:54

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

score 3 · Answer 11 · answered Mar 04 '15 at 07:16

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

Hey single31 above lines has to be added in your hibernate configuration file then it will definitely work. I always post thing which I have done practically. — Vijay Bhatt, Jul 02 '15 at 10:04

score 3 · Answer 12 · answered Jun 30 '16 at 10:25

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.

score 3 · Answer 13 · answered May 18 '17 at 01:30

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

}

score 3 · Answer 14 · answered Apr 30 '19 at 11:43

3

for development with Wildfly ( standalone.xml), add those loggers:

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

answered Apr 30 '19 at 11:43

womd

2,514
18
16

score 3 · Answer 15 · answered Mar 23 '12 at 12:04

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>

score 2 · Answer 16 · answered Oct 26 '13 at 11:35

2

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

answered Oct 26 '13 at 11:35

Frizz1977

912
11
19

Hey - this is nice. But I think here the queries are printed with the ? followed with parameters values. Since I have infinitely many queries, I need something that I can just copy-paste on the sql editor and they will be executed. Is there a way I can do that using this approach. I am not so keen to go for 3rd party libraries. Thanks :) – dev ray Aug 27 '14 at 10:33
Thanks. I was hoping not to have to use any 3rd party solutions and hibernate directly, but I guess I have no other choice. – dev ray Sep 12 '14 at 08:25

score 2 · Answer 17 · answered Sep 05 '14 at 15:55

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/

score 2 · Answer 18 · answered Oct 09 '15 at 05:23

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.

This was the easiest path forward for me in my Spring Boot application, where I was trying to log SQL generated from a unit test. I added a test dependency to Gradle (testCompile 'p6spy:p6spy:3.8.5'), adjusted application.yml to set spring.datasource.url=jdbc:p6spy:h2:mem:testdb and spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver, and then added spy.properties with realdriver=org.h2.Driver and logfile set to my preferred path. It was easy to extract the complete SQL from the resulting log file. The only hiccup was that H2 didn't like the generated timestamp format. — Ken Pronovici, Sep 08 '19 at 15:15

score 2 · Answer 19 · answered Mar 25 '16 at 05:12

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

How does this relate to the question? – hotzst Mar 25 '16 at 12:01 — hotzst, Mar 25 '16 at 12:01

score 2 · Answer 20 · edited May 23 '17 at 11:54

Using Hibernate 4 and slf4j/log4j2 , I tried adding the following in 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.

S'chn T'gai Spock · Answer 21 · 2017-08-13T04:53:02.377

2

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

edited Aug 13 '17 at 04:53

answered Aug 01 '17 at 14:18

S'chn T'gai Spock

757
10
13

score 1 · Answer 22 · answered Nov 11 '09 at 12:41

1

if you are using hibernate 3.2.xx use

log4j.logger.org.hibernate.SQL=trace

instead of

log4j.logger.org.hibernate.SQL=debug

answered Nov 11 '09 at 12:41

diy

3,270
3
16
15

score 1 · Answer 23 · edited Oct 09 '15 at 05:44

1

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.

edited Oct 09 '15 at 05:44

Sindhoo Oad

1,134
2
13
29

answered Oct 10 '13 at 11:09

Aneesh Vijendran

3,476
2
26
45

2

Log4JDBC will. See above. – Alan Hay Oct 10 '13 at 15:18

score 1 · Answer 24 · answered Oct 31 '13 at 14:09

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 )

Hey ... I cant find any examples of this approach. Could you provide any reference/examples/tutorials. And is it still same with the latest versions or hibernate/log4j or has it become some org.hibernate.QueryTranslator or something. Thanks — dev ray, Aug 27 '14 at 11:23
Hey ... I tried this, but this does not seem to work with save or update. I guess it only works for select queries, where translation from hql to sql comes into play — dev ray, Aug 28 '14 at 11:01

score 1 · Answer 25 · answered Aug 03 '16 at 17:51

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

score 1 · Answer 26 · answered May 29 '18 at 10:02

1

In Java:

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

Then:

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

answered May 29 '18 at 10:02

Frédéric Nell

101
1
1
12

1

Thanks, It prints the query, but not the parameters that it used, there is a way to print the parameters too? – Liz Lamperouge Jun 04 '19 at 10:55
I'm sorry I don't think its possible to map them like this. I would log them and do the work manually as a workaround ;) – Frédéric Nell Nov 27 '20 at 14:00

Suneet Khurana · Answer 27 · 2018-06-14T16:55:16.873

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]

Even this doesn't show the values for _limit_ and _offset_ in queries. — T3rm1, Jan 04 '19 at 13:10

score 0 · Answer 28 · answered Jun 16 '18 at 20:03

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.

score 0 · Answer 29 · answered Jun 21 '20 at 19:10

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.

score 0 · Answer 30 · answered Mar 03 '21 at 01:55

0

Using yaml property

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

answered Mar 03 '21 at 01:55

DarkMode

140
3
6

xenoterracide · Answer 31 · 2021-03-18T16:25:39.830

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.

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 silence, 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.

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

31 Answers31

Update 2016

logging.level.org.hibernate.SQL=DEBUG

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

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

Linked

Related