Spring Boot Download CSV file from Database example

A CSV (comma-separated values) file is a plain text file that contains data which format is described in RFC4180. Each row has a number of fields, separated by commas. Each line should contain the same number of fields throughout the file. In this tutorial, I will show you how to build Spring Boot download CSV file Rest API example that exports data from MySQL Database table using & Apache Commons CSV.

Related Posts:
– Spring Boot: Upload & Read CSV file into MySQL Database | Multipart File
– Spring Boot Multipart File upload example
– How to upload multiple files in Java Spring Boot

Excel file instead: Spring Boot: Download Excel file example

Deployment:
– Deploy Spring Boot App on AWS – Elastic Beanstalk
– Docker Compose: Spring Boot and MySQL example

Spring Boot Download CSV File Rest API

Assume that we have a tutorials table in MySQL database like this:

We’re gonna create a Spring Boot Application that provides APIs for downloading MySQL table data as CSV file.
If you send request to /api/csv/download, the server will return a response with an CSV file tutorials.csv like this:

How to do this?
You need to set the HTTP header:

"Content-disposition" : "attachment; filename=[yourFileName]" 
"Content-Type" : "application/csv"

We’re gonna apply the information above later in this tutorial.

You can also find how to upload CSV File to the Spring Server & store data in MySQL Database in the post:
Spring Boot: Upload & Read CSV file into MySQL Database

Technology

• Java 8
• Spring Boot 2 (with Spring Web MVC)
• Maven 3.6.1
• Apache Commons CSV 1.8

Project Structure

This is the project directory that we’re gonna build:

– CSVHelper provides functions to write to CSV file.

– Tutorial data model class corresponds to entity and table tutorials.
– TutorialRepository is an interface that extends JpaRepository for persisting data.

– CSVService uses CSVHelper and TutorialRepository methods load data to CSV file.

– CSVController calls CSVService methods and export Rest API for downloading CSV files.

– application.properties contains configuration for Spring Data.
– pom.xml for Spring Boot, MySQL connector, Apache Common CSV dependencies.

Setup Spring Boot Download CSV File project

Use Spring web tool or your development tool (Spring Tool Suite, Eclipse, Intellij) to create a Spring Boot project.

Then open pom.xml and add these 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>org.apache.commons</groupId>
	<artifactId>commons-csv</artifactId>
	<version>1.8</version>
</dependency>

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

Configure Spring Datasource, JPA, Hibernate

Under src/main/resources folder, open application.properties and write these lines.

spring.datasource.url= jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username= root
spring.datasource.password= 123456

spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.MySQL5InnoDBDialect

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

• spring.datasource.username & spring.datasource.password properties are the same as your database installation.
• Spring Boot uses Hibernate for JPA implementation, we configure MySQL5InnoDBDialect for MySQL database
• spring.jpa.hibernate.ddl-auto is used for database initialization. We set the value to update value so that a table will be created in the database automatically corresponding to defined data model. Any change to the model will also trigger an update to the table. For production, this property should be validate.

Define Data Model

Our Data model is Tutorial with four fields: id, title, description, published.
In model package, we define Tutorial class.

model/Tutorial.java

package com.bezkoder.spring.files.csv.model;

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

@Entity
@Table(name = "tutorials")
public class Tutorial {

  @Id
  @Column(name = "id")
  private long id;

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

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

  @Column(name = "published")
  private boolean published;

  public Tutorial() {

  }

  public Tutorial(long id, String title, String description, boolean published) {
    this.id = id;
    this.title = title;
    this.description = description;
    this.published = published;
  }

  public long getId() {
    return id;
  }

  public void setId(long id) {
    this.id = id;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle(String title) {
    this.title = title;
  }

  public String getDescription() {
    return description;
  }

  public void setDescription(String description) {
    this.description = description;
  }

  public boolean isPublished() {
    return published;
  }

  public void setPublished(boolean isPublished) {
    this.published = isPublished;
  }

  @Override
  public String toString() {
    return "Tutorial [id=" + id + ", title=" + title + ", desc=" + description + ", published=" + published + "]";
  }
}

– @Entity annotation indicates that the class is a persistent Java class.
– @Table annotation provides the table that maps this entity.
– @Id annotation is for the primary key.
– @Column annotation is used to define the column in database that maps annotated field.

Create Data Repository for working with Database

Let’s create a repository to interact with Tutorials from the database.
In repository package, create TutorialRepository interface that extends JpaRepository.

repository/TutorialRepository.java

package com.bezkoder.spring.files.csv.repository;

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

import com.bezkoder.spring.files.csv.model.Tutorial;

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
}

Now we can use JpaRepository’s methods: save(), findOne(), findById(), findAll(), count(), delete(), deleteById()… without implementing these methods.

More Derived queries at:
JPA Repository query example in Spring Boot

The quantity of rows in CSV file (also tutorials table) could be large, so you may want to get only several at once by modifying this Repository to work with Pagination, the instruction can be found at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable

Custom query with @Query annotation:
Spring JPA @Query example: Custom query in Spring Boot

Implement CSV Helper Class

We’re gonna use Apache Commons CSV classes such as: CSVFormat, CSVPrinter.

Let me summarize the steps for writing to CSV file:

• create a new ByteArrayOutputStream (as stream)
• create a new CSVPrinter with PrintWriter for the stream and CSVFormat
• iterate over tutorials list
• for each Tutorial, create a List<String> data that contains all the fields
• use CsvPrinter.printRecord() with the List above
• call CsvPrinter.flush() to write out the stream

ByteArrayOutputStream out = new ByteArrayOutputStream();
CSVPrinter csvPrinter = new CSVPrinter(new PrintWriter(out), CSVFormat.DEFAULT...);

for (Tutorial tutorial : tutorials) {
  List<String> data = Arrays.asList(
        String.valueOf(tutorial.getId()),
        tutorial.getTitle(),
        tutorial.getDescription(),
        String.valueOf(tutorial.isPublished())
      );

  csvPrinter.printRecord(data);
}

csvPrinter.flush();
return new ByteArrayInputStream(out.toByteArray());

Under helper package, we create CSVHelper class with tutorialsToCSV() method that processes a list of Tutorials, return a ByteArrayInputStream for CSV file later.

Here is full code of helper/CSVHelper.java:

package com.bezkoder.spring.files.csv.helper;

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

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.QuoteMode;

import com.bezkoder.spring.files.csv.model.Tutorial;

public class CSVHelper {

  public static ByteArrayInputStream tutorialsToCSV(List<Tutorial> tutorials) {
    final CSVFormat format = CSVFormat.DEFAULT.withQuoteMode(QuoteMode.MINIMAL);

    try (ByteArrayOutputStream out = new ByteArrayOutputStream();
        CSVPrinter csvPrinter = new CSVPrinter(new PrintWriter(out), format);) {
      for (Tutorial tutorial : tutorials) {
        List<String> data = Arrays.asList(
              String.valueOf(tutorial.getId()),
              tutorial.getTitle(),
              tutorial.getDescription(),
              String.valueOf(tutorial.isPublished())
            );

        csvPrinter.printRecord(data);
      }

      csvPrinter.flush();
      return new ByteArrayInputStream(out.toByteArray());
    } catch (IOException e) {
      throw new RuntimeException("fail to import data to CSV file: " + e.getMessage());
    }
  }
}

Create CSV File Service

CSVService class will be annotated with @Service annotation, it uses CSVHelper and TutorialRepository in load() method which read data from database and return ByteArrayInputStream.

service/CSVService.java

package com.bezkoder.spring.files.csv.service;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.bezkoder.spring.files.csv.helper.CSVHelper;
import com.bezkoder.spring.files.csv.model.Tutorial;
import com.bezkoder.spring.files.csv.repository.TutorialRepository;

@Service
public class CSVService {

  @Autowired
  TutorialRepository repository;
  
  public ByteArrayInputStream load() {
    List<Tutorial> tutorials = repository.findAll();

    ByteArrayInputStream in = CSVHelper.tutorialsToCSV(tutorials);
    return in;
  }
}

Create Controller for CSV File Download

In controller package, we create CSVController class for Rest Apis.
– @CrossOrigin is for configuring allowed origins.
– @Controller annotation indicates that this is a controller.
– @GetMappinggetFile() method.
– We also use @Autowired to inject implementation of CSVService bean to local variable.

controller/CSVController.java

package com.bezkoder.spring.files.csv.controller;

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.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.bezkoder.spring.files.csv.service.CSVService;

@CrossOrigin("http://localhost:8081")
@Controller
@RequestMapping("/api/csv")
public class CSVController {

  @Autowired
  CSVService fileService;
  
  @GetMapping("/download")
  public ResponseEntity<Resource> getFile() {
    String filename = "tutorials.csv";
    InputStreamResource file = new InputStreamResource(fileService.load());

    return ResponseEntity.ok()
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + filename)
        .contentType(MediaType.parseMediaType("application/csv"))
        .body(file);
  }
}

Run the Spring Boot Download CSV File App

Run Spring Boot application with command: mvn spring-boot:run.

Conclusion

Today we’ve built a Rest API using Spring Boot to download CSV file that contains table data in MySQL database.

We also see how to use Apache Common CSV to write data to CSV file, JpaRepository to retrieve items in database table without need of boilerplate code.

More Derived queries at:
JPA Repository query example in Spring Boot

Custom query with @Query annotation:
Spring JPA @Query example: Custom query in Spring Boot

If you want to add Pagination to this Spring project, you can find the instruction at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable

You can also find how to upload CSV File to the Spring Server & store data in MySQL Database in the post:
Spring Boot: Upload & Read CSV file into MySQL Database

Happy learning! See you again.

Further Reading

• Spring Data JPA Reference Documentation
• Apache Commons CSV
• Documentation: Spring Boot + Swagger 3 example (with OpenAPI 3)
• Caching: Spring Boot Redis Cache example

Deployment:

• Deploy Spring Boot App on AWS – Elastic Beanstalk
• Docker Compose: Spring Boot and MySQL example

Source Code

You can find the complete source code for this tutorial on Github.