Jasper is a powerful tool which can be used to generate reports for several data sources. These data sources can be MySQL database, Oracle databases etc. In this article, I will show you how to give report on a MySQL database using Jasper as a PDF in your web site.
Pre - Requisites,
Following software and tools have to be in your pc,
Optional,
Say if you have a MySQL database which has the marks of a particular subject for a set of students.
If you want to give report about students marks as a chart, you can use Jasper for the task. I will show you how to give the student's marks graph as PDF in your web site using Jasper.
First task will be creating a Jasper report File(.jrxml) using iReport designer to generate the student marks chart.
Step 1 - Open iReport Designer and Click File - > New. Then select Report template and click the button Open this template.
Step 2 - Give a name to the report project and click next
Step 3 - The new report will be shown in the iReport designer. Click on the Report Datasources button and then click new button.
Step 4 - Click on the Database JDBC Connection an click next.
Step 5 - Select MySQL as JDBC driver type and enter the details for your database connection. Then click save button to go back to the report.
Step 6 - Click on the Report Query Button on the report and following UI will be shown. In that type the SQL query which you need to add to your report. I have used "select * from Marks".
Step 7 - When we typed the query, all the field will be retrieved automatically. Click the preview data button to view the data. Then click OK.
Step 8 - Drag and drop a Static TextBox from palette to the report and give title to the report. I have used "Student Marks".
Step 9 - Drag and drop a Chart from palette to the report's summary section . It will show following UI. In that select line chart.
Step 10 - In the next dialog click the edit button near the text Dummy Series to edit the name of the data series of the graph. I have used Student Marks as the data series name. An click apply and next.
Step 11 - In the next dialog click the edit button in the category expression to select the value set for the X axis of the chart.
Step 12 - In the next dialog, I have double clicked on the column name to add it as the data series for the X axis.
Step 13 - In the next dialog click the edit button in the value expression to select the value set for the Y axis of the chart. In the next dialog, I have double clicked on the column marks to add it as the data series for the Y axis. And then click Finish.
Step 14 - Now you have completed your Jasper report. Click on the XML tab to view the jrxml file for the particular report and Preview tab to view the report.
Step 15 - Save your jrxml file in a known location. It will be needed to future steps.
Next step will be adding this report to a web page as PDF which user can view or download. For that I a going to create a web application using intelliJ IDEA. Please follow this tutorial to see how to create a web application and run it in Tomcat Server in IntelliJ IDEA. http://wiki.jetbrains.net/intellij/Creating_a_simple_Web_application_and_deploying_it_to_Tomcat.
Step 16 - Create a new Web Aplication Prject in IDEA
Step 17 - Create a new JSP file in it and name it as marksGraph.jsp.
Step 18 - Change the index.jsp as follows.
Step 19- Download all these jars and add the to the lib folder inside your project - > web folder - > WEB-INF folder. Also add the jasperreports jar to the dependencies folder of your project.
Step 21 - Change the marksGraph.jsp as follows.
This code is responsible for load the jrxml file and generate the report. Also it writes the report to the web page as a PDF.
Step 22 - Cick on the run button to run this web application in Tomcat Server.
Step 23 - The newly deployed web application will be displyed in the web page like this,
Step 24 - Click on the Show Student Marks Graph link.
Step 25 - The students marks graph will be shown as a PDF like this,
Pre - Requisites,
Following software and tools have to be in your pc,
Optional,
Say if you have a MySQL database which has the marks of a particular subject for a set of students.
If you want to give report about students marks as a chart, you can use Jasper for the task. I will show you how to give the student's marks graph as PDF in your web site using Jasper.
First task will be creating a Jasper report File(.jrxml) using iReport designer to generate the student marks chart.
Step 1 - Open iReport Designer and Click File - > New. Then select Report template and click the button Open this template.
Step 2 - Give a name to the report project and click next
Step 3 - The new report will be shown in the iReport designer. Click on the Report Datasources button and then click new button.
Step 4 - Click on the Database JDBC Connection an click next.
Step 5 - Select MySQL as JDBC driver type and enter the details for your database connection. Then click save button to go back to the report.
Step 6 - Click on the Report Query Button on the report and following UI will be shown. In that type the SQL query which you need to add to your report. I have used "select * from Marks".
Step 7 - When we typed the query, all the field will be retrieved automatically. Click the preview data button to view the data. Then click OK.
Step 8 - Drag and drop a Static TextBox from palette to the report and give title to the report. I have used "Student Marks".
Step 9 - Drag and drop a Chart from palette to the report's summary section . It will show following UI. In that select line chart.
Step 10 - In the next dialog click the edit button near the text Dummy Series to edit the name of the data series of the graph. I have used Student Marks as the data series name. An click apply and next.
Step 11 - In the next dialog click the edit button in the category expression to select the value set for the X axis of the chart.
Step 12 - In the next dialog, I have double clicked on the column name to add it as the data series for the X axis.
Step 13 - In the next dialog click the edit button in the value expression to select the value set for the Y axis of the chart. In the next dialog, I have double clicked on the column marks to add it as the data series for the Y axis. And then click Finish.
Step 14 - Now you have completed your Jasper report. Click on the XML tab to view the jrxml file for the particular report and Preview tab to view the report.
Step 15 - Save your jrxml file in a known location. It will be needed to future steps.
Next step will be adding this report to a web page as PDF which user can view or download. For that I a going to create a web application using intelliJ IDEA. Please follow this tutorial to see how to create a web application and run it in Tomcat Server in IntelliJ IDEA. http://wiki.jetbrains.net/intellij/Creating_a_simple_Web_application_and_deploying_it_to_Tomcat.
Step 16 - Create a new Web Aplication Prject in IDEA
Step 17 - Create a new JSP file in it and name it as marksGraph.jsp.
Step 18 - Change the index.jsp as follows.
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title></title> </head> <body> <a href="marksGraph.jsp">Show Students Marks Graph</a> </body> </html>
Step 19- Download all these jars and add the to the lib folder inside your project - > web folder - > WEB-INF folder. Also add the jasperreports jar to the dependencies folder of your project.
- http://mvnrepository.com/artifact/jasperreports/jasperreports/3.5.3
- http://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.24
- http://mvnrepository.com/artifact/commons-digester/commons-digester/2.1
- http://mvnrepository.com/artifact/commons-logging/commons-logging/1.1.2
- http://mvnrepository.com/artifact/commons-collections/commons-collections/3.2.1
- http://mvnrepository.com/artifact/commons-beanutils/commons-beanutils/1.8.3
- http://mvnrepository.com/artifact/jfree/jfreechart/1.0.13
- http://mvnrepository.com/artifact/org.codehaus.groovy/groovy-all/2.1.2
- http://mvnrepository.com/artifact/jfree/jcommon/1.0.16
- http://mvnrepository.com/artifact/com.lowagie/itext/4.2.0
Step 21 - Change the marksGraph.jsp as follows.
This code is responsible for load the jrxml file and generate the report. Also it writes the report to the web page as a PDF.
<%@ page contentType="application/pdf" %>
<%@ page trimDirectiveWhitespaces="true"%>
<%@ page import="net.sf.jasperreports.engine.*" %>
<%@ page import="java.io.File" %>
<%@ page import="java.io.FileInputStream" %>
<%@ page import="java.io.FileNotFoundException" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.SQLException" %>
<%
Connection conn=null;
try {
//Connecting to the MySQL database
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Students?user=root&password=12345");
//Loading Jasper Report File from Local file system
String jrxmlFile = session.getServletContext().getRealPath(request.getContextPath())+"/GenerateReport.jrxml";
InputStream input = new FileInputStream(new File(jrxmlFile));
//Generating the report
JasperReport jasperReport = JasperCompileManager.compileReport(input);
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null, conn);
//Exporting the report as a PDF
JasperExportManager.exportReportToPdfStream(jasperPrint, response.getOutputStream());
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (JRException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally {
if(conn!=null){
conn.close();
}
}
%>
Step 22 - Cick on the run button to run this web application in Tomcat Server.
Step 23 - The newly deployed web application will be displyed in the web page like this,
Step 24 - Click on the Show Student Marks Graph link.
Step 25 - The students marks graph will be shown as a PDF like this,
Also please note that after saving the jrxml file using iReport designer, remove all the uuid tags of each and every element. Also you can use this kind of report generation logic in any web application. I have attached the source code of my sample web app here. https://www.dropbox.com/s/d8tk1midq4tdy7e/ReportGenerationWebApp.zip Hope this tutorial helped you. Comments and questions are welcomed.
