Download CSV File from SpringBoot RestAPI + MySQL – using Apache Commons CSV + Spring JPA

In the tutorial, we show you how to create a SpringBoot RestAPIs application that uses Spring JPA to get data from MySQL records and uses Apache Commons CSV library to write data to a CSV file.

Related posts:
– Java – How to read/write CSV file with Apache Commons CSV
– How to use Spring JPA MySQL | Spring Boot
– SpringBoot – Upload/Download MultipartFile to FileSystem – Bootstrap 4 + JQuery Ajax

Technologies

– Spring Boot – 2.0.6.RELEASE
– Spring JPA
– MySQL
– Apache Commons CSV – 1.5

Practice

Project Structure ->

Create SpringBoot project

Use SpringToolSuite to create a SpringBoot project with below 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>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
	<groupId>org.apache.commons</groupId>
	<artifactId>commons-csv</artifactId>
	<version>1.5</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Customer Model

– Customer.java ->

package com.ozenero.csvdownload.model;

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 = "customers")
public class Customer{
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;
 
	@Column(name = "firstname")
	private String firstName;
 
	@Column(name = "lastname")
	private String lastName;
 
	protected Customer() {
	}
 
	public Customer(String firstName, String lastName) {
		this.firstName = firstName;
		this.lastName = lastName;
	}
 
	public void setId(Long id) {
		this.id = id;
	}
	
	public Long getId() {
		return this.id;
	}
	
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	
	public String getFirstName() {
		return this.firstName;
	}
	
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	
	public String getLastName() {
		return this.lastName;
	}
	
	@Override
	public String toString() {
		return String.format("Customer[id=%d, firstName='%s', lastName='%s']", id, firstName, lastName);
	}
}

JPA Customer Repository

– CustomerRepository.java ->

package com.ozenero.csvdownload.repository;

import org.springframework.data.repository.CrudRepository;

import com.ozenero.csvdownload.model.Customer;
 
public interface CustomerRepository extends CrudRepository{
}

Implement CSV Writer

– WriteDataToCSV.java ->

package com.ozenero.csvdownload.util;

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 com.ozenero.csvdownload.model.Customer;

public class WriteDataToCSV {

	public static void writeObjectToCSV(PrintWriter writer,List customers) {
		try (
				CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
	                    .withHeader("ID", "FirstName", "LastName"));
		) {
			for (Customer customer : customers) {
				List data = Arrays.asList(
						customer.getId().toString(),
						customer.getFirstName(),
						customer.getLastName()
					);
				
				csvPrinter.printRecord(data);
			}
			csvPrinter.flush();
		} catch (Exception e) {
			System.out.println("Writing CSV error!");
			e.printStackTrace();
		}
	}
}

Implement RestAPI Controller

– CustomerCSVDownloadRestAPI ->

package com.ozenero.csvdownload.controller;

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

import javax.servlet.http.HttpServletResponse;

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 com.ozenero.csvdownload.model.Customer;
import com.ozenero.csvdownload.repository.CustomerRepository;
import com.ozenero.csvdownload.util.WriteDataToCSV;

@RestController
@RequestMapping("/api/customers")
public class CustomerCSVDownloadRestAPI {

	@Autowired
	CustomerRepository customerRepository;

	@GetMapping("/download/customers.csv")
	public void downloadCSV(HttpServletResponse response) throws IOException{
		response.setContentType("text/csv");
		response.setHeader("Content-Disposition", "attachment; file=customers.csv");
		
		List customers = (List) customerRepository.findAll(); 
		WriteDataToCSV.writeObjectToCSV(response.getWriter(), customers);
	}
}

Implement Download View

– Create ViewController.java ->

package com.ozenero.csvdownload.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class ViewController {

	@RequestMapping("/")
	public String home() {
		return "home";
	}
}

– Create .html view home.html ->

<html>
	<head>
		<title>SpringBoot CSV</title>
	</head>
	<body>
		<h3>Download CSV Customers File</h3>
		<div>
			<a href="http://localhost:8080/api/customers/download/customers.csv">Customers .CSV</a>
		</div>
	</body>
</html>

Initial Customers

– In main class, we use CommandLineRunner to init Customer’s records:

package com.ozenero.csvdownload;

import java.util.Arrays;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.ozenero.csvdownload.model.Customer;
import com.ozenero.csvdownload.repository.CustomerRepository;

@SpringBootApplication
public class SpringJpaCsvDownloaderApplication implements CommandLineRunner {

	@Autowired
	CustomerRepository repository;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringJpaCsvDownloaderApplication.class, args);
	}
	
    @Override
    public void run(String... args) throws Exception {
    	
    	if(repository.count() == 0) {
    		// save a list of Customers
    		repository.saveAll(Arrays.asList(new Customer("Jack", "Smith"), 
    										new Customer("Adam", "Johnson"), 
    										new Customer("Kim", "Smith"),
    										new Customer("David", "Williams"), 
    										new Customer("Peter", "Davis")));
    	}

    }	
}

Database Configuration

– application.properties ->

spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=12345
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create-drop

Run & Check Results

Run the SpringBoot project,

-> MySQL’s records:

-> CSV Downloaded File:

SourceCode

SpringJpaCSVDownloader