hibernate show real sql

Question

Hi all,

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?

thanks!

Answer 1

Can I see (...) the real SQL

If you want to see it formatted exactly as in 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 Core Documentation
  • 3.5. Logging

Answer 2

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.

Answer 3

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

Answer 4

You can also use Datasource Proxy if you're using Spring. It can be used outside of Spring environments too. It will log the SQL statements like this:

Time:13, Num:1, Query:{[create table emp ( id integer primary key, name varchar(10) );][]}
Time:10, Num:1, Query:{[insert into emp ( id, name )values (?, ?);][1, foo]}

Answer 5

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>

Answer 6

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.