Converting SQL JasperReports to use HQL (Updated)

One problem with JasperReports is the lack of documentation (there's an official manual, but isn't free) - you'd think this would encourage people to write HOWTOs, but these seem pretty thin on the ground, too.

So, a JasperReports HOWTO on converting existing reports using sql queries via jdbc, to use HQL (hibernate query language). HQL makes reports are independent of the underlying database, which is attractive, and as far as syntax HQL is broadly similar to SQL.

I used iReport 1.2.2, a graphical report designer for JasperReports. Before you can use iReport with hibernate, you need to put the jdbc driver for the database you are using (postgresql, in my case) in the lib/ directory of iReport. If you don't do this, you will be given the helpful error "General Error: Could Not Open Connection" when you try to execute the report.

So, to convert you reports to HQL using iReport:

1. Configure your classpath (options>classpath). Make sure your jdbc driver is on it, as well as your hibernate.cfg.xml file and all the hibernate mapping files (.hbm.xml). The persisent classes you are mapping (for example, Drug.class, which is mapped by Drug.hbm.xml) should also be on the classpath.

2. Add the report connection (Data>Connections/Datasources). Add a new connection of type Hibernate Connection.

3. Open a report. Open the Report Query (Data>Report Query). Select HQL as the query language from the drop-down box, and write your query in HQL. Note that it may work better to uncheck 'Automatically Retrieve Fields' while you are writing the query. A good HQL reference is here.

4. If you have a subreport, you will need to a new subreport parameter to pass the hibernate session. The parameter name is HIBERNATE_SESSION and its value is $P{HIBERNATE_SESSION}. You should obviously also edit the subreport to use HQL, as described in steps 1-3, and compile it.

5. You should now be able to run your report in iReport. To fill the compiled report from your application, you will need to pass the hibernate session as a parameter, HIBERNATE_SESSION, as in this snippet (if more code is needed, I can post it):

File n = new File("Reports"+java.io.File.separator);
HashMap map = new HashMap();
map.put("patientId", thePatientId);
map.put("path", n.getCanonicalPath());
map.put("HIBERNATE_SESSION", HibernateUtil.currentSession());

// Report to use
FileInputStream fileInputStream = getJasperReportFromJRXMLorJASPER("Reports"+java.io.File.separator
+"prescriptionHistory");

// Define the connection
Connection connection = HibernateUtil.currentSession().connection();

JasperPrint jp = JasperFillManager.fillReport(fileInputStream, map,
connection);
JasperViewer.viewReport(jp, false);



UPDATED: As it turns out, this is now different in the current version of Hibernate. You do not need to pass the hibernate session from the main report to the subreport, as all the builtin parameters are passed automatically. If you do try and pass the session the old way, it overwrites the builtin session and the subreport gets a null value.