Main Tutorials

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 Author

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

Comments

Subscribe
Notify of
21 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Thomas
9 years ago

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
10 years ago

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

Stephan
4 years ago

Hey, nice hints at all! Do you have an idea how I can achieve this:

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

by using the application.yml? It should most likely be a key path next to the well known

spring:
jpa:
show-sql: true

I suppose… But can’t make up the name for the parameter from all documentation I found.

Additionally, I was looking for a reference for Spring / JPA which lists all those Parameters…

Thanks,
Stephan
PS: Would appreciate if you could cc a possible answer to my email.

fiky yuvita
7 years ago

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
8 years ago

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
8 years ago

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

vineela
4 years ago
Reply to  chandru

same with me. I cannot see the parameters with hibernate 5

Raghunandana
4 years ago
Reply to  vineela

I am using same configuration i am able to get logs, add logging.level.org.hibernate.type=trace in application.properties file

Sam
9 years ago

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

Madhu
10 years ago

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

Shuddhaa
10 years ago

Plzz Speak to me on [email protected] 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
10 years ago

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
10 years ago
Reply to  Michael R

Awesome, thanks. This did the trick!

haris
11 years ago

thanks lot helpful,,,

czarny
11 years ago

Thx a lot, needed that!

Preetam
11 years ago

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
11 years ago

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
13 years ago

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.

Shuddhaa
10 years ago
Reply to  mkyong

Plzz write to me …
[email protected]

friend
14 years ago

Thanks.
That did the trick!