In a Spring-Hibernate application, hibernate.show_sql=true
in log4j property file is enough to show to hibernate generated query. But what we have to do if we need the actual sql query.(For production environment i need to observer and verify the query before updating the schema).
What i am going to do is, after the first schema generation in production( by hibernate itself) i dont want hibernate to update the schema(DDL).I want to hijack the alter, update (DDL) queries and after verification i would like to run those scripts manually in DB.
Is there any approach to do that?
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.
hibernate.cfg.xml
show_sql
Enable the logging of all the generated SQL statements to the console
<property name="show_sql">true</property>
format_sql
Format the generated SQL statement to make it more readable, but takes up more screen space.
<property name="format_sql">true</property>
use_sql_comments
Hibernate will put comments inside all generated SQL statements to hint what’s the generated SQL trying to do
<property name="use_sql_comments">true</property>
log4j.properties
log4j.logger.org.hibernate=INFO, hb
log4j.logger.org.hibernate.SQL=DEBUG ## is equivalent to hibernate.show_sql=true
log4j.logger.org.hibernate.type=TRACE ## allows you to see the binding parameters
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
log4j.logger.org.hibernate.tool.hbm2ddl=debug
To print the bind parameters as well, add the following to your log4j.properties file:
log4j.logger.net.sf.hibernate.type=debug
Add the following configurations in log4j.properties file
logs the SQL statements
log4j.logger.org.hibernate.SQL=debug
Logs the JDBC parameters passed to a query
log4j.logger.org.hibernate.type=trace
Enable the show sql property in hibernate configuration file as follows.
<property name="show_sql">true</property>
For more details, check the post