Spring boot CSV 다운로드

Spec

• Java 11
• Spring Boot 2.5.4
• Spring Data JPA
• H2 Database
• Lombok
• IDE : Intellij

Spring boot 초기 셋팅

• https://start.spring.io 에서 다음과 같이 설정 후 generate 클릭
  

Intellij project open

• csv.zip 파일을 압축 풀고 intellij 에서 프로젝트 open 하기
  

CSV library dependency

• csv 관련 라이브러리가 다양하게 존재하지만 여기서는 apache의 commons-csv을 사용하기 위해 아래의 build.gradle 에 추가
  ~(ProjectFolder)/csv/build.gradle

  implementation group: 'org.apache.commons', name: 'commons-csv', version: '1.9.0'
  

application.properties 설정

• h2 데이터베이스로 테스트하기 위해 설정
  ~(ProjectFolder)/csv/src/main/resources/application.properties

  spring.datasource.url=jdbc:h2:mem:testdb
  spring.datasource.driverClassName=org.h2.Driver
  spring.datasource.username=sa
  spring.datasource.password=
  spring.jpa.properties.hibernate.show_sql=true
  spring.jpa.properties.hibernate.format_sql=true
  spring.h2.console.enabled=true
  

학생(Student) 엔티티 만들기

~(ProjectFolder)/csv/src/main/java/com/tutorial/csv/student/domain/Student.java

package com.tutorial.csv.student.domain;

import lombok.Getter;
import lombok.Setter;

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

@Getter
@Setter
@Entity
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private int age;
    private String className;
    private int mathScore;
    private int koreanScore;
    private int englishScore;
    private int scienceScore;

    public static Student of(String name, int age, String className, int mathScore, int koreanScore, int englishScore, int scienceScore) {
        Student student = new Student();
        student.name = name;
        student.age = age;
        student.className = className;
        student.mathScore = mathScore;
        student.koreanScore = koreanScore;
        student.englishScore = englishScore;
        student.scienceScore = scienceScore;
        return student;
    }
}

학생(Student) 레파지토리 만들기

~(ProjectFolder)/csv/src/main/java/com/tutorial/csv/student/domain/StudentRepository.java

package com.tutorial.csv.student.domain;

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

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
}

학생(Student) Parameter/Dto 만들기

~(ProjectFolder)/csv/src/main/java/com/tutorial/csv/student/service/StudentParameter.java

package com.tutorial.csv.student.service;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class StudentParameter {
    private String name;
    private int age;
    private String className;
    private int mathScore;
    private int koreanScore;
    private int englishScore;
    private int scienceScore;
}

~(ProjectFolder)/csv/src/main/java/com/tutorial/csv/student/service/StudentDto.java

package com.tutorial.csv.student.service;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class StudentDto {
    private Long id;
    private String name;
    private int age;
    private String className;
    private int mathScore;
    private int koreanScore;
    private int englishScore;
    private int scienceScore;
}

학생(Student) 서비스 만들기

~(ProjectFolder)/csv/src/main/java/com/tutorial/csv/student/service/StudentService.java

package com.tutorial.csv.student.service;

import com.tutorial.csv.student.domain.Student;
import com.tutorial.csv.student.domain.StudentRepository;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.io.Writer;

@Service
public class StudentService {

    private final StudentRepository studentRepository;

    public StudentService(StudentRepository studentRepository) {
        this.studentRepository = studentRepository;
    }

    @Transactional
    public StudentDto save(StudentParameter parameter) {
        Student student = Student.of(parameter.getName(), parameter.getAge(), parameter.getClassName(), parameter.getMathScore(), parameter.getKoreanScore(), parameter.getEnglishScore(), parameter.getScienceScore());
        Student savedStudent = studentRepository.save(student);
        return this.toStudentDto(savedStudent);
    }

    @Transactional(readOnly = true)
    public Page<StudentDto> page(Pageable pageable) {
        Page<Student> page = studentRepository.findAll(pageable);
        return page.map(this::toStudentDto);
    }

    @Transactional(readOnly = true)
    public void writeStudentDtoToCsv(Writer writer) throws IOException {
        CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT);
        csvPrinter.printRecord("ID", "이름", "클래스명", "수학점수", "국어점수", "영어점수", "과학점수");

// 굳이 페이징을 하지 않아도 된다. 이건 예제이고 참고용이다.
        Pageable pageable = PageRequest.of(0, 10);
        while (true) {
            Page<StudentDto> page = this.page(pageable);

            page.getContent().forEach(dto -> {
                try {
                    csvPrinter.printRecord(
                            dto.getId(),
                            dto.getName(),
                            dto.getClassName(),
                            dto.getMathScore(),
                            dto.getKoreanScore(),
                            dto.getEnglishScore(),
                            dto.getScienceScore()
                    );
                } catch (IOException e) {
                    e.printStackTrace();
                }
            });

            if (!page.hasNext()) {
                break;
            }
            pageable = page.nextPageable();
        }
    }

    private StudentDto toStudentDto(Student student) {
        StudentDto dto = new StudentDto();
        dto.setId(student.getId());
        dto.setName(student.getName());
        dto.setAge(student.getAge());
        dto.setClassName(student.getClassName());
        dto.setMathScore(student.getMathScore());
        dto.setKoreanScore(student.getKoreanScore());
        dto.setEnglishScore(student.getEnglishScore());
        dto.setScienceScore(student.getScienceScore());
        return dto;
    }

}

학생(Student) 컨트롤러 만들기

~(ProjectFolder)/csv/src/main/java/com/tutorial/csv/student/StudentController.java

package com.tutorial.csv.student;

import com.tutorial.csv.student.domain.Student;
import com.tutorial.csv.student.service.StudentDto;
import com.tutorial.csv.student.service.StudentParameter;
import com.tutorial.csv.student.service.StudentService;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.web.PageableDefault;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.time.LocalDate;

@RestController
public class StudentController {
    private final StudentService studentService;

    public StudentController(StudentService studentService) {
        this.studentService = studentService;
    }

    @PostMapping("/students")
    public StudentDto save(@RequestBody StudentParameter parameter) {
        return studentService.save(parameter);
    }

    @GetMapping("/students")
    public Page<StudentDto> page(@PageableDefault(size = 10, sort = "id", direction = Sort.Direction.DESC) Pageable pageable) {
        return studentService.page(pageable);
    }

    @GetMapping(value = "/students", produces = "text/csv")
    public void export(HttpServletResponse response) throws IOException {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/csv; charset=UTF-8");

        String exportFileName = "students-" + LocalDate.now().toString() + ".csv";
        response.setHeader("Content-disposition", "attachment;filename=" + exportFileName);

        studentService.writeStudentDtoToCsv(response.getWriter());
    }
}

테스트

학생 추가

POST http://localhost:8080/students
Content-Type: application/json

{
  "name": "홍길동",
  "age": 17,
  "className": "활빈당",
  "mathScore": 80,
  "koreanScore": 90,
  "englishScore": 50,
  "scienceScore": 84
}

학생 목록 조회

GET http://localhost:8080/students

학생 목록 csv 다운로드

GET http://localhost:8080/students
Accept: text/csv

끗