This page will walk through Spring Boot + Jasper Report + MySQL Database example. A report is a document that presents information in an organized format for a specific audience and purpose. Jasper Report is an open source Java reporting tool. It can generate verity of reports like PDF, Excel, etc.
What we’ll build
In this example, we fetch the employees records from the MySQL database using Spring Data JPA and fill the list of employees into the report. At last, it generates a PDF report as shown below:
Technology Used
Find the list of technologies used in this example.
- STS 4
- Java 8
- Spring Boot 2.1.2.RELEASE
- Spring Data JPA
- Jaspersoft iReport Designer
Dependency Required
Make sure the below dependencies are in your project build path.
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
1. Report Templates
Report designs are defined in JRXML files.
employee-rpt-database.jrxml
<?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="employee-rpt" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="ireport.zoom" value="1.5"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<parameter name="createdBy" class="java.lang.String"/>
<field name="empNo" class="java.lang.Long"/>
<field name="empName" class="java.lang.String"/>
<field name="job" class="java.lang.String"/>
<field name="salary" class="java.lang.Long"/>
<field name="manager" class="java.lang.Long"/>
<field name="hireDate" class="java.util.Date"/>
<field name="commission" class="java.lang.Long"/>
<field name="deptNo" class="java.lang.Long"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="42" splitType="Stretch">
<staticText>
<reportElement x="64" y="0" width="481" height="42"/>
<textElement textAlignment="Center">
<font size="20" isBold="true"/>
</textElement>
<text><![CDATA[Employee Report]]></text>
</staticText>
</band>
</title>
<columnHeader>
<band height="61" splitType="Stretch">
<textField>
<reportElement x="456" y="21" width="99" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$P{createdBy}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="398" y="21" width="58" height="20"/>
<textElement/>
<text><![CDATA[Created By:]]></text>
</staticText>
<staticText>
<reportElement x="0" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Emp No]]></text>
</staticText>
<staticText>
<reportElement x="61" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Emp Name]]></text>
</staticText>
<staticText>
<reportElement x="122" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Job]]></text>
</staticText>
<staticText>
<reportElement x="183" y="41" width="62" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Salary]]></text>
</staticText>
<staticText>
<reportElement x="245" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Manager]]></text>
</staticText>
<staticText>
<reportElement x="306" y="41" width="73" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Hire Date]]></text>
</staticText>
<staticText>
<reportElement x="379" y="41" width="87" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Commission]]></text>
</staticText>
<staticText>
<reportElement x="466" y="41" width="89" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Dept No]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="20" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{empNo}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="61" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{empName}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="122" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{job}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="183" y="0" width="62" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{salary}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="245" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{manager}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="306" y="0" width="73" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{hireDate}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="379" y="0" width="87" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{commission}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="466" y="0" width="89" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{deptNo}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>
2. Entity
Create a simple Employee class with its fields. Generate the getters and setters method of all fields.
Employee.java
package org.websparrow.report.entity; import java.util.Date; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "emp") public class Employee { @Id @Column(name = "empno") private Long empNo; @Column(name = "ename") private String empName; @Column(name = "job") private String job; @Column(name = "mgr") private Long manager; @Column(name = "hiredate") private Date hireDate; @Column(name = "sal") private Long salary; @Column(name = "comm") private Long commission; @Column(name = "deptno") private Long deptNo; // Generate getters and setters... }
3. application.properties
application.properties file contains all the database credentials to connect with database.
application.properties
spring.datasource.url=jdbc:mysql://localhost/websparrow spring.datasource.username=root spring.datasource.password= spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
4. Repository
ReportRepository interface extends the JpaRepository to query with database.
ReportRepository.java
package org.websparrow.report.repository; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.stereotype.Repository; import org.websparrow.report.entity.Employee; @Repository public interface ReportRepository extends JpaRepository<Employee, Long> { }
5. Service
In EmployeeReportService class, autowired the ReportRepository interface and class its findAll() method to fetch all the employees details from the database.
EmployeeReportService.java
package org.websparrow.report.service; import java.util.HashMap; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.websparrow.report.entity.Employee; import org.websparrow.report.repository.ReportRepository; import net.sf.jasperreports.engine.JasperCompileManager; import net.sf.jasperreports.engine.JasperExportManager; import net.sf.jasperreports.engine.JasperFillManager; import net.sf.jasperreports.engine.JasperPrint; import net.sf.jasperreports.engine.JasperReport; import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource; @Service public class EmployeeReportService { @Autowired private ReportRepository reportRepository; public String generateReport() { try { List<Employee> employees = reportRepository.findAll(); String reportPath = "F:\Content\Report"; // Compile the Jasper report from .jrxml to .japser JasperReport jasperReport = JasperCompileManager .compileReport(reportPath + "\employee-rpt-database.jrxml"); // Get your data source JRBeanCollectionDataSource jrBeanCollectionDataSource = new JRBeanCollectionDataSource(employees); // Add parameters Map<String, Object> parameters = new HashMap<>(); parameters.put("createdBy", "Websparrow.org"); // Fill the report JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, jrBeanCollectionDataSource); // Export the report to a PDF file JasperExportManager.exportReportToPdfFile(jasperPrint, reportPath + "\Emp-Rpt-Database.pdf"); System.out.println("Done"); return "Report successfully generated @path= " + reportPath; } catch (Exception e) { e.printStackTrace(); return "Error--> check the console log"; } } }
5. Controller
Autowire the EmployeeReportService in EmployeeReportController and call its generateReport() method.
EmployeeReportController.java
package org.websparrow.report.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import org.websparrow.report.service.EmployeeReportService; @RestController @RequestMapping("/emp") public class EmployeeReportController { @Autowired private EmployeeReportService employeeReportService; @GetMapping("/report") public String generateReport() { return employeeReportService.generateReport(); } }
Test it
To test the application, follow the below steps:
- Build your application using Maven
mvn installcommand. - After a successful build, run the application Run As » Spring Boot App.
- When the application stated, hit the http://localhost:8080/emp/report URL in the web browser.
- It will generate below PDF report at the specified location in the service class.
Download Source Code: spring-boot-jasper-report-mysql-database-example.zip
<?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="employee-rpt" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="ireport.zoom" value="1.5"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<parameter name="createdBy" class="java.lang.String"/>
<field name="empNo" class="java.lang.Long"/>
<field name="empName" class="java.lang.String"/>
<field name="job" class="java.lang.String"/>
<field name="salary" class="java.lang.Long"/>
<field name="manager" class="java.lang.Long"/>
<field name="hireDate" class="java.util.Date"/>
<field name="commission" class="java.lang.Long"/>
<field name="deptNo" class="java.lang.Long"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="42" splitType="Stretch">
<staticText>
<reportElement x="64" y="0" width="481" height="42"/>
<textElement textAlignment="Center">
<font size="20" isBold="true"/>
</textElement>
<text><![CDATA[Employee Report]]></text>
</staticText>
</band>
</title>
<columnHeader>
<band height="61" splitType="Stretch">
<textField>
<reportElement x="456" y="21" width="99" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$P{createdBy}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="398" y="21" width="58" height="20"/>
<textElement/>
<text><![CDATA[Created By:]]></text>
</staticText>
<staticText>
<reportElement x="0" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Emp No]]></text>
</staticText>
<staticText>
<reportElement x="61" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Emp Name]]></text>
</staticText>
<staticText>
<reportElement x="122" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Job]]></text>
</staticText>
<staticText>
<reportElement x="183" y="41" width="62" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Salary]]></text>
</staticText>
<staticText>
<reportElement x="245" y="41" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Manager]]></text>
</staticText>
<staticText>
<reportElement x="306" y="41" width="73" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Hire Date]]></text>
</staticText>
<staticText>
<reportElement x="379" y="41" width="87" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Commission]]></text>
</staticText>
<staticText>
<reportElement x="466" y="41" width="89" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Dept No]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="20" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{empNo}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="61" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{empName}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="122" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{job}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="183" y="0" width="62" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{salary}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="245" y="0" width="61" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{manager}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="306" y="0" width="73" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{hireDate}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="379" y="0" width="87" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{commission}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="466" y="0" width="89" height="20"/>
<box leftPadding="0">
<pen lineWidth="0.25"/>
<topPen lineWidth="0.25"/>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{deptNo}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>