[SpringBoot][MongoDB]CSV 파일 다운로드(opencsv 사용)

opencsv

1. 우선 Gradle을 통해 필요한 의존성을 설정해야 합니다. 

implementation 'com.opencsv:opencsv:5.5.1'

---

2. 다음으로, MongoDB에서 데이터를 조회하여 CSV 파일로 변환하는 메서드를 작성해야 합니다. 위 코드에서 exportCsv 메서드는 MongoDB에서 데이터를 페이지별로 조회하여 CSV 파일로 내보내는 역할을 합니다.

public void exportCsv(HttpServletResponse res, Class<?> entityClass) {

        int pageSize = 50000; // 페이지 크기를 조절할 수 있음
        try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(res.getOutputStream(), StandardCharsets.UTF_8))) {
            // Set HTTP response headers
            res.setContentType("text/csv; charset=UTF-8");
            res.setHeader("Content-Disposition", "attachment; filename=\"export.csv\"");
            // Get fields
            Field[] fields = entityClass.getDeclaredFields();
            String[] headers = Arrays.stream(fields)
                    .map(Field::getName)
                    .toArray(String[]::new);
            writer.writeNext(headers); // Write headers
            // Paginate data and write to CSV
            int page = 0;
            while (true) {
                Query query = new Query().limit(pageSize).skip(page * pageSize);
                List<?> entities = mongoTemplate.find(query, entityClass);

                if (entities.isEmpty()) break; // No more data

                // Write lines to CSV
                for (Object entity : entities) {
                    String[] csvLine = Arrays.stream(fields)
                            .map(field -> {
                                field.setAccessible(true);
                                try {
                                    Object value = field.get(entity);
                                    return (value != null) ? value.toString() : "";
                                } catch (IllegalAccessException e) {
                                    throw new RuntimeException("Failed to export CSV", e);
                                }
                            })
                            .toArray(String[]::new);
                    writer.writeNext(csvLine);
                }
                page++;
                System.out.println(page);
                if (page >= 20) break; // Limit export to 100 pages
            }
        } catch (IOException e) {
            throw new RuntimeException("Failed to export CSV", e);
        }
    }

entityClass.getDeclaredFields(): 리플렉션을 사용하여 주어진 엔티티 클래스의 필드를 검색합니다. 리플렉션은 프로그램이 자신의 구조를 검사하고 수정할 수 있는 기능을 의미합니다. 여기서는 엔티티 클래스의 필드를 동적으로 검색하여 CSV 파일의 헤더를 생성합니다.

MongoDB 페이징 데이터 가져오기:
mongoTemplate.find(query, entityClass): MongoDB에서 쿼리를 실행하여 주어진 엔티티 클래스와 일치하는 데이터를 가져옵니다. 이 코드에서는 MongoDB 페이징 쿼리를 사용하여 데이터를 페이지별로 가져옵니다.

CSV 파일 작성:
CSVWriter: opencsv 라이브러리를 사용하여 CSV 파일을 작성합니다.
writer.writeNext(headers): CSV 파일의 첫 번째 줄에는 열 제목(헤더)을 작성합니다.
writer.writeNext(csvLine): 각 페이지의 데이터를 CSV 파일에 쓰기 위해 필드 값을 CSV 줄로 작성합니다.

---

TEST

100만건 결과:
기존 100.2초에서 67.3로 감소했습니다.

---

코드 모듈화 해서 재사용성 높이기!

   public void exportCsv(HttpServletResponse res, Class<?> entityClass) {
        int pageSize = 50000; // 페이지 크기를 조절할 수 있음
        try {
            // Set HTTP response headers
            res.setContentType("text/csv; charset=UTF-8");
            res.setHeader("Content-Disposition", "attachment; filename=\"export.csv\"");

            try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(res.getOutputStream(), StandardCharsets.UTF_8))) {
                // Write headers
                writeHeaders(writer, entityClass);

                // Write data
                writeData(writer, entityClass, pageSize);
            }
        } catch (IOException e) {
            throw new RuntimeException("Failed to export CSV", e);
        }
    }

 

exportCsv() 메서드: 이 메서드는 응답이 CSV 파일 첨부임을 지정하여 HTTP 응답 헤더를 설정합니다. 그런 다음 'CSVWriter'를 초기화하여 응답 출력 스트림에 데이터를 씁니다.

 

 private void writeHeaders(CSVWriter writer, Class<?> entityClass) {
        Field[] fields = entityClass.getDeclaredFields();
        String[] headers = Arrays.stream(fields)
                .map(Field::getName)
                .toArray(String[]::new);
        writer.writeNext(headers);
    }

writeHeaders() 메소드: 이 메소드는 리플렉션을 사용하여 제공된 엔터티 클래스에서 필드 이름을 추출하고 이를 CSV 파일의 헤더 행으로 씁니다.

 

   private void writeData(CSVWriter writer, Class<?> entityClass, int pageSize) {
        int page = 0;
        while (true) {
            Query query = new Query().limit(pageSize).skip(page * pageSize);
            List<?> entities = mongoTemplate.find(query, entityClass);

            if (entities.isEmpty()) break; // No more data

            // Write lines to CSV
            for (Object entity : entities) {
                String[] csvLine = getCsvLine(entity, entityClass.getDeclaredFields());
                writer.writeNext(csvLine);
            }
            page++;
            System.out.println(page);
            if (page >= 20) break; // Limit export to 100 pages
        }
    }

writeData() 메소드: 이 메소드는 페이지 매김 기능이 있는 Spring Data MongoDB의 mongoTemplate.find() 메소드를 사용하여 MongoDB에서 데이터를 검색합니다. 데이터의 각 페이지를 반복하고 엔터티를 CSV 줄로 변환한 다음 CSV 파일에 씁니다.

 

    private String[] getCsvLine(Object entity, Field[] fields) {
        return Arrays.stream(fields)
                .map(field -> {
                    field.setAccessible(true);
                    try {
                        Object value = field.get(entity);
                        return (value != null) ? value.toString() : "";
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException("Failed to export CSV", e);
                    }
                })
                .toArray(String[]::new);
    }

getCsvLine() 메서드: 이 메서드는 해당 필드에서 값을 추출하여 엔터티 개체를 CSV 줄로 변환합니다.

---

전체 코드

  public void exportCsv(HttpServletResponse res, Class<?> entityClass) {
        int pageSize = 50000; // 페이지 크기를 조절할 수 있음
        try {
            // Set HTTP response headers
            res.setContentType("text/csv; charset=UTF-8");
            res.setHeader("Content-Disposition", "attachment; filename=\"export.csv\"");

            try (CSVWriter writer = new CSVWriter(new OutputStreamWriter(res.getOutputStream(), StandardCharsets.UTF_8))) {
                // Write headers
                writeHeaders(writer, entityClass);

                // Write data
                writeData(writer, entityClass, pageSize);
            }
        } catch (IOException e) {
            throw new RuntimeException("Failed to export CSV", e);
        }
    }

    private void writeHeaders(CSVWriter writer, Class<?> entityClass) {
        Field[] fields = entityClass.getDeclaredFields();
        String[] headers = Arrays.stream(fields)
                .map(Field::getName)
                .toArray(String[]::new);
        writer.writeNext(headers);
    }

    private void writeData(CSVWriter writer, Class<?> entityClass, int pageSize) {
        int page = 0;
        while (true) {
            Query query = new Query().limit(pageSize).skip(page * pageSize);
            List<?> entities = mongoTemplate.find(query, entityClass);

            if (entities.isEmpty()) break; // No more data

            // Write lines to CSV
            for (Object entity : entities) {
                String[] csvLine = getCsvLine(entity, entityClass.getDeclaredFields());
                writer.writeNext(csvLine);
            }
            page++;
            System.out.println(page);
            if (page >= 20) break; // Limit export to 100 pages
        }
    }

    private String[] getCsvLine(Object entity, Field[] fields) {
        return Arrays.stream(fields)
                .map(field -> {
                    field.setAccessible(true);
                    try {
                        Object value = field.get(entity);
                        return (value != null) ? value.toString() : "";
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException("Failed to export CSV", e);
                    }
                })
                .toArray(String[]::new);
    }