Creating Dynamic Reports from Databases Using JasperReports 3.5

Exclusive offer: get 80% off this eBook here
JasperReports for Java Developers

JasperReports for Java Developers — Save 80%

Create, Design, Format and Export Reports with the world's most popular Java reporting library

₨587.06    ₨117.41
by David R. Heffelfinger | August 2009 | Java Open Source

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:

JasperReports for Java Developers

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:

JasperReports for Java Developers

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:

 

JasperReports for Java Developers Create, Design, Format and Export Reports with the world's most popular Java reporting library
Published: August 2006
eBook Price: ₨587.06
Book Price: ₨1,200.00
See more
Select your format and quantity:

Modifying a report query through report parameters

Although embedding a database query into a report template is the simplest way to generate a database report, this approach is not very flexible. As in order to modify the report query, it is also necessary to modify the report's JRXML template.

The example JRXML template that we discussed in the previous section generates a report that displays all the aircraft in the database with a horsepower equal to or greater than 1000. If we wanted to generate a report to display all the aircraft with a horsepower greater than or equal to 750, then we would have to modify the JRXML and recompile it. Too much of work for such a small change! Fortunately, JasperReports allows us to modify an embedded database query easily by using the report parameters. The following JRXML template is a new version of the one we saw in the previous section but modified to take advantage of report parameters:

<?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="DbReportParam">
<parameter name="hp" class="java.lang.Integer" />
<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 >= $P{hp})
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 only difference between this JRXML template and the previous one is that we declared a report parameter in the following line:

<parameter name="hp" class="java.lang.Integer" />

We then used the declared parameter to retrieve the horsepower dynamically in the where clause of the report query. As can be seen in this example, the value of a report parameter can be retrieved by using the syntax $P{paramName}, where paramName is the parameter name as defined in its declaration (hp in this example).

Passing a parameter to a report from Java code is very simple. In most of the examples we have seen so far, we have been passing an empty HashMap to report templates when we fill them. The purpose of that HashMap is to pass parameters to the report template. The following servlet is a new version of the one we saw in the previous section but modified to send a report parameter to the report template:

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 DbReportParamServlet 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/
DbReportParam.jasper");
HashMap parameterMap = new HashMap();
parameterMap.put("hp", new Integer(750));
try
{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306
/flightstats?user=dbuser&password=secret");
JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream,
parameterMap, 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 this servlet and the one in the previous section is that here we declare a HashMap and populate it with the report parameters. Notice how the HashMap key must match the report parameter name.

After deploying the servlet and directing the browser to its URL, we should see a report as seen in the following screenshot:

JasperReports for Java Developers

Dynamically modifying the report queries is only one of many possible uses of report parameters.

Database reporting through a datasource

Another way we can generate reports based on database data is by using a datasource. In JasperReports terminology, a datasource is a class implementing the net.sf.jasperreports.engine.JRDataSource interface.

To use a database as a datasource, the JasperReports API provides the net.sf.jasperreports.engine.JRResultSetDataSource class. This class implements the JRDataSource interface. It has a single public constructor that takes a java.sql.ResultSet as its only parameter. The JRResultSetDataSource class provides no public methods or variables. To use it, all we need to do is provide a result set to its constructor and pass it to the report through the JasperFillManager class.

Let's modify the last JRXML template so that it uses a JRResultSetDataSource class to obtain database data.

The only change we need to make in the JRXML template is to remove the <queryString> element.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN"
"http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport name="DbReportDS">
<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="69" 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 procedure for compiling a database report by using JRResultSetDataSource is no different from what we have already seen. To fill the report, we need to execute a database query in our Java code and pass the query results to the report in a datasource, as seen in the following example:

package net.ensode.jasperbook;

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

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

public class DbReportDSFill
{
Connection connection;
Statement statement;
ResultSet resultSet;

public void generateReport()
{
try
{
String query = "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";
Class.forName("com.mysql.jdbc.Driver");

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

JRResultSetDataSource resultSetDataSource = new
JRResultSetDataSource(resultSet);

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

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

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

As seen in this example, to provide a report with database data by using JRResultSetDataSource, we must execute the database query from the Java code and wrap the resulting resultSet object into an instance of JRResultSetDataSource by passing it to its constructor. The instance of JRResultSetDataSource must then be passed to the JasperFillManager.fillReportToFile() method. Strictly speaking, any method that takes an instance of a class implementing JRDataSource can be called. In this example, we wished to save the report to a file, so we chose to use fillReportToFile(). This method fills the report with data from the datasource and saves it to a file in the filesystem. It has the potential of throwing a JRException if there is something wrong. Consequently, this exception must either be caught or declared in the throws clause.

After executing this code, a report identical to the first one we saw in the previous section is generated. The following example demonstrates how a web-based report can be created by using a database datasource:

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.sql.ResultSet;
import java.sql.Statement;
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.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperRunManager;

public class DbDSReportServlet extends HttpServlet
{
protected void doGet(HttpServletRequest request, HttpServletResponse
response)
throws ServletException, IOException
{
Connection connection;
Statement statement;
ResultSet resultSet;

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

try
{
String query = "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";

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection("jdbc:mysql:
//localhost:3306/flightstats?user=dbuser&password=secret");
statement = connection.createStatement();
resultSet = statement.executeQuery(query);

JRResultSetDataSource resultSetDataSource = new
JRResultSetDataSource(resultSet);

JasperRunManager.runReportToPdfStream(reportStream,
servletOutputStream, new HashMap(), resultSetDataSource);

resultSet.close();
statement.close();
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());
}
}
}

This code is very similar to the previous examples. It executes an SQL query through JDBC and wraps the resulting resultSet in an instance of JRResultSetDataSource. This instance of JRResultSetDataSource is then passed to the JasperRunManager.runReportToPdfStream() method to export the report to PDF format and stream it to the browser window.

All the examples in this article use simple SQL select queries to obtain report data. It is also possible to obtain report data from the database by calling stored procedures or functions (if supported by the RDBMS and JDBC driver we are using).

A comparison of database report methods

Although embedding a database query into a report template is the simpler way that JasperReports allows us to create database reports, it is also the least flexible one. Using a JRResultSetDataSource involves writing some more code but results in more flexible reports, as the same report template can be used for different datasources.

Which method to use depends on our needs. If we are sure that we will always be using a database as a datasource for our report, and the database query is unlikely to change much, then embedding the database query into the JRXML template at design time is the most straightforward solution. If the query is likely to change, or if we need to use datasources other than a database for our reports, then using a datasource provides the most flexibility.

Some report design tools will only generate database reports by embedding a database query into the report template. If we are using one of these tools, then we have little choice but to use this method. We are free to remove the <queryString> element from the JRXML after we are done designing the report and passing the JRResultSetDataSource at runtime. However, if we do this, we lose the ability to modify the report template from the report designer.

Summary

In this article, we learned the different ways we can create database reports and how to use the <queryString> JRXML element to embed SQL queries in a report template.

Additionally, we saw how to populate an instance of JRResultSetDataSource with data from a result set and use it to fill a report. We also covered how to declare report fields to access data from individual columns in the result set of the query used to fill the report. Finally, we learned how to generate reports that are displayed both in the user's web browser and in PDF format.

JasperReports for Java Developers Create, Design, Format and Export Reports with the world's most popular Java reporting library
Published: August 2006
eBook Price: ₨587.06
Book Price: ₨1,200.00
See more
Select your format and quantity:

About the Author :


David R. Heffelfinger

David Heffelfinger is the Chief Technology Officer of Ensode Technology, LLC, a software consulting firm based in the greater Washington DC area. He has been architecting, designing and developing software professionally since 1995 and has been using Java as his primary programming language since 1996. He has worked on many large scale projects for several clients including the US Department of Homeland Security, Freddie Mac, Fannie Mae, and the US Department of Defense. He has a Masters degree in Software Engineering from Southern Methodist University. David is editor in chief of Ensode.net (http://www.ensode.net), a website about Java, Linux, and other technology topics.

Books From Packt

  WebSphere Application Server 7.0 Administration Guide
WebSphere Application Server 7.0 Administration Guide

WordPress MU 2.7: Beginner's Guide
WordPress MU 2.7: Beginner's Guide

Spring 2.5 Aspect Oriented Programming
Spring 2.5 Aspect Oriented Programming

Pentaho Reporting 3.5 for Java Developers
Pentaho Reporting 3.5 for Java Developers

Grails 1.1 Web Application Development
Grails 1.1 Web Application Development

Drools JBoss Rules 5.0 Developer's Guide
Drools JBoss Rules 5.0 Developer's Guide

jQuery UI 1.6: The User Interface Library for jQuery
jQuery UI 1.6: The User Interface Library for jQuery

LWUIT 1.1 for Java ME Developers
LWUIT 1.1 for Java ME Developers

 

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software