[Spring Boot/MyBatis] ResultHandler를 활용한 대용량 데이터 엑셀 다운로드

intelliJ로 구현하였다.

 

여러 블로그 참고하였음.

 

데이터 생성은 아래 주소 참고.

(https://hsdevelopment.tistory.com/841)

 

근데 데이터를 약 7만 3천 건 정도만 생성하였고, 그냥 진행하였다.

(100만 건도 잘 되겠지..?)

 

프로젝트 전체 구조는 아래와 같다.(맨 아래에 git 주소와 프로젝트 전체 소스도 첨부하였음)

 

 

DbConfig.java

package com.excel.exceldown.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan(value = "com.excel.exceldown", sqlSessionFactoryRef = "SqlSessionFactory")
public class DbConfig {

    @Value("${spring.datasource.mapper-locations}")
    String mPath;

    // mybatis 설정 파일을 따로 작성해서 임포트할 예정 - snake_case -> camelCase 를 위함
    @Value("${mybatis.config-location}")
    String mybatisConfigPath;

    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory SqlSessionFactory(@Qualifier("dataSource") DataSource DataSource, ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(DataSource);
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources(mPath));
        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource(mybatisConfigPath));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "SessionTemplate")
    public SqlSessionTemplate SqlSessionTemplate(@Qualifier("SqlSessionFactory") SqlSessionFactory firstSqlSessionFactory) {
        return new SqlSessionTemplate(firstSqlSessionFactory);
    }
}

 

 

 

ExcelDownloadController.java

package com.excel.exceldown.controller;

import com.excel.exceldown.domain.PersonData;
import com.excel.exceldown.service.ExcelDownloadService;
import jakarta.servlet.http.HttpServletResponse;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RequestMapping("/excelDownload")
@Controller
public class ExcelDownloadController {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private ExcelDownloadService excelDownloadService;

    @GetMapping
    public List<PersonData> excelDownload(HttpServletResponse response){

        logger.info("■■■■■■■■■■■■■■■■■■■■■■■");
        logger.info("ExcelDownload START");
        logger.info("■■■■■■■■■■■■■■■■■■■■■■■");

        return excelDownloadService.download(response);
    }
}

 

 

 

ExcelDownloadService.java

package com.excel.exceldown.service;

import com.excel.exceldown.domain.PersonData;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.OutputStream;
import java.util.List;

@Service
public class ExcelDownloadService {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    public List<PersonData> download(HttpServletResponse response) {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다.
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sheet = wb.createSheet();

        try {
            sqlSession.select("com.excel.exceldown.mapper.UserMapper.excelDownloadSelectId"
                    , new ResultHandler<PersonData>() {
                @Override
                public void handleResult(ResultContext<? extends PersonData> resultContext) {
                    PersonData vo = resultContext.getResultObject();
                    Row row = sheet.createRow(resultContext.getResultCount() - 1);
                    Cell cell = null;
                    cell = row.createCell(0);
                    cell.setCellValue(vo.getId());
                    cell = row.createCell(1);
                    cell.setCellValue(vo.getName());
                    cell = row.createCell(2);
                    cell.setCellValue(vo.getAge());
                }
            });

            response.setHeader("Set-Cookie", "fileDownload=true; path=/");
            response.setHeader("Content-Disposition", String.format("attachment; filename=\"test.xlsx\""));
            wb.write(response.getOutputStream());

        } catch(Exception e) {

            response.setHeader("Set-Cookie", "fileDownload=false; path=/");
            response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.setHeader("Content-Type","text/html; charset=utf-8");

            OutputStream out = null;
            try {
                out = response.getOutputStream();
                byte[] data = new String("fail..").getBytes();
                out.write(data, 0, data.length);
            } catch(Exception ignore) {
                ignore.printStackTrace();
            } finally {
                if(out != null) try { out.close(); } catch(Exception ignore) {}
            }

        } finally {
            sqlSession.close();

            // 디스크 적었던 임시파일을 제거합니다.
            wb.dispose();
            try {
                wb.close();
            } catch(Exception ignore) {

            }
        }
        return null;
    }
}

 

 

 

PersonData.java

package com.excel.exceldown.domain;

import lombok.Data;

@Data
public class PersonData {

    private int id;
    private String name;
    private int age;
    private String gender;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }
}

 

 

 

UserMapper.java

package com.excel.exceldown.mapper;

import com.excel.exceldown.domain.PersonData;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    void excelDownloadSelectId();

}

 

 

 

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.excel.exceldown.mapper.UserMapper">
    <select id="excelDownloadSelectId"
            parameterType="java.util.Map"
            resultType="com.excel.exceldown.domain.PersonData">
        /* excelDownloadMapper.excelDownloadSelectId */
        select
           id
         , name
         , age
         , gender
        from person
    </select>
</mapper>

 

 

 

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <settings>
        <!-- DB 조회결과 snake_case -> camelCase 변환 -->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
</configuration>

 

 

 

index.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<ul>
    <button type="button" onclick="location.href='/excelDownload'">엑셀다운로드</button>
</ul>
</body>
</html>

 

 

 

application.yml

spring:
  datasource:
    username: root
    password: 1234
    mapper-locations: classpath:/mapper/**/*.xml
    jdbc-url: jdbc:mysql://localhost:3306/tempdb?&serverTimezone=UTC&autoReconnect=true&allowMultiQueries=true&characterEncoding=UTF-8
    driver: com.mysql.cj.jdbc.Driver

mybatis:
  configuration:
    map-underscore-to-camel-case: true
  config-location: classpath:/mybatis/mybatis-config.xml

 

 

 

 

 

아래에 git 주소와 프로젝트 소스를 압축한 폴더 첨부.

 

https://github.com/parkhaeseon/excel

 

 

exceldown.zip

0.18MB