#1. Enable Logging in your log4j.xml, log4j.properties, or myapp-logback.xml
In the log4j.properties file, you can enable Hibernate SQL logging as shown below
|
1 2 |
log4j.logger.org.hibernate.SQL=DEBUG log4j.logger.org.hibernate.type=trace |
or in your log4j.xml or myapp-logback.xml as
|
1 2 3 4 5 6 7 8 |
<logger name="org.hibernate.SQL" additivity="false"> <level value="DEBUG" /> <appender-ref ref="FILE"/> </logger> <logger name="org.hibernate.type" additivity="false"> <level value="TRACE" /> <appender-ref ref="FILE"/> </logger> |
You can then check your logs for the SQLs executed and the parameters passed. The example below shows 3 parameters passed to an update query
|
1 2 3 4 |
2015-09-01 12:28:44,586 DEBUG [org.hibernate.SQL] (http-/127.0.0.1:8080-1) update my_table set id=?, authorise_date=?, authorise_id=? 2015-09-01 12:28:44,677 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (http-/127.0.0.1:8080-1) binding parameter [1] as [INTEGER] - <null> 2015-09-01 12:28:44,677 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (http-/127.0.0.1:8080-1) binding parameter [2] as [TIMESTAMP] - <null> 2015-09-01 12:28:44,677 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (http-/127.0.0.1:8080-1) binding parameter [3] as [VARCHAR] - <null> |
#2. Setting show_sql to true in the hibernate configuration for development only
Spring application context file example. Note the last 3 entries:
|
1 2 3 4 5 6 7 8 9 10 |
<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:1200/mydb"/> <property name="username" value="root"/> <property name="password" value="1234"/> <property name="validationQuery" value="SELECT 1"/> <property name="show_sql" value="true" /> <property name="format_sql" value="true" /> <property name="use_sql_comments" value="true" /> </bean> |
#3. P6Spy (or log4jdbc) to see the SQL directly sent to the database
This approach is more intrusive, but is way more powerful. It consists of putting a proxy driver between JDBC and the real driver so that all generated SQL will be logged.
Step 1: Add P6Spy driver to the Maven pom.xml
|
1 2 3 4 5 |
<dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>2.1.4</version> </dependency> |
Step 2: Tell hibernate to use “com.p6spy.engine.spy.P6SpyDriver”
|
1 2 3 4 |
<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.p6spy.engine.spy.P6SpyDriver"/> ..... </bean> |
Step 3: In the spy.properties
|
1 2 3 4 5 |
module.log=com.p6spy.engine.logging.P6LogFactory realdriver=com.mysql.jdbc.Driver autoflush=true excludecategories=debug,info,batch,result appender=com.p6spy.engine.logging.appender.StdoutLogger |
If you need to use P6Spy in an application server, the configuration should be done on the application server itself, at the datasource level.
#4. Knowing what the common hibernate errors are and how to fix them
There are some very common mistakes made due to lack of understanding of how Hibernate works. Here are very detailed posts on Hibernate common issues, and how to go about fixing them.
1. Hibernate mistakes – accessing objects outside of a transaction
2. Understanding Hibernate proxy objects and avoiding potential pitfalls
3. Identifying and fixing LazyInitializationException in Hibernate
4. Identifying and fixing NonUniqueObjectException in Hibernate