POI -> 대용량 데이터 Excel 업로드 및 DB 저장 (Batch 사용)

Poi 를 사용하기 위한 의존성 추가

// excel
implementation 'org.apache.poi:poi:5.2.3'
implementation 'org.apache.poi:poi-ooxml:5.2.3'

---

간단한 RestController 를 작성

import com.kaii.dtmt.application.excel.ExcelFacade;
import com.kaii.dtmt.infrastructure.annotation.AuthToken;
import com.kaii.dtmt.presentation.shared.response.dto.BaseResponse;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@Slf4j
@RequestMapping(value = "/excel", name = "엑셀")
@RestController
@RequiredArgsConstructor
public class ExcelController {

    private final ExcelFacade excelFacade;

    @PostMapping(value = "/upload", name = "업로드")
    public BaseResponse<Void> excel(
        @AuthToken String token,
        @RequestPart MultipartFile file
    ) {
        excelFacade.upload(token, file);
        return BaseResponse.ofSuccess();
    }

}

---

간단히 Entity Users 추가

public class Users {
    private long num;
    private String name;
    private int age;

    public Users(long num, String name, int age) {
        this.num = num;
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return String.format("num : %d, name : %s, age : %d", num, name, age);
    }
}

---

업로드를 수행할 서비스 파일 추가

import com.kaii.dtmt.infrastructure.exception.UnauthorizedException;
import com.kaii.dtmt.infrastructure.jwt.JWTProperties;
import com.kaii.dtmt.infrastructure.jwt.JwtTokenUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.concurrent.ConcurrentLinkedQueue;

@Slf4j
@Service
@RequiredArgsConstructor
public class ExcelFacade {

    private final UsersService usersService;

    public void upload(String token, MultipartFile file) {
        // resources/excel 폴더를 생성하여 샘플 파일을 심어둠
        ClassPathResource classPathResource = new ClassPathResource("excel/test.xlsx");
        int sheetIndex = 0;

        // ? - Entity
        // ConcurrentLinkedQueue<?> usersQueue = new ConcurrentLinkedQueue<>();
        ConcurrentLinkedQueue<Users> usersQueue = new ConcurrentLinkedQueue<>();

        // 파일 읽어서 큐에 저장
        try (FileInputStream inputStream = new FileInputStream(classPathResource.getFile());
             XSSFWorkbook workbook = new XSSFWorkbook(inputStream)) {

            XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

            int rowIdx = 0;
            for (Row row : sheet) {
                if (rowIdx++ == 0)
                    continue;

                long num = (long) row.getCell(0).getNumericCellValue();
                String name = row.getCell(1).getStringCellValue();
                int age = (int) row.getCell(2).getNumericCellValue();
                // Entity Builder -> null 자리에 대체
                // usersQueue.add(null);
                Users users = new Users(num, name, age);
                usersQueue.add(users);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        usersService.excelUpload(usersQueue);
    }
}

---

UsersService 인터페이스를 생성하고 구현부(UsersServiceImpl)에서 다음을 수행

// JdbcTemplate 선언
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

// Batch Query 실행을 위한 PreparedStatementCreatorFactory 생성
PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(
    "INSERT INTO users (num, name, age) VALUES (?, ?, ?)");
pscf.setBatchSize(1000);
pscf.setResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);

// Batch Query 실행을 위한 PreparedStatementSetter 생성
PreparedStatementSetter pss = ps -> {
    while (!usersQueue.isEmpty()) {
        Users user = usersQueue.poll();
        ps.setLong(1, user.getNum());
        ps.setString(2, user.getName());
        ps.setInt(3, user.getAge());
        ps.addBatch();
    }
};

// Batch Query 실행
jdbcTemplate.batchUpdate(pscf.newPreparedStatementCreatorBatch(usersQueue.size()), pss);