Using Hibernate queries with JasperReports

Introduction
In the article, we examine a performance-optimised approach for using Hibernate queries to generate reports with JasperReports.

JasperReports is a powerful and flexible Open Source reporting tool. Combined with the graphical design tool iReport, for example, you get a complete Java Open Source reporting solution. In this article, we will investigate how you can integrate JasperReports reporting with Hibernate data sources in an optimal manner, without sacrificing ease-of-use or performance.

Basic Hibernate/JasperReports integration
To integrate Hibernate and JasperReports, you have to define a JasperReports data source. One simple and intuitive approach is to use the JRBeanCollectionDataSource data source (This approach is presented here) :

List results = session.find("from com.acme.Sale");

Map parameters = new HashMap();
parameters.put("Title", "Sales Report");

InputStream reportStream
= this.class.getResourceAsStream("/sales-report.xml");
JasperDesign jasperDesign = JasperManager.loadXmlDesign(reportStream);
JasperReport jasperReport = JasperManager.compileReport(jasperDesign);

JRBeanCollectionDataSource ds = new JRBeanCollectionDataSource(results);
JasperPrint jasperPrint = JasperManager.fillReport(jasperReport,
parameters,
ds);

JasperExportManager.exportReportToPdfFile(jasperPrint, "sales-report.pdf");


This approach will work well for small lists. However, for reports involving tens or hundreds of thousands of lines, it is inefficiant, memory-consuming, and slow. Indeed, experience shows that, when running on a standard Tomcat configuration, a list returning as few as 10000 business objects can cause OutOfMemory exceptions. It also wastes time building a bulky list of objects before processing them, and pollutes the Hibernate session (and possibly second-level caches with temporary objects.

Optimised Hibernate/JasperReports integration
We need a way to efficiently read and process Hibernate queries, without creating too many unnecessary temporary objects in memory. One possible way to do this is the following :

Define an optimised layer for executing Hibernate queries efficiently
Define an abstraction layer for these classes which is compatible with JasperReports
Wrap this data access layer in a JasperReports class that can be directly plugged into JasperReports
The Hibernate Data Access Layer : The QueryProvider interface and its implementations
We start with the optimised Hibernate data access. (you may note that this layer is not actually Hibernate-specific, so other implementations could implement other types of data access without impacting the design).

This layer contains two principal classes :

The CriteriaSet class
The QueryProvider interface
A CriteriaSet is simply a JavaBean which contains parameters which may be passed to the Hibernate query. It is simply a generic way of encapsulating a set of parameters. A QueryProvider provides a generic way of returning an arbitrary subset of the query results set. The essential point is that query results are read in small chunks, not all at once. This allows more efficient memory handling and better performance.

/**
* A QueryProvidor provides a generic way of fetching a set of objects.
*/
public interface QueryProvider {

/**
* Return a set of objects based on a given criteria set.
* @param firstResult the first result to be returned
* @param maxResults the maximum number of results to be returned
* @return a list of objects
*/
List getObjects(CriteriaSet criteria,
int firstResult,
int maxResults) throws HibernateException;
}

A typical implementation of this class simply builds a Hibernate query using the specified criteria set and returns the requested subset of results. For example :

public class ProductQueryProvider implements QueryProvider {

public List getObjects(CriteriaSet criteria,
int firstResult,
int maxResults)
throws HibernateException {
//
// Build query criteria
//
Session sess = SessionManager.currentSession();
ProductCriteriaSet productCriteria
= (ProductCriteriaSet) criteria;
Query query = session.find("from com.acme.Product p "
+ "where p.categoryCode = :categoryCode ");

query.setParameter("categoryCode",
productCriteria.getCategoryCode();
return query.setCacheable(true)
.setFirstResult(firstResult)
.setMaxResults(maxResults)
.setFetchSize(100)
.list();
}
}


A more sophisticated implementation is helpful for dynamic queries. We define an abstract BaseQueryProvider class which can be used for dynamic query generation. This is typically useful when the report has to be generated using several parameters, some of which are optionnal.. Each derived class overrides the buildCriteria() method. This method builds a Hibernate Criteria object using the specified Criteria set as appropriate :

public abstract class BaseQueryProvider implements QueryProvider {

public List getObjects(CriteriaSet criteria, int firstResult, int maxResults)
throws HibernateException {

Session sess = SessionManager.currentSession();
Criteria queryCriteria = buildCriteria(criteria, sess);
return queryCriteria.setCacheable(true)
.setFirstResult(firstResult)
.setMaxResults(maxResults)
.setFetchSize(100)
.list();

}

protected abstract Criteria buildCriteria(CriteriaSet criteria, Session sess);
}

A typical implementation is shown here :

public class SalesQueryProvider extends BaseQueryProvider {

protected Criteria buildCriteria(CriteriaSet criteria,
Session sess) {
//
// Build query criteria
//
SalesCriteriaSet salesCriteria
= (SalesCriteriaSet) criteria;

Criteria queryCriteria
= sess.createCriteria(Sale.class);

if (salesCriteria.getStartDate() != null) {
queryCriteria.add(
Expression.eq("getStartDate",
salesCriteria.getStartDate()));
}
// etc...

return queryCriteria;
}
}



Note that a QueryProvider does not need to return Hibernate-persisted objects. Large-volume queries can sometimes be more efficiently implemented by returning custom-made JavaBeans containing just the required columns. HQL allows you to to this quite easily :

public class CityQueryProvider implements QueryProvider {

public List getObjects(CriteriaSet criteria,
int firstResult,
int maxResults)
throws HibernateException {
//
// Build query criteria
//
Session sess = SessionManager.currentSession();
Query query
= session.find(
"select new CityItem(city.id, "
+ " city.name, "
+ " city.electrityCompany.name) "
+ " from City city "
+ " left join city.electrityCompany");

return query.setCacheable(true)
.setFirstResult(firstResult)
.setMaxResults(maxResults)
.setFetchSize(100)
.list();
}
}


Hibernate data access abstraction : the ReportDataSource interface
Next, we define a level of abstraction between the Hibernate querying and the JasperReport classes. The ReportDataSource does this :

public interface ReportDataSource extends Serializable {
Object getObject(int index);
}

The standard implementation of this interface reads Hibernate objects using a given QueryProvider and returns them to JasperReports one by one. Here is the source code of this class (getters, setters, logging code and error-handling code have been removed for clarty) :

public class ReportDataSourceImpl implements ReportDataSource {

private CriteriaSet criteriaSet;
private QueryProvider queryProvider;
private List resultPage;
private int pageStart = Integer.MAX_VALUE;
private int pageEnd = Integer.MIN_VALUE;
private static final int PAGE_SIZE = 50;

//
// Getters and setters for criteriaSet and queryProvider
//
...

public List getObjects(int firstResult,
int maxResults) {

List queryResults = getQueryProvider()
.getObjects(getCriteriaSet(),
firstResult,
maxResults);
if (resultPage == null) {
resultPage = new ArrayList(queryResults.size());
}
resultPage.clear();
for(int i = 0; i < queryResults.size(); i++) {
resultPage.add(queryResults.get(i));
}
pageStart = firstResult;
pageEnd = firstResult + queryResults.size() - 1;
return resultPage;
}

public final Object getObject(int index) {
if ((resultPage == null)
|| (index < pageStart)
|| (index > pageEnd)) {
resultPage = getObjects(index, PAGE_SIZE);
}
Object result = null;
int pos = index - pageStart;
if ((resultPage != null)
&& (resultPage.size() > pos)) {
result = resultPage.get(pos);
}
return result;
}
}


Finally, we have to be able to call the Hibernate data source from JasperReports. To do so, we start by looking at the JasperManager fillReport() method, which takes a JRDataSource object as its third parameter and uses it to generate the report :

JasperPrint jasperPrint = JasperManager.fillReport(jasperReport, parameters, ds);

To implement our own optimised JRDataSource, we extended the JRAbstractBeanDataSource class. This class is presented here (logging and error-handling code has been removed for clarty).

public class ReportSource extends JRAbstractBeanDataSource {

private ReportDataSource dataSource;
protected int index = 0;
protected Object bean;
private static Map fieldNameMap = new HashMap();

public ReportSource(ReportDataSource dataSource) {
super(true);
this.dataSource = dataSource;
index = 0;
}

public boolean next() throws JRException {
bean = dataSource.getObject(index++);
return (bean != null);
}

public void moveFirst() throws JRException {
index = 0;
bean = dataSource.getObject(index);
}

public Object getFieldValue(JRField field) throws JRException {
String nameField = getFieldName(field.getName());
return PropertyUtils.getProperty(bean, nameField);
}

/**
* Replace the character "_" by a ".".
*
* @param fieldName the name of the field
* @return the value in the cache or make
* the replacement and return this value
*/
private String getFieldName(String fieldName) {
String filteredFieldName
= (String) fieldNameMap.get(fieldName);
if (filteredFieldName == null) {
filteredFieldName = fieldName.replace('_','.');
fieldNameMap.put(fieldName,filteredFieldName);
}
return filteredFieldName;
}
}


This class is basically just a proxy between JasperReports and the Hibernate data source object. The only tricky bit is field name handling. For some reason, JasperReports does not accept field names containing dots (ex. "product.code"). However, when you retrieve a set of Hibernate-persisted business objects, you often need to access object attributes. To get around this, we replace the "." by a "_" in the JasperReport template (ex. "product_code" instead of "product.code"), and convert back to a conventional JavaBean format in the getFieldName() method.

Putting it all together
So, when you put it all together, you get something like this :

List results = session.find("from com.acme.Sale");

Map parameters = new HashMap();
parameters.put("Title", "Sales Report");

InputStream reportStream
= this.class.getResourceAsStream("/sales-report.xml");
JasperDesign jasperDesign
= JasperManager.loadXmlDesign(reportStream);
JasperReport jasperReport
= JasperManager.compileReport(jasperDesign);

ReportDataSource hibernateDataSource
= new ReportDataSourceImpl();
hibernateDataSource.setQueryProvider(new SalesQueryProvider());
hibernateDataSource.setCriteriaSet(salesCriteria);
ReportSource rs = new ReportSource(hibernateDataSource);

JasperPrint jasperPrint
= JasperManager.fillReport(jasperReport,
parameters,
rs);
JasperExportManager.exportReportTsoPdfFile(jasperPrint,
"sales-report.pdf");


Further JasperReports optimisations

Compiled Report caching
In the previous code, the JasperReport is loaded and compiled before it is run. In a serverside application, for optimal performance, the reports should be loaded and compiled once and then cached. This will save several seconds on each query generation.

Optimising Hibernate Queries
The first cause of slow report generation is sub-optimal querying. Hibernate is a high-performance persistence library which gives excellent results when correctly used. So you should treate any report which takes an excessive time to generate as suspicious. Common Hibernate optimisation strategies include :

Correct use of joins
Correct use of lazy associations
Reading a subset of columns rather than whole Hibernate-persisted objects
Some Hibernate query optimisation techniques are discussed here.