Storing JasperReports in a database using iBATIS and Oracle 10g
Comments(1) I’ve used JasperReports in two different jobs now to provide reporting services for clients of the various web applications I’ve worked on. My first experience definitely turned out to be a great learning opportunity.
It was a great opportunity because I learned what not to do when designing a web-based reporting system the main lesson of which was:
- Don’t store report defintions on the file system.
We experienced a lot of deployment headaches in the clustered application server environment that was provided to us because we were not provided any sort of shared file system for application assets such as reports. Each application server in the cluster only had access to it’s own file system.
This article shows how I solved the file system issue by storing JasperReports report definitions in a database. I’ll assume familiarity with the iBATIS “ORM” database framework since I am not showing a full iBATIS setup here.
Part 1: Oracle Setup
The first thing that I did was get my database tables in place. Our needs were pretty simple and I created two tables: one, REPORT, to hold the various pieces of information about the report such as the name, description, and ID and another, REPORT_XML, to hold the actual report definition. These are tied together via a foreign key on the ID column of the REPORT and a REPORT_ID column on the REPORT_XML table. The most important piece of information here is the choice of the datatype to hold the report definition.
To allow easy creation of reports we are using the iReport visual report designer and saving the reports in the JRXML format. I didn’t see any benefit to compiling the definition and putting that in the database. With XML it makes the report definition fairly easy to read and very easy to parse should that be necessary. With that in mind I chose to use the CLOB type in Oracle.
CLOBs are good for storing large pieces of character data and they are handled effortlessly by iBATIS since the only thing being passed in to the library are String objects. As of release 10g of the Oracle JDBC driver there is no longer a need to use the extensions that Oracle provided for dealing with CLOBs (oracle.sql.CLOB) in previous releases. The only requirement is setting a new Connection property, SetBigStringTryClob, to true. When this is done the String being passed in to a PreparedStatement (or to a ResultSet for data coming out of the CLOB column) is inspected to find it’s size and if its over 32k a different call is made, to an OraclePreparedStatement object, to set the CLOB value properly. This is handled transparently by the driver.
The iBATIS and Java Code
Let’s take a look at how this works. Here is an iBATIS XML snippet to handle inserting a report definition into the REPORT_XML table:
<insert id="addReportXML" parameterClass="map"> insert into report_xml (id,last_modified_date,last_modified_by,description,report_id,xml_def) values (#id#,sysdate,user,#reportId#,#reportId#,#xmlData#) </insert> |
There is no special CLOB handling here. We are just dealing with standard types.
Here is the code in my DAO object that is calling the insert statement above:
private int addReportXML(String reportId, String xmlData) { IdManager idMgr = IdManager.getIdManager(); Connection connection = null; int result = 0; Map paramMap = new HashMap(); paramMap.put("id", idMgr.getUniqueId()); paramMap.put("reportId", reportId); paramMap.put("xmlData", xmlData); try { result = this.getSqlMapClientTemplate().update("ReportConfig.addReportXML", paramMap); } catch (SQLException e) { } return result; } |
In my actual code I do have exception handling code in the catch block above 
I am just trying to only include the important stuff here and for this posting database error handling isn’t important. I also left out the connection handling.
IdManager is just a class that will generate a unique ID string based on various bits of system information. We could use a sequence generator in the database as well the the ID generator works better for how we deploy our applications.
This code is obviously pretty straightforward. The only thing we are doing here is passing in a String that contains the full XML as well as the report ID this report definition corresponds to. Java Strings can be very large (char array indexed by an integer) so you’d have to have an awfully large report definition to reach that limit.
Just note again that we are only dealing with standard types here. There is no special handling being done by either the Java code or iBATIS. All of the heavy lifting is being done internally by the Oracle JDBC driver.
That is pretty much it. There is nothing special that needs to be done to store the JasperReports report definitions in the database. Had I known on the first project that it was this easy I certainly would have done it this way It is better by far to centralize the storage of the reports when deploying in a clustered environment. It allows easy deployment and updating, easy backup and recovery, and easy access. How can those be bad things? 
