Bulk Exel Data Insert 2부

엑셀을 사용하여 Bulk 차량 등록하기 기능 구현하기 2부

• 1부에서는 Excel 데이터를 서버에서 요청 VS Client에서 요청 하는 부분에 대해서 글을 작성하였다. (궁금하신 분들은 여기로 가주세요
  💁🏻1부 바로가기)
• 2부에서는 정말 정말 많은 생각을 했었던 요청한 데이터들 차량번호, 차량코드, 등록날짜, 제조날짜, 업체, 지점들의 데이터를 어떻게 Save() 를 할 것인가...!! 에 대해서 공유해보려고한다.

가장 단순한 Save()

• 1부에서 언급했던 단건 차량 등록처럼 List<RequestDTO> carRequestDTO 가 들어와도 반복문을 사용해서 carRepository.save() 를 해주면 간단히 끝난다. 하지만 요청 차량이 500개면?? 실제로 이번에 400개의 차량을 등록해야하는 요청이 들어왔다. 그러면 400번의 save 메서드를 호출해야하고 DB에 400번의 insert 구문을 호출해야한다. DB Connection은 비싼 자원이고 DB에 과부하가 걸리게 되면 자칫 다운도 될 수 있기에 조심해야한다.
• 그렇다면 어떤 방법들이 있을까?
  1. Repository에서 제공하는 saveAll() 사용하기
  2. JdbcTemplate에서 제공하는 batchUpdate 사용하기

JdbcTemplate batchUpdate 사용해보기

먼저 Hibernate의 Batch Insert를 알아보자.

Batch Insert 란?

• DB에 레코드를 N(N>=2) 삽입 시, insert문을 N번 반복하는 것이 아닌 하나의 Insert문에 N개의 데이터를 담아서 삽입하는 것

  // 개별 
  INSERT INTO table1 (col1, col2) VALUES (val11, val12); 
  INSERT INTO table1 (col1, col2) VALUES (val21, val22); 
  INSERT INTO table1 (col1, col2) VALUES (val31, val32); 
  // Batch 
  INSERT INTO table1 (col1, col2) 
  VALUES (val11, val12), (val21, val22), (val31, val32);

Hibernate의 Batch Insert 제약사항

Hibernate 레퍼런스 문서 12.2.1 Batch insert 의 바로 위에 보게되면 생성자 생성에 IDENTITY 방식을 사용하면 Hibernate가 JDBC 수준에서 batch insert를 비활성화 한다고 나와있다.

Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.

• 이유는 다음과 같다.
  → 새로 할당할 Key 값을 미리 알 수 없는 IDENTITY 방식을 사용할 때 Batch Support를 지원하면 Hibernate가 채택한 flush 방식인 'Transactional Write Behind' 와 충돌하기 때문에 IDENTITY 방식에서는 Batch Insert를 비활성화 한다는 얘기다. 따라서 그냥 일상적으로 가장 널리 사용하는 IDENTITY 방식을 사용하면 Batch Insert는 동작하지 않는다.
  출처
• 그래서 Spring Data JPA( 구현체 : Hibernate ) 대신 Sprin Data JDBC 사용하였다.

Spring Data JDBC 사용하기

• 배치 크기에 따라 다르지만 이 방식은 Hibernate Batch Sequence 보다 대략 23배 빠르고, IDENTITY 보다 225배 정도 빠르다는 분석이 있다.

예제 보기
CarJdbcBatchRepository.java

@Repository  
public class CarJdbcBatchRepository {  
    private final JdbcTemplate jdbcTemplate;  
    int batchSize = 500;  

    public CarJdbcBatchRepository(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate)  
    {        this.jdbcTemplate = jdbcTemplate;  
    }  

    public Integer carBatchSaveAll(List<Car> cars) {  
        int batchCount = 0;  
        List<Car> insertCars = new ArrayList<>();  
        for (int i=0; i<cars.size(); i++)  
        {            insertCars.add((cars.get(i)));  
            if ((i+1) % batchSize == 0)  
            {                batchCount = carBatchInsert(insertCars);  
            }  
        }        if ( !cars.isEmpty() ) {  
            batchCount = carBatchInsert(insertCars);  
        }  
        return batchCount;  
    }  

    private int carBatchInsert(List<Car> cars) {  
        FMSBatchPreparedStatementSetter batchPreparedStatementSetter = new FMSBatchPreparedStatementSetter(cars);  
        jdbcTemplate.batchUpdate("INSERT INTO car(프로퍼티 column) VALUES(?,?,?,?,?,?,?,?,?,?)",  batchPreparedStatementSetter);  
        return batchPreparedStatementSetter.queryCount();  
    }  

}

FMSBatchPreparedStatementSetter.java

@RequiredArgsConstructor  
public class FMSBatchPreparedStatementSetter implements BatchPreparedStatementSetter  
{  
    private int batchCount = 0;  
    private final List<Car> cars;  
    @Override  
    public void setValues(PreparedStatement ps, int i) throws SQLException  
    {  
        ps.setString(1, cars.get(i).get...());  
        ps.setString(2, cars.get(i).get...());  
        ps.setString(3, cars.get(i).get...());  
        ps.setDate(4, Date.valueOf(cars.get(i).get...()));  
        ps.setDate(5, Date.valueOf(cars.get(i).get...()));  
        ps.setLong(6, cars.get(i).get...());  
        ps.setLong(7, cars.get(i).get...());  
        ps.setBoolean(8, true);  
        ps.setBoolean(9, false);  
        ps.setString(10, cars.get(i).get...());  
        batchCount++;  
    }  

    @Override  
    public int getBatchSize()  
    {        return cars.size();  
    }  

    public int queryCount(){  
        return batchCount;  
    }  

}

• FMSBatchPreparedStatementSetter코드중에 batchCount는 테스트 코드에서 해당 insert가 요청한 데이터 만큼 수행했는지를 위한 변수이기 때문에 사용하는 사람들에 따라 삭제 가능하다.

실제 DB SQL문 보기

• 해당 코드를 실행하면 개별 insert문이 실행되는 걸 확인할 수 있다...😱

  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update Hibernate: update hibernate_sequences set next_val=? where next_val=? and sequence_name=? 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?) 
  Hibernate: insert into car (프로퍼티 column) values (?, ?, ?, ?, ?, ?, ?, ?, ?)

• 이 문제는 Hibernate는 드라이버에 여러 개의 insert문을 보낸다. 이 출력된 개별 insert문은 드라이버가 DB에 보낸 SQL이 아니라 SQL Hibernate가 드라이버에 보낸 내용만 보여주는것이다.
• 그래서 application.yml 파일에서 jdbc-url 에 rewriteBatchedStatements=true&profileSQL=true 해당 옵션만 추가해주면 실제로 DB에서 어떤 SQL문이 실행되는지 알 수 있다.

00:23:28.747 DEBUG org.springframework.jdbc.core.JdbcTemplate (JdbcTemplate.java:933) - Executing SQL batch update [INSERT INTO car(프로퍼티 column) VALUES(?,?,?,?,?,?,?,?,?,?)] 
00:23:28.749 DEBUG org.springframework.jdbc.core.JdbcTemplate (JdbcTemplate.java:609) - Executing prepared SQL statement [INSERT INTO car(프로퍼티 column) VALUES(?,?,?,?,?,?,?,?,?,?)] 
00:23:28.786 DEBUG org.springframework.jdbc.core.JdbcTemplate (JdbcTemplate.java:933) - Executing SQL batch update [INSERT INTO car(프로퍼티 column) VALUES(?,?,?,?,?,?,?,?,?,?)] 
00:23:28.786 DEBUG org.springframework.jdbc.core.JdbcTemplate (JdbcTemplate.java:609) - Executing prepared SQL statement [INSERT INTO car(프로퍼티 column) VALUES(?,?,?,?,?,?,?,?,?,?)] 
00:23:28.824 DEBUG org.springframework.jdbc.core.JdbcTemplate (JdbcTemplate.java:933) - Executing SQL batch update [INSERT INTO car(프로퍼티 column) VALUES(?,?,?,?,?,?,?,?,?,?)] 
00:23:28.824 DEBUG org.springframework.jdbc.core.JdbcTemplate (JdbcTemplate.java:609) - Executing prepared SQL statement [INSERT INTO car(프로퍼티 column) VALUES(?,?,?,?,?,?,?,?,?,?)]

마무리

• 이번 2부에서는 요청된 데이터를 어떻게 save 하는지에 대해서 방법을 알아보았고, 마지막 3분에서는 그렇다면 saveAll() 은 왜 사용하지 않았나? 그리고 두 개의 방법에서 어떤게 더 좋은지에 대해서 분석하고 마무리하겠습니다!

참고

Sooyoung님 블로그 바로가기
HomoEfficio님 블로그 바로가기
Belluga님 블로그 바로가기