In this article by David Heffelfinger, we will explore how to create a report from the data obtained from a database using JasperReports 3.5. In this article, we will cover the following topics:

• How to embed SQL queries into a report template
• How to pass rows returned by an SQL query to a report through a datasource
• How to use report fields to display data obtained from a database
• How to display database data in a report by using the element of the JRXML template

Datasource definition
A datasource is what JasperReports uses to obtain data for generating a report. Data can be obtained from databases, XML files, arrays of objects, collections of objects, and XML files.

In this article, we will focus on using databases as a datasource.

Database for our reports

We will use a MySQL database to obtain data for our reports. The database is a subset of public domain data that can be downloaded from http://dl.flightstats.us. The original download is 1.3 GB, so we deleted most of the tables and a lot of data to trim the download size considerably. MySQL dump of the modified database can be found as part of code download at http://www.packtpub.com/files/code/8082_Code.zip.

The flightstats database contains the following tables:

• aircraft
• aircraft_models
• aircraft_types
• aircraft_engines
• aircraft_engine_types

The database structure can be seen in the following diagram:

The flightstats database uses the default MyISAM storage engine for the MySQL RDBMS, which does not support referential integrity (foreign keys). That is why we don't see any arrows in the diagram indicating dependencies between the tables.

Let's create a report that will show the most powerful aircraft in the database. Let's say, those with horsepower of 1000 or above. The report will show the aircraft tail number and serial number, the aircraft model, and the aircraft's engine model. The following query will give us the required results:

SELECT a.tail_num, a.aircraft_serial, am.model as aircraft_model,
ae.model AS engine_model
FROM aircraft a, aircraft_models am, aircraft_engines ae
WHERE a.aircraft_engine_code in (select aircraft_engine_code
from aircraft_engines
where horsepower >= 1000)
AND am.aircraft_model_code = a.aircraft_model_code
AND ae.aircraft_engine_code = a.aircraft_engine_code

The above query retrieves the following data from the database:

Generating database reports

There are two ways to generate database reports—either by embedding SQL queries into the JRXML report template or by passing data from the database to the compiled report through a datasource. We will discuss both of these techniques.

We will first create the report by embedding the query into the JRXML template. Then, we will generate the same report by passing it through a datasource containing the database data.

Embedding SQL queries into a report template

JasperReports allows us to embed database queries into a report template. This can be achieved by using the <queryString> element of the JRXML file. The following example demonstrates this technique:

<?xml version="1.0" encoding="UTF-8" ?>
<jasperReport
 xmlns="http://jasperreports.sourceforge.net/jasperreports"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports 
 http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"
 name="DbReport">
 <queryString>
 <![CDATA[select a.tail_num, a.aircraft_serial,
 am.model as aircraft_model,
 ae.model as engine_model 
 from aircraft a, aircraft_models am, aircraft_engines ae
 where a.aircraft_engine_code in (
 select aircraft_engine_code
 from aircraft_engines
 where horsepower >= 1000)
 and am.aircraft_model_code = a.aircraft_model_code
 and ae.aircraft_engine_code = a.aircraft_engine_code]]>
 </queryString>
 <field name="tail_num" class="java.lang.String" />
 <field name="aircraft_serial" class="java.lang.String" />
 <field name="aircraft_model" class="java.lang.String" />
 <field name="engine_model" class="java.lang.String" />
 <pageHeader>
 <band height="30">
 <staticText>
 <reportElement x="0" y="0" width="69" height="24" />
 <textElement verticalAlignment="Bottom" />
 <text>
 <![CDATA[Tail Number: ]]>
 </text>
 </staticText>
 <staticText>
 <reportElement x="140" y="0" width="79" height="24" />
 <text>
 <![CDATA[Serial Number: ]]>
 </text>
 </staticText>
 <staticText>
 <reportElement x="280" y="0" width="69" height="24" />
 <text>
 <![CDATA[Model: ]]>
 </text>
 </staticText>
 <staticText>
 <reportElement x="420" y="0" width="69" height="24" />
 <text>
 <![CDATA[Engine: ]]>
 </text>
 </staticText>
 </band>
 </pageHeader>
 <detail>
 <band height="30">
 <textField>
 <reportElement x="0" y="0" width="69" height="24" />
 <textFieldExpression class="java.lang.String">
 <![CDATA[$F{tail_num}]]>
 </textFieldExpression>
 </textField>
 <textField>
 <reportElement x="140" y="0" width="69" height="24" />
 <textFieldExpression class="java.lang.String">
 <![CDATA[$F{aircraft_serial}]]>
 </textFieldExpression>
 </textField>
 <textField>
 <reportElement x="280" y="0" width="69" height="24" />
 <textFieldExpression class="java.lang.String">
 <![CDATA[$F{aircraft_model}]]>
 </textFieldExpression>
 </textField>
 <textField>
 <reportElement x="420" y="0" width="69" height="24" />
 <textFieldExpression class="java.lang.String">
 <![CDATA[$F{engine_model}]]>
 </textFieldExpression>
 </textField>
 </band>
 </detail>
</jasperReport>

The <queryString> element is used to embed a database query into the report template. In the given code example, the <queryString> element contains the query wrapped in a CDATA block for execution. The <queryString> element has no attributes or subelements other than the CDATA block containing the query.

Text wrapped inside an XML CDATA block is ignored by the XML parser. As seen in the given example, our query contains the > character, which would invalidate the XML block if it wasn't inside a CDATA block. A CDATA block is optional if the data inside it does not break the XML structure. However, for consistency and maintainability, we chose to use it wherever it is allowed in the example.

The <field> element defines fields that are populated at runtime when the report is filled. Field names must match the column names or alias of the corresponding columns in the SQL query. The class attribute of the <field> element is optional; its default value is java.lang.String. Even though all of our fields are strings, we still added the class attribute for clarity. In the last example, the syntax to obtain the value of a report field is $F{field_name}, where field_name is the name of the field as defined.

The next element that we'll discuss is the <textField> element. Text fields are used to display dynamic textual data in reports. In this case, we are using them to display the value of the fields. Like all the subelements of <band>, text fields must contain a <reportElement> subelement indicating the text field's height, width, and x, y coordinates within the band. The data that is displayed in text fields is defined by the <textFieldExpression> subelement of <textField>. The <textFieldExpresson> element has a single subelement, which is the report expression that will be displayed by the text field and wrapped in an XML CDATA block. In this example, each text field is displaying the value of a field. Therefore, the expression inside the <textFieldExpression> element uses the field syntax $F{field_name}, as explained before.

Compiling a report containing a query is no different from compiling a report without a query. It can be done programmatically or by using the custom JasperReports jrc ANT task.

Generating the report

As we have mentioned previously, in JasperReports terminology, the action of generating a report from a binary report template is called filling the report. To fill a report containing an embedded database query, we must pass a database connection object to the report. The following example illustrates this process:

package net.ensode.jasperbook;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperFillManager;

public class DbReportFill
{
 Connection connection;

 public void generateReport()
 {
 try
 {
 Class.forName("com.mysql.jdbc.Driver");

 connection = DriverManager.getConnection("jdbc:mysql:
 //localhost:3306/flightstats?user=user&password=secret");

 System.out.println("Filling report...");
 JasperFillManager.fillReportToFile("reports/DbReport.
  jasper", new HashMap(), connection);
 System.out.println("Done!");

 connection.close();
 }
 catch (JRException e)
 {
 e.printStackTrace();
 }
 catch (ClassNotFoundException e)
 {
 e.printStackTrace();
 }
 catch (SQLException e)
 {
 e.printStackTrace();
 }
 }

 public static void main(String[] args)
 {
 new DbReportFill().generateReport();
 }
}

As seen in this example, a database connection is passed to the report in the form of a java.sql.Connection object as the last parameter of the static JasperFillManager.fillReportToFile() method. The first two parameters are as follows: a string (used to indicate the location of the binary report template or jasper file) and an instance of a class implementing the java.util.Map interface (used for passing additional parameters to the report). As we don't need to pass any additional parameters for this report, we used an empty HashMap.

There are six overloaded versions of the JasperFillManager.fillReportToFile() method, three of which take a connection object as a parameter.

For simplicity, our examples open and close database connections every time they are executed. It is usually a better idea to use a connection pool, as connection pools increase the performance considerably. Most Java EE application servers come with connection pooling functionality, and the commons-dbcp component of Apache Commons includes utility classes for adding connection pooling capabilities to the applications that do not make use of an application server.

After executing the above example, a new report, or JRPRINT file is saved to disk. We can view it by using the JasperViewer utility included with JasperReports.

In this example, we created the report and immediately saved it to disk. The JasperFillManager class also contains methods to send a report to an output stream or to store it in memory in the form of a JasperPrint object. Storing the compiled report in a JasperPrint object allows us to manipulate the report in our code further. We could, for example, export it to PDF or another format.

The method used to store a report into a JasperPrint object is JasperFillManager.fillReport(). The method used for sending the report to an output stream is JasperFillManager.fillReportToStream(). These two methods accept the same parameters as JasperFillManager.fillReportToFile() and are trivial to use once we are familiar with this method. Refer to the JasperReports API for details.

In the next example, we will fill our report and immediately export it to PDF by taking advantage of the net.sf.jasperreports.engine.JasperRunManager.runReportToPdfStream() method.

package net.ensode.jasperbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.HashMap;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.jasperreports.engine.JasperRunManager;

public class DbReportServlet extends HttpServlet
{
 protected void doGet(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException
 {
 Connection connection;
 response.setContentType("application/pdf");
 ServletOutputStream servletOutputStream = response
 .getOutputStream();
 InputStream reportStream = getServletConfig()
 .getServletContext().getResourceAsStream(
 "/reports/DbReport.jasper");
 try
 {
 Class.forName("com.mysql.jdbc.Driver");
 connection = DriverManager.getConnection("jdbc:mysql:
 //localhost:3306/flightstats?user=dbUser&password=secret");
 JasperRunManager.runReportToPdfStream(reportStream,
 servletOutputStream, new HashMap(), connection);

 connection.close();

 
 servletOutputStream.flush();
 servletOutputStream.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());
 }
 }
}

The only difference between static and dynamic reports is that for dynamic reports we pass a connection to the report for generating a database report. After deploying this servlet and pointing the browser to its URL, we should see a screen similar to the following screenshot: