Intro
Crystal Reports® from Business Objects SA has been a popular business reporting solution for many years. However, the licensing costs and lack of Linux support has led many report developers to look for an alternative open source solution.
In this article we will show you how to write reports using MySQL as the database, JasperReports as the engine, iReport as the designer and a Java servlet to serve reports as PDF documents from Tomcat.
JasperReports
The JasperReports engine is just that: a report engine. Tools from other providers are used to design the report content and layout. This approach gives the report developer flexibility in assembling a reporting toolbox that far exceeds the flexibility offered by traditional one-size-fits-all reporting tools. It is also possible to edit the report definition files by hand for the ultimate in customization.
Use the checklist below to assist you with installing required libraries and components for an end-to-end JasperReports development environment.
Setup Checklist
| Complete | Description | How to get it | What to do |
|---|---|---|---|
| Java 1.4.2 SDK (SDK required as iReport uses tools.jar) | Download J2SE 1.4.2 from java.sun.com. | Install to local hard drive. (The directory created by the installer is called | |
| iReports | Download iReport-0.4.1.zip from Sourceforge. | Extract to C:\Program Files\. (This directory is called | |
| tools.jar | Located on your machine in the | Copy this file to your |
iReports
Starting iReport for the first time
To start iReport for the first time, simply execute the provided startup script. iReport provides two such scripts: startup.bat for Windows and startup.sh for Unix-like OSes, both of which are located in the
iReport home (ireport.home system property):.
User home ( user.home system property): C:\WINNT\Profiles\yourusername\.ireport.
iReport default compile directory: D:\temp.
iReport user home directory (iReport compiles here if there are no settings or the user hasn't selected to compile to the report directory):.
To change the compile directory select "Tools/Options..." from the menu and click the "Compiler" tab. For our example, we chose to compile reports to the same directory as the report definition files.
Preparing the Database
Of course, our report is useless without a source of data with which to populate it. For this, we'll be using a MySQL database accessed via JDBC.
The first step, of course, is creating the database. Since this is just an example app, we'll also insert the data during this step. Click here to download a text file containing the commands used to create and populate the database. Once the DB is up and populated, it's time to set up a way for our report to access it.
Since this webapp is going to be served by Tomcat, we should take advantage of Tomcat's connection management. To do so, we'll define a DataSource as a JNDI resource in the application's context. The document fragment below is the context.xml the sample app uses.
01 <Context path="/jasper_example" docBase="jasper_example" debug="5" reloadable="true"02 crossContext="true">
03 <Resource name="jdbc/jasper_example" auth="Container" type="javax.sql.DataSource" />
04 <ResourceParams name="jdbc/jasper_example">
05 <parameter>
06 <name>factory</name>
07 <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
08 </parameter>
09 <!-- MySQL Server DB username and password for DB connections -->
10 <parameter>
11 <name>username</name>
12 <value>username</value>
13 </parameter>
14 <parameter>
15 <name>password</name>
16 <value>password</value>
17 </parameter>
18 <!-- Class name for the JDBC driver -->
19 <parameter>
20 <name>driverClassName</name>
21 <value>com.mysql.jdbc.Driver</value>
22 </parameter>
23 <!-- The JDBC connection url for connecting to your MySQL Server DB. -->
24 <parameter>
25 <name>url</name>
26 <value>jdbc:mysql://127.0.0.1/jasperarticle</value>
27 </parameter>
28 <!-- maximum active connections -->
29 <parameter>
30 <name>maxActive</name>
31 <value>3</value>
32 </parameter>
33 <!-- maximum idle connections -->
34 <parameter>
35 <name>maxIdle</name>
36 <value>3</value>
37 </parameter>
38 <!-- connections attempts timeout after 10 seconds -->
39 <parameter>
40 <name>maxWait</name>
41 <value>10000</value>
42 </parameter>
43 </ResourceParams>
44 </Context>
Creating a New Report
Create a Database Connection
To create a new data source to use in your report select "Datasource" from the iReport menu bar, then "Connections / Datasources". Click the "New" button and select "Database JDBC connection" from the first dropdown list. If connecting to a MySQL database, select the com.mysql.jdbc.Driver JDBC Driver. After completing the connection parameters, you should have a JDBC URL similar in form to: jdbc:mysql://192.168.1.100/NameOfYourDB.
If when you test your connection you receive an error dialog with the following message, "Communication link failure: java.io.IOException, underlying cause: Unexpected end of input stream", you should update your MySQL JDBC driver. The latest driver can be found at: http://www.mysql.com/products/connector/j/.
The MySQL driver that ships with iReport does not seem compatible with version 4.1 of MySQL.
At the time of this writing, mysql-connector-java-3.1.8a.zip was the latest production-ready archive available for download. Inside this Zip file, you will find a file named mysql-connector-java-3.1.8-bin.jar; copy this file into the iReport/lib directory, e.g., C:\Program Files\iReport-0.4.1\lib\, and delete any older versions, such as mysql-connector-java-3.0.8-stable-bin.jar.
When iReport starts, it will automatically load all .jar files found in the \lib directory. Restart iReports and test your new Datasource connection.
Creating a New Report File
To create a new report select "File / New Document" from the menu bar. Name the report and select your desired paper size. Do not use spaces in the report name. Next, you will need to set the active database connection in order to use values from your database queries. To do this, select "Build / Set active connection" from the menu and choose the connection defined earlier.
Add Fields From Database Table
First, define a query. Select "Datasource / Report query" from the menu bar; this will open the query dialog where we will create a select query to return values to our report. Enter the following: SELECT ID, Name, Address, City, PostalCode FROM Customer. Note: Explicitly select columns, otherwise only non-null columns will appear in the Report Fields selection dialog.
As you type this query, iReport will attempt to execute the query as you type. Note: You may see exception messages while typing, this is normal behavior and is a way to let you know when the query is malformed. When a parsable query has been entered, iReport will display a list of returned columns beneath the query edit window.
To add database fields to the report, select "View / Report Fields". This will open a dialog with a list of fields returned by the SQL query.
Drag from the Field name grid column into the "detail" section of the report canvas. You should now have something like this:
To test the report, select "Build / Execute report (using active conn.)" from the menu bar. The status window in iReport should display something similar to:
Compiling to file... JasperReports\Simple.jasper -> ...\TestReport.java
Compilation running time : 300
Filling report... jrviewerExporting
Viewing with JasperReports Viewer
Running time : 600
Modifying the Report
Filter Results
Next we'll look at filtering the results returned by our SQL query. iReport allows the designer to add parameters to the report that can be used to filter the SQL results. To add a parameter select "View / Report Parameters" and then click New.
For this example, we defined a parameter with the following values:
To add a where condition filter, open the query edit dialog ("Datasource / Report query"). We'll modify the SQL statement to look like: SELECT ID, Name, Address, City, PostalCode FROM Customer WHERE Name Like '$P!{NameFilter}%'. If we had hard coded the WHERE condition, it might look like: WHERE Name Like 'ABC%'. To insert the parameter value we replace ABC with $P!{NameFilter}. When the report runs, $P!{NameFilter} will be replaced with the supplied actual value.
Serving JasperReports From a Web Server Using Tomcat
The next thing we will look at is serving our report from a webserver using the Apache Tomcat webserver / servlet container. Installing and configuring Tomcat is beyond the scope of this article. The official Tomcat site can be found at http://jakarta.apache.org/tomcat/index.html.
Setting up the Webapp
Now that we have a report to serve and a database set up to populate our report with data, we just need a web component to serve up the report. That component will be a servlet, so we'll also need a web.xml file so Tomcat will know which requests should be handled by the servlet.
The document below is the web.xml file for the sample application. It is fairly straightforward:
- it describes the servlet that will be serving the report; we will visit this servlet in more detail in the next section
- it maps that servlet to handle requests coming to the /ReportEngine path; this could be a regular expression, but we have no need of such complications for this application
- it sets sessions to time out after 30 minutes
- it tells the servlet container to use welcome.html as the default page for this webapp's virtual directory
- it references the jdbc/jasper_example JNDI resource we defined in the previous section
02 <!DOCTYPE web-app PUBLIC '-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN' 03 'http://java.sun.com/dtd/web-app_2_3.dtd'>
04 <web-app id="AgreeMgtSys_WebApp">
05 <display-name>jasper_example WAR</display-name>
06 <description>WAR file containing all web pages and servlets.</description>
07 <servlet>
08 <servlet-name>ReportEngine</servlet-name>
09 <display-name>Report Engine</display-name>
10 <description>The servlet that handles requests to display a 11 Jasper report.</description>
12 <servlet-class>com.skillfusion.articles.jasper.ReportEngine</servlet-class>
13 </servlet>
14 <servlet-mapping>
15 <servlet-name>ReportEngine</servlet-name>
16 <url-pattern>/ReportEngine</url-pattern>
17 </servlet-mapping>
18 <session-config>
19 <session-timeout>30</session-timeout>
20 </session-config>
21 <welcome-file-list>
22 <welcome-file>default.html</welcome-file>
23 </welcome-file-list>
24 <resource-ref>
25 <description>DB Connection</description>
26 <res-ref-name>jdbc/jasper_example</res-ref-name>
27 <res-type>javax.sql.DataSource</res-type>
28 <res-auth>Container</res-auth>
29 </resource-ref>
30 </web-app>
Now that we've got the webapp set up properly, let's see how the servlet is actually performing its task.
Serving the Report
Serving JasperReports From a Web Server Using Tomcat
The next thing we will look at is serving our report from a webserver using the Apache Tomcat webserver / servlet container. Installing and configuring Tomcat is beyond the scope of this article. The official Tomcat site can be found at http://jakarta.apache.org/tomcat/index.html.
Once you have Tomcat up and running, it's time to write the servlet: ReportEngine.
001 package com.skillfusion.articles.jasper.example.servlets;
002
003 import java.io.IOException;
004 import java.io.PrintWriter;
005 import java.sql.Connection;
006 import java.sql.SQLException;
007 import java.util.HashMap;
008
009 import javax.naming.Context;
010 import javax.naming.InitialContext;
011 import javax.naming.NamingException;
012 import javax.servlet.ServletException;
013 import javax.servlet.ServletOutputStream;
014 import javax.servlet.http.HttpServlet;
015 import javax.servlet.http.HttpServletRequest;
016 import javax.servlet.http.HttpServletResponse;
017 import javax.sql.DataSource;
018
019 import net.sf.jasperreports.engine.JRException;
020 import net.sf.jasperreports.engine.JasperExportManager;
021 import net.sf.jasperreports.engine.JasperFillManager;
022 import net.sf.jasperreports.engine.JasperPrint;
023
024 /**
025 * The <code>ReportEngine</code> class serves html and pdf reports using
026 * Jasper reports.
027 *
028 * @author SkillFusion
029 */
030 public class ReportEngine extends HttpServlet {
031 /**
032 * Called by the server to allow a servlet to handle a GET request.
033 *
034 * @param request
035 * the request the client has made of the servlet
036 * @param response
037 * the response the servlet sends to the client
038 * @throws ServletException
039 * @throws IOException
040 */
041 public void doGet(HttpServletRequest request, HttpServletResponse response)
042 throws ServletException, IOException {
043
044 // Pass this request off to the handleSubmit method. It's an accepted
045 // best pracice to handle GET and POST submissions the same way unless
046 // there's a good reason not to do so.
047 this.handleSubmit(request, response);
048
049 }
050
051 /**
052 * Called by the server to allow a servlet to handle a POST request.
053 *
054 * @param request
055 * the request the client has made of the servlet
056 * @param response
057 * the response the servlet sends to the client
058 * @throws ServletException
059 * @throws IOException
060 */
061 public void doPost(HttpServletRequest request, HttpServletResponse response)
062 throws ServletException, IOException {
063 // Pass this request off to the handleSubmit method. It's an accepted
064 // best pracice to handle GET and POST submissions the same way unless
065 // there's a good reason not to do so.
066 this.handleSubmit(request, response);
067 }
068
069 /**
070 * Handles form submissions for <code>#doGet</code> and
071 * <code>#doPost</code>.
072 *
073 * @param request
074 * The <code>HttpServletRequest</code> wrapping the HTTP
075 * request that triggered this method.
076 * @param response
077 * The <code>HttpServletResponse</code> that gives this method
078 * access to the HTTP response the servlet container will send
079 * back to the user agent.
080 * @throws IOException
081 * when an error occurs while trying to access the output stream
082 * to notify the user of an error.
083 */
084 protected void handleSubmit(HttpServletRequest request,
085 HttpServletResponse response) throws IOException {
086 // Declare the printwriter (which we'll use if an error occurs), but
087 // don't instantiate it yet because instantiating it will prevent us
088 // from streaming the PDF back to the client if everything else works.
089 PrintWriter out = null;
090
091 Connection conn = null;
092 try {
093 // Get a JDBC Connection
094 Context initContext = new InitialContext();
095 DataSource dataSource = (DataSource) initContext
096 .lookup("java:comp/env/jdbc/jasper_example");
097 conn = dataSource.getConnection();
098
099 // Get report file name from params
100 String rptfilename = request.getParameter("rptfilename");
101
102 // get the name filter
103 String namefilter = request.getParameter("NameFilter");
104
105 // Add filter value to a hashtable of report parameters
106 HashMap params = new HashMap();
107 params.put("NameFilter", namefilter);
108 params.put("testing", "some stuff");
109
110 // fill the report
111 JasperPrint jasperprint = JasperFillManager.fillReport(
112 getServletContext().getRealPath("/") + rptfilename, params,
113 conn);
114
115 // export report to pdf and stream back to browser
116 byte[] pdfasbytes = JasperExportManager
117 .exportReportToPdf(jasperprint);
118
119 ServletOutputStream outstream = response.getOutputStream();
120 response.setContentType("application/pdf");
121 response.setContentLength(pdfasbytes.length);
122
123 response.setHeader("Content-disposition",
124 "inline; filename=\"Report.pdf\"");
125 outstream.write(pdfasbytes);
126
127 } catch (NamingException ne) {
128 // Get the writer from the response so we can output markup
129 out = response.getWriter();
130
131 // There was a problem, either in retrieving the initial context, or
132 // looking for a specific JNDI resource inside that context. Either
133 // way, let the user know.
134 out.println("<html>");
135 out.println("\t<body>");
136 out.println("\t\t<br /><br />");
137 out.println("\t\tDue to a naming problem with this servlet's"
138 + "initial context, the system is unable to display"
139 + "the report at this time.");
140 out.println("\t\t<br /><br />");
141 out.println("\t\tError Message ==> " + ne.getLocalizedMessage());
142 out.println("\t\t<br />");
143 out.println("\t\tCause of Error ==> " + ne.getCause());
144 out.println("\t</body>");
145 out.println("</html>");
146 } catch (SQLException sqle) {
147 // Get the writer from the response so we can output markup
148 out = response.getWriter();
149
150 // Start the html, but don't fill in the juicy bits yet
151 out.println("<html>");
152 out.println("\t<body>");
153
154 // SQLExceptions can be chained. We have at least one exception, so
155 // set up a loop to make sure we let the user know about all of them
156 // if there happens to be more than one.
157 SQLException tempException = sqle;
158 while (null != tempException) {
159 // work
160 // Write out the useful info on this exception to the response
161 out.println("\t\t<br /><br />");
162 out.println("\t\tThe following database error occurred:");
163 out.println("\t\t<br /><br />");
164 out.println("\t\tError Message ==> "
165 + sqle.getLocalizedMessage());
166 out.println("\t\t<br />");
167 out.println("\t\tCause of Error ==> " + sqle.getCause());
168 out.println("\t\t<br />");
169 out.println("\t\tSQL State ==> " + sqle.getSQLState());
170 out.println("\t\t<br />");
171 out.println("\t\tVendor Error Code ==> " + sqle.getErrorCode());
172
173 // loop to the next exception
174 tempException = tempException.getNextException();
175 }
176
177 // Write out the closing tags to the response
178 out.println("\t</body>");
179 out.println("</html>");
180 } catch (JRException jre) {
181 // Get the writer from the response so we can output markup
182 out = response.getWriter();
183
184 // Jasper had an internal error when filling the report. Give the
185 // user the lowdown.
186 out.println("<html>");
187 out.println("\t<body>");
188 out.println("\t\t<br /><br />");
189 out.println("\t\tJasper encountered a problem when attempting"
190 + "to populate the report.");
191 out.println("\t\t<br /><br />");
192 out.println("\t\tError Message ==> " + jre.getLocalizedMessage());
193 out.println("\t\t<br />");
194 out.println("\t\tCause of Error ==> " + jre.getCause());
195 out.println("\t</body>");
196 out.println("</html>");
197 } catch (IOException ioe) {
198 // Get the writer from the response so we can output markup
199 out = response.getWriter();
200
201 // This was an IO error, so it quite possibly resulted from an error
202 // creating the print writer, but we're still gonna give it the old
203 // college try and attempt to send back useful info to the user
204 out.println("<html>");
205 out.println("\t<body>");
206 out.println("\t\t<br /><br />");
207 out.println("\t\tDue to a naming problem with this servlet's"
208 + "initial context, the system is unable to display"
209 + "the report at this time.");
210 out.println("\t\t<br /><br />");
211 out.println("\t\tError Message ==> " + ioe.getLocalizedMessage());
212 out.println("\t\t<br />");
213 out.println("\t\tCause of Error ==> " + ioe.getCause());
214 out.println("\t</body>");
215 out.println("</html>");
216 } finally {
217 try {
218 // We're done here, so clean up the connection
219 conn.close();
220 } catch (SQLException sqle) {
221 // We don't actually care, we were just trying to clean up an
222 // expensive DB connection
223 }
224 }
225 }
226 }
Best practices tell us that when the doGet and doPost methods exhibit the same behavior, they should delegate the implementation of that behavior to a helper method. In keeping with that ideal, all the actual work in this servlet is done in the handleSubmit method, which is called by both doGet and doPost.
Inside handleSubmit, we establish a JDBC database connection by looking up a JNDI resource named jdbc/jasper_example, which defines the DataSource for our JDBC connection. Once the connection has been established, handleSubmit begins pulling required information out of the HttpRequest's parameters. Once all the necessary info has been extracted from the HttpRequest and pushed into a Map of parameters, handleSubmit makes a call to JasperFillManager.fillReport. Next, we pass the results of that call to JasperExportManager.exportReportToPdf to convert the report to PDF format. Once that's done, it's a simple matter to write the resulting document back to the HttpResponse as an array of bytes.
To try it out, you can just invoke the deploy target of the Ant buildfile shipped with the sample application. It uses standard Ant targets, along with a few targets provided by Tomcat, to compile the whole webapp into a standard WAR file and deploy it into the servlet container. After that, you should be able to serve up PDF reports using an open-source solution based on Jasper Reports and iReport.
Conclusion
0 comments:
Post a Comment