Hibernate show real SQL
Solution 1
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"/>
Solution 2
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
- Hibernate 4.1 Core Documentation
Solution 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
Solution 4
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.
Solution 5
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.
Tommaso Taruffi
Updated on July 08, 2022Comments
-
Tommaso Taruffi almost 2 years
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?
-
elduff about 14 yearsI 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.
-
Rachel about 12 yearsis there a way we can get information about bind parameters in the logs?
-
Scarlett about 12 yearscan you post the log output example?
-
Arjan over 11 years@Rachel, what more than logging such as
TRACE [BasicBinder] binding parameter [1] as [VARCHAR] - john doe
do you need? -
Adam Gent over 11 years@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.
-
David Anderson about 10 yearsThe problem is not the This but the ?
-
reznic over 9 yearsdont work on hibernate 4.3!!
-
Emil Lundberg about 9 yearsEnabling the
org.hibernate.type
category didn't work for me, but enabling theorg.hibernate.loader.hql
category instead did work. -
TheConstructor almost 9 yearsIf you add
org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl
you can deduce the bound values from theReleasing statement
- orClosing prepared statement
-lines (at least with H2) -
Jolly1234 almost 9 yearsthis applies only to hibernate version < 3.0 right?
-
Vlastimil Ovčáčík over 8 yearsYou may need to tell Hibernate what logging manager you use (log4j, slf4j), see how.
-
Alkanshel over 8 years@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.
-
The Student about 8 yearsFor Eclipse Mars the file is "hibernate-log4j.properties"
-
Ray Hulha about 8 yearsP6Spy has a bit of an annoying log format that can't be changed easily: stackoverflow.com/questions/17789223/…
-
deFreitas almost 8 yearsIn my case I use logback and want to do it programatically on IDE debug, so I did this
-
senyor about 7 yearsOne can use -Dhibernate.show_sql=true with Java process to enable sql output in Hibernate.
-
Karen Goh over 6 years@Brian - do I need to put in <Loggers></Loggers> and appenderRef ?
-
Rafael Andrade almost 5 yearsWhen 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.
-
p0tta over 3 yearsWhat if I don't have log4j in my project?