[mybatis] SELECT로 INSERT 그리고 foreach

merci·2023년 8월 30일

SELECT

보통 INSERT 구문은 아래처럼 작성합니다.

INSERT INTO students (name) VALUES ('John Doe');

다른 테이블의 데이터를 삽입한다면 아래와 같이 작성합니다.
VALUES를 SELECT 구문으로 대체하여 INSERT를 수행합니다.

INSERT INTO table1 (column1, column2, ...)
SELECT column1, column2, ...
FROM table2
WHERE some_condition;

하지만 EXISTS를 이용하면 조건에 따라 INSERT의 여부를 정할 수 있습니다.
students 테이블에서 John Doe라는 이름이 존재하지 않을 경우 INSERT를 실행하게 됩니다.

INSERT INTO students (name)
SELECT 'John Doe'
WHERE NOT EXISTS (
    SELECT 1
    FROM students
    WHERE name = 'John Doe'
);

마찬가지로 SELECT구문을 이용하여 UPDATE구문을 작성하면 아래와 같이 작성할 수 있습니다.
두 테이블의 키가 조인으로 연결된다면 UPDATE구문을 실행합니다.

UPDATE orders
SET product_price = (
    SELECT price
    FROM products
    WHERE products.product_id = orders.product_id
)
WHERE EXISTS (
    SELECT 1
    FROM products
    WHERE products.product_id = orders.product_id
);

foreach - INSERT

mybatis에서 MySQL을 이용해서 List를 등록할 경우 foreach구문을 사용해서 다중 INSERT를 구성합니다.

  <insert id="insert" parameterType="list">
      insert into interest_tb ( 
                       user_id 
                     , interest_ct 
                 ) values
            <foreach collection="list" item="item" index="idx" separator=",">
                 ( 
                       #{item.userId}
                     , #{item.interest}
                 )
            </foreach>
  </insert>

하지만 Oracle의 경우에는 foreach를 통해서 위 쿼리를 구현한다면 아래와 같이 작성합니다.
여기서 SELECT 1 FROM DUAL가 사용되는데 이는 INSERT ALL로 시작하는 쿼리의 관용적인 코드로 필요합니다.

<insert id="insert" parameterType="list">
    <foreach collection="list" item="item" index="idx" separator=" " open="INSERT ALL" close="SELECT 1 FROM DUAL">
        INTO interest_tb (
                  user_id
                , interest_ct
               ) 
        VALUES (
                  #{item.userId}, 
                  #{item.interest}
               )
    </foreach>
</insert>

위 쿼리는 아래와 동일한 쿼리입니다.

<insert id="insert" parameterType="list">
    INSERT ALL
        <foreach collection="list" item="item" index="idx">
            INTO interest_tb (
                      user_id
                    , interest_ct
                   ) 
            VALUES (
                      #{item.userId}
                    , #{item.interest}
                   )
        </foreach>
    SELECT 1 FROM DUAL;
</insert>

Oracle 에서 INSERT ALL을 이용해서 다중 INSERT를 구현하고
이 때 SELECT는 구문으로서 필요하게 됩니다. ( INTO ... SELECT ... FROM DUAL 패턴 )

마찬가지로 VALUES대신 SELECT로 데이터를 넣을 수 있습니다.

        SELECT 
            #{item.userId}
          , #{item.interest}
        FROM DUAL;

추가로 아래와 같이 작성한다면 DB에서 중복된 데이터 입력을 막을 수 있습니다.

        INTO interest_tb (
                  user_id
                , interest_ct
               ) 
        SELECT 
                  #{item.userId}
                , #{item.interest}
        FROM DUAL
        WHERE NOT EXISTS (
            SELECT interest_id
            FROM interest_tb
            WHERE 1=1 
            AND interest_id = #{item.interestId}
        )

시퀀스

오라클에서 여러번의 INSERT 쿼리를 보낼때 시퀀스를 사용할 때가 있습니다.
이때 위 방식처럼 작성한뒤 시퀀스.NEXTVAL 를 사용하면 아래와 같은 여러 에러가 발생합니다.

// INSERT ALL + 시퀀스 사용시 에러

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (테이블.제약조건명) violated

// (SELECT SEQ_WRK_BLDG_MNG.NEXTVAL FROM DUAL) 사용시 에러

java.sql.SQLSyntaxErrorException: ORA-02287: sequence number not allowed here

이러한 에러가 발생하는 이유는 INSERT ALL 구문은 하나의 쿼리로 만들어져 DB에 보내지게 되는데 시퀀스는 하나의 쿼리에서 여러번 호출하더라도 한번만 증가하기 때문입니다.

오라클은 INSERT 쿼리에서 여러 VALUES 절을 가지지 못하므로 UNION ALL을 통해서 INSERT 쿼리의 값을 합쳐서 보낸다고 하더라도 마찬가지로 시퀀스의 값은 하나의 쿼리에서는 여러번 증가하지 않습니다.

이때는 PL/SQL 블록을 사용함으로써 여러 INSERT쿼리에서 시퀀스를 사용할 수 있었습니다.

<foreach collection="DtlIds" item="id" index="idx" separator=";" open="DECLARE BEGIN" close="; END;">
      INSERT INTO 테이블명 (
             칼럼_A
      ,      칼럼_B
      ,      칼럼_C
      ,      칼럼_D
      )
      SELECT #{데이터_A}
      ,      #{데이터_B}
      ,      #{tnoRegUserId}
      ,      #{id}
      ,      시퀀스.NEXTVAL
      FROM DUAL
</foreach>

foreach - UPDATE

MySQL에서 다중 UPDATE 쿼리는 아래와 같지만

        <foreach item="menu" collection="menuList" separator=";">
            UPDATE TB_MENU
            SET FLAG = #{menu.flag}
            ,   USER_ID = #{menu.userId}
            ,   UPD = TO_DATE(#{menu.dateTime}, 'YYYYMMDDHH24MISS')
            WHERE ID = #{menu.id}
        </foreach>

ORACLE에서 다중 UPDATE 쿼리를 날려야 할때는 DECLARE BEGIN + END 를 사용해야 합니다.

        <foreach item="menu" collection="menuList" separator=";" open="DECLARE BEGIN" close="; END;">
            UPDATE TB_MENU
            SET FLAG = #{menu.flag}
            ,   USER_ID = #{menu.userId}
            ,   UPD = TO_DATE(#{menu.dateTime}, 'YYYYMMDDHH24MISS')
            WHERE ID = #{menu.id}
        </foreach>

그리고 조건에 따라 일부 칼럼만 수정하고 싶다면 동적쿼리를 이용합니다.
prefixOverrides 는 trim 문 내부의 쿼리가 지정된 문자로 시작한다면 제거합니다.

        UPDATE BOARD
        SET
        <trim prefixOverrides="," >
            <if test="조건1">, A = #{a}</if>
            <if test="조건2">, B = #{b}</if>
        </trim>