Spring Boot Download CSV File Example (2024)

In this article, we'll demonstrate how to generate and download CSV files as well as export data from a database using JPA in Spring Boot and the supercsv library.

Q: What is Super CSV?
Ans:

An open-source library called Super CSV offers ways for reading and writing headers as needed.

1. Data formatting options provided by SuperCSV, which enable cutting and regex replacements while processing, make parsing simpler.
2. The library can be used in systems with low memory and performance because it also supports stream-based input and output.
3. The other CSV parsers do not support partial reading or partial writing, but the SuperCSV library does.
4. The user has the option to write a dataset with optional values without adding their own error-handling, or to set selected header column values to null and continue processing the other columns.

Create Spring Boot application

Create Spring Boot application from Spring Initializr.

Project Structure

Add Dependencies

Add net.sf.supercsv dependency in pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.techgeeknext</groupId>
	<artifactId>spring-boot-export-csv</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-boot-export-csv</name>
	<description>Spring Boot Export CSV Example</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<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>

		<!-- https://mvnrepository.com/artifact/net.sf.supercsv/super-csv -->
		<dependency>
			<groupId>net.sf.supercsv</groupId>
			<artifactId>super-csv</artifactId>
			<version>2.4.0</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Application Properties

Add database connection details in application.properties file.

spring.datasource.url=jdbc:mysql://localhost/employeetestdb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.initialization-mode=always

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
# spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = create-drop

#spring.jpa.defer-datasource-initialization=true
spring.sql.init.mode=always

Test Data for CSV Document

There must be some data in the database in order to generate documents and export data using JPA. We will now enter some test data in database. Hibernate generates the schema automatically; the data.sql file is then executed to populate the table with test data.

INSERT INTO employees(id, name, role) VALUES(1, 'User1', 'Admin');
INSERT INTO employees(id, name, role) VALUES(2, 'User2', 'Supervisor');
INSERT INTO employees(id, name, role) VALUES(3, 'User3', 'Tester');
INSERT INTO employees(id, name, role) VALUES(4, 'User4', 'HR');
INSERT INTO employees(id, name, role) VALUES(5, 'User5', 'Developer');

Data Model

Create Employee class, contains id, name and role.

package com.techgeeknext.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "employees")
public class Employee {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;

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

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

JPA Repository

Create EmployeeRepository interface that extends JpaRepository.

package com.techgeeknext.repository;

import com.techgeeknext.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

Controller to generate the CSV File

Create the rest endpoint to generate the CSV File and to extract data from database using JPA repository.

package com.techgeeknext.controller;

import com.techgeeknext.repository.EmployeeRepository;
import com.techgeeknext.util.CSVWriterUtility;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Random;

@RestController
public class EmployeeController {

    @Autowired
    EmployeeRepository employeeRepository;


    @GetMapping("/csv")
    public void employeeDetailsReport(HttpServletResponse response) throws IOException {

        Random random = new Random();
        String fileType = "attachment; filename=employee_details_" + random.nextInt(1000) + ".csv";
        response.setHeader("Content-Disposition", fileType);
        response.setContentType("text/csv");

        CSVWriterUtility.employeeDetailReport(response,
                employeeRepository.findAll());
    }
}

CSV Generator Utility

To write the data to a CSV file, we will create a common utility class.

package com.techgeeknext.util;

import com.techgeeknext.model.Employee;
import org.supercsv.cellprocessor.constraint.NotNull;
import org.supercsv.cellprocessor.constraint.UniqueHashCode;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.io.CsvBeanWriter;
import org.supercsv.io.ICsvBeanWriter;
import org.supercsv.prefs.CsvPreference;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

public class CSVWriterUtility {

    private static CellProcessor[] getProcessors() {

        final CellProcessor[] processors = new CellProcessor[]{
                new UniqueHashCode(), // employee No (must be unique)
                new NotNull(), // Name
                new NotNull(), // role

                //can be used for date field
                // new FmtDate("dd/MM/yyyy"),

                //for optional fields
                //  new Optional(new FmtBool("Y", "N")),
                // new Optional(),

        };

        return processors;
    }


    public static void employeeDetailReport(HttpServletResponse response, List<Employee> employees) {

        try (ICsvBeanWriter beanWriter = new CsvBeanWriter(response.getWriter(),
                CsvPreference.STANDARD_PREFERENCE)) {

            final String[] header = new String[]{"Id", "Name", "Role"};

            final CellProcessor[] processors = getProcessors();

            //set Header
            beanWriter.writeHeader(header);

            //Set data
            for (Employee emp : employees) {
                beanWriter.write(emp, header, processors);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

We can also refer to the official super-csv documentation, which describes how to write using map, list, partial read/write, and so on.

Test Spring Boot to Generate and Export CSV

1. Start the Spring Boot Application by running spring-boot:run or by running main class.

2. Generate CSV

   Use GET method with end point http://localhost:8080/csv which will generate and download the CSV file.

3. Download/Export CSV File

   After using the above rest end point, the following CSV file will be generated.

Download Source Code

The full source code for this article can be found below.

1. Download it here - Spring Boot Export Data To CSV File Example