How to generate PDF report from MySQL database in Spring Boot

I am going to show yo how to download PDF file from MySQL database using Spring Boot and iText library in Java programming language. Many applications require dynamic generation of PDF documents for reporting purpose, such as, generating statements for customers, readers buying online specific chapters of a book, etc.

In my another tutorial I had shown how to create or write to a PDF file using Java based iText library. The iText library is freely available and can be used using maven or gradle build tool. We can also generate HTML, RTF, XML documents using iText library apart from PDF documents.

PDF files can be useful for various reasons:

  • layout is uniform across different computer systems
  • tend to have a small file size in comparison to other file formats
  • can be protected by password
  • different levels of access can be set based on requirements
  • works uniformly across different operating systems
  • graphics or images, links, etc. can be easily integrated

Prerequisites

Java at least 8, Gradle 6.5.1, Maven 3.6.3, iText 5.5.13.1, Spring Boot 2.3.3, MySQL 8.0.17

Project Setup

You can create gradle or maven based project in your favorite IDE or tool. According to your gradle or maven based project you can use one of the following build file.

You need to add additional jaxb runtime API if you are using MySQL 8 and Java higher than version 8.

build.gradle

buildscript {
	ext {
		springBootVersion = '2.3.3.RELEASE'
	}
	
    repositories {
    	mavenCentral()
    }
    
    dependencies {
    	classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
	
	implementation('com.itextpdf:itextpdf:5.5.13.1')
	
	implementation 'mysql:mysql-connector-java:8.0.21'
	
    //required for JDK 9 or above
    implementation 'javax.xml.bind:jaxb-api:2.4.0-b180830.0359'
}

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>spring-pdf-report-generation</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.3.RELEASE</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		
		<dependency>
			<groupId>com.itextpdf</groupId>
			<artifactId>itextpdf</artifactId>
			<version>5.5.13.1</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.21</version>
		</dependency>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>

    <build>
        <plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Data

As I am going to test the application right away once coding part is done, so I have stored some sample data in MySQL table product under database roytuts as shown below.

In MySQL version 8 you do not need to specify the size of the int data type. If you do so then MySQL server will give you warning.

CREATE TABLE `product` (
	`id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
	`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
	`price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sales_count` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_date` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into product(id,name,price,sale_price,sales_count,sale_date) values(1, 'Desktop','30000','35000','55','02-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(2, 'Desktop','30300','37030','43','03-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(3, 'Tablet','39010','48700','145','04-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(4, 'Phone','15000','17505','251','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(5, 'Phone','18000','22080','178','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(6, 'Tablet','30500','34040','58','05-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(7, 'Adapter','2000','2500','68','06-04-2018');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(8, 'TV','45871','55894','165','07-04-2018');

DataSource Configuration

To establish connection to database and to fetch data from or write data to database table you need to configure datasource in Spring framework. I am using annotation based or Java based datasource configuration.

For establishing conneecting to database, the first thing would be to put the database settings into application.properties file under classpath directory src/main/resources.

#datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root

#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none

The Java based datasource configuration is given below. The application.properties file is automatically read by Spring Boot framework as it is under the classpath directory with standard naming convention.

Spring framework provides Environment to retrieve your configuration properties easily. The class is annotation with @Configuration to indicate it is used for configuration purpose.

The datasource requires entity manager factory and if you do not create it then Spring Boot will create one for you.

You need to let Spring know which package contains entity classes and which package contains Spring Data JPA repository interfaces. Spring Data JPA Repository is built-in API that provides functions for performing basic CRUD (Create, Read, Update and Delete) operations out of the box.

package com.roytuts.pdf.report.generation.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

@Configuration
@EnableJpaRepositories(basePackages = "com.roytuts.pdf.report.generation.repository")
public class Config {

	@Autowired
	private Environment environment;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driverClassName"));
		ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));
		ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));
		ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));
		return ds;
	}

	@Bean
	public EntityManagerFactory entityManagerFactory(DataSource dataSource) {
		HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
		vendorAdapter.setDatabase(Database.MYSQL);

		LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
		factory.setJpaVendorAdapter(vendorAdapter);
		factory.setPackagesToScan("com.roytuts.pdf.report.generation.entity");
		factory.setDataSource(dataSource);
		factory.afterPropertiesSet();
		return factory.getObject();
	}

}

Entity Class

The entity class maps the database table columns into your Java class attributes for the given table name using annotation @Table on the entity class.

The @Column annotation is used to map your Java field or attribute with corresponding table column name. If your java attribute and table column name are same then you don’t need to specify the name using @Column annotation.

To map primary key column you need to put @Id annotation. I am using generation type identity because I have the auto increment primary key for the table.

package com.roytuts.pdf.report.generation.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "product")
public class Product {

	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "name")
	private String name;

	@Column(name = "price")
	private Double price;

	@Column(name = "sale_price")
	private Double salePrice;

	@Column(name = "sales_count")
	private Integer salesCount;

	@Column(name = "sale_date")
	private String saleDate;

	// getters and setters

}

Repository

Here is the repository that extends Spring’s JpaRepository interface to get the maximum benefits from Spring framework.

You need to pass entity class type and primary key type when you are extending the JpaRepository. The type is here Product and the primary key type is Integer in the Product entity class.

I have not declared any method in this interface because I don’t need to. I am just going to fetch all records for generating excel file or report for the table data.

package com.roytuts.pdf.report.generation.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.roytuts.pdf.report.generation.entity.Product;

public interface ProductRepository extends JpaRepository<Product, Integer> {

}

REST Controller

I am creating Spring REST controller class wherein I am going to implement the PDF file download functionality.

I have auto-wired the repository in this class which is not a good practice but for the sake of simplicity for this example. You can read best practices in Spring application.

I have created an endpoint /report/product/ with GET http request for downloading the PDF report from MySQL database.

I have fetched all data from MySQL table using repository to write to ByteArrayOutputStream using pdf Document intance. I have not created any file that would be stored in your system or machine. Rather on the fly I am generating the PDF content and sending as an attachment which can be downloaded or saved in the client side.

I am creating a title or heading before writing the actual data from MySQL table to PDF output. Then I iterate through each object of the Product and write to the table in the PDF content.

Finally in the response I have set few headers, such as, media type for file, file name and content disposition (as attachment) to forcefully download the file instead of rendering the data onto browser.

package com.roytuts.pdf.report.generation.rest.controller;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Font;
import com.itextpdf.text.FontFactory;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import com.roytuts.pdf.report.generation.entity.Product;
import com.roytuts.pdf.report.generation.repository.ProductRepository;

@RestController
public class ProductRestController {

	@Autowired
	private ProductRepository repository;

	@GetMapping("/report/product/")
	public ResponseEntity<Resource> generateExcelReport() throws IOException, DocumentException {
		List<Product> products = repository.findAll();

		Document document = new Document(PageSize.A4, 25, 25, 25, 25);

		ByteArrayOutputStream os = new ByteArrayOutputStream();

		PdfWriter.getInstance(document, os);

		document.open();

		Paragraph title = new Paragraph("Sales Information for Products",
				FontFactory.getFont(FontFactory.HELVETICA, 14, Font.BOLD, new BaseColor(0, 255, 255)));

		document.add(title);

		PdfPTable table = new PdfPTable(6);
		table.setSpacingBefore(25);
		table.setSpacingAfter(25);

		PdfPCell c1 = new PdfPCell(new Phrase("Id"));
		table.addCell(c1);

		PdfPCell c2 = new PdfPCell(new Phrase("Name"));
		table.addCell(c2);

		PdfPCell c3 = new PdfPCell(new Phrase("Price"));
		table.addCell(c3);

		PdfPCell c4 = new PdfPCell(new Phrase("Sale Price"));
		table.addCell(c4);

		PdfPCell c5 = new PdfPCell(new Phrase("Sales Count"));
		table.addCell(c5);

		PdfPCell c6 = new PdfPCell(new Phrase("Sale Date"));
		table.addCell(c6);

		for (Product product : products) {
			table.addCell(String.valueOf(product.getId()));
			table.addCell(product.getName());
			table.addCell(String.valueOf(product.getPrice()));
			table.addCell(String.valueOf(product.getSalePrice()));
			table.addCell(String.valueOf(product.getSalesCount()));
			table.addCell(product.getSaleDate());
		}

		document.add(table);
		
		document.close();

		ByteArrayInputStream is = new ByteArrayInputStream(os.toByteArray());

		HttpHeaders headers = new HttpHeaders();
		headers.setContentType(MediaType.parseMediaType("application/pdf"));
		headers.setCacheControl("must-revalidate, post-check=0, pre-check=0");
		headers.set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=ProductPdfReport.pdf");

		ResponseEntity<Resource> response = new ResponseEntity<Resource>(new InputStreamResource(is), headers,
				HttpStatus.OK);

		return response;
	}

}

Main Class

The purpose of having a class with main method and @SpringBootAppliation is to deploy the app into embedded Tomcat server which is by default shipped with Spring Boot framework.

package com.roytuts.pdf.report.generation;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringPdfReportApp {

	public static void main(String[] args) {
		SpringApplication.run(SpringPdfReportApp.class, args);
	}

}

Testing the Application

Once you run the above main class your application will start up on default port 8080. If you cannot use default port for some reasons then you can specify the one you want in application.properties file using the key/value pair server.port=<port number>.

As it is GET request so you would be able to call the REST API endpoint (http://localhost:8080/report/product/) directly from the browser.

Once you call the URL in the browser you will see a pdf file named ProductPdfReport.pdf is ready for the save. You can save it anywhere on your system and open it to see the output as shown in the following image:

download pdf file report from mysql database in spring

Source Code

Download

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *