Viewing the SQL Query in liferay : debugging the SQL Query in Hibernate

Sometimes we have to know SQL query hiting in DB to retrieve the list.

Suppose When we are writing the DynamicQuery , Some times we have to know SQL query it is generating to hit DB. So generally it is useful when we are debugging the Query it is generated.

In hibernate we used the write the property in XML file as hibernate.show.sql to view the query.

In Liferay also we can also set the below property to true in the portal-ext.properties to view the SQL query at the console.


hibernate.show_sql=true.


By adding the above property it will just reterive the parameters. What if we want to display the parameters.

Add the below properties in log4j.properties it will display the paramters also.

# logs the SQL statements
#log4j.logger.org.hibernate.SQL=debug

# Logs the JDBC parameters passed to a query
#log4j.logger.org.hibernate.type=trace

In Liferay log4j is located in the below location

tomcat-7.0.42\webapps\ROOT\WEB-INF\classes



Also we want to see hibernate session leaks in a portlet

Whilst diagnosing a performance problem and memory leak, I turned on the following logging for Liferay Portal 6.0 EE SP1 to get a better view of Hibernate:

log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.jdbc=DEBUG
log4j.logger.org.hibernate.transaction=DEBUG

I started seeing this line which made me wonder if sessions were being leaked:

transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources!

In the end, I enabled the following logging of Hibernate sessions - specifically to see the opening and closing of sessions:  

log4j.logger.org.hibernate.impl.SessionImpl=TRACE

It turns out the above message is just a debug warning so it's nothing to worry about. The number of closes equals the number of opens:

[DEBUG] [http-9280-211] org.hibernate.impl.SessionImpl - opened session at timestamp: 5407217215401990
[TRACE] [http-9280-211] org.hibernate.impl.SessionImpl - setting flush mode to: MANUAL