iReport is a report builder/designer for JasperReports written in java, which allows users to visually edit reports instead of editing raw XML. Hibernate related forums include postings addressing the integration of iReport/JasperReports with Hibernate into a common solution. Most of them suggest the definition of a custom data source implementing the JRDataSource interface.

However, the iReport/JasperReports solution supports the use of the HQL query language, since version 1.2.0. This document addresses the problems that arise when the fields of the HQL query are read by the iReport tool, using the it.businesslogic.ireport.hibernate.HQLFieldsReader class. A replacement of the specific class is attempted and made available to this area, in case it is of any use to community developers.

In the context of this document, the suggested solution refers to:

• the iReport 1.2.3 release,
• the JasperReports 1.2.3 release,
• and the Hibernate 3.1.3 release.

Reading HQL query fields

The HQLFieldsReader class takes an HQL query string as input and builds a Vector of it.businesslogic.ireport.JRField objects. A sample JRField instantiation is as follows:

      JRField jrField = new JRField(fieldName.toString(), type.toSting());
      jrField .setDescription(description.toSting());
      jrFields.add(jrField );

The current HQLFieldsReader solution works well only when plain object aliases are used in the HQL query select clause, but fails when more explicit select properties are specified, e.g.:

select a.lastName, b.title, b.Address.city from Employee a join a.Company b

A simple work around is to manually create fields and assign names from field aliases along with the corresponding field types. For the above example:

• the field names would be: 0, 1, 2
• and the field types: String, String, String.

However, this is not a very comfortable solution. The suggested concept is to automatically build intuitive field names from the HQL query that work properly. This can be realized if the retrieved field names match the select clause properties of the hql query, while the aliases are passed in the description property of the JRField. So, for the above example:

• the field names would be: a.lastName, b.title, b.Address.city
• the field descriptions: 0, 1, 2
• and the field types: String, String, String.

The replacement of the HQLFieldsReader class builds the Vector of JRField objects by using the org.hibernate.engine.query.HQLQueryPlan and org.hibernate.hql.ast.HqlParser classes (get full java source):

package it.businesslogic.ireport.hibernate;

...

public class HQLFieldsReader {
  private Vector  reportParameters;
  private String queryString = "";
  
  /** Creates a new instance of HQLFieldsReader */
  public HQLFieldsReader(String query, Vector reportParameters) {
    StringBuffer sb = new StringBuffer(query);
    for (int i = sb.indexOf("$P{"); i != -1; i = sb.indexOf("$P{")) {
      sb.replace(i, i+3, ":");
      int j = sb.indexOf("}", i);
      if (j != -1) sb.replace(j, j+1, "");
    }
    this.setQueryString(sb.toString());
    this.setReportParameters(reportParameters);
  }

  public Vector readFields() throws Exception {
    SessionFactory sf = new Configuration().configure().buildSessionFactory();
    Session session = sf.openSession();
    
    HQLQueryPlan hqlPlan = ((SessionFactoryImpl)sf).getQueryPlanCache()
     .getHQLQueryPlan( getQueryString(), false, ((SessionImpl)session).getEnabledFilters() );
    String[] aliases = hqlPlan.getReturnMetadata().getReturnAliases();
    Type[] types = hqlPlan.getReturnMetadata().getReturnTypes();
    
    session.close();
    
    List fieldNameList = getHQLSelectFieldNames();
    Vector jrFields = new Vector();
    int i = 0;
    for (Iterator fieldNames = fieldNameList.iterator(); fieldNames.hasNext();) {
      String fieldName = (String) fieldNames.next();
      JRField jrField = new JRField(fieldName.toString(), types[i].getReturnedClass().getName());
      jrField .setDescription(aliases[i]);
      jrFields.add(jrField );
      i++;
    }
    if (jrFields.size() > 0) return jrFields;
    // If there are not any explicit select fields, fill with implicit fields.
    for (i = 0; i < types.length; i++) {
      Type type = types[i];
      PropertyDescriptor[] pd = org.apache.commons.beanutils.PropertyUtils
          .getPropertyDescriptors(types[i].getReturnedClass());
      for (int nd =0; nd < pd.length; ++nd) {
        String fieldName = pd[nd].getName();
        if (pd[nd].getPropertyType() != null && pd[nd].getReadMethod() != null) {
          if (fieldName.equals("class")) continue;
          String returnType =  pd[nd].getPropertyType().getName();
          JRField jrField = new JRField(types[i].getReturnedClass()
               .getSimpleName() + "." + fieldName, returnType);
          jrField .setDescription(fieldName);
          jrFields.add(jrField);
        }
      }
    }
    return jrFields;
  }
  
  private List getHQLSelectFieldNames() throws Exception {
    List fields = new ArrayList();
    HqlParser parser = HqlParser.getInstance(queryString);
    parser.setFilter(false);
    parser.selectClause();
    AST select = parser.getAST();
    if (select == null) {
      return fields; 
    }
    AST property = select.getFirstChild();
    int memo = 0;
    while (property != null) {
      switch (property.getType()) {
      case HqlSqlTokenTypes.DISTINCT: // DISTINCT keyword is not an HQL query field.
        break;
      case HqlSqlTokenTypes.AS: //aliasedSelectExpr
        fields.add(ASTUtil.getLastChild(property).toString());
        break;
      case HqlSqlTokenTypes.DOT: //p:propertyRef
      case HqlSqlTokenTypes.IDENT: //p:propertyRef
        fields.add(ASTUtil.getPathText(property));
        break;
      ...
      }
      if (property.getType() != HqlSqlTokenTypes.DISTINCT) memo++;
      property = property.getNextSibling();
    }
    return fields;
  }
...
}

Integrating the HQLFieldsReader replacement into iReport

• Get the full version of the HQLFieldsReader source, copy it into the package corresponding directory path and compile it. You will need the following jars in your classpath:
  • hibernate3.jar (from the Hibernate 3.1.3 release)
  • jasperreports-1.2.3.jar
  • iReport.jar (from the iReport 1.2.3 release)
  • antlr.jar (you can use the antlr-2.7.6rc1.jar that comes with the Hibernate 3.1.3 release)
  • commons-beanutils.jar (you can use the commons-beanutils-1.5.jar that comes with the iReport 1.2.3 release).

• Replace the original HQLFieldsReader class file in the iReport.jar with the newly HQLFieldsReader compiled class.
• Replace the hibernate3.jar in the iReport/lib directory with the hibernate3.jar from the Hibernate 3.1.3 release.
  • Certain classes, which are used by the replacing HQLFieldsReader class, are missing from the iReport/lib/hibernate3.jar.

Note that the JRField description property is used by default as the field's name when filling the report. If for any case this does not occur, define a custom property, in accordance with the JasperReports framework, that explicitly specifies the use of field description properties (from iReport 1.2.3, Edit > Custom properties):

• net.sf.jasperreports.hql.field.mapping.descriptions=true

If parameters are used in the where clause, the parameter name convention from iReport is used. For example, an HQL query that uses the $P{L_NAME} parameter may look like:

select a.firstName, a.lastName from Employee a where a.lastName like $P{L_NAME} || '%'

This is the way iReport and JasperReports are expecting named parameters in HQL queries. When executing the report from within iReport, the parameter will be replaced by its value. If the "Is for prompting" attribute is not checked, the default value will be used.

The following code fragment shows how to instantiate a named query parameter programmatically with the JasperReports framework - it is assumed that the above query is specified in the C:/temp/test.jrxml report file, and the compilation resides in C:/temp/test.jasper:

...
    SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
    Session session = sessionFactory.openSession();
    Map parameters = new HashMap();
    parameters.put(JRHibernateQueryExecuterFactory.PARAMETER_HIBERNATE_SESSION, session);
    parameters.put("L_NAME", startingWith.toString());
    Transaction transaction = session.beginTransaction();
    JasperFillManager.fillReportToFile("C:/temp/test.jasper", parameters);
    transaction.rollback();
    session.close();
    JasperExportManager.exportReportToPdfFile("C:/temp/test.jrprint");
...

However, when the Hibernate framework is used to parse and syntactically validate the HQL query, the iReport naming convention for parameters is not recognized. The HQLFieldsReader class takes that into account and performs a very simplistic transformation before parsing the query. After such a transformation the HQL query would look like:

select a.firstName, a.lastName from Employee a where a.lastName like :L_NAME || '%'

Since the HQLFieldsReader does not perform the query against the database, the actual value of the parameter does not have to be provided.

Known issues and shortcomings

iReport 1.2.3 bug with entities in select clause

Consider the following HQL query:

select a.lastName, a.Address.street, a.Address.number, b from Employee a join a.Company b

For the above query:

• the field names would be: a.lastName, a.Address.street, a.Address.number, b
• the field descriptions: 0, 1, 2, 3
• and the field types: String, String, Integer, some.package.Company

Such a field definition will work fine with the JasperReports framework. If it is assumed that the above query is in the C:/temp/test.jrxml report file, and the compilation resides in C:/temp/test.jasper, the following code fragment can be used to verify that the b entity can be used as a field in the report query:

...
    SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
    Session session = sessionFactory.openSession();
    Map parameters = new HashMap();
    parameters.put(JRHibernateQueryExecuterFactory.PARAMETER_HIBERNATE_SESSION, session);
    Transaction transaction = session.beginTransaction();
    JasperFillManager.fillReportToFile("C:/temp/test.jasper", parameters);
    transaction.rollback();
    session.close();
    JasperExportManager.exportReportToPdfFile("C:/temp/test.jrprint");
...

However, when running the report from within iReport (1.2.3), a java.lang.ClassCastException will be thrown, which should be due to custom class loading performed by iReport.

Absence of select clause

The HQL query language allows the absence of the select clause in a select statement. In that case the fields are implied by the from clause. However, the JasperReports framework does not seem to support this for more than one query spaces in the from clause.

Filters

Use of filters (instead of an HQL query) has not been tested with the suggested solution.