Spring Boot + Jasper Report + MySQL Database Example

By Atul Rai | Last Updated: May 25, 2019

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.

Similar Post: Spring Boot + Jasper Report Example

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:

Spring Boot + Jasper Report + MySQL Database Example

Technology Used

Find the list of technologies used in this example.

  1. STS 4
  2. Java 8
  3. Spring Boot 2.1.2.RELEASE
  4. Spring Data JPA
  5. 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:

  1. Build your application using Maven mvn install command.
  2. After a successful build, run the application  Run As » Spring Boot App.
  3. When the application stated, hit the http://localhost:8080/emp/report URL in the web browser.
  4. It will generate below PDF report at the specified location in the service class.
Spring Boot + Jasper Report + MySQL Database Example

References

  1. Spring Boot + Jasper Report Example
  2. Struts 2 and Jasper Report Integration Example
  3. Struts 2 and Jasper Subreports Integration Example

Similar Posts

About the Author

Atul Rai
I love sharing my experiments and ideas with everyone by writing articles on the latest technological trends. Read all published posts by Atul Rai.