Hibernate show real SQL

Question

if I set

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

in my hibernate.cfg.xml configuration file in the console I can see the SQL.

But it's not real SQL... Can I see the SQL code that will be passed directly to database?

Example:

I see

select this_.code from true.employee this_ where this_.code=?

Can I see

select employee.code from employee where employee.code=12

the real SQL?

Hibernate uses prepared statements internally, so it doesn't ever have the SQL in a format where they values would be embedded — Narayan, Mar 29 '10 at 09:20
the only working solution I have found is here : http://www.mkyong.com/hibernate/how-to-display-hibernate-sql-parameter-values-solution/ — Christian Achilli, Jan 23 '13 at 17:10
Contrary to what is written here, I have not found an answer to this question there : [*Print query string in hibernate with parameter values*](http://stackoverflow.com/questions/1710476/print-query-string-in-hibernate-with-parameter-values). — Nicolas Barbulesco, Sep 03 '13 at 08:30
@Narayan — Surely Hibernate has the real SQL queries at some point. The real queries are generated, before being sent to the database. — Nicolas Barbulesco, Sep 03 '13 at 08:33
@NicolasBarbulesco The version with the question marks is the real SQL. All JDBC drivers can accept queries in this format. Under the hood, the JDBC driver may reformat the query to match the DB's native placeholder format (e.g, Oracle and PostgreSQL will replace `?` with `:1`, `:2`, etc), and in some specific cases it may replace the bind placeholders with escaped values (some MySQL drivers do this). However, most DBs are capable of handling queries with placeholders natively. — James_pic, Jan 21 '15 at 13:58

score 388 · Answer 1 · edited Mar 28 '17 at 11:33

388

Can I see (...) the real SQL

If you want to see the SQL sent directly to the database (that is formatted similar to your example), you'll have to use some kind of jdbc driver proxy like P6Spy (or log4jdbc).

Alternatively you can enable logging of the following categories (using a log4j.properties file here):

log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE

The first is equivalent to hibernate.show_sql=true, the second prints the bound parameters among other things.

Reference

Hibernate 3.5 Core Documentation
- 3.5. Logging
Hibernate 4.1 Core Documentation
- 4.1. Logging

edited Mar 28 '17 at 11:33

MegaMatt

21,542
37
97
141

answered Mar 29 '10 at 09:20

Pascal Thivent

535,937
127
1,027
1,106

10

I like P6Spy, especially when running unit tests, because it'll also give you the result set of your query in addition to the bind parameter values. – elduff Mar 29 '10 at 16:59
1

can you post the log output example? – Scarlett Mar 15 '12 at 08:13
@Pascal I don't think you should say *"If you want to see it formatted **exactly** as in your example"* because it it depends highly on what database he is using and if hibernate chooses to batch/prepare the statement. – Adam Gent Jan 02 '13 at 21:24
Enabling the `org.hibernate.type` category didn't work for me, but enabling the `org.hibernate.loader.hql` category instead did work. – Emil Lundberg Feb 13 '15 at 16:05
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:33
For Eclipse Mars the file is "hibernate-log4j.properties" – The Student Jan 28 '16 at 12:20
P6Spy has a bit of an annoying log format that can't be changed easily: http://stackoverflow.com/questions/17789223/only-show-effective-sql-string-p6spy – Ray Hulha Feb 04 '16 at 10:39
In my case I use logback and want to do it programatically on IDE debug, so [I did this](https://gist.github.com/mageddo/6f379236bb3a867893c9b85ebaec6004) – deFreitas Jun 13 '16 at 15:08
One can use -Dhibernate.show_sql=true with Java process to enable sql output in Hibernate. – senyor Mar 14 '17 at 19:34
When I use this configurations the result of each query is also printed on console. Is there a way to print only the query? Sometimes the results take so much time to print. – Rafael Andrade Jul 08 '19 at 18:03

score 260 · Accepted Answer · edited May 11 '17 at 17:49

260

log4j.properties

log4j.logger.org.hibernate=INFO, hb
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.logger.org.hibernate.hql.ast.AST=info
log4j.logger.org.hibernate.tool.hbm2ddl=warn
log4j.logger.org.hibernate.hql=debug
log4j.logger.org.hibernate.cache=info
log4j.logger.org.hibernate.jdbc=debug

log4j.appender.hb=org.apache.log4j.ConsoleAppender
log4j.appender.hb.layout=org.apache.log4j.PatternLayout
log4j.appender.hb.layout.ConversionPattern=HibernateLog --> %d{HH:mm:ss} %-5p %c - %m%n
log4j.appender.hb.Threshold=TRACE

hibernate.cfg.xml

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

persistence.xml

Some frameworks use persistence.xml:

<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>
<property name="hibernate.use_sql_comments" value="true"/>

edited May 11 '17 at 17:49

Dave Jarvis

28,853
37
164
291

answered Mar 29 '10 at 15:05

Tommaso Taruffi

8,462
9
40
54

is there a way we can get information about bind parameters in the logs? – Rachel Jan 24 '12 at 18:53
4

@Rachel, what more than logging such as `TRACE [BasicBinder] binding parameter [1] as [VARCHAR] - john doe` do you need? – Arjan Dec 08 '12 at 12:52
3

dont work on hibernate 4.3!! – reznic Oct 15 '14 at 09:18
If you add `org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl` you can deduce the bound values from the `Releasing statement`- or `Closing prepared statement`-lines (at least with H2) – TheConstructor May 06 '15 at 13:50
1

@Arjan Having the params listed separately really isn't very helpful. Part of why I'd want sql prints like this is so I can run the statements myself and see what's going on. Having to pluck through and insert params by hand is just clumsy. Shame there isn't a more streamlined way to have these printed. – Amalgovinus Oct 05 '15 at 19:49
What if I don't have log4j in my project? – p0tta Jan 04 '21 at 20:20

score 16 · Answer 3 · answered Mar 29 '10 at 12:51

16

If you can already see the SQL being printed, that means you have the code below in your hibernate.cfg.xml:

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

To print the bind parameters as well, add the following to your log4j.properties file:

log4j.logger.net.sf.hibernate.type=debug

answered Mar 29 '10 at 12:51

Brian Riehman

23,040
2
21
17

1

this applies only to hibernate version < 3.0 right? – Jolly1234 May 20 '15 at 19:13
@Brian - do I need to put in and appenderRef ? – Karen Goh Sep 07 '17 at 04:28

score 11 · Answer 4 · answered Apr 06 '10 at 21:11

11

Worth noting that the code you see is sent to the database as is, the queries are sent separately to prevent SQL injection. AFAIK The ? marks are placeholders that are replaced by the number params by the database, not by hibernate.

answered Apr 06 '10 at 21:11

gub

4,739
3
24
33

score 3 · Answer 5 · answered Mar 29 '10 at 09:49

3

select this_.code from true.employee this_ where this_.code=? is what will be sent to your database.

this_ is an alias for that instance of the employee table.

answered Mar 29 '10 at 09:49

Stephen Denne

33,845
10
42
60

14

The problem is not the This but the ? – David Anderson Apr 18 '14 at 15:02

Hibernate show real SQL

5 Answers5

Reference

log4j.properties

hibernate.cfg.xml

persistence.xml

Linked

Related