Creating Dynamic Reports from Databases Using JasperReports 3.5

jieforest · 发表于 2009-9-3 23:27

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:

jieforest · 发表于 2009-9-3 23:27

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

jieforest · 发表于 2009-9-3 23:27

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.

jieforest · 发表于 2009-9-3 23:27

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.