提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

Spring 4 Jasper Report integration example with mysql database in eclipse

mysqlDatabaseSpringeclipse in with Example report
2023-09-27 14:23:15 时间

This tutorial shows that how to generate dynamic reports from database using Jasper Reports in Spring 4 mvc. Jasper Reports uses XML templates to generate reports that can be saved/opened as HTML, PDF or CSV, XLS. In this example, let us create jasper report to generate the list of employees who are working more or equal to given year. We will get no of years and report format as input. Assume that spring 4 framework is configured in eclipse for this example.

Jars used in this tutorial for Jasper integration

commons-beanutils-1.9.0.jar
commons-codec-1.5.jar
commons-collections-3.2.1.jar
commons-digester-2.1.jar
commons-fileupload-1.3.1.jar
commons-io-2.2.jar
commons-javaflow-20060411.jar
commons-lang3-3.1.jar
commons-logging-1.1.1.jar
commons-logging-api-1.1.jar
itextpdf-5.5.0.jar
jasper-compiler-jdt.jar
jasperreports-6.0.0.jar
jasperreports-fonts-6.0.0.jar
jasperreports-javaflow-6.0.0.jar 
mysql-5.1.10.jar
As JRHtmlExporter is depreciated, i have used HtmlExporter for generating html report. The dependencies for HtmlExporter are as follows.

com.fasterxml.jackson.annotations.jar
com.fasterxml.jackson.core.jar
com.fasterxml.jackson.databind.jar

 

Direct link to download the Latest JasperReport Libraries including old version

http://sourceforge.net/projects/jasperreports/files/jasperreports/

 

Let us see the Steps to generate report with the data stored in MySql database using JasperReports and Spring 4

Step 1: Create a table emp_master in MySql Database with necessary fields. (In our example, fields used emp_code, EmpName, Salary, Doj)

DDL to create table

create table emp_master (emp_code varchar(10), EmpName varchar(100),Salary decimal, Doj date);

insert some rows into the table.

insert into emp_master values (‘5000’, ‘Kumar’, 4500000, ‘2000-01-01’);

insert into emp_master values (‘5001’, ‘Jacob’, 4000000, ‘2002-01-01’);

select Emp_code, EmpName, Salary, Doj from emp_master where TIMESTAMPDIFF(YEAR, Doj, CURDATE())>=9

Step 2: Create JRXML (JREmp1.jrxml) which is a reusable template that can be used by Jasper report engine to populate data from the database. This XML template includes various sections like Title, queryString, Page Header/Footer, Column Header/Footer, etc.. Write your query in the queryString part. Query parameter (noy) can be embedded using the $P{noy) where noy is the number of year. The JRXML file(JREmp1.jrxml) used in this example is given below.

 

 

 

 

 

<?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="StockReport" pageWidth="500" pageHeight="600" columnWidth="450" leftMargin="5" rightMargin="5" topMargin="10" bottomMargin="1">
<reportFont name="Arial" isDefault="true" fontName="Arial" size="11" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<parameter name="Title" class="java.lang.String"/>
<parameter name="noy" class="java.lang.Integer"/>
<queryString>
    <![CDATA[select Emp_code, EmpName,  Salary, Doj from emp_master where TIMESTAMPDIFF(YEAR, Doj, CURDATE())>=$P{noy}]]>
</queryString>
<field name="Emp_code" class="java.lang.String"/>
<field name="EmpName" class="java.lang.String"/>
<field name="Salary" class="java.lang.Integer"/>
<field name="Doj" class="java.lang.String"/>
 
<title>
<band height="50" splitType="Stretch">
    <textField isBlankWhenNull="true">
        <reportElement x="0" y="5" width="490" height="30"/>
        <textElement textAlignment="Center">
            <font reportFont="Arial" size="22"/>
        </textElement>
        <textFieldExpression class="java.lang.String"><![CDATA[$P{Title}]]></textFieldExpression>
    </textField>
</band>
</title>
<pageHeader>
<band height="20" splitType="Stretch">
    <textField>
        <reportElement mode="Opaque" x="0" y="5" width="490" height="15" forecolor="#FFFFFF" backcolor="#777765"/>
        <textElement textAlignment="Center">
            <font reportFont="Arial"/>
        </textElement>
        <textFieldExpression class="java.lang.String"><![CDATA["Employees  who are working more than  " + String.valueOf($P{noy})+" Years"]]></textFieldExpression>
    </textField>
</band>
</pageHeader>
<columnHeader>
   <band height="20" splitType="Stretch">
    <staticText>
        <reportElement mode="Opaque" x="0" y="4" width="100" height="15" backcolor="#CBB453"/>
        <textElement textAlignment="Left">
            <font reportFont="Arial"/>
        </textElement>
        <text>
            <![CDATA[Employee Code]]>
        </text>
    </staticText>
    <staticText>
        <reportElement positionType="Float" mode="Opaque" x="100" y="4" width="170" height="15" backcolor="#CBB453"/>
        <textElement>
            <font reportFont="Arial"/>
        </textElement>
        <text>
            <![CDATA[Employee Name]]>
        </text>
    </staticText>
    <staticText>
        <reportElement positionType="Float" mode="Opaque" x="270" y="4" width="100" height="15" backcolor="#CBB453"/>
        <textElement>
            <font reportFont="Arial"/>
        </textElement>
        <text>
            <![CDATA[Salary]]>
        </text>
    </staticText>
    <staticText>
        <reportElement positionType="Float" mode="Opaque" x="370" y="4" width="120" height="15" backcolor="#CBB453"/>
        <textElement>
            <font reportFont="Arial"/>
        </textElement>
        <text>
            <![CDATA[Date of Joining]]>
        </text>
    </staticText>
    </band>
</columnHeader>
<detail>
   <band height="20" splitType="Stretch">
    <textField>
        <reportElement x="0" y="4" width="100" height="15"/>
        <textElement textAlignment="Left"/>
        <textFieldExpression class="java.lang.String"><![CDATA[$F{Emp_code}]]></textFieldExpression>
    </textField>
    <textField isStretchWithOverflow="true">
        <reportElement positionType="Float" x="100" y="4" width="170" height="15"/>
        <textElement/>
        <textFieldExpression class="java.lang.String"><![CDATA[$F{EmpName}]]></textFieldExpression>
    </textField>
    <textField isStretchWithOverflow="true">
        <reportElement positionType="Float" x="270" y="4" width="100" height="15"/>
        <textElement/>
        <textFieldExpression class="java.lang.Integer"><![CDATA[$F{Salary}]]></textFieldExpression>
    </textField>
    <textField isStretchWithOverflow="true">
        <reportElement positionType="Float" x="370" y="4" width="100" height="15"/>
        <textElement/>
        <textFieldExpression class="java.lang.String"><![CDATA[$F{Doj}]]></textFieldExpression>
    </textField>
   </band>
</detail>
<pageFooter>
    <band height="40" splitType="Stretch">
    <textField>
        <reportElement x="200" y="20" width="85" height="15"/>
        <textElement textAlignment="Right"/>
        <textFieldExpression class="java.lang.String"><![CDATA["Page " + String.valueOf($V{PAGE_NUMBER})]]></textFieldExpression>
    </textField>
    <textField evaluationTime="Report">
        <reportElement x="285" y="20" width="75" height="15"/>
        <textElement textAlignment="Left"/>
        <textFieldExpression class="java.lang.String"><![CDATA[" of " + String.valueOf($V{PAGE_NUMBER})]]></textFieldExpression>
    </textField>
    </band>
</pageFooter>
<summary>
   <band height="35" splitType="Stretch">
    <textField isStretchWithOverflow="true">
        <reportElement x="175" y="20" width="165" height="15"/>
        <textElement textAlignment="Center">
            <font reportFont="Arial"/>
        </textElement>
        <textFieldExpression class="java.lang.String">
            <![CDATA["Total Number of Employees " + String.valueOf($V{REPORT_COUNT})]]>
        </textFieldExpression>
    </textField>
    </band>
</summary>
</jasperReport>

 

The above XML template can be created either by using IReports OR by editing the existing XML template file as per your requirement.The below lines mentioned in the XML template are used to define the external parameters.

 

 

<parameter name="Title" class="java.lang.String"/>
<parameter name="noy" class="java.lang.Integer"/>

Step 3: Place the XML template in jasper folder under WebContent folder.The following code compiles the JREmp1.jrxml and creates JREmp1.jasper in the same folder jasper.

JasperCompileManager.compileReportToFile(request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jrxml"),request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jasper"));

JREmp1.jasper can be used multiple times with different sets of parameters & data to generate dynamic report

Step 4: Load the .jasper file with JRLoader.loadObjectFromFile method which returns the JasperReport instance.

File reportFile = new File( request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jasper"));
        // If compiled file is not found, then compile XML template
        if (!reportFile.exists()) {
                   JasperCompileManager.compileReportToFile(request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jrxml"),request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jasper"));
            }
            JasperReport jasperReport = (JasperReport) JRLoader.loadObjectFromFile(reportFile.getPath());
           return jasperReport;
        }

 

Step 5: Now pass the jasperReport instance, parameters as HashMap and connection object to methods like JasperRunManager.runReportToPdf(), JasperManager.fillReport() to generate the report as PDF or HTML.

Now let us see the complete code

The following JSP (loadJasper.jsp) captures no of years and Report format from the user.

loadJasper.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"     pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<html>
<head>
<style>
.error {
    color: #ff0000;
}
 
</style>
</head>
 
<body>
    <h2>Spring MVC Jasper Report example</h2>
 
    <h3>Generate Report for Employees Working More than or equal to given year</h3>
 
    <form:form method="POST" action="generateReport.do" commandName="jasperInputForm">
 
        <table id="reptbl" width="350px" border="1">
        <tr>
        <td colspan="2"><form:errors path="noofYears" cssClass="error"/></td> </tr>
        <tr>
            <td>
                Enter Number of Years <form:input path="noofYears" id="noofYears"/>
                <input type="submit"  value="Generate Employee List"  />
            </td>
            <td>
                <form:radiobuttons path="rptFmt" items="${jasperRptFormats}"/>
            </td>
 
        </tr>
 
         </table>  
 
    </form:form>
 
</body>
</html>

Model Form :

JasperInputForm.java

package net.javaonline.spring.jasper.form;
 
import org.hibernate.validator.constraints.NotEmpty;
 
public class JasperInputForm {
    @NotEmpty
    private String noofYears;
    private String rptFmt="Html";
 
    public String getRptFmt() {
        return rptFmt;
    }
 
    public void setRptFmt(String rptFmt) {
        this.rptFmt = rptFmt;
    }
 
    public String getNoofYears() {
        return noofYears;
    }
 
    public void setNoofYears(String noofYears) {
        this.noofYears = noofYears;
    }
 
    }

The following is the controller code. loadJasper method loads view (loadJasper.jsp) page. generateReport method generates the report.

loadJasper.java (Controller)

package net.javaonline.spring.jasper.controller;
 
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import javax.naming.NamingException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
 
import net.javaonline.spring.jasper.form.JasperInputForm;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.JasperRunManager;
import net.sf.jasperreports.engine.export.HtmlExporter;
import net.sf.jasperreports.engine.util.JRLoader;
import net.sf.jasperreports.export.SimpleExporterInput;
import net.sf.jasperreports.export.SimpleHtmlExporterOutput;
import net.sf.jasperreports.export.SimpleHtmlReportConfiguration;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
 
@Controller
public class LoadJasperReport {
 
    @ModelAttribute("jasperRptFormats")
    public ArrayList getJasperRptFormats()
    {
        ArrayList < String> jasperRptFormats = new ArrayList<String>();
        jasperRptFormats.add("Html");
        jasperRptFormats.add("PDF");
 
        return jasperRptFormats;
    }   
 
@RequestMapping(value = "/loadJasper", method = RequestMethod.GET)
    public String loadSurveyPg(@ModelAttribute("jasperInputForm") JasperInputForm jasperInputForm,Model model) {
    model.addAttribute("JasperInputForm", jasperInputForm);
 
        return "loadJasper";
}
 
@RequestMapping(value = "/generateReport", method = RequestMethod.POST)
public String generateReport(@Valid @ModelAttribute("jasperInputForm") JasperInputForm jasperInputForm,BindingResult result,Model model, HttpServletRequest request,HttpServletResponse response) throws ParseException {
 
    if (result.hasErrors()) {
        System.out.println("validation error occured in jasper input form");
        return "loadJasper";
 
    }
 
    String reportFileName = "JREmp1";
 
    Connection conn = null;
    try {
        try {
 
             Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                System.out.println("Please include Classpath Where your MySQL Driver is located");
                e.printStackTrace();
            }  
 
         conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","******");
 
     if (conn != null)
     {
         System.out.println("Database Connected");
     }
     else
     {
         System.out.println(" connection Failed ");
     }
 
          String rptFormat = jasperInputForm.getRptFmt();
          String noy = jasperInputForm.getNoofYears();
 
          System.out.println("rpt format " + rptFormat);
          System.out.println("no of years " + noy);
 
           //Parameters as Map to be passed to Jasper
           HashMap<String,Object> hmParams=new HashMap<String,Object>();
 
           hmParams.put("noy", new Integer(noy));
 
                   hmParams.put("Title", "Employees working more than "+ noy + " Years");
 
            JasperReport jasperReport = getCompiledFile(reportFileName, request);
 
        if (rptFormat.equalsIgnoreCase("html") ) {
 
            JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, hmParams, conn);
            generateReportHtml(jasperPrint, request, response); // For HTML report
 
        }
 
        else if  (rptFormat.equalsIgnoreCase("pdf") )  {
 
            generateReportPDF(response, hmParams, jasperReport, conn); // For PDF report
 
            }
 
       } catch (Exception sqlExp) {
 
           System.out.println( "Exception::" + sqlExp.toString());
 
       } finally {
 
            try {
 
            if (conn != null) {
                conn.close();
                conn = null;
            }
 
            } catch (SQLException expSQL) {
 
                System.out.println("SQLExp::CLOSING::" + expSQL.toString());
 
            }
 
           }
 
return null;
 
}
 
private JasperReport getCompiledFile(String fileName, HttpServletRequest request) throws JRException {
    System.out.println("path " + request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jasper"));
    File reportFile = new File( request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jasper"));
    // If compiled file is not found, then compile XML template
    if (!reportFile.exists()) {
               JasperCompileManager.compileReportToFile(request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jrxml"),request.getSession().getServletContext().getRealPath("/jasper/" + fileName + ".jasper"));
        }
        JasperReport jasperReport = (JasperReport) JRLoader.loadObjectFromFile(reportFile.getPath());
       return jasperReport;
    } 
 
    private void generateReportHtml( JasperPrint jasperPrint, HttpServletRequest req, HttpServletResponse resp) throws IOException, JRException {
         HtmlExporter exporter=new HtmlExporter();
         List<JasperPrint> jasperPrintList = new ArrayList<JasperPrint>();
         jasperPrintList.add(jasperPrint);
         exporter.setExporterInput(SimpleExporterInput.getInstance(jasperPrintList));
         exporter.setExporterOutput( new SimpleHtmlExporterOutput(resp.getWriter()));
         SimpleHtmlReportConfiguration configuration =new SimpleHtmlReportConfiguration();
         exporter.setConfiguration(configuration);
          exporter.exportReport();
 
    }
 
    private void generateReportPDF (HttpServletResponse resp, Map parameters, JasperReport jasperReport, Connection conn)throws JRException, NamingException, SQLException, IOException {
        byte[] bytes = null;
        bytes = JasperRunManager.runReportToPdf(jasperReport,parameters,conn);
        resp.reset();
        resp.resetBuffer();
        resp.setContentType("application/pdf");
        resp.setContentLength(bytes.length);
        ServletOutputStream ouputStream = resp.getOutputStream();
        ouputStream.write(bytes, 0, bytes.length);
        ouputStream.flush();
        ouputStream.close();
    } 
 
}

Jasper-Context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
      xmlns:context="http://www.springframework.org/schema/context"
      xmlns:mvc="http://www.springframework.org/schema/mvc"
      xmlns:tx="http://www.springframework.org/schema/tx"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-4.1.xsd">
 
      <context:component-scan base-package="net.javaonline.spring.jasper.controller"/>
      <mvc:annotation-driven />   
 
      <bean id="messageSource" class="org.springframework.context.support.ResourceBundleMessageSource">
        <property name="basename" value="messages"/>
    </bean>
      <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/Pages/" />
        <property name="suffix" value=".jsp" />
    </bean>
 
</beans>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
 
<web-app version="2.4"
         xmlns="http://java.sun.com/xml/ns/j2ee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
         http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" >
 
  <servlet>
    <servlet-name>Jasper</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
     <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>/WEB-INF/Jasper-Context.xml</param-value>
      </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
 
 <servlet-mapping>
    <servlet-name>Jasper</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>
 
</web-app>

Run the application by calling the below URL

http://localhost:8080/<Project Name>/loadJasper.do

http://localhost:8080/SpringJasperDemo/loadJasper.do

 

 

 

 

 

 

 

 

Enter Number of years as 9, Select Html  and Click on Generate Employee List

Report in Html 

Enter Number of years as 3,  Select Pdf and Click on Generate Employee List

Report in PDF

 

 

Download the complete source code of the above project including necessary Jars  at SpringJasperDemo.war

For downloading the above example with maven support, please visit Spring Jasper Report Example with Maven 

 

https://github.com/vkjegan/SpringJasperDemo

 

相关文章