How to display hibernate sql parameter values – Log4j

Problem

Hibernate has basic logging feature to display the SQL generated statement with show_sql configuration property.


Hibernate: INSERT INTO mkyong.stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
VALUES (?, ?, ?, ?, ?, ?)

However , it just isn’t enough for debugging, the Hibernate SQL parameter values are missing.

Solution – Log4j

Log4J is required to display the real Hibernate SQL parameter value.

1. Configure the Log4j in Hibernate

Follow this article to configure Log4j in Hibernate

2. Change the Log level

Modify the Log4j properties file, and change the log level to “debug” or “trace” in “log4j.logger.org.hibernate.type” property.

File : log4j.properties


# Direct log messages to stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

# Root logger option
log4j.rootLogger=INFO, stdout

# Hibernate logging options (INFO only shows startup messages)
log4j.logger.org.hibernate=INFO

# Log JDBC bind parameter runtime arguments
log4j.logger.org.hibernate.type=trace

3. Done

The Hibernate real parameter values are display now

Output…


Hibernate: INSERT INTO mkyong.stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
VALUES (?, ?, ?, ?, ?, ?)
13:33:07,253 DEBUG FloatType:133 - binding '10.0' to parameter: 1
13:33:07,253 DEBUG FloatType:133 - binding '1.1' to parameter: 2
13:33:07,253 DEBUG DateType:133 - binding '30 December 2009' to parameter: 3
13:33:07,269 DEBUG FloatType:133 - binding '1.2' to parameter: 4
13:33:07,269 DEBUG IntegerType:133 - binding '11' to parameter: 5
13:33:07,269 DEBUG LongType:133 - binding '1000000' to parameter: 6
Note
If this logging is still not detail enough for you to debug the SQL problem, you can use the P6Spy library to log the exact SQL statement that send to database. Check this article – How to display hibernate sql parameter values with P6Spy

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
fiky yuvita
Guest
fiky yuvita

i want to ask, (pardon my English), in my application, why binding data from database (hibernate) to my jsp with struts framework have very long response? i use struts 1 and hibernate 3. Maybe i have to upgrade my hibernate or i have to change to struts 2?
Tengkyu for the response.

Tarun Gupta
Guest
Tarun Gupta

Really simple and helpful trick, I use to think that hibernate doesn’t provide this feature. For the benefit of others xml based log4j.xml looks like this

chandru
Guest
chandru

Hi, With hibernate 5.1.3.Final and log4j2 i am not able to see the parameters? did i need to do anything special?

Sam
Guest
Sam

Actually I want to use two schema in one cfg file how can I do that reply ASAp

Thomas
Guest
Thomas

If your application uses slf4j as its central logging framework you can enable the logger using

Dont forget to set env-variable “-Dorg.jboss.logging.provider=slf4j” to make Hibernate use slf4j for acquiring Logger instances

Michal Boška
Guest
Michal Boška

Actually you need to set only org.hibernate.type.descriptor.sql.BasicBinder to TRACE. If you set the whole org.hibernate.type package to TRACE, you will get flooded by BasicExtractor telling you about each parameter filled into object from SQL ResultSet

Madhu
Guest
Madhu

Still not able to see binding values After changing my log4.properties to trace

Shuddhaa
Guest
Shuddhaa

Plzz Speak to me on shuddhaa.sd@gmail.com I need to Speak on many aspects…Plzz Dont Post this on web delete if after reading
thanks
hope u will mail me

Michael R
Guest
Michael R

Hibernate 4 requires TRACE level

TRACE org.hibernate.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] – john
TRACE org.hibernate.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARCHAR] – doe

Alex
Guest
Alex

Awesome, thanks. This did the trick!

haris
Guest
haris

thanks lot helpful,,,

czarny
Guest
czarny

Thx a lot, needed that!

Preetam
Guest
Preetam

Don’t have any words to explain how informative this website is. Million times better than any book or tutorial.

Keep up the good work, MYYONG!

Tien Thai
Guest
Tien Thai

I added the following line in my log4j.properties file and the hibernate binding parameters displayed just fine in my local dev environment (i.e. Apache Tomcat 6) but they are not displayed in the SystemOut.log in WebSphere 7 application server. Any suggestion of what might cause this issue? Your help is appreciated.

log4j.logger.org.hibernate.type=trace

airgray
Guest
airgray

Brother, your web site it the best on the web for learning Hibernate. Simple, concise, accurate. I’ve stopped asking the Google. I just come here. Keep up the good work.

trackback
Hibernate Tutorials | Tutorials

[…] How to display hibernate sql parameter values – Log4j Using Log4j to display the Hibernate SQL parameter value. […]

trackback
Hibernate display generated SQL to console – show_sql , format_sql and use_sql_comments | Hibernate

[…] 1. How to display hibernate sql parameter values – P6Spy 2. How to display hibernate sql parameter values – Log4J […]

friend
Guest
friend

Thanks.
That did the trick!