hibernate by 21 comments

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

21 comments on “How to display hibernate sql parameter values – Log4j”

  1. 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.

    Reply

  2. 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.

    Reply

  3. 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

    Reply

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

    Reply

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

      Reply

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

        Reply

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

    Reply

  6. 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

    Reply

  7. 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

    Reply

  9. 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

    Reply

  10. 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

    Reply

  13. 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!

    Reply

  14. 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

    Reply

  15. 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.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *