Export Data Into The CSV File in Spring Boot

In this example, we can export data into a CSV file from the H2 Database. To achieve this task we can use the Apache Commons CSV library.

Configuring Apache Common CSV library

<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.8</version>
</dependency>

In this example of the application, we can create one table on the database and insert some records on that table i.e., student. We will create one Java Bean class i.e., Student.java which is annotated with @Entity annotation to create a table on the database. We will create one repository interface for the persistence operation of that table i.e.,StudentRepoCSV.java. We will create a service interface i.e., StudentService.java and create one class implementation class of this service interface i.e.,StudentServiceImpl.java.We will create one utility class for generating the CSV file i.e.,CsvFileGenerator.java which contains code for creating the CSV file and writing data that will be getting from the database’s table. We will create one controller for generating a CSV file of the database table’s records i.e.,StudentController.java with a mapping URL that converts data into a CSV file. In this controller class, we will auto wiring service interface and CSV generating utility class. We will call the service’s method that will get all records of the database table. We will call the CSV file generating utility class’s method for writing the record that we will get from the service into the CSV file. We will one view page i.e., index.html.Then we will export that record into the CSV file

Table of content

1.  Keep eclipse IDE ready
2. Create a Spring Boot Starter Project for this example of  the exporting data to a CSV file(Select Spring Web, Spring Data JPA, and H2 Database dependencies)
3. Define Database Connection in the application.properties file for the example of exporting data to a CSV file
4. Create Entity class
5. Create a Repository
6. Create Service
7.  Create a CSV File Generator class
8.  Create a Controller class
9.  Create a view page on the static folder for this example of exporting data to a CSV file
10. Create CommandLineRunner on SpringBootApplication class
11. Run the Project

1.  Keep eclipse IDE ready
2. Create a Spring Boot Starter Project for this example of the exporting data to CSV file(Select Spring Web, Spring Data JPA, and H2 Database dependencies)

Maven Dependency

<?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.6.3</version>
		<relativePath />
		<!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>Export_CSV_Spring_Boot_Example</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>Export_CSV_Spring_Boot_Example</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</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>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-csv</artifactId>
			<version>1.8</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

3. Define Database Connection in the application.properties file for the example of exporting data to a CSV file

#H2 Database

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto= update
server.port=8888
server.servlet.context-path= /demo

4. Create Entity class
Student.java

package com.example.entity;

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

@Entity

public class Student {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private long id;
  private String studentName;
  private String email;
  private String mobileNo;

  public Student() {
  }

  public long getId() {
    return id;
  }

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

  public String getStudentName() {
    return studentName;
  }

  public void setStudentName(String studentName) {
    this.studentName = studentName;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }

  public String getMobileNo() {
    return mobileNo;
  }

  public void setMobileNo(String mobileNo) {
    this.mobileNo = mobileNo;
  }
}

5. Create a Repository
StudentRepository.java

package com.example.repo;

import org.springframework.data.jpa.repository.JpaRepository;
import com.example.entity.Student;
public interface StudentRepoCSV extends JpaRepository < Student, Long > {
}

6. Create Service
StudentService.java

package com.example.service;

import java.util.List;
import com.example.entity.Student;

public interface StudentService {
  void addStudent(Student student);
  List < Student > getStudentOfList();
}

StudentServiceImpl.java

package com.example.service;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.entity.Student;
import com.example.repo.StudentRepoCSV;

@Service
public class StudentServiceImpl implements StudentService {

  @Autowired
  StudentRepoCSV studentRepo;

  @Override
  public void addStudent(Student student) {
    studentRepo.save(student);
  }

  @Override
  public List <Student> getStudentOfList() {
    return studentRepo.findAll();
  }
}

7. Create a CSV File Generator class
CsvFileGenerator.java

package com.example.util;

import java.io.IOException;
import java.io.Writer;
import java.util.List;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.springframework.stereotype.Component;
import com.example.entity.Student;

@Component
public class CsvFileGenerator {
public void writeStudentsToCsv(List<Student> students, Writer writer) {
try {

CSVPrinter printer = new CSVPrinter(writer, CSVFormat.DEFAULT);
for (Student student : students) {
printer.printRecord(student.getId(), student.getStudentName(), student.getEmail(),
student.getMobileNo());
}
} catch (IOException e) {
e.printStackTrace();
}
}
}

8. Create a Controller class
StudentController.java

package com.example.controller;

import java.io.IOException;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import com.example.service.StudentService;
import com.example.util.CsvFileGenerator;

@Controller
public class StudentController {

@Autowired
private StudentService studentService;

@Autowired
private CsvFileGenerator csvGenerator;

  @GetMapping("/export-to-csv")
  public void exportIntoCSV(HttpServletResponse response) throws IOException {
    response.setContentType("text/csv");
    response.addHeader("Content-Disposition", "attachment; filename=\"student.csv\"");
    csvGenerator.writeStudentsToCsv(studentService.getStudentOfList(), response.getWriter());
  }
}

9. Create a view page on the static folder for this example of exporting data to CSV file
index.html

<!DOCTYPE html>

<html>
<head>
<title>Export CSV File</title>
</head>
<body class="container">
<p>Click the button below to export the CSV file.</p>
<a href="http://localhost:8888/demo/export-to-csv" class="btn btn-primary">Download CSV File</a>
</body>
</html>

10. Create CommandLineRunner on SpringBootApplication class

package com.example;

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.example.entity.Student;
import com.example.service.StudentService;

@SpringBootApplication
public class ExportCSVSpringBootExampleApplication implements CommandLineRunner {

  @Autowired
  private StudentService studentService;
  public static void main(String[] args) {
    SpringApplication.run(ExportCSVSpringBootExampleApplication.class, args);
  }

  @Override
  public void run(String...a) {
    for (int i = 0; i <= 10; i++) {
      Student student = new Student();
      student.setStudentName("Student Name");
      student.setEmail("student@mail.com");
      student.setMobileNo("XXXXXXXXXX");
      studentService.addStudent(student);
    }
  }
}

11. Run the Project

RightClick on SpringBootApplication class(ExportCSVSpringBootExampleApplication) then click on Run as Java Application.To verify the database through the console. We can type this URL on the browser "http://localhost:8888/demo/h2-console/" for testing whether the table is created on the database and data is inserted properly.

We can type this URL "http://localhost:8888/demo/" on the browser for checking whether this application export data to a CSV file properly or not.

Click on the Download CSV File link for downloading the CSV file.

1,Student Name,student@mail.com,XXXXXXXXXX

2,Student Name,student@mail.com,XXXXXXXXXX

3,Student Name,student@mail.com,XXXXXXXXXX

4,Student Name,student@mail.com,XXXXXXXXXX

5,Student Name,student@mail.com,XXXXXXXXXX

6,Student Name,student@mail.com,XXXXXXXXXX

7,Student Name,student@mail.com,XXXXXXXXXX

8,Student Name,student@mail.com,XXXXXXXXXX

9,Student Name,student@mail.com,XXXXXXXXXX

10,Student Name,student@mail.com,XXXXXXXXXX

11,Student Name,student@mail.com,XXXXXXXXXX

Conclusion

This example is explained:

   • How to transform data into a CSV file from a database?

   • How to use the Apache Common CSV library?