Tags: Hibernate , log4j , logger

What is Hibernate?
Hibernate is framework that provides object oriented interaction with database. Now a days  it is used widely to handle large database.

If you are working behind large application which uses hibernate for all of its transaction, I think you might need to configure query log and tune up your hibernate criteria.


Problem: JOIN is one creates massive overload in hibernate criteria. To get your desired output you always use JOIN in your criteria. You should be careful while using JOIN in any of your hibernate criteria or queries (HQL).  Single JOIN will load tons of database records and create overhead on your application.

Why you need to configure query log?
For development purpose it will be very useful to track down each query execution through hibernate. I'm not talking about hibernate.show_sql property. It'll only show you query with ? but won't print actual input and output of that query.

We'll configure log4j to get input query parameter and output values. You should turn off this mechanism at production server of will create IO overhead on server.

Step 1: Creating log4j.properties file. I already created one for you all you need to do is place it in your project.

log4j.logger.org.hibernate=INFO
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
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.rootLogger = DEBUG, FILE

# Define the file appender
log4j.appender.FILE=org.apache.log4j.DailyRollingFileAppender
# Set the DatePattern
log4j.appender.FILE.DatePattern='.'yyyy-MM-dd-HH-mm

# Set the append to false, should not overwrite
log4j.appender.FILE.Append=true

# Set the name of the file.
# It'll be created every minute with different filename(appnded with yyyy-MM-dd-HH-mm) and placed under log4j folder.
log4j.appender.FILE.File=log4j/QueryLog.log

# Define the layout for file appender
log4j.appender.FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.FILE.layout.conversionPattern= %d{HH:mm:ss} %-5p %c - %m%n

Step 2: Configure this log4j.properties file before you perform any database transaction (Select, Insert, Update, Delete). Note: Download log4j [http://www.findjar.com/jar/log4j/jars/log4j-1.2.15.jar.html]

import org.apache.log4j.PropertyConfigurator; // import required
/** 
  * In below code I placed log4j.properties as string. 
  * Its file path, I placed log4j.properties in root directory/default package. You need to change path. 
  * If you are working with web application place it in war folder for below code to work.
  */
PropertyConfigurator.configure("log4j.properties");

Output:

17:43:45 DEBUG org.hibernate.SQL - select this_.UID as UID0_0_, this_.FIRSTNAME as FIRSTNAME0_0_, this_.Lastname as Lastname0_0_ from user_master this_ where this_.UID=?
17:43:45 TRACE org.hibernate.type.IntegerType - binding '1' to parameter: 1
17:43:45 DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
17:43:45 TRACE org.hibernate.type.IntegerType - returning '1' as column: UID0_0_
17:43:45 TRACE org.hibernate.type.StringType - returning 'Vicky' as column: FIRSTNAME0_0_
17:43:45 TRACE org.hibernate.type.StringType - returning 'Thakor' as column: Lastname0_0_

As you can see it shows what is value of input parameter where this_.UID=? with binding '1' to parameter: 1 and return values of fired query.

Conclusion: We have to be very careful about what we are trying to fetch from database and what will be fetched by frameworks. So configure it in your application and tune it up!

Tags: Hibernate , log4j , logger