Xlsx 데이터 파싱, DB 입력

김태성·2024년 8월 19일

개인 프로젝트-1

목록 보기
22/53
post-thumbnail

이제 데이터를 파싱하여 DB에 넣어줄 시간이 왔다.
우선 ERD를 보면서 뭘 해야 하는지 생각해 보자.

우선 전체적인 erd이다.
많은 것이 있지만, 가장 먼저 넣어야 할 데이터는 다음과 같다.

바로 위치 데이터이다.
이 데이터들은 받아오는 데이터는 없지만 다른 테이블에 key로 많이 보내지는 데이터이다.

따라서, 이 위치 데이터를 먼저 넣도록 하자.

Domain

@Getter
@NoArgsConstructor
@Entity
public class Location {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long location_key;

    private String location_city;
    private String location_country;
    private String location_town;
    private double location_x;
    private double location_y;

    // 빌더 패턴을 위한 생성자
    @Builder
    public Location(String location_city, String location_country, String location_town, double location_x, double location_y) {
        this.location_city = location_city;
        this.location_country = location_country;
        this.location_town = location_town;
        this.location_x = location_x;
        this.location_y = location_y;
    }
}

이전에 @Data를 알아볼때 무분별한 @Setter 사용은 큰 독이 된다고 배웠다.
따라서, 직접 받아야 하는 파라미터를 제외하고, 중요한 Key값은 Setter를 사용하지 않기 위해 Builder라는 패턴을 찾았다.

레퍼런스 : https://velog.io/@yu-jin-song/SpringBoot-%EA%B2%8C%EC%8B%9C%ED%8C%90-%EA%B5%AC%ED%98%84-1-Entity-%EB%B0%8F-Repository-%EA%B5%AC%ED%98%84

위의 코드를 봤을때, @Builder를 활용해 Location에 파라미터값을 받아오는 것을 확인 할 수 있고, 이로 인해 location_key 값은 @Id, @Getter, @Entity, @GeneratedValue 만 붙은것을 확인 할 수 있다.

Repository

public interface LocationRepository extends JpaRepository<Location, Long> {
}

여기는 별거 없다.

Service / Controller

레퍼런스1 : https://stackoverflow.com/questions/50849800/how-to-read-excel-file-using-spring-boot

레퍼런스2 : https://poi.apache.org/apidocs/5.0/org/apache/poi/ss/usermodel/WorkbookFactory.html

레퍼런스3 : https://poi.apache.org/apidocs/5.0/org/apache/poi/ss/usermodel/Workbook.html

레퍼런스1을 살펴보자.

@PostMapping("/import")
public void mapReapExcelDatatoDB(@RequestParam("file") MultipartFile reapExcelDataFile) throws IOException {
    
    List<Test> tempStudentList = new ArrayList<Test>();
    XSSFWorkbook workbook = new XSSFWorkbook(reapExcelDataFile.getInputStream());
    XSSFSheet worksheet = workbook.getSheetAt(0);
    
    for(int i=1;i<worksheet.getPhysicalNumberOfRows() ;i++) {
        Test tempStudent = new Test();
            
        XSSFRow row = worksheet.getRow(i);
            
        tempStudent.setId((int) row.getCell(0).getNumericCellValue());
        tempStudent.setContent(row.getCell(1).getStringCellValue());
        tempStudentList.add(tempStudent);   
    }
}

처음에 보고 멘탈이 살짝 나갔다.
쭉 읽어보자.

@PostMapping("/import")
public void mapReapExcelDatatoDB(@RequestParam("file") MultipartFile reapExcelDataFile) throws IOException {

PostMapping으로 데이터를 받아온다.
이후 @RequestParam("file")으로 파일을 받아온다.

그래서 데이터를 받아오기 위해 Controller를 작성했다.

@RestController
public class Location_CSV_controller {

    private final Location_CSV locationCsv;
    public Location_CSV_controller(Location_CSV locationCsv) {
        this.locationCsv = locationCsv;
    }

    @PostMapping("/upload-xlsx")
    public void data(@RequestParam("file") MultipartFile file) throws IOException {
        locationCsv.ExcelData(file);
    }
}

간단하게 Controller를 작성했다.
위와 같이 작성하게 되면 파일을 Post로 보내줘야 하는데,

Postman에 해당 기능이 있어서 사용해줬다.
Key값은 file로, Value에 Xlsx 파일을 넣고 쏘면 된다.

Controller를 작성했는데 이에 맞게 Service 또한 작성했다.

이제 코드를 작성해 보자.

    public void ExcelData(MultipartFile file) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        List<Location> locationList = new ArrayList<>();

        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { 
            Row row = sheet.getRow(i);

            Location location = Location.builder()
                    .location_city(row.getCell(2).getStringCellValue()) 
                    .location_country(row.getCell(3).getStringCellValue())
                    .location_town(row.getCell(4).getStringCellValue()) 
                    .location_x(Double.parseDouble(row.getCell(13).getStringCellValue())) 
                    .location_y(Double.parseDouble(row.getCell(14).getStringCellValue())) 
                    .build();

            locationList.add(location);
        }

        // 데이터베이스에 일괄 저장
        locationRepository.saveAll(locationList);
    }

레퍼런스 : https://velog.io/@nueey/List%EB%A5%BC-DB%EC%97%90-%EC%A0%80%EC%9E%A5%ED%95%98%EA%B8%B0
스택오버플로우 형님이 Repostitory를 통한 DB 저장코드를 안적었다..

코드를 적고 Postman으로 데이터를 쏘니 에러가 났다.

java.io.IOException: Zip bomb detected! The file would exceed the max. ratio of compressed file size to the size of the expanded data.
This may indicate that the file is used to inflate memory usage and thus could pose a security risk.

무슨말인고 하니 Zip Bomb이 터졌다고 한다.
레퍼런스 : https://en.wikipedia.org/wiki/Zip_bomb

간단하게 말하면, 엄청나게 큰 용량의 zip를 풀게 되면 리소스를 엄청나게 빨아들여 시스템을 터뜨린다는 것이다.(그래서 폭탄)

생각해보니 파일의 크기가 좀 컸던거 같다.

단발성으로 데이터를 보내서 괜찮긴하지만, 여러 데이터를 한번에 보내는거면 큰일이 나겠구나 싶었다..

해결법은 간단하다!

리미트를 풀어버리면 된다.
Todo에도 적었다시피 중요한 취약점이 되어 데이터 요금 폭탄을 맞을 수 있다.
그러니 배포할때 꼭 확인하도록 하자.

그래서 오류도 잡았고 다시 Postman으로 데이터를 쐈는데

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell] with root cause

또 오류가 터졌다.
에러코드를 잘 읽어보면

  • path[]에 있는 Servlet이 에러가 났다.
  • NUMERIC cell에서 STRING value를 가져올 수 없다.

라고 한다.


??
분명히 계산식에는 문제가 없을 것이다.

.location_x(Double.parseDouble(row.getCell(13).getStringCellValue())) // 경도(초/100)

다시 봐도 문제가 없다.
cell 값을 string으로 가져와서 Double로 바꾸는 간단한 식이다.
그래서 Xlsx 파일을 살펴보니

(아니 공공데이터 관리 안하십니까)
0값이 있다.
이게 아마 Double으로 변환이 안되서 터지는거 같다.
혹시 null값인가 싶어서 확인도 해 봤는데

그건 아닌거같다.
첫번째 데이터에 null 값이 있는데도 안터지고 3000번대 후반의 0값에서 터지는걸 보면
단순한 자료형 불일치 문제인거 같다.

그래서 예외처리를 해줬다.

    private double parseDoubleCell(Cell cell) {
        if (cell.getCellType() == CellType.NUMERIC) {
            return cell.getNumericCellValue();
        } else {
            String cellValue = cell.getStringCellValue();
            if (cellValue.isEmpty() || cellValue.equals("0")) {
                return 0.0;
            }
            return Double.parseDouble(cellValue);
        }
    }

예외 처리를 해줬고 Postman으로 데이터를 쏴봤다.

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: java.lang.NullPointerException: Cannot invoke "org.apache.poi.ss.usermodel.Cell.getStringCellValue()" because the return value of "org.apache.poi.ss.usermodel.Row.getCell(int)" is null] with root cause

어림도 없지 또 터졌다.

getStringCellValue()을 할때 null 값이 나왔다는 거다.
위에도 확인했다시피 위치 데이터는

  • 시만 있는 데이터
  • 시/구 까지 있는 데이터
  • 시/구/동 까지 있는 데이터

3단계로 관리하기 때문에 null값이 무조건 발생한다.
그럼 이걸 null 처리를 해야하고..

    private String parseStringCell(Cell cell) {
        if (cell == null) {
            return "";
        }
        else {
            return cell.getStringCellValue();}

    }

null 처리를 해주니

데이터가 잘 들어온 모습이다.
전체를 하나하나 확인하지는 못하지만, 갯수가 재대로 왔는지 확인하면

기존 데이터 3831개 중에서 첫번째 줄을 재외한
3830개가 잘 들어온게 보인다.





프로젝트 시작하고 처음으로 Data를 DB에 넣어봤다.
간단해 보이는데 알아야할거는 왜이렇게 많고 복잡한지 모르겠다
하지만 힘들긴 했어도 로직에 맞춰서 데이터가 업데이트 되는 모습은 정말 신기하고 재밌었다.

그리고 하기 전에 진짜 고민도 많이하고 감당이 안되었는데, 하루 시간을 투자해서 끝내고 나니 생각보다 할만한가? 싶기도 했다.
사람 마음이라는게 참 간사하다

profile
김태성
닭이 되고싶은 병아리
이전 포스트

API 호출코드 리팩토링2

다음 포스트

전력 데이터 API 저장

0개의 댓글