Hibernate show real SQL [duplicate]

415
votes

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?

5
javasqlhibernateorm
Hibernate uses prepared statements internally, so it doesn't ever have the SQL in a format where they values would be embedded - Narayan
Does it really say true.employee? - Stephen Denne
the only working solution I have found is here : mkyong.com/hibernate/… - Christian Achilli
Contrary to what is written here, I have not found an answer to this question there : Print query string in hibernate with parameter values. - Nicolas Barbulesco
@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

5 Answers

391
votes

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

261
votes

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"/>
16
votes

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

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.

3
votes

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.