Display Hibernate SQL to console – show_sql , format_sql and use_sql_comments

Hibernate has build-in a function to enable the logging of all the generated SQL statements to the console. You can enable it by add a “show_sql” property in the Hibernate configuration file “hibernate.cfg.xml“. This function is good for basic troubleshooting, and to see what’s Hibernate is doing behind.

1. show_sql

Enable the logging of all the generated SQL statements to the console


<!--hibernate.cfg.xml -->
<property name="show_sql">true</property>

Output


Hibernate: insert into mkyong.stock_transaction 
(CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
values (?, ?, ?, ?, ?, ?)

2. format_sql

Format the generated SQL statement to make it more readable, but takes up more screen space. :)


<!--hibernate.cfg.xml -->
<property name="format_sql">true</property>

Output


Hibernate: 
    insert 
    into
        mkyong.stock_transaction
        (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
    values
        (?, ?, ?, ?, ?, ?)

3. use_sql_comments

Hibernate will put comments inside all generated SQL statements to hint what’s the generated SQL trying to do


<!--hibernate.cfg.xml -->
<property name="use_sql_comments">true</property>

Output


Hibernate: 
    /* insert com.mkyong.common.StockTransaction
        */ insert 
        into
            mkyong.stock_transaction
            (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
        values
            (?, ?, ?, ?, ?, ?)

Hibernate configuration file

Full example of “hibernate.cfg.xml“.


<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="use_sql_comments">true</property>
    </session-factory>
</hibernate-configuration>

How about Hibernate SQL parameter value?

This basic SQL logging is good enough for the normal debugging, however it’s unable to display the Hibernate SQL parameter value. Some third party libraries integration are required to display the Hibernate SQL parameter value to console or file. Check the following two articles :

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

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
prince shah
Guest
prince shah

Multiple time the same mysql query shows up on Hibernate console. Any idea why so? please have a look to this following post… http://stackoverflow.com/questions/37806355/multiple-time-the-same-mysql-query-shows-up-on-hibernate-console-which-probably

razorree
Guest
razorree

can Hibernate print query/update etc. times ? (in milliseconds for example)

ashwinkraj
Guest
ashwinkraj

This works for Hibernate/JPA Query, but when I employ the EntityManager’s createNativeQuery to build and use custom SQL, I do not see any SQL statements in the log file.

uttam
Guest
uttam

how it can be done for the EntityManager configuration org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean

Bharathi
Guest
Bharathi

Hi sir ,gud mng,i hava one requirement,that requirement is state related to number of cities and each city related to number of villages ,i want retrive data from database state-cities-city1,city2 and city1-vil1,vil2
in spring hibernate template,using hql quaries,plz help me,very urgent

Ranjan Sai
Guest
Ranjan Sai

is there any way to see sql query while using org.apache.commons.dbcp.BasicDataSource with spring JdbcTemplate

trackback
Liferay e le query di hibernate | pietrosantoro

[…] Un utilizzo generico della direttiva per hibernate:¬†http://www.mkyong.com/hibernate/hibernate-display-generated-sql-to-console-show_sql-format_sql-and-u… […]

Robert Nicholson
Guest
Robert Nicholson

use the following in log4J to see the type bindings

trackback
How to display hibernate sql parameter values – P6Spy

[…] that passed to database? Hibernate just display all parameter values as question mark (?). With show_sql property, Hibernate will shows all generated SQL statements, but not the SQL parameter values. […]

trackback
Hibernate Tutorials | J2EE Web Development Tutorials

[…] show_sql , format_sql and use_sql_comments Configure Hibernate to display the generated SQL statement to console. […]