Monday, May 08, 2006

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";
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"

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

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

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.


Blogger Darryl said...

Hello Mellisa,

I am having trouble passing the Hibernate session to a sub report. Please can you help further. You can respond to

1:06 PM  
Blogger Darryl said...

PS. Its good to see that a fellow UCT grad is making waves with jasperreports and ireports. Makes me proud to say that I'm From UCT :) If you have any experience with Jasper, ireport, HQL. Please get in touch with me, I would love to pick your brain about some issues that I'm having. Darryl (

1:11 PM  
Blogger melissa said...


subreportParameter name=HIBERNATE_SESSION
subreportParameterExpression $P{HIBERNATE_SESSION}
connectionExpression $P{REPORT_CONNECTION}

3:14 PM  
Blogger erased said...

Hello Melissa,

I just want to thank you, this guide is very useful for me.

best regards.

12:07 PM  
Blogger kopcheski said...

Hello Melissa.

First of all, I'd like to congratulate you for your blog. This is something pretty useful to our community.

But, I also would like to ask for your help.

I think the only different point between your example, and my application is that you usu xml to map classes, and I do annotations.

But, when I try to set up a Hibernate Connection, i got the following message: "An AnnotationConfiguration instance is required to use mapping clazz="MyClass"/>".

The problem was detailed here, look:

If you could help me on this, I would be grateful. If you couldnt, I'm also grateful.

Vinicius Kopcheski

8:33 PM  
Blogger RamKri said...

How to Pass Hibernate session to sub report?

2:42 PM  
Anonymous Anonymous said...

thank you so much,i have a trouble about the fillreport thing in the application,i used the common jdbc connect ,that was useless, now from your dialog i get the way

10:58 AM  
Anonymous Jeff said...

melissa, thanks so much for your post. it helped me solve and issue that i had wrested with for several hours and my boss had tackled for several days.

1:58 AM  

Post a Comment

<< Home