Spring 4.3 + Mybatis + 대용량 EXCEL 다운로드

스프링프레임워크와 마이바티스를 이용한 대용량엑셀 다운로드

 

1. 프로젝트 구조

 

 

2. Maven pom.xml

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.inface</groupId>
    <artifactId>EXCELDEMO</artifactId>
    <packaging>war</packaging>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <java-version>1.8</java-version>
        <org.springframework-version>4.3.10.RELEASE</org.springframework-version>
        <org.aspectj-version>1.6.10</org.aspectj-version>
        <logback.version>1.1.2</logback.version>
        <org.slf4j-version>1.6.6</org.slf4j-version>
        <jstl.version>1.2</jstl.version>
        <org.apache.fileupload>1.3.1</org.apache.fileupload>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- Jackson -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.9.1</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!-- AspectJ -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>${org.aspectj-version}</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>${org.aspectj-version}</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjtools</artifactId>
            <version>${org.aspectj-version}</version>
        </dependency>

        <!-- @Inject -->
        <dependency>
            <groupId>javax.inject</groupId>
            <artifactId>javax.inject</artifactId>
            <version>1</version>
        </dependency>

        <!-- Servlet -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.1</version>
            <scope>provided</scope>
        </dependency>
        <!-- JSTL -->

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>taglibs</groupId>
            <artifactId>datetime</artifactId>
            <version>1.0.1</version>
        </dependency>
        <dependency>
            <groupId>taglibs</groupId>
            <artifactId>string</artifactId>
            <version>1.1.0</version>
        </dependency>
        <dependency>
            <groupId>taglibs</groupId>
            <artifactId>standard</artifactId>
            <version>1.1.2</version>
        </dependency>

        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
            <scope>provided</scope>
        </dependency>



        <!-- Apache Commons -->
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.2</version>
        </dependency>

        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib-nodep</artifactId>
            <version>3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.9</version>
        </dependency>

        <dependency>
            <groupId>commons-collections</groupId>
            <artifactId>commons-collections</artifactId>
            <version>3.2.1</version>
        </dependency>

        <dependency>
            <groupId>commons-digester</groupId>
            <artifactId>commons-digester</artifactId>
            <version>2.1</version>
        </dependency>

        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>

        <!-- file upload -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>${org.apache.fileupload}</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

        <!-- mysql-connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>

        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.0</version>
        </dependency>

        <!-- Spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${org.springframework-version}</version>
            <exclusions>
                <!-- Exclude Commons Logging in favor of SLF4j -->
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>

        <!-- Logging -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-access</artifactId>
            <version>${logback.version}</version>
        </dependency>

        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>${logback.version}</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>log4j-over-slf4j</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-jcl</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>

        <!-- log4jdbc -->
        <dependency>
            <groupId>org.bgee.log4jdbc-log4j2</groupId>
            <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
            <version>1.16</version>
        </dependency>


        <!-- Test -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.7</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <compilerArgument>-Xlint:all</compilerArgument>
                    <showWarnings>true</showWarnings>
                    <showDeprecation>true</showDeprecation>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <configuration>
                    <mainClass>org.test.int1.Main</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

 

3. Controller

package com.inface.excel.controller;

import com.inface.common.ResultRowDataHandler;
import com.inface.excel.service.ExcelDownloadService;
import com.inface.excel.vo.CommonVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

@Controller
public class ExcelDownloadController {
   private Logger logger = LoggerFactory.getLogger(getClass());

   @Resource
   private ExcelDownloadService excelDownloadService;

   @RequestMapping(value = "/excelDownload.do")
   public void excelDownload(HttpServletResponse response){
      CommonVo commonVo = new CommonVo();
      ResultRowDataHandler resultRowDataHandler = null;
      try {
         resultRowDataHandler = new ResultRowDataHandler(response);
         excelDownloadService.download(commonVo, resultRowDataHandler);
      }catch (Exception e){
         logger.error("ExcelDownload Controller Exception : {}", e);
      }finally {
         if(resultRowDataHandler != null){
            resultRowDataHandler.close();
         }
      }

   }
}

 

4. Service InterFace & Service

 

4-1 Service InterFace

package com.inface.excel.service;

import com.inface.common.ResultRowDataHandler;
import com.inface.excel.vo.CommonVo;


public interface ExcelDownloadServiceInterface {
   public void download(CommonVo commonVo, ResultRowDataHandler resultRowDataHandler);
}

 

4-2 Service

package com.inface.excel.service;

import com.inface.common.ResultRowDataHandler;
import com.inface.excel.dao.ExcelDownloadDao;
import com.inface.excel.vo.CommonVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service
public class ExcelDownloadService implements ExcelDownloadServiceInterface {
   private Logger logger = LoggerFactory.getLogger(getClass());

   @Resource
   private ExcelDownloadDao excelDownloadDao;

   @Override
   public void download(CommonVo commonVo, ResultRowDataHandler resultRowDataHandler) {
      excelDownloadDao.getAttQuery(commonVo, resultRowDataHandler);
   }
}

 

5. Dao Interface & Dao

 

5-1 Dao Interface

package com.inface.excel.dao;

import com.inface.common.ResultRowDataHandler;
import com.inface.excel.vo.CommonVo;

public interface ExcelDownloadDaoInterface {
   public void getAttQuery(CommonVo commonVo, ResultRowDataHandler resultRowDataHandler);
}

 

5-2 Dao

package com.inface.excel.dao;

import com.inface.common.ResultRowDataHandler;
import com.inface.excel.vo.CommonVo;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class ExcelDownloadDao implements ExcelDownloadDaoInterface {
   @Autowired
   private SqlSessionTemplate sqlSessionTemplate;

   @Override
   public void getAttQuery(CommonVo commonVo, ResultRowDataHandler resultRowDataHandler) {
      sqlSessionTemplate.select("common.attData", commonVo,resultRowDataHandler);
   }
}

 

6. ResultHandler

package com.inface.common;


import com.inface.excel.vo.CommonVo;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;

public class ResultRowDataHandler implements ResultHandler<CommonVo> {
   private Logger logger = LoggerFactory.getLogger(getClass());
   private HttpServletResponse response;
   private SXSSFWorkbook workbook;
   private SXSSFSheet sheet;
   private boolean isStarted = false;


   public ResultRowDataHandler(HttpServletResponse response){
      this.response=response;

      //메모리에 100개의 행을 누적후 행의 수가 10000개를 넘을시 디스크에 기록한다.
      workbook = new SXSSFWorkbook(10000);
      sheet = workbook.createSheet();
   }

   @Override
   public void handleResult(ResultContext<? extends CommonVo> resultContext) {
      CommonVo commonVo = resultContext.getResultObject();
      if(!isStarted){
         open(resultContext.getResultObject());
         isStarted = true;
      }

      // 현재 처리중인 행번호(1부터 시작됨.)
      Row row = sheet.createRow(resultContext.getResultCount() - 1);
      Cell cell = null;
      cell = row.createCell(0);
      cell.setCellValue(commonVo.getId());

      cell = row.createCell(1);
      cell.setCellValue(commonVo.getUserid());

      cell = row.createCell(2);
      cell.setCellValue(commonVo.getChecktime());

      cell = row.createCell(3);
      cell.setCellValue(commonVo.getVerifycode());

      cell = row.createCell(4);
      cell.setCellValue(commonVo.getSn());

      cell = row.createCell(5);
      cell.setCellValue(commonVo.getSensorid());
   }

   public void open(CommonVo resultObject){
      response.setHeader("Set-Cookie", "fileDownload=true; path=/");
      response.setHeader("Content-Disposition", String.format("attachment; filename=\"test.xlsx\""));
   }

   public void close(){
      try {
         workbook.write(response.getOutputStream());
         workbook.dispose();
      }catch (Exception e){
         logger.error("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 Download......").getBytes();
            out.write(data,0,data.length);
         }catch (Exception ex){
            logger.error("Exception : {}", ex);
         }
      }finally {
         if(workbook != null){
            try { workbook.close(); }catch(Exception ex){}
         }
      }
   }


}

 

8. index Page

<%--
  Created by IntelliJ IDEA.
  User: Daniel
  Time: 오후 2:18
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java"  pageEncoding="utf-8" %>
<%@ taglib uri="http://www.springframework.org/tags" prefix="s" %>
<%@ taglib  uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/functions" prefix = "fn" %>
<!DOCTYPE html>
<html lang="ko">
<head>
   <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
   <title>엑셀 다운로드</title>
   <link rel="stylesheet" href="<c:url value='/webresources/js/jquery-ui-1.12.0/jquery-ui.min.css'/>"/>
   <script src="<c:url value="/webresources/js/jquery-3.1.0.min.js"/>"></script>
   <script src="<c:url value="/webresources/js/jquery-ui-1.12.0/jquery-ui.min.js"/>"></script>
   <script src="<c:url value="/webresources/js/jquery.fileDownload.js"/>"></script>
   <script type="text/javascript">
      //<![CDATA[
      $(function() {
         $("#btn-excel").on("click", function () {
            var fileModal = $("#preparing-file-modal");
            fileModal.dialog({ modal: true });
            $("#progressbar").progressbar({value: false});
            $.fileDownload("/excelDownload.do", {
               successCallback: function (url) {
                  fileModal.dialog('close');
               },
               failCallback: function (responseHtml, url) {
                  fileModal.dialog('close');
                  $("#error-modal").dialog({ modal: true });
               }
            });
            // 버튼의 원래 클릭 이벤트를 중지 시키기 위해 필요합니다.
            return false;
         });

      });
      //]]>
   </script>
</head>
<body>

<button id="btn-excel">엑셀 다운로드</button>

<!-- 파일 생성중 보여질 진행막대를 포함하고 있는 다이얼로그 입니다. -->
<div title="Data Download" id="preparing-file-modal" style="display: none;">
   <div id="progressbar" style="width: 100%; height: 22px; margin-top: 20px;"></div>
</div>

<!-- 에러발생시 보여질 메세지 다이얼로그 입니다. -->
<div title="Error" id="error-modal" style="display: none;">
   <p>생성실패.</p>
</div>

</body>
</html>