서론
과거 카카오 테크 캠퍼스에서 활동할 때 Bulk Insert라는 것을 배웠었다.
그동안(학생으로서 프로젝트를 진행할 때)은 대용량으로 Insert를 쿼리를 작성할 일이 없었기 때문에 그다지 와닿지 않는 개념이었다.
많아봐야 한번에 10건 정도의 동시 Insert를 진행했는데 이는 단일 Insert 쿼리로도 충분했었기 때문이다.
이러한 이유로 단순히 개념만 알고있었는데, 우연히 이를 적용하기 좋은 예시를 발견해서 적용시켜보았다.
나는 엑셀에 있는 컬럼들을 DB에 Insert 시키는 기능을 구현하고 있었다.
그러던 중 기존에 작성되어있는 코드를 보게 되었고, 이 코드를 보면서 문제점은 무엇이었고 나는 어떤식으로 리팩토링 했는지에 대해서 말해보겠다.
기존 코드
아래 코드는 실제 코드가 아니며 편의를 위해 수정된 코드임을 말씀드립니다.
for(int cn=0 ; cn<sheetCn ; cn++) {
// 해당하는 시트, 행, 셀을 가져온다.
XSSFSheet sheet = workbook.getSheetAt(cn);
int rows=sheet.getPhysicalNumberOfRows();//행
int cells = sheet.getRow(cn).getPhysicalNumberOfCells();//셀
for (int r = 1; r < rows; r++) { //0번째 행은 목록이기에 제외
row = sheet.getRow(r); // row 가져오기
String sql="INSERT INTO "+TableName+" (NO,PHONE_NO,SN,IMEI,USIM,LOCATION,USIM_NO,MODEL,OPEN_DT,ORDER_NO) VALUES('"
+NO+"','"+PHONE_NO+"','"+SN+"','"+IMEI+"','"+USIM+"','"+LOCATION+"','"+USIM_NO+"','"+MODEL+"','"+OPEN_DT+"','"+ORDER_NO+"')";
// 명령어 실행
stmt.execute(sql);
}
}
위 코드를 이야기 해보기전 Bulk Insert에 대해서 모르는 분들이 있을 수도 있기에, 해당 개념을 이야기 해보고 가자.
아래의 단일 쿼리와 달리
INSERT INTO NameTable (col1, col2) VALUES (val11, val12);
INSERT INTO NameTable (col1, col2) VALUES (val21, val22);
INSERT INTO NameTable (col1, col2) VALUES (val31, val32);Bulk Insert는
INSERT INTO NameTable (col1, col2) VALUES
(val11, val12),
(val21, val22),
(val31, val32);이런식으로 3개의 단일 쿼리를 작성했던 것과 달리 하나의 쿼리로 묶어서 처리하는 방식이다.
이제 기본적인 개념을 알았으니 위의 코드를 이야기해보자.
for (int r = 1; r < rows; r++) { //0번째 행은 목록이기에 제외
String sql="INSERT INTO "+TableName+" (NO,PHONE_NO,SN,IMEI,USIM,LOCATION,USIM_NO,MODEL,OPEN_DT,ORDER_NO) VALUES('"
+NO+"','"+PHONE_NO+"','"+SN+"','"+IMEI+"','"+USIM+"','"+LOCATION+"','"+USIM_NO+"','"+MODEL+"','"+OPEN_DT+"','"+ORDER_NO+"')";
// 명령어 실행
stmt.execute(sql);
}코드를 살펴보면 단순히 For문을 돌면서 단일 Insert 쿼리를 날리고 있다.
이러한 코드도 사실 엑셀의 row가 10개 정도된다고 하면, 기존의 내 프로젝트들처럼 단일 쿼리로 작성해도 아무 문제 없을 것이다.
그러나
내가 구현하고자 하는 기능은 한번의 요청에 약 10,000개에 가까운 row들이 Insert 된다.
만약 단일 쿼리로 이러한 엑셀 데이터를 DB에 Insert 하고자 한다면,
데이터 하나 Insert하고, 커밋하고, Connection 닫고 이러한 과정을 불필요하게 반복하게 될 것이고 이에 따라서 이 기능을 사용하는 Client는 오랜 시간을 기다려야하는 불쾌한 경험을 하게 될 것이다.
그래서 나는 이 코드를 Batch Insert 방식으로 리팩토링 하고자 했다.
현재 코드
아래 코드는 실제 코드가 아니며 편의를 위해 수정된 코드임을 말씀드립니다.
public class BulkInsertUtil {
public static void bulkInsert(List<ExcelBulkInsert> dataList) throws Exception {
// JDBC 커넥션 얻기
Connection con = DriverManager.getConnection(GlobalProperties.getProperty("Globals.Url"), GlobalProperties.getProperty("Globals.UserName"), GlobalProperties.getProperty("Globals.Password"));
// Bulk Insert를 위한 쿼리문 작성
String sql = "INSERT INTO EX_TABLE(a, b, c, d) "
+ "VALUES (?, ?, ?, ?)";
// PreparedStatement 객체 미리 생성
PreparedStatement pstmt = con.prepareStatement(sql);
// 롤백을 위한 자동 커밋 방지
con.setAutoCommit(false);
// 100 단위로 끊기 위한 카운터 변수 선언
int cnt = 0;
try {
for (ExcelBulkInsert data : dataList) {
// ? 파라미터에 값 주입
pstmt.setString(1, data.getModelName());
pstmt.setTimestamp(2, data.getOpeningDate());
pstmt.setTimestamp(3, data.getDeliveryDate());
pstmt.setString(4, data.getDestinationCompany());
// 배치에 추가
pstmt.addBatch();
// batch 메모리에 넣은 후 파라미터 클리어
pstmt.clearParameters();
// 100개 단위로 배치 실행
if (cnt % 100 == 0 && cnt != 0) {
pstmt.executeBatch();
pstmt.clearBatch();
}
}
// 마지막으로 남은 배치 실행 및 커밋
pstmt.executeBatch();
con.commit();
} catch (Exception e) {
// 롤백
con.rollback();
throw e;
} finally {
// 자동 커밋 다시 설정
con.setAutoCommit(true);
con.close();
// PreparedStatement 닫기
pstmt.close();
}
}
}위 코드가 Batch Insert를 적용한 코드이다.
각 코드들이 어떤 역할을 하는지 주석보다 좀 더 자세하게 이야기해보자.
Connection con = DriverManager.getConnection(GlobalProperties.getProperty("Globals.Url"), GlobalProperties.getProperty("Globals.UserName"), GlobalProperties.getProperty("Globals.Password")); 먼저 나는 해당 Bulk Insert를 사용하기 위해 JDBC를 사용하였다.
JPA의 .saveAll()을 통해서 Insert 하면 되는거 아닌가라는 생각이 들 수 있겠지만,
JPA에서는 테이블 ID 규칙이 @GeneratedValue(strategy = GenerationType.IDENTITY) 로 되어 있을 경우 Bulk Insert가 아니라 단일 쿼리로 각각 insert를 하기 때문이다.
이러한 이유로 JDBC를 사용하기 위해 DB 정보를 통해서 Connection 객체를 생성하였다.
// Bulk Insert를 위한 쿼리문 작성
String sql = "INSERT INTO EX_TABLE(a, b, c, d) "
+ "VALUES (?, ?, ?, ?)";
// PreparedStatement 객체 미리 생성
PreparedStatement pstmt = con.prepareStatement(sql);또한 JDBC 쿼리를 String 문자열로 작성을 하였고, PreparedStatement를 통해서 동적인 쿼리를 작성하고자 했다.
PreparedStatement에 대해서 간단하게 개념을 말해보겠다.
기존 String 문자열 만을 사용해 쿼리를 작성할 때는 문자열에 ?를 작성하지 못하고, 직접 변수를 선언해주어야 했다.
이게 무슨 말이냐면
String sql = "INSERT INTO EX_TABLE(a, b, c, d) VALUES (" + a, ", ", + b, ", " + c + ", " + d)";원래는 위와같이 직접 문자열에 변수를 지정했어야 했다…
그러나PreparedStatement 의 도입으로 위 코드와 같이 동적 쿼리에서 변수가 들어갈 곳을 ?로 작성하고, 추후 set메서드를 통해서 주입하는 방식으로 작성이 가능해졌다.
이는 동적 쿼리를 작성하는데 있어서 굉장히 편리하고, 유지보수가 뛰어나게 작성을 하는데 도움을 준다.
// 롤백을 위한 자동 커밋 방지
con.setAutoCommit(false);기본적으로 JDBC API의 Connection 객체는 setAutoCommit이라는 메서드가 true가 기본값으로 되어있다.
이 때 setAutoCommit이 true가 의미하는 바는 하나의 쿼리당 자동 커밋 시작 밑 자동 커밋이 일어난다는 의미이다.
그러나 우리는 Bulk Insert를 통해 한번에 쿼리를 날리려고 하는 것이기에 해당 설정을 통해서 false로 지정을 해 직접 Commit 및 Rollback을 적는다.
try {
for (ExcelBulkInsert data : dataList) {
// ? 파라미터에 값 주입
pstmt.setString(1, data.getModelName());
pstmt.setTimestamp(2, data.getOpeningDate());
pstmt.setTimestamp(3, data.getDeliveryDate());
pstmt.setString(4, data.getDestinationCompany());
// 배치에 추가
pstmt.addBatch();
// batch 메모리에 넣은 후 파라미터 클리어
pstmt.clearParameters();
// 100개 단위로 배치 실행
if (cnt % 100 == 0 && cnt != 0) {
pstmt.executeBatch();
pstmt.clearBatch();
}
}
// 마지막으로 남은 배치 실행 및 커밋
pstmt.executeBatch();
con.commit();
먼저 SQL 관련해서 트랜잭션 및 여러 예외가 생길 수 있기에 try 문으로 감싸두었다.
현재는 Exception으로 예외를 묶어두었는데, 이는 좋은 방향은 아니기에 더 세밀하게 리팩토링 해 나갈 계획이다.
그리고 위에서 이야기한 것처럼 ? 인자에 변수를 설정해주는데 이 때 set Method의 첫인자는 해당 변수가 들어갈 ?의 순서이고, 두번째 인자는 첫번째 인자 순서의 ?에 들어갈 변수이다.
? 인자들에 대한 설정을 마치면, 해당 SQL문을 배치에 추가해주어 Batch 메모리에 넣어준다.
그 후 ? 파라미터들에 대해서 초기화를 해서 다음 for문에서 인자가 겹치는 문제를 방지한다.
그리고 한번에 모든 Batch들을 Insert 하는 것 또한 많은 리소스가 소모되므로, 100개 단위로 끊어서 Batch를 실행하고, Batch 메모리를 초기화 해준다.
이 때 100개 미만의 Batch가 남아있는 경우를 대비해서도 남은 Batch를 실행하고, 커밋해준다.
} catch (Exception e) {
// 롤백
con.rollback();
throw e;
} finally {
// 자동 커밋 다시 설정
con.setAutoCommit(true);
con.close();
// PreparedStatement 닫기
pstmt.close();
}
}
}만약 예외가 발생할 경우에 rollback을 진행한다.
그리고 finally 문을 통해서 최종적으로 setAutoCommit을 다시 true 기본값으로 변경해주고, Connection과 PreparedStatement 를 닫아준다.
이 때 열었던 순서대로 닫아주어야 한다.
