프로젝트 리펙토링을 진행하면서 발생한 문제.
다중 insert를 처리해야 하는데 서버에서 반복문을 통해 호출하도록 하지 않고 처리하고자 했으나 Sequence가 정상동작하지 않아 발생한 문제다.
프로젝트 환경
Spring MVC
MyBatis
Oracle 18c
발생한 문제
기존에 진행했던 프로젝트를 Spring Boot 환경으로 재구성한 프로젝트와 동일하게 리펙토링 하기 위해 테이블 구조를 해당 MySQL 환경의 구조와 동일한 설계의 데이터베이스 환경으로 수정.
수정하게 되면서 많은 테이블의 Primary Key가 NUMBER 타입으로 수정되었고 IDENTITY 설정을 통해 Sequence를 통한 처리를 하도록 수정했다.
그러나 다중 insert 처리 시 SQLIntegrityConstraintViolationException: ORA-00001: unique constraint ... 오류가 발생.
Unique가 보장되어야 하는 Primary key가 중복되는 요청으로 인해 오류가 발생했다.
원인 파악
여러 테스트를 해보고 검색도 해보니 Oracle에서 다중 insert 즉, INSERT ALL 처리 시 Sequence는 매번 호출되는 것이 아니라 최초 호출된 값을 재사용한다.
INSERT ALL
INTO testTable (title, content, userId)
VALUES(title, content, userId)
INTO testTable (title, content, userId)
VALUES(title2, content2, userId2)
SELECT * FROM DUAL;
이러한 쿼리를 실행하면서 IDENTITY가 설정된 id의 값이 1, 2 로 처리되어야 하지만 최초 호출된 1이 계속해서 재사용되기 때문에 1, 1로 처리되는 것이 문제였다.
이건 Oracle의 메커니즘이 그렇기 때문에 어떻게 할 수 있는 방법이 없었다.
문제 해결 방법
Sequence를 계속해서 호출하도록 수정할 수 없었기 때문에 다른 문제 해결 방법을 찾아야 했다.
그래서 찾은 방법은 3가지가 있었다.
- 서버에서 반복문을 통해 mapper를 여러번 호출한다.
- xml에서 PL/SQL을 직접 작성해 처리한다.
- 데이터베이스에 Procedure를 정의하고 xml에서 Procedure를 호출하도록 해 처리한다.
여기서 1번 방법은 동적 처리가 아닌 매번 호출하는 방법이다.
적은 반복 횟수가 보장된다면 괜찮은 방법이지만 반복 횟수를 보장할 수 없다면 큰 문제가 발생할 수 있다.
가장 큰 단점으로는 반복문을 통해 매번 mapper를 호출하기 때문에 매 요청마다 Connection 요청으로 인해 Connection Overhead가 증가하게 된다. 그렇기에 횟수에 제한을 두지 않은 경우 선택하기에는 어려운 방법이다.
2번의 PL/SQL을 직접 작성해서 처리하는 방식의 경우 Java 코드와 더 가까운 형태로 작성할 수 있기 때문에 Java 코드의 흐름을 유지하면서 데이터베이스 작업을 처리하는데 유리하다.
또한, 배포 역시 코드가 애플리케이션에 포함되어있어 데이터베이스의 업데이트를 따로 수행하지 않아도 된다는 장점이 있다.
그러나 데이터베이스에 대한 각 호출은 Network Overhead를 초래할 수 있고, 많은 데이터를 처리할 때 성능 저하가 발생할 수 있다.
PL/SQL 코드가 여러번 호출되는 경우 더 느려질 가능성도 존재한다.
3번의 Procedure 정의 후 호출하는 방식의 경우 데이터베이스에서 Procedure가 직접 실행되기 때문에 성능이 뛰어나다.
그만큼 Network Overhead 역시 줄일 수 있으며 Procedure 정의 시 조건문, 반복문을 통해 복잡한 로직을 처리하기에 더 적합하다.
하지만, 스키마가 변경되는 경우 프로시저 역시 같이 수정해야 하는 경우가 발생할 수 있기 때문에 가독성이 떨어지고 유지보수 측면에서 불리할 수 있다.
그리고 Java 디버깅 도구를 사용할 수 없기 때문에 문제발생 시 원인을 찾기가 다른 방법들에 비해 더 어려울 수 있다.
배포 역시 데이터베이스에 직접 배포해야 하기 때문에 배포 처리 과정이 복잡해 질 수 있다는 단점이 존재한다.
각 방법에 대해 장단점이 존재하지만 처리하는 기능은 적은 횟수만 존재할 것이라고 보장할 수 없기 때문에 1번은 택하지 않게 되었다.
2, 3번 중 고민했는데 좀 더 성능적으로 유리한 3번 방법으로 문제를 해결했다.
각 해결 방법에 대한 정리
해결 방법 정리를 하며 예제 코드를 정리하기 위해 Entity 클래스와 Table 구조를 먼저 정리.
public class Board {
private Long id;
private String title;
private String content;
private String userId;
}
CREATE TABLE board (
id NUMBER GENERATED BY DEFAULT AS IDENTITY
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE
NOMAXVALUE
PRIMARY KEY,
title VARCHAR2(100),
content CLOB,
userId VARCHAR2(50)
);
우선 IDENTITY에 대해 조금 정리.
IDNETITY는 MySQL에서의 auto_increment와 유사하게 동작한다.
IDENTITY는 12c 부터 도입된 기능이다. 기존에는 Sequence를 따로 정의하고 SQL에서 SEQ.nextval을 명시해야 사용할 수 있었던 반면, MySQL의 auto_increment처럼 명시하지 않아도 자동 증가를 처리할 수 있다.
기본적으로는 Sequence가 생성되고 Oracle에서 해당 Sequence를 통해 자동으로 처리해주는 개념이기 때문에 IDENTITY가 Sequence와 완전 다른 개념이라고 보기에는 어렵다고 생각한다. 편의성정도..?
IDENTITY의 옵션 설정에 대해서는 Sequence와 동일한 옵션을 갖는다.
차이로는 AS IDENTITY 이전에 DEFAULT, DEFAULT ON NULL, ALWAYS라는 옵션을 설정할 수 있다.
크게 나누면 DEFAULT, ALWAYS로 나눠볼 수 있는데 ALWAYS는 SQL에 값을 명시하더라도 Sequence의 값을 따른다.
그러나 DEFAULT는 값을 SQL에 직접 명시한다면 Sequence의 값이 아닌 해당 값을 따른다.
즉, ALWAYS 설정에서 id에 2를 넣도록 SQL을 작성하더라도 Sequence의 값이 1이라면 1이 들어가게 되고 DEFAULT는 2가 들어가게 된다.
DEFAULT ON NULL의 경우는 NULL로 명시하는 경우 Sequence의 값으로 처리되는 방법이다.
그래서 DEFAULT로 설정하는 경우 NULL로 명시했을 때 NULL을 그대로 저장해주지만 DEFAULT ON NULL을 설정하게 되면 NULL을 넣고 싶어도 Sequence 값이 들어가기 때문에 담을 수 없게 된다.
IDENTITY에 대한 주의 사항으로는 생성 이후 수정이 불가능하다는 점이다.
이것 때문에 발생한 문제도 있었는데 기존 MySQL에 담아두었던 더미데이터를 옮기는 과정에서 문제가 있었다.
연관관계 설정이 되어있기 때문에 IDENTITY가 동작하지 않고 그대로 데이터를 담도록 처리했다.
하지만, IDENTITY의 START WITH 값을 그대로 1로 두었고 이걸 수정할 수 없다보니 이후 처리에 대해서 계속 1만 가져오니 오류가 발생했다.
그래서 더미데이터를 최초 삽입하는 경우 IDENTITY를 통해 처리하도록 하면 간단하지만, 기존 더미데이터를 옮기는 경우에는 IDENTITY의 START WITH값을 더미데이터 이후의 값으로 설정해야 한다.
1. 서버에서 반복문을 통해 mapper 호출
서버에서 반복문을 통해 mapper를 호출하는 방법은 간단하기 때문에 설명없이 코드만 정리한다.
@Override
public void insertBoard(List<Board> boardList) {
for(Board board : boardList)
mapper.saveEntity(board);
}
<insert id="saveEntity" parameterType="Board">
INSERT INTO board(title, content, userId)
VALUES (#{title}, #{content}, #{userId})
</insert>
2. PL/SQL을 xml에 직접 작성해서 처리
@Override
public void insertBoard(List<Board> boardList) {
mapper.saveEntity(boardList);
}
<insert id="saveEntity" parameterType="java.util.List">
BEGIN
<foreach collection="list" item="board" seperator=";">
INSERT INTO board(titie, content, userId)
VALUES (#{board.title}, #{board.content}, #{board.userId})
</foreach>
END;
</insert>
PL/SQL을 직접 작성하는 경우 BEGIN 과 END; 사이에 foreach를 통해 list를 반복하게 처리해주면 된다.
3. Procedure 정의 후 서버에서 Procedure 호출
이 방법은 위 두 방법에 비해서 조금 더 설정해야 하는 부분들이 존재한다.
우선 데이터베이스에서 Procedure를 정의해야 한다. 그리고 처리하는 방법에 따라 Type을 정의해야 하기도 한다.
이때 Type은 Oracle 사용자 정의 타입(User-Defined Type, UDT)을 말한다.
좀 더 가볍게 정리하면 Oracle의 Type은 Java에서 Class와 비슷한 역할을 한다고 생각하면 편하다.
그래서 조금 여러 방식의 요청을 정리한다.
1. 클래스 리스트를 매개변수로 받아 처리하는 방법
2. 클래스 리스트와 단일 객체를 받아 처리하는 방법
3. 클래스가 아닌 객체 리스트를 받아 처리하는 방법
모두 비슷하지만 정의할 때 타입에 대한 처리와 같은 부분에서 차이가 조금씩 발생한다.
3-1. 클래스 리스트를 매개변수로 받아 처리하는 방법
매개변수인 클래스 리스트는 Entity List라고 가정하고 정리한다.
우선적으로 데이터베이스에서 Procedure를 정의해야 하는데 클래스를 매개변수로 처리하는 경우 Type을 먼저 정의한 후에 Procedure를 정의하면 된다.
CREATE OR REPLACE TYPE BOARD_OBJ AS OBJECT(
title VARCHAR2(100),
content CLOB,
userId VARCHAR2(50)
);
CREATE OR REPLACE TYPE BOARD_OBJ_LIST AS TABLE OF BOARD_OBJ;
CREATE OR REPLACE PROCEDURE insert_board(
board_data IN BOARD_OBJ_LIST
) IS
BEGIN
FOR i IN 1..board_data.COUNT LOOP
INSERT INTO board(title, content, userId)
VALUES(board_data(i).title
, board_data(i).content
, board_data(i).userId
)
END LOOP;
END insert_board;
가장 먼저 정의된 BOARD_OBJ는 매개변수로 받을 클래스의 필드들을 작성해주면 된다.
그리고 이 BOARD_OBJ라는 Type은 자바에서 Class를 정의한 것과 유사하다고 했다.
BOARD_OBJ_LIST는 BOARD_OBJ들을 담고 있는 배열이다.
자바 코드로 정리해보면 아래와 같은 개념이다.
public class Board_obj {
private String title;
private String content;
private String userId;
}
Board_obj[] board_obj_list;
그리고 이 Type들을 통해 서버에서 Oracle에서 이해할 수 있도록 변환해 Procedure 매개변수에 매핑을 하게 된다.
그래서 Procedure 코드를 보면 BOARD_OBJ_LIST 타입으로 매개변수가 선언되어있다.
서버에서 List<Board> 를 BOARD_OBJ_LIST로 변환해서 요청하게 될 것이고 Procedure에서는 해당 타입의 데이터를 받아 board_data라는 변수를 통해 받게 된다.
그리고 이후 LOOP를 통해 insert 를 여러번 처리하게 되는 것이다.
그럼 Sequence가 정상적으로 동작한다.
왜냐하면 INSERT ALL에서 Sequence가 정상적으로 증가되지 않는 것이 문제인데 여기에서는 LOOP를 통해 개별적인 INSERT 요청을 처리하는 것이 되기 때문이다.
여기서 깊게 생각 안하고 겉으로만 본다면 Procedure에서도 LOOP를 통해 insert를 여러번 반복하네? 라고 볼 수도 있지만,
초반에 정리했듯이 mapper를 여러번 호출하면 connection을 계속 요청해야 한다는 문제가 있지만, 이건 한번의 connection으로 Procedure를 호출하고 procedure 내부에서 여러번 반복을 처리하는 경우다. 또한, Procedure 동작이 데이터베이스에서 이루어지기 때문에 더 효율적으로 처리할 수 있게 된다.
데이터베이스에서는 여기까지 정의하면 끝이다.
이후는 서버에서 처리하는 방법에 대해 정리한다.
우선 Java Class 리스트를 Oracle에서 그대로 받을 수 없기 떄문에 Oracle에서 받을 수 있는 타입으로 변환이 필요하다.
그래서 TypeHandler를 정의해야 한다.
MyBatis에서는 일반적인 SQL 타입인 VARCHAR, INTEGER 등의 단일 객체 타입은 자동으로 변환해서 Oracle에 전달하지만 Class 객체나 리스트같은 경우는 알아서 변환하지 못하기 때문에 데이터베이스에서도 Type을 정의해야 하고 서버에서는 TypeHandler를 통해 변환한 뒤 넘겨줘야 한다.
import oracle.jdbc.OracleArray;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStruct;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import com.example.domain.entity.Board;
import java.sql.*;
import java.util.List;
public class BoardTypeHandler implements TypeHandler<List<Board>> {
@Override
public void setParameter(PreparedStatement ps
, int i
, List<Board> parameter
, JdbcType jdbcType) throws SQLException {
if(parameter != null && !parameter.isEmpty()) {
OracleConnection oracleConn = ps.getConnection()
.unwrap(OracleConnection.class);
OracleStruct[] structArray = new OracleStruct(parameter.size());
for(int idx = 0; idx < parameter.size(); idx++) {
Board board = parameter.get(idx);
Object[] boardAttributes = {
board.getTitle(),
board.getContent(),
board.getUserId()
};
OracleStruct struct = (OracleStruct) oracleConn
.createStruct("BOARD_OBJ", boardAttributes);
structArray[idx] = struct;
}
OracleArray array = (OracleArray) oracleConn
.createOracleArray("BOARD_OBJ_LIST", structArray);
ps.setArray(i, array);
}else
ps.setNull(i, Types.ARRAY);
}
@Override
public List<Board> getResult(ResultSet rs, String columnName) throws SQLException {
return null;
}
@Override
public List<Board> getResult(ResultSet rs, int columnIdx) throws SQLException {
return null;
}
@Override
public List<Board> getResult(CallableStatement cs, int columnIdx) throws SQLException {
return null;
}
}
TypeHandler에 대한 코드를 조금 정리하고 넘어간다.
- setParameter()
- 매개변수 i는 parameter의 인덱스를 의미한다.
- jdbcType은 여기서 사용되지 않았으나 JDBC 타입 정보다.
- OracleConnection 객체는 Oracle의 고유한 타입인 Struct, Array를 다루기 위해 생성한다.
- 반복문 내에서는 Board 리스트의 각 요소들을 OracleStruct 객체로 변환한 뒤 배열에 담아준다.
- 반복문 종료 이후 반복문 내에서 생성한 배열로 OracleArray를 생성한다.
- 생성된 OracleArray를 PreparedStatement의 파라미터로 생성한다.
- getResult()
- SQL 쿼리의 결과를 Java 객체로 변환하는 역할을 한다.
- 예제에서는 return null만 처리하기 때문에 구현되지 않은 상태다.
- String columnName을 받는 메소드는 컬럼 이름을 통해 ResultSet에서 값을 추출한다.
- int columnIndex를 받는 메소드는 인덱스 값을 통해 ResultSet에서 값을 추출한다.
- CallableStatement를 받는 메소드는 CallableStatement의 출력 파라미터에서 값을 추출한다.
setParameter 메소드 반복문 내부에 대해 조금 더 정리하면 oracleConn.createStruct()를 통해 데이터베이스에서 정의한 BOARD_OBJ라는 이름을 가진 Struct타입으로 boardAttributes를 변환하는 것이다.
이후 createOracleArray()에서도 역시 BOARD_OBJ_LIST라는 이름을 가진 OracleArray를 생성하는데 그 안에 Struct 배열을 담도록 처리하는 과정이다.
이렇게 변환을 거쳐 Oracle에 정의한 Procedure에서 정상적으로 매개변수를 받을 수 있게 된다.
getResult()의 경우 아무것도 작성하지 않았는데 프로젝트 진행중에서도 필요하지 않은 처리였고 해서 사용해보진 않았다.
다중 insert를 위해 사용하기 때문에 따로 결과값을 반환 받을 것도 없었다.
이 getResult 메소드에 대해 알아봤을 때 JDBCTemplate에서 ResultSet을 통해 결과를 받아보듯이 사용하는 것을 확인할 수 있었다.
그럼 이제 서비스에서 Mapper를 호출할 때 데이터를 어떻게 담아야 하고, xml에서는 어떻게 데이터를 넘기는 프로시저 호출을 작성해야 하는지 정리.
환경은 serviceImpl - MapperInterface - mapper.xml 구조다.
@Service
@RequiredArgsConstructor
public class BoardServiceImpl implements BoardService {
private final BoardMapper mapper;
@Override
public void insertBoard(List<Board> boardList) {
Map<String, Object> params = new HashMap<>();
params.put("boards", boardList);
mapper.saveAll(params);
}
}
public interface BoardMapper {
void saveAll(Map<String, Object> boards);
}
<insert id="saveAll" parameterType="map">
{CALL insert_board(
#{boards, jdbcType=ARRAY, jdbcTypeName="BOARD_OBJ_LIST",
typeHandler=com.example.mapper.typeHandler.BoardTypeHandler}
)}
</insert>
서비스단에서는 해당 List를 Map<String, Object> 타입의 Map에 담아 Mapper를 호출한다.
그리고 xml에서는 받은 매개변수명, jdbcType, jdbcTypeName, typeHandler를 작성한다.
jdbcType은 현재 배열을 넘기는 것이기 때문에 ARRAY로 정의한다.
jdbcTypeName은 데이터베이스에서 정의한 컬렉션 Type의 이름을 정의한다.
typeHandler는 해당 데이터를 변환할 TypeHandler 경로를 작성해주면 된다.
그럼 TypeHandler를 통해 데이터를 변환한 뒤에 Procedure를 호출해 정상적으로 처리할 수 있게 된다.
3-2. 클래스 리스트와 단일 객체를 받아 처리하는 방법
다음은 클래스 리스트와 단일 객체를 넘기는 방법이다.
그래서 title, content만 필드로 갖는 BoardDTO라는 클래스가 존재한다고 가정하고, userId는 String으로 넘겨 insert 처리 시 모든 데이터가 동일한 userId를 갖도록 한다.
이 경우는 1번의 클래스 리스트를 넘기는 방법과 크게 다르지 않다.
Type 정의 시 userId는 단일 객체로 받을 것이기 때문에 포함하지 않는다.
그리고 Procedure의 매개변수에 userId를 받는 매개변수를 추가해주면 된다.
CREATE OR REPLACE TYPE BOARD_OBJ AS OBJECT(
title VARCHAR2(100),
content CLOB
);
CREATE OR REPLACE TYPE BOARD_OBJ_LIST AS TABLE OF BOARD_OBJ;
CREATE OR REPLACE PROCEDURE insert_board(
user_id IN VARCHAR2,
board_data IN BOARD_OBJ_LIST
) IS
BEGIN
FOR i IN 1..board_data.COUNT LOOP
INSERT INTO board(title, content, userId)
VALUES(board_data(i).title
, board_data(i).content
, user_id
)
END LOOP;
END insert_board;
이정도만 간단하게 수정된다.
Type 정의에서 userId를 제외하고, Procedure 에서는 userId를 VARCHAR2 타입으로 받도록 해 문자열을 받을 수 있도록 한다.
이때 만약 userId가 Integer, Long 과 같은 타입이라면 NUMBER 타입으로 받도록 처리하면 된다.
그럼 TypeHandler에서 역시 boardAttributes 정의 시 userId를 제외하도록 하기만 하면 나머지는 동일하다.
import oracle.jdbc.OracleArray;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStruct;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import com.example.domain.entity.Board;
import java.sql.*;
import java.util.List;
public class BoardDTOTypeHandler implements TypeHandler<List<BoardDTO>> {
@Override
public void setParameter(PreparedStatement ps
, int i
, List<BoardDTO> parameter
, JdbcType jdbcType) throws SQLException {
if(parameter != null && !parameter.isEmpty()) {
OracleConnection oracleConn = ps.getConnection()
.unwrap(OracleConnection.class);
OracleStruct[] structArray = new OracleStruct(parameter.size());
for(int idx = 0; idx < parameter.size(); idx++) {
BoardDTO board = parameter.get(idx);
Object[] boardAttributes = {
board.getTitle(),
board.getContent()
};
OracleStruct struct = (OracleStruct) oracleConn
.createStruct("BOARD_OBJ", boardAttributes);
structArray[idx] = struct;
}
OracleArray array = (OracleArray) oracleConn
.createOracleArray("BOARD_OBJ_LIST", structArray);
ps.setArray(i, array);
}else
ps.setNull(i, Types.ARRAY);
}
@Override
public List<BoardDTO> getResult(ResultSet rs, String columnName) throws SQLException {
return null;
}
@Override
public List<BoardDTO> getResult(ResultSet rs, int columnIdx) throws SQLException {
return null;
}
@Override
public List<BoardDTO> getResult(CallableStatement cs, int columnIdx) throws SQLException {
return null;
}
}
서비스와 xml 에서의 처리는 아래와 같이 하면 된다.
@Service
@RequiredArgsConstructor
public class BoardServiceImpl implements BoardService {
private final BoardMapper mapper;
@Override
public void insertBoard(List<BoardDTO> boardList, String userId) {
Map<String, Object> params = new HashMap<>();
params.put("boards", boardList);
params.put("userId", userId);
mapper.saveAll(params);
}
}
public interface BoardMapper {
void saveAll(Map<String, Object> boards);
}
<insert id="saveAll" parameterType="map">
{CALL insert_board(
#{userId, jdbcType=VARCHAR},
#{boards, jdbcType=ARRAY, jdbcTypeName="BOARD_OBJ_LIST"
, typeHandler=com.example.mapper.typeHandler.BoardDTOTypeHandler}
)}
</insert>
처리는 의외로 간단하다.
서비스에서는 Map에 userId를 추가로 담아주면 된다.
xml에서는 userId의 jdbcType을 VARCHAR로 처리해주기만 하면 된다.
여기서 만약 userId가 Integer, Long 타입인 경우에는 NUMERIC으로 명시해주면 된다.
3-3. 클래스가 아닌 객체 리스트를 받아 처리하는 방법
이번에는 클래스를 전혀 넘기지 않고 각 객체들을 넘기는 방법이다.
조금 여러 케이스를 정리하기 위해 Board 필드 객체 타입에 살짝 억지를 부려서
List<Integer> title, List<String> content, List<Long> userId 라는 값들을 받는다고 가정한다.
그럼 여기서는 Procedure에 매개변수로 클래스를 넘기지 않는다.
그렇기 때문에 Type 정의를 해줄 필요가 없게 된다.
Procedure만 정의해주면 되는데 아래와 같이 정의한다.
CREATE OR REPLACE PROCEDURE insert_board(
board_title IN SYS.ODCINUMBERLIST,
board_content IN SYS.ODCIVARCHAR2LIST,
user_id IN SYS.ODCINUMBERLIST
) IS
BEGIN
FOR i IN 1..board_title.COUNT LOOP
INSERT INTO board(title, content, userId)
VALUES(board_title(i), board_content(i), user_id(i))
END LOOP;
END insert_board;
굳이 매개변수 타입을 조금 억지 부린 이유가 여기있다.
Integer, Long 타입인 title, userId의 경우 SYS.ODCINUMBERLIST 타입으로 선언하게 된다.
Java에서는 Integer, Long으로 나눠서 처리하지만 Oracle에서는 동일하게 NUMBER 타입이기 때문이다.
그리고 String의 경우 SYS.ODCIVARCHAR2LIST 타입으로 받아주게 된다.
이 타입들의 경우 Oracle의 내장컬렉션이기 때문에 Type 정의를 해줄 필요가 없다.
그리고 이렇게 클래스가 아닌 객체 리스트 역시 변환이 필요하다.
최초 Entity를 넘겨 처리하는 방법에서 정리했다시피 MyBatis에서는 List 역시 제대로 변환하지 못하기 때문에 TypeHandler를 정의해야 할 필요가 있다.
TypeHandler는 타입만 조금 바꿔주면 되는 정도로 유사하기 때문에 Integer 타입에 대한 Handler만 정리한다.
import oracle.jdbc.OracleConnection;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import java.sql.*;
import java.util.List;
public class IntegerArrayTypeHandler implements TypeHandler<List<Integer>> {
@Override
public void setParameter(PreparedStatements ps
, int i
, List<Integer> parameter
, JdbcType jdbcType) throws SQLException {
if(parameter != null && !parameter.isEmpty()) {
OracleConnection oracleConn = ps.getConnection()
.unwrap(OracleConnection.class);
Integer[] arrayData = parameter.toArray(new Inteter[0]);
Array oracleArray = oracleConn
.createOracleArray("SYS.ODCINUMBERLIST", arrayData);
ps.setArray(i, oracleArray);
}else
ps.setNull(i, Types.ARRAY);
}
@Override
public List<Integer> getResult(ResultSet rs
, String columnName) throws SQLException {
return null;
}
@Override
public List<Integer> getResult(ResultSet rs
, int columnIdx) throws SQLException {
return null;
}
@Override
public List<Integer> getResult(CallableStatement cs
, int columnIdx) throws SQLException {
return null;
}
}
attributes 객체를 따로 생성해줄 필요가 없기 때문에 코드가 좀 더 간결하다.
다른 타입들의 TypeHandler와의 차이점으로는 타입 선언부분에서의 차이, createOracleArray에서 타입명의 차이 정도가 있다.
Long 타입의 경우 동일하게 SYS.ODCINUMBERLIST로 처리되지만 String의 경우 SYS.ODCIVARCHAR2LIST로 수정해주면 된다.
서비스와 Mapper는 아래와 같다.
@Service
@RequiredArgsConstructor
public class BoardServiceImpl implements BoardService {
private final BoardMapper boardMapper;
@Override
public void insertBoard(List<Integer> titleList
, List<String> contentList
, List<Long> userIdList) {
Map<String, Object> params = new HashMap<>();
params.put("titleList", titleList);
params.put("contentList", contentList);
params.put("userIdList", userIdList);
mapper.saveAll(params);
}
}
public interface BoardMapper {
void saveAll(Map<String, Object> boards);
}
<insert id="saveAll" parameterType="map">
{CALL insert_board(
#{titleList, jdbcType=ARRAY, jdbcTypeName="SYS.ODCINUMBERLIST",
typeHandler=com.example.mapper.typeHandler.IntegerArrayTypeHandler},
#{contentList, jdbcType=ARRAY, jdbcTypeName="SYS.ODCIVARCHAR2LIST",
typeHandler=com.example.mapper.typeHandler.StringArrayTypeHandler},
#{userIdList, jdbcType=ARRAY, jdbcTypeName="SYS.ODCINUMBERLIST",
typeHandler=com.example.mapper.typeHandler.LongArrayTypeHandler}
)}
</insert>
Procedure를 호출해 사용하는 방법들은 이렇게 여러가지 방법이 있다.
사실 여러가지 방법이라기 보다도 User-Defined Type을 어떤 경우 정의해야 하며, 어떻게 정의해야 하고 Procedure에서 매개변수 타입에 대한 설정을 어떻게 처리할지, TypeHandler는 언제 작성해야 하고 어떻게 처리해야 할지, MyBatis에서 호출할때는 어떻게 해야할지만 이해한다면 조합해서 사용하는 정도이다.
이번 문제해결을 하며 느낀점.
결과적으로 이 문제가 발생한 이유는 Sequence가 INSERT ALL에서는 정상적으로 동작하지 않는다는 데에서 시작한 것이다.
최초 프로젝트 수행시에는 11g를 사용했고 SEQ.nextval을 직접 작성해가며 처리했었다.
이번에 리펙토링하면서 18c로 업그레이드를 해줬고 IDENTITY가 자동 증가처리를 해준다길래 MySQL 설계와 동일하게 처리했더니 완전 폭탄 맞았다.
보통 id를 정수형으로 사용하는 이유는 검색 속도 때문이라고 알고 있다.
아무래도 문자열을 index로 갖고 검색하는 것 보다는 정수형이 정렬도 빠르기 때문이다.
그래서 이번에 리펙토링을 하면서 Oracle에서도 그렇게 써봐야지 했던건데 아무래도 일이 커졌다..
만약 성능에 영향이 덜하다면 굳이 정수형을 사용해서 IDENTITY를 설정하고 다중 insert를 이렇게 처리할 필요는 없지 않을까? 라는 생각이 들었다.
아무래도 Procedure를 사용하거나 PL/SQL을 직접 작성해 처리하도록 하면 복잡도도 늘어나고 관리해야 할 포인트들이 늘어난다.
그렇다고 서버에서 반복문을 통해 처리하자니 대량의 데이터를 처리해야 한다면 그건 그거대로 또 문제가 될 수 있다.
Oracle 환경에서는 성능에 큰 영향이 없고 앞으로의 운영 또는 확장에 대해서도 성능이 보장되는 경우에는 서버에서 직접 기본키를 생성하고 넘기는 형태가 더 유리하지 않을까 라는 생각이 들었다.
물론, 성능 측면에서는 관리 포인트가 늘어나더라도 조금이라도 더 좋은 성능을 낼 수 있는 방법을 택하는 것이 우선적이겠지만.
이번 문제 해결을 통해 데이터베이스 설계시 Sequence 역시 고려해야 할 포인트가 MySQL에 비해 더 많다는 것을 느낄 수 있었다.
