Working with HSQLDB, JasperReports and iReport

In the previous post we added support for selection and pagination to Serendipity. In this post, we're going to add support for reports to Serendipity by taking advantage of the JasperReports reporting engine and the visual report designer, iReport.

In this post, we'll:

  1. Install JasperReports
  2. Install iReport
  3. Use iReport to create a report
  4. Add support for reports to Serendipity
  5. Test the application in development mode

Note: You can download the sample application from the crmdipity (Se-r-en-dipity) project's download page.

1. Install JasperReports

Download and then unzip JasperReports. I downloaded:

  • jasperreports-4.0.0-project.zip - Release 4.0.0 of JasperReports

 

And add the following libraries to the project (e.g. copy them into the project's war/WEB-INF/lib directory):

  • jasperreports-4.0.0.jar (from the dist directory)
  • commons-digester-1.7.jar (from the lib directory)
  • iText-2.1.7.jar (from the lib directory)

Add the libraries to your build path by right-clicking on each of the jar files and selecting "Build Path -> Add to Build Path".

2. Install iReport

Download and then install iReport. I downloaded:

  • iReport-4.0.0-windows-installer-1.2.16.exe - Release 4.0.0 of iReport

 

And add the following library to iReport (e.g. copy it from Seredipity's war/WEB-INF/lib directory into iReport's libs directory):

  • hsqldb.jar

Add the driver to iReport by right-clicking on 'Drivers' in the Services pane and selecting "New Driver...":

 

Note: Don't forget to delete the old hsqldb.jar in iReport's modules/ext directory.

3. Use iReport to create a report

iReport is a visual report designer that you can use to create JasperReports. The report creation process has three main steps:

  • Creating a database (or data source) connection
  • Designing the report by specifying the layout of its elements
  • Running the report

To create a new connection click the Report Datasources button in iReport's toolbar, then click New and choose "Database JDBC connection" and enter the following settings:

 

Make sure you test the connection:

 

Then click the Save button and the new connection will be added to iReport's connections list:

 

Now that we have a connection to Serendipity's HSQL database we can create a new report. However, as JasperForge has several step-by-step tutorials that demonstrate the visual report design process we'll skip this step and instead we'll take a look at a sample report and a report template.

The Accounts Report in the iReport Designer pane:

 

The Accounts Report (AccountsReport.jrxml):

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
name="AccountsReport" pageWidth="842" pageHeight="595" 
orientation="Landscape" columnWidth="802" leftMargin="20" 
rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<template>
		<![CDATA["C:\\tools\\IDEs\\GWT\\workspace\\Serendipity\\reports\\Serendipity.jrtx"]]>
	</template>
	<queryString>
		<![CDATA[select a.ACCOUNT_NAME, a.MAIN_PHONE, a.PRIMARY_CONTACT, a.EMAIL_PRIMARY_CONTACT,
		b.ADDRESS_LINE_1, b.ADDRESS_LINE_2, b.ADDRESS_LINE_3, b.CITY, b.STATE, b.POSTAL_CODE
		from ACCOUNT a INNER JOIN ACCOUNT_ADDRESS aa
		on a.ACCOUNT_ID = aa.ACCOUNT_ID JOIN ADDRESS b
		on aa.ADDRESS_ID = b.ADDRESS_ID]]>
	</queryString>
	<field name="ACCOUNT_NAME" class="java.lang.String"/>
	<field name="MAIN_PHONE" class="java.lang.String"/>
	<field name="PRIMARY_CONTACT" class="java.lang.String"/>
	<field name="EMAIL_PRIMARY_CONTACT" class="java.lang.String"/>
	<field name="ADDRESS_LINE_1" class="java.lang.String"/>
	<field name="ADDRESS_LINE_2" class="java.lang.String"/>
	<field name="ADDRESS_LINE_3" class="java.lang.String"/>
	<field name="CITY" class="java.lang.String"/>
	<field name="STATE" class="java.lang.String"/>
	<field name="POSTAL_CODE" class="java.lang.String"/>
	<variable name="LOCATION" class="java.lang.String">
		<variableExpression>
			<![CDATA[$F{ADDRESS_LINE_1} + ", " + $F{ADDRESS_LINE_2} + " " + $F{CITY} + 
			" " + $F{STATE} + " " + $F{POSTAL_CODE}]]>
		</variableExpression>
	</variable>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="72" splitType="Stretch">
			<image>
				<reportElement x="10" y="20" width="32" height="32"/>
				<imageExpression class="java.lang.String">
					<![CDATA["C:\\tools\\IDEs\\GWT\\workspace\\Serendipity\\war\\images\\logo.png"]]>
				</imageExpression>
			</image>
			<staticText>
				<reportElement style="title" x="230" y="10" width="342" height="32"/>
				<textElement textAlignment="Center">
					<font fontName="Tahoma" size="18" isBold="true"/>
				</textElement>
				<text><![CDATA[Accounts Report]]></text>
			</staticText>
			<line>
				<reportElement x="0" y="69" width="802" height="2" forecolor="#3399FF"/>
			</line>
			<staticText>
				<reportElement style="logoLabel" x="42" y="35" width="87" height="17"/>
				<textElement>
					<font fontName="Tahoma" isBold="true" isItalic="false"/>
				</textElement>
				<text><![CDATA[Serendipity]]></text>
			</staticText>
		</band>
	</title>
	<pageHeader>
		<band splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band height="34" splitType="Stretch">
			<staticText>
				<reportElement style="columnHeader" x="0" y="4" width="168" height="19"/>
				<textElement>
					<font fontName="Tahoma" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Account Name]]></text>
			</staticText>
			<staticText>
				<reportElement style="columnHeader" x="259" y="4" width="249" height="20"/>
				<textElement>
					<font fontName="Tahoma" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Location]]></text>
			</staticText>
			<staticText>
				<reportElement style="columnHeader" x="171" y="4" width="88" height="20"/>
				<textElement>
					<font fontName="Tahoma" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Main Phone]]></text>
			</staticText>
			<staticText>
				<reportElement style="columnHeader" x="508" y="4" width="120" height="20"/>
				<textElement>
					<font fontName="Tahoma" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Primary Contact]]></text>
			</staticText>
			<staticText>
				<reportElement style="columnHeader" x="634" y="4" width="163" height="20"/>
				<textElement>
					<font fontName="Tahoma" size="12" isBold="true"/>
				</textElement>
				<text><![CDATA[Email (Primary Contact)]]></text>
			</staticText>
			<line>
				<reportElement x="0" y="25" width="802" height="2" forecolor="#3399FF"/>
			</line>
		</band>
	</columnHeader>
	<detail>
		<band height="20" splitType="Stretch">
			<textField>
				<reportElement style="detail" x="0" y="0" width="168" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String">
					<![CDATA[$F{ACCOUNT_NAME}]]>
				</textFieldExpression>
			</textField>
			<textField>
				<reportElement style="detail" x="171" y="0" width="88" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String">
					<![CDATA[$F{MAIN_PHONE}]]>
				</textFieldExpression>
			</textField>
			<textField>
				<reportElement style="detail" x="508" y="0" width="120" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String">
					<![CDATA[$F{PRIMARY_CONTACT}]]>
				</textFieldExpression>
			</textField>
			<textField>
				<reportElement style="detail" x="634" y="0" width="163" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String">
					<![CDATA[$F{EMAIL_PRIMARY_CONTACT}]]>
				</textFieldExpression>
			</textField>
			<textField>
				<reportElement style="detail" x="259" y="0" width="249" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String">
					<![CDATA[$V{LOCATION}]]>
				</textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band height="40" splitType="Stretch">
			<image>
				<reportElement x="10" y="10" width="105" height="26"/>
				<imageExpression class="java.lang.String">
					<![CDATA["C:\\tools\\IDEs\\GWT\\workspace\\Serendipity\\war\\images\\jasperreports.png"]]>
				</imageExpression>
			</image>
			<line>
				<reportElement x="0" y="2" width="802" height="2" forecolor="#3399FF"/>
			</line>
			<textField>
				<reportElement style="footer" x="693" y="10" width="80" height="20"/>
				<textElement textAlignment="Right"/>
				<textFieldExpression class="java.lang.String">
					<![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]>
				</textFieldExpression>
			</textField>
			<textField evaluationTime="Report">
				<reportElement style="footer" x="773" y="10" width="40" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String">
					<![CDATA[" " + $V{PAGE_NUMBER}]]>
				</textFieldExpression>
			</textField>
			<textField pattern="dd MMMMM yyyy">
				<reportElement style="footer" x="344" y="10" width="100" height="20"/>
				<textElement textAlignment="Center"/>
				<textFieldExpression class="java.util.Date">
					<![CDATA[new java.util.Date()]]>
				</textFieldExpression>
			</textField>
		</band>
	</pageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
</jasperReport>

The Serendipity Report Template (Serendipity.jrtx):

<?xml version="1.0"?>
<!DOCTYPE jasperTemplate
  PUBLIC "-//JasperReports//DTD Template//EN"
  "http://jasperreports.sourceforge.net/dtds/jaspertemplate.dtd">

<jasperTemplate>

    <style name="normal" isDefault="true" fontName="Tahoma"
		isBold="false" fontSize="10"/>
    <style name="logoLabel" style="normal" isBold="true" fontSize="8"/>
    <style name="title" style="normal" isBold="true" fontSize="18"/>
    <style name="columnHeader" style="normal" isBold="true" fontSize="12"/>
    <style name="detail" style="normal" isBold="false"/>
    <style name="footer" style="normal" isBold="false" fontSize="9"/>

</jasperTemplate>

You can preview the report by switching to Preview mode in the iReport Designer pane. The report will be compiled in the background and then filled with data retrieved by executing the embedded SQL query using the JDBC connection.

4. Add support for reporting to Serendipity

JasperReports includes many samples that demonstrate how to integrate JasperReports with an application. For example, the webapp sample application:

 

We'll follow the webapp example and use a servlet to generate a report and then stream the results to the browser.

The ReportServlet:

...

@Singleton
@SuppressWarnings("serial")
public class ReportServlet extends HttpServlet {

  // as per ReportsRecord
  private static final String REPORT_FILENAME = "reportFilename";
  private static final String DEFAULT_REPORTS_SERVICE_PATH = "/reports/";

  @Override
  public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    this.processRequest(request, response);
  }

  @Override
  public void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    this.processRequest(request, response);
  }

  private void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    Log.info("Report Servlet");

    String reportFilename = request.getParameter(REPORT_FILENAME);
    String resourceName = DEFAULT_REPORTS_SERVICE_PATH + reportFilename;

    response.setContentType("application/pdf");
    ServletOutputStream servletOutputStream = response.getOutputStream();
    InputStream reportStream = getServletConfig().getServletContext().getResourceAsStream(resourceName);

    try {
      Connection connection = DriverManager.getConnection("jdbc:hsqldb:file:/db/serendipitydb",
          "sa", "");

      JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream,
          new HashMap<Object, Object>(), connection);

      connection.close();
    }
    catch (Exception e) {
      // display stack trace in the browser
      StringWriter stringWriter = new StringWriter();
      PrintWriter printWriter = new PrintWriter(stringWriter);
      e.printStackTrace(printWriter);
      response.setContentType("text/plain");
      response.getOutputStream().print(stringWriter.toString());
    }
    finally {
      servletOutputStream.flush();
      servletOutputStream.close();
    }
  }
}

Take a look at the processRequest method and you'll notice that there are only a few steps we need to complete in order to generate a report and then stream it to the browser. First, we need to create an input stream (for the compiled JasperReports report file e.g. AcountsReport.jasper) and a servlet output stream. We then need to get a connection to our data source and make a call to the runReportToPdfStream method and pass it the input stream, the output stream, any report parameters and the data source connection.

4. Test the application in development mode

At this point, you should be able to compile Serendipity and launch it from within Eclipse.

You can use the updated AccountTestCase to populate the 'Report' table and don't forget to update the "hibernate.hbm2ddl.auto" property (e.g. run the JUnit test with value="create" then comment it out) in the persistence.xml file.

The Main page (and the nested Reports presenter and view):

 

The Accounts Report:

 

Note: You can download the sample application from the CRMdipity (Se-r-en-dipity) project's download page.

What's Next

At this point, we've learnt how to add support for reports to Serendipity by taking advantage of the JasperReports reporting engine and the visual report designer, iReport. Next we'll look at how to import data into Serendipity using Apache fileUpload and opencsv.

Taking advantage of Apache FileUpload and opencsv

Comments

Reporting Issue

Hi,

I've been experiencing some reporting issues since I can't generate any report. "C:\tools\IDEs\GWT\workspace\Serendipity\reports\Serendipity.jrtx" is unfound (I want to put it in another folder).

Where should I generate the"*.jrxml files" and what's with the EJBQL folder?

Thanks in advance. Great job

Re: Reporting Issue

Hi,

Serendipity.jrtx is the Serendipity Report Template. It is referenced by the sample Accounts Report (AccountsReport.jrxml):

<template>
  <![CDATA["C:\\tools\\IDEs\\GWT\\workspace\\Serendipity\\reports\\Serendipity.jrtx"]]>
</template>

If you want to place it somewhere else then you just need to update the 'template' element in the .jrxml.

Compiled reports (e.g. .jasper files) should be copied into the project's war/reports directory):

Cheers
Rob

Sample database

Hi,

I have downloaded your application, how do I create a sample database?

Is any sample database to test this application is available?

Thanks
JP

Re: Sample database

Hi,

Set the "hibernate.hbm2ddl.auto" property in the persistence.xml file (in the src/META-INF directory) to "create" and then run the AccountTestCase and it will create the sample database and populate the 'User', 'Account' and 'Report' tables.

Comment out the "hibernate.hbm2ddl.auto" property in the persistence.xml file and then compile Serendipity and launch it from within Eclipse.

Cheers
Rob

How to create database

Thanks Rob, it works for me.

JP