Creating Dynamic Reports from Databases Using JasperReports 3.5

jieforest

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:

jieforest

Although not directly related to database reporting, one more thing worth mentioning is that we used the <pageHeader>
element of the JRXML template to lay out the report labels. If our report had more than one page, these labels would appear at the top of every page.

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:

jieforest

<?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>

jieforest

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:

jieforest

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

jieforest

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:

jieforest

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.

jieforest

<?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>

jieforest

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:

jieforest

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