JasperReports Server and Hibernate HQL

Ad Hoc Reports with Hibernate HQL queries

Recently someone asked my if it was possible to create Ad Hoc reports in JasperReports Server (JRS) using a Hibernate data source. I was quick to say yes. I have heard of projects at Jaspersoft where that was done, and in general one of the great strengths of Jaspersoft is that custom data sources are easy to implement. (Sidenote: that flexibility is one reason that lots of big data datasources were so easy to implement.) But when I investigated the precise steps around how to setup Ad Hoc reporting with Hibernate, I found that things were not well documented. I will log bugs and enhancement requests to create new samples and document existing samples better. But for an immediate solution I thought it would be useful to document this for future reference.

JasperReports and Hibernate

Can JasperReports (JR) work with Hibernate? This one is almost too easy. Yes. Not only can it work with hibernate, but there’s even a sample that ships with JR showing it in action. Here’s how to get it:

• Head to JasperForge.org, then to the JR project, then click “Download JasperReports Now“, then sign-in or join jasperforge, then get re-directed to the downloads hosted on sourceforge.net, then choose the 4.0.1 JR release to correspond to the current [as of 1 July 2011] JasperReports Server 4.1 release (not a typo: JRS 4.1 uses JR 4.0.1).
• Or click here.

Then choose jasperreports-4.0.1-project.tar.gz or jasperreports-4.0.1-project.zip.

Download either of the "project" files to get the JR binaries and source and, most importantly, the samples.

After unzipping the archive you’ll find the sample database and the sample hibernate example:

.../jasperreports-4.0.1/demo/hsqldb
.../jasperreports-4.0.1/demo/samples/hibernate

To start the sample database you can simply run “ant” in the directory “hsqldb”.
To execute the sample reports you can simply run “ant” in the directory “hibernate”. You’ll to open a command line to do this. It will look something like this:

Run "ant" to launch the sample database.

Then you’ll find the generated reports here:

.../jasperreports-4.0.1/demo/samples/hibernate/build/reports

There are lots more details to investigate. You can see the sample HQL queries in the .jrxml files. You can see the hibernate cfg files and hbm files, etc. But to just get it working, it takes only those two commands. Here’s the sample HQL query from one of the provided reports:

select address as address, document.id as documentId, document.total as documentTotal
from Address as address join address.documents as document
where city not in ($P{CityFilter})
order by address.city, address.lastName, address.firstName, address.id

iReport and Hibernate

Having seen that JasperReports works with Hibernate, the next practical step is to start creating your own reports. The best place to do this is in iReport. (Coming soon: the best place to do this will be Jaspersoft Studio!)

Open the iReport Preferences window to the tab “Query Executers” and you can see that “hql” is supported as a query language by default. This is a nice start. But to be able to execute a query you need your Hibernate classes and configuration files in iReport’s classpath. To set the classpath go to the menu Tools → Options… (on Mac: ⌘,). I wanted to open and execute the reports that came with the JR sample shown above, so I went to the tab “Classpath” and added this directory:

…/jasperreports-4.0.1/demo/samples/hibernate/build/classes

Next define a Hibernate connection, and you’re all set. The connection has only a single parameter to set; for this sample be sure to use Hibernate Annotations. Click the button “Test” to make sure all is well. Make a puzzled expression when it prompts you for a password and just click OK (isn’t the login info included in the file hibernate.cfg.xml?). Make a questioning noise when it prompts you for a password again (Huh? What possible reason could it have for prompting twice?) and click OK again without entering a password. Smile when the connection is successful.

A Hibernate connection has only one parameter.

Now you can open and run the reports from the hibernate sample project. I started with “HibernateQueryReport.jrxml”. As a convenience to the user, it would be nice if the three parameters in this report had default values. They don’t. As a convenience to the reader, I have suggested reasonable values below.

ReportTitle: My Hibernate Report

CityFilter: Lexington,Indianapolis,Jackson

OrderClause: id

Make a derisive snort when iReport prompts again for the password. Just click OK. Roll your eyes when iReport prompts yet again for a password. (Surely someone should log a bug in the iReport tracker for this. I haven’t.) And voilà: a report using HQL has been automatically translated into SQL and executed against the sample Hypersonic database. This is great! Those JR guys didn’t shirk their duties either: it’s not just a trivial query populating a tabular report. This is a reasonably interesting HQL query including parameters, and the report even has a subreport. Here is a snippet from the generated report using the parameter values indicated above:

Preview of the generated HQL report

JasperReports Server and Hibernate

Now that we can build reports, let’s deploy them.

Deploy the Hibernate Data Source

JasperReports Server (JRS) does not ship with built-in support for Hibernate like it does for JDBC SQL data sources. Creating a custom data source isn’t too hard, but it’s not trivial either. Fortunately, we don’t have to create the datasource ourselves. JRS ships with sample custom data sources to show you the way.

The precise location of the samples folder will depend on where you installed JRS, of course, but in my case the folder is here:

.../jasperreports-server-4.1-bin/samples/customDataSource

So how do you install these sample data sources? Fortunately, there’s a readme.txt with instructions. Unfortunately, it hasn’t been turned over to the documentation team for proofreading (sorry, Bob). It reads “JasperServer” where it should indicate “JasperReports Server”. Jaspersoft changes product names pretty often, so this is forgivable in a readme that was written long ago. It has things like “you will have Ant installed already”. That future tense is awkward. “Ant may be run with the following command” Passive voice would have been disliked by a doc writer (he he). “The example reports for this data source read a web page …” Subject verb agreement. Etc.

But ignoring style considerations, the readme file contains quite a bit of useful information which can be distilled from 76 lines into two critical steps for us in our task of enabling Hibernate reports:

1. Edit build.xml to set the webAppDir property
2. Run ant with no arguments

Edit build.xml to set the webAppDir property
original: <property name="webAppDir" value="c:/download/apache-tomcat-5.5.12/webapps/jasperserver-pro"/>
modified: <property name="webAppDir" value="/usr/local/tomcat/webapps/jasperserver-pro"/>
Your modified version will be slightly different. Be sure that it points to the location where JRS is deployed.

This copies 20 files into your existing JRS deployment. Since we only care about the Hibernate at the moment, let’s look at the 7 files relevant to this data source. (The web scraper sample is pretty cool. Maybe I’ll write about that another time.) These are the files:

WEB-INF/applicationContext-hibernateDS.xml
bundles/hibernateds.properties
classes/hibernate.cfg.xml
classes/example/cds/BasicHib.class
classes/example/cds/basichib.hbm.xml
classes/example/cds/HibernateDataSourceService.class
classes/example/cds/HibernateSessionFactoryFinder.class

The applicationContext file makes sense if you’re familiar with Spring. If you aren’t, don’t worry. It just defines the bean hibernateDataSource, and you don’t need to worry about it. The properties file defines some text strings that show up in the JRS GUI.

The files in the directory “classes” are the interesting ones. These define our Hibernate data model. Presumably you have your own version of these or you wouldn’t be reading this article. But let’s test with the sample version before you swap in your own version of these.

Create a Hibernate Connection (to the JRS Repository)

Start JasperReports Server (JRS). Or restart it if it was running. It must restart to pickup the new configuration files. Create a new data source. Notice that you now have three new data source types.

Three new data sources in JRS

Now that the data source “Hibernate Data Source” has been installed, you can create a data source pointing to your particular Hibernate sources. First, we can test a special case. The JRS Repository uses Hibernate to connect the web application to the database. This is what makes it easy to support PostgreSQL, MySQL, Oracle, MS SQL Server, etc. We can use the JRS Repository as the data source for a report.

Right-click on a folder in the JRS repository view and add a data source. It should look like this:

Special case: use the JRS Repository as the data source for a report

To use the JRS Repository as the data source you must set the Session Factory Bean Name value to “sessionFactory“. This is a standard bean that JRS uses for its repository connection. This allows the data source to get data out of the JRS repository, and we count on Hibernate to make sure that the query works regardless of whether the repository is on PostgreSQL or MySQL or Oracle or MS SQL Server etc.

Now that the data source is defined, next you need to upload the report. Find the file “hqlTest.jrxml” here:

.../jasperreports-server-4.1-bin/samples/customDataSource/reports/hqlTest.jrxml

Right-click a folder in the JRS Repository view and add a JasperReport. Use the data source that you just created (it’s called “Hibernate_Sample_Datasource” in the image above.)

Run the Hibernate Report

The sample report report hqlTest.jrxml isn’t a beautiful one. It’s unsorted, and it’s not particularly well formated. But it’s running HQL to get its data. Therefore it’s pretty cool. It could have quite a few practical uses. We could use this to get an interesting list of reports from the repository, and we could add a drill-down link to launch each report. The possibilities are very interesting.

Preview of the sample Hibernate Repository Report

But the task at hand—the reason you’ve labored through this lengthy article—is to run a query against your own Hibernate data model. Let’s move on.

Create a Hibernate Connection (to an arbitrary Hibernate source)

The file “hibernate.cfg.xml” that is currently deployed to your JRS defines a connection to a MySQL database called hibernatesample. To use this connection you could create the database “hibernatesample” and create a table like this:

create table basichib ( EVENT_ID int, name varchar(10), value int );
insert into basichib values ( 1, 'nameA', 1 );
insert into basichib values ( 2, 'nameB', 42 );

Creating a connection to this Hibernate data source is easier than one might expect. Indeed, it took me a few days to realize what I needed to do, since I thought there were probably quite a few steps needed. In fact, the only thing you need to do is create another Hibernate data source and NOT specify anything for the Session Factory Bean Name. It was great to figure this out… but it was also a bit anti-climactic. The data source definition looks like this (it should have no parameters set at all):

General case: Connection to any Hibernate source

Then create a report with an HQL query like this:

from example.cds.BasicHib h

I created the requisite table and report. I found that I had a report which is dead boring from a practical standpoint… but I confess to being wildly excited as I first got data from my database without writing any SQL. On the other hand, you’ll be far more interested in connecting to your existing Hibernate data source. Fear not, the task is finally at home. Read on.

Create a Hibernate Connection (to your own Hibernate source!)

Creating and reporting on sample data lacks a certain level of gravitas. Let’s modify this connection to use your own database. We don’t need to make any connection to the data source defined in JRS. It doesn’t have any properties after all, so there’s nothing to modify. Instead you must copy the relevant Hibernate configuration files into the JRS classpath.

For my article I’ll use the Hypersonic database that ships with the JR samples which is referenced at the start of this article which is by default inaccessible to a JRS instance. I simply need to copy the hibernate configuration files from the JR sample to the deployed JRS to make it accessible.

from: .../jasperreports-4.0.1/demo/samples/hibernate/build/classes
to: .../apache-tomcat-6.0.32/webapps/jasperserver-pro/WEB-INF/classes

In theory, all that’s left is to restart JRS and start deploying reports. Hooray! In fact, I ran into three problems with this idea. Bummer.

First, JRS didn’t have the Hypersonic database driver. This is pretty obvious requirement, so I wasn’t too surprised. I had to copy hsqldb-1.8.0-10.jar into WEB-INF/lib.

Second, it seems that JR had no need for the current session… but the data source in JRS needs this. This wasn’t quite as obvious. But the error message was quite clear. So it wasn’t hard to figure out what was needed. I had to add this additional property to hibernate.cfg.xml:

<property name="current_session_context_class">thread</property>

After these two changes I was able to run the sample reports that came with the JR hibernate sample as canned reports. This was great, and now I’m nearly done. Next, I wanted to use these queries as Ad Hoc topics, so I copied the reports into the repository folder /adhoc/topics.  You can see them here:

Closeup view of the Topics folder

The first topic—the one that was a copy of the report used the repository as a data source—worked immediately. I was able to create a new Ad Hoc report using the drag and drop Ad Hoc interface to get whatever I wanted.

My second test used the sample query listed above: “from example.cds.BasicHib h”. It worked perfectly as well.

My third test used the sample report that shipped with the JR hibernate sample. This one failed. It ran with no errors, but it didn’t return any data. This one was more confusing. In the end I realized that the problem is that the HQL query executer relies on the field description as important metadata. But the Ad Hoc engine assumes that the description is not important, so it ignores this information. Having found this crucial issue, the solution was straightforward. It’s a single line of code that needs to change. But for a JRS user like myself this is not a trivial demand. In the release of JasperReports Server 4.2 this change will be included, so HQL reports should work immediately as topics. Until that’s available, I have made the relevant re-compiled .jar file available. Just replace your existing version of ji-gyroscope-4.1.0.jar with the one attached to this article. Note: my second test worked, and you may find that your HQL reports work just fine as topics right out of the box too. Since the fix will be released quite soon, I won’t bother documenting the circumstances when the failure to pass in the field description matters and when it doesn’t.

After that final tweak I was all set. The report AddressesReport.jrxml was already working in the JRS repository as a canned report. Now it’s working as expected as a Topic. Hooray!

Ad Hoc Editor with sample hibernate source

Summary

This article is surprisingly long. That’s because there are samples in a couple of different places, and there are independent steps involved in getting HQL working in iReport and in JasperReports Server. I wanted to include  extensive details on each stage of the process for future reference. Good luck. Please let me know if you have questions about the setup.

Appendix

For the curious, here is a diff of the code change that was needed:

--- gyroscope/src/main/java/com/jaspersoft/ji/adhoc/strategy/NewClassicDataStrategy.java        (revision 20735)
+++ gyroscope/src/main/java/com/jaspersoft/ji/adhoc/strategy/NewClassicDataStrategy.java        (working copy)
@@ -146,7 +146,7 @@
                                slField.setName(f.getName());
                                slField.setType(f.getType());
                                // pass description through because MDX queries need it
-                               if (lang != null && lang.equalsIgnoreCase("mdx")) {
+                               if (lang != null && (lang.equalsIgnoreCase("mdx") || lang.equalsIgnoreCase("hql"))) {
                                        slField.setProperty(BaseField.PROP_JR_DESCRIPTION, f.getDescription());
                                }
                                // if other props possibly needed by QE are there (e.g. webscraper custom QE sample), pass them through

You can see that the solution was to treat the HQL query executer just like we already treat the MDX query executer.

About these ads