MyBatis의 ResultHandler를 통해 POI SXSSF로 대용량 엑셀파일 만들기

목차

MyBatis의 ResultHandler를 통해 POI SXSSF로 대용량 엑셀파일 만들기

개발환경

• SpringBoot 2.6.2
• JAVA 1.8
• Gradle 7.3.2
• IntelliJ

정산 데이터를 엑셀파일로 만드는 과정에서 적은 건수를 만들때는 이슈가 없었지만 몇십만 건 또는 몇백만건의 데이터를 한번에 호출하게 되면서 OOM을 만나게 되어 엑셀파일로 저장을 할 수가 없었습니다. MyBatis를 사용하고 있다면 MyBatis에서 제공하는 ResultHandler를 이용하면 몇백만건의 데이터를 한번에 호출하지 않고 레코드별로 다룰수 있습니다.
https://mybatis.org/mybatis-3/ko/java-api.html#SqlSession (아래 ResultHandler 참고)
다음은 가지고 온 데이터의 저장은 Apache POI SXSSF 방식을 이용하여 엑셀파일에 저장하였습니다. SXSSF는 Streaming방식으로 데이터를 메모리에 계속 가지고 있지 않고 임시 파일에 기록한 후 메모리를 비워내는 방식입니다. 임시파일은 기본 경로는 리눅스 서버 기준으로 /var/tmp/poi??? 맥북기준 터미널에서 echo $TMPDIR를 쳐서 임시디렉토리 경로로 들어가서 poi??? 디렉토리 들어가서 확인해보면 됩니다. 임시파일 용량은 데이터 압축옵션을 안주었을 경우 100만건당 2G가 정도였고 압축옵션을 주었을 경우 256M가 정도였습니다. 압축하면 엑셀만들어지는 속도가 느릴것 것같았지만 거의 차이 없었습니다. 서버 기준으로 용량 문제가 발생할수 있기 때문에 임시파일이 만들어지는 경로도 지정하였습니다.
https://poi.apache.org/components/spreadsheet/

POI 쓰기 확인

HSSF	엑셀 이전버젼(.xls)	65535라인까지 가능
XSSF	엑셀 2007 이상버젼(.xls)	65535라인 이상 가능
SXSSF	XSSF의 Streaming Usermodel API	65535라인 이상 가능

엑셀 시트 최대 행수 확인

xls	65535 행
xlsx	1,048,576 행

MyBatis의 ResultHandler 셋팅

TestDao

@RequiredArgsConstructor
@Repository
public class TestDAOImpl implements TestDAO {

    private final SqlSessionTemplate sqlSessionTemplate;

    @Override
    public boolean createExcelByTmpTableMap(TmpTableSchCmd tmpTableSchCmd, TestExcelHandler testExcelHandler) {
        sqlSessionTemplate.select("com.sample.batch.dao.TestDAO.selectMapOfTmpTable", tmpTableSchCmd, testExcelHandler);
        testExcelHandler.createExcelByTmpFile();
        return testExcelHandler.isSuccess();
    }
}

mybatis sqlsession

우선 MyBatis에서 ResultHandler를 사용하기 위해서 sqlsessionTemplate의 select함수에 들어가 살펴보면 위와 같이 ResultHandler라는 것을 매개변수 받는 것을 확인 할수 있습니다. 따라서 우리는 ResultHandler를 구현한 객체를 인수로 전달하면 됩니다. (친절히 주석으로 ResultHandler를 사용하여 키와 파라미터 문에 매핑된 단일 행을 조회합니다) 여기서 한번더 ResultHandler의 클래스도 따라 들어가 보겠습니다.

interface로 선언되어있고 generic 타입으로 되어있는걸 보니 MyBatis에 resultType에 셋팅 된 객체가 리턴될 것으로 예상할수 있습니다. 그러면 ResultHandler를 구현하면 MyBatis에서 ResultHandler부분의 역할은 끝입니다

즉 select함수에 ResultHandler를 구현한 class를 넣어주면 handlerResult에 resultType의 object가 리턴됩니다

@Slf4j
public abstract class ExcelResultHandler implements ResultHandler {

    protected SXSSFWorkbook sxssfWorkbook;
    protected SXSSFSheet sxssfSheet;

    // row = xls:65,535 xlsx:1,048,576
    protected final int MAX_SHEET_ROW = 1000000;

    private FlushControl flushControl;
    protected int sheetRowCnt = 1;

    protected ExcelFileInfo excelFileInfo;


    public ExcelResultHandler(ExcelFileInfo excelFileInfo, FlushControl flushControl) {
        this.excelFileInfo = excelFileInfo;
        this.flushControl = flushControl;
        // 0. 워크북 생성
        this.sxssfWorkbook = new SXSSFWorkbook(flushControl.getRowAccessWindowSize()); // rowAccessWindowSize개의 로우만 메모리 보유, 나머지 디스크로 내보낸다.
        sxssfWorkbook.setCompressTempFiles(true);                    // 임시파일 압축여부
    }
 ... 중간 생략
 
    /**
     * rowCnt는 1부터 시작함
     */
    public abstract void createExcelBody(int rowCnt, Object dbData);

    @Override
    public void handleResult(ResultContext resultContext) {
        createExcelBody(resultContext.getResultCount(), resultContext.getResultObject());
    }

ResultHandler를 바로 class에 받아서 구현해줘도 되지만 abstract로 받아서 기본적인 공통 부분 (POI 워크북생성, 시트생성, 헤더생성) 등을 공통으로 사용할수 있도록 하였고 @Override되어있는 handlerResult에 createExcelBody의 함수만 추상클래스로 하여 상속받는 class가 구현하게 하였습니다.

@Slf4j
public class TestExcelHandler extends ExcelResultHandler {

    public TestExcelHandler(ExcelFileInfo excelFileInfo, FlushControl flushControl) {
        super(excelFileInfo, flushControl);
    }

    @Override
    public void createExcelBody(int rowCnt, Object dbData) {
        //워크북생성 -> 시트생성 -> 쉘 타이틀생성
        if(rowCnt % MAX_SHEET_ROW == 1) {
            createSXSSFSheet((rowCnt/MAX_SHEET_ROW)+1);
            //HashMap 요부분은 resultType으로 casting하세요 저는 map
            createCellTitle((HashMap<String, Object>)dbData);
            //새로운 시트의 ROW는 1부터 시작
            sheetRowCnt = 1;
        }
        //ROW 생성
        createDataCell((HashMap<String, Object>)dbData);

        //...중간중간에 flush
        flushMem(rowCnt);
    }

}

POI 임시파일 용량 압축 및 경로 수정

// 0. 워크북 생성
this.sxssfWorkbook = new SXSSFWorkbook(flushControl.getRowAccessWindowSize()); // rowAccessWindowSize개의 로우만 메모리 보유, 나머지 디스크로 내보낸다.
sxssfWorkbook.setCompressTempFiles(true);                    // 임시파일 압축여부

POI 임시파일의 압축은 setCompressTempFiles(true)로 설정하면 gz으로 압축이 됩니다

뭐 이것도  함수 따라가다 보면 그렇게 정의되어 있습니다 ^^;;

임시파일경로는 new SXSSFWorkbook의 클래스를 따라서 추적하다보면 아래와 같이 TempFIle.createTempFile 이라는 함수를 찾을 수 있습니다.

TempFIle.createTempFile을 또 쫓아가보면 아래와 같으며

strategy를 쫓아가보면 아래와 같이 기본으로 DefaultTempFileCreationStrategy를 구현한 클래스네요

DefaultTempFileCreationStrategy를 보면 생성자에 파일의 디렉토리가 보입니다

TempFile의 그 아래를 보면 strategy를 setTempFileCreationStrategy(TempFileCreationStrategy strategy)가 있네요

이제 퍼즐을 맞춰 보겠습니다.

TempFile의 .setTempFileCreationStrategy 함수에 TempFileCreationStrategy를 구현한 클래스 DefaultTempFileCreationStrategy를 넣어주면 되겠네요 따라서 DefaultTempFileCreationStrategy를 생성할때 File Dir를 내가 생성해 지정해주고 DefaultTempFileCreationStrategy를 생성해서 set해주면 해결 되겠네요

/**
* org.apache.poi tmp파일 경로 수정
*/
private void setTmpPath() {
	TempFile.setTempFileCreationStrategy(new DefaultTempFileCreationStrategy(new File(excelProperties.getTmpDirPath())));
}

이제 setTmpPath함수를 POI 워크북을 생성하기전에 미리 실행을 하면 임시파일은 내가 지정한 경로에 만들어집니다

POI SXSSF 자동 or 수동 flush

서두에서 말했듯이 SXSSF는 Streaming방식으로 데이터를 메모리에 계속 가지고 있지 않고 설정된 값을 넘으면 메모리에 들고있던 데이터를 임시 파일에 기록한 후 메모리를 비워내는 방식입니다. 이렇게 반복하여 마지막까지 처리를 하고 임시파일을 마지막에는 설정한 엑셀파일로 만들어줍니다. 메모리를 비워낼때 자동으로 셋팅을 할수 있고 아니면 수동으로 설정할수 있습니다.

	//자동 생성할때 지정한 값(100)에 도달하면 flush
	this.sxssfWorkbook = new SXSSFWorkbook(100);


	//수동일때는 -1이면 무제한 액세스이고 중간중간에 수동으로 flush해야합니다.
	this.sxssfWorkbook = new SXSSFWorkbook(-1);

    /**
     *  rowAccessWindowSize -1이면 수동 flush작동
     */
    protected void flushMem(int rowCnt) {
        if(flushControl.getRowAccessWindowSize() == -1 && rowCnt % flushControl.getManualFlushSize() == 1) {
            try {
            	//100 쌓이면 flush
                ((SXSSFSheet)sxssfSheet).flushRows(100);
                log.info("[Excel] flushMem {}", rowCnt);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

위의 내용은 https://poi.apache.org/components/spreadsheet/how-to.html#sxssf 여기에 잘 나와있습니다

 

테스트로 만들어본 소스

깃헙 : https://github.com/moyanada/bigsize_excel_sample