[MyBatis] List 파라메터 foreach 사용 (INSERT, DELETE, MERGE, UPDATE) - Oracle

foreach문은 사용할 때마다 사용되는 위치도 속성도 어려워서 더이상 헷갈리지 않기 위해 남겨본다 :-) !

 

MySQL foreach 사용방법은 ?

[MyBatis] List 파라메터 foreach 사용 (INSERT, DELETE) - MySQL

 

foreach 속성

구분	설명
collection	전달받은 인자값
item	전달받은 인자값을 다른이름으로 대체
open	해당 구문이 시작할 때
close	해당 구문이 끝날 때
index	항목의 인덱스 값을 꺼낼 때 사용할 변수 이름을 지정
separator	구분자. 한번 이상 반복할 때 반복되는 사이에 해당 문을 넣어줌

 

예제 VO

다음 예제의 파라미터로 활용되는 VO 객체이다.

public class TestVO {
    private String idx;
    private String timestampAccident;
    private String vin;
    private String dataGb;
    
    @Override
    public String toString() {
        return "TestVO [idx=" + idx + ", timestampAccident=" + timestampAccident 
 + ", vin=" + vin + ", dataGb=" + dataGb + "]";
    }
    public void setIdx(String idx) {
        this.idx = idx;
    }
    public void setTimestampAccident(String timestampAccident) {
        this.timestampAccident = timestampAccident;
    }
    public void setVin(String vin) {
        this.vin = vin;
    }
    public void setDataGb(String dataGb) {
        this.dataGb = dataGb;
    }
}

 

INSERT 구문

<insert id="insCompareResult" parameterType="java.util.List" >
    <foreach collection="list" item="item" open="INSERT ALL" close="SELECT * FROM SYS.DUAL" 
        separator=" ">
    INTO KTF_COMPARE_RESULT(
          IDX
        , TIMESTAMP_ACCIDENT
        , VIN
        , DATA_GB
    ) VALUES (
          #{item.idx}
        , #{item.timestampAccident}
        , #{item.vin}
        , #{item.dataGb}
    )
    </foreach>
</insert>

• Line1. VO의 ArrayList로 파라메터를 넘겼기 때문에 parameterType = "java.util.List"로 설정해준다.
• Line2-3. foreach 속성을 설정해주는 부분이다. ORACLE의 경우 멀티 INSERT 시 INSERT ALL을 해주어야 한다.

 

위의 코드는 아래와 같이 실행된다. 

INSERT ALL
    INTO KTF_COMPARE_RESULT(
          IDX
        , TIMESTAMP_ACCIDENT
        , VIN
        , DATA_GB
    ) VALUES (
          '1'
        , '1565052057'
        , 'VINVINVIN1'
        , '1'
    ) 
    INTO KTF_COMPARE_RESULT(
          IDX
        , TIMESTAMP_ACCIDENT
        , VIN
        , DATA_GB
    ) VALUES (
          '2'
        , '1565052058'
        , 'VINVINVIN2'
        , '2'
    )
SELECT * FROM SYS.DUAL

 

DELETE 구문

<delete id="delCompareResult" parameterType="java.util.List">
    DELETE FROM KTF_COMPARE_RESULT
    <where>
    <foreach collection="list" item="item" open="" close="" separator="OR">
        (IDX = #{item.idx} AND VIN = #{item.vin})
    </foreach>
    </where>
</delete>

 

위의 코드는 아래와 같이 실행된다

DELETE FROM KTF_COMPARE_RESULT
WHERE (TIMESTAMP_ACCIDENT = '1565059999' AND VIN = 'VINVINVIN1')
   OR (TIMESTAMP_ACCIDENT = '1565059999' AND VIN = 'VINVINVIN2')

 

만약 리스트 내 인자값의 따라 조건을 동적으로 주고 싶다면, 아래와 같이 foreach문 내에 if태그를 활용하면 된다.

<delete id="delCompareResult" parameterType="java.util.List">
    DELETE FROM KTF_COMPARE_RESULT
    <where>
    <foreach collection="list" item="item" open="" close="" separator="OR">
       <if test='item.dataGb==null or "".equals(item.dataGb)'>
           (TIMESTAMP_ACCIDENT = #{item.timestampAccident} AND VIN = #{item.vin})
       </if>
       <if test='item.dataGb!=null and !"".equals(item.dataGb)'>
          (TIMESTAMP_ACCIDENT = #{item.timestampAccident} AND IDX = #{item.dataGb})
       </if>
    </foreach>
    </where>
</delete>

 

MERGE 문

<insert id="insCoapLog" parameterType="java.util.List" >
    MERGE INTO KTF_COMPARE_RESULT R1
        USING (
            <foreach  collection="list" item="item" open="" close="" separator="union">
                SELECT #{item.timestampAccident} AS timestampAccident
                     , #{item.vin} AS vin
                     , #{item.objGb} AS idx
                     , #{item.dataGb} AS dataGb
                 FROM SYS.DUAL
            </foreach>
        ) T1
            ON (R1.TIMESTAMP_ACCIDENT = T1.timestampAccident)
        WHEN MATCHED THEN
            UPDATE 
            <set>
                  R1.VIN= T1.vin
                , R1.IDX= T1.idx
                , R1.DATA_GB= T1.dataGb
            </set>
        WHEN NOT MATCHED THEN
            INSERT 
            <trim prefix="(" suffix=")" suffixOverrides="," > 
                  TIMESTAMP_ACCIDENT
                , VIN
                , IDX
                , DATA_GB
            </trim>
            <trim  prefix="values (" suffix=")" suffixOverrides=",">
                  T1.timestampAccident
                , T1.vin
                , T1.idx
                , T1.dataGb
            </trim>
</insert>

 

• Line5-10. 위의 MERGE문에서의 foreach는 list 파라미터를 가져와서 MERGE문에 사용할 테이블을 먼저 만들었다.
• Line12. ON 조건에는 테이블의 키 값인 TIMESTAMP_ACCIDENT를 넣어주었고,
• Line13-19. 키 값이 매칭되는 데이터가 있다면 나머지 컬럼의 데이터를 UPDATE 해준다.
  * 이 때, ON 에 넣어준 컬럼을 UPDATE에 넣어주면 에러가 난다.
• Line20-33. 키 값이 매칭되지 않을 때는 INSERT를 해준다.

 

―――――――――――――――――――――― (2020-09-15 추가) ――――――――――――――――――――――

 

UPDATE문

update 경우 파라미터 구조에 따라 여러가지 쿼리로 사용할 수 있다.

• VO 안에 String[] 타입의 taArray 라는 배열이 있을 때 taArray 값에 따라 데이터를 업데이트 하고 싶은 경우

public class TestVO {
	private String idx;
	private String timestampAccident;
	private String vin;
	private String dataGb;
	private String[] taArr;

	... (생략)
}

 

데이터 taArr 에는 timestampAccident(키) 값을 문자열 배열로 받는 변수라고 볼 때

timestampAccident 값이 1, 2, 3,... 여러개인 경우에 idx = "idx1", vin = "vin1", dataGb="dataGb1" 과 같이 정해진 값으로 똑같이 업데이트를 하고 싶은 경우라고 생각하면 된다.

 

<update id="udtCompareResult" parameterType="testVO">
	UPDATE KTF_COMPARE_RESULT
	   SET IDX = #{idx}
	     , VIN = #{vin}
	     , DATA_GB = #{dataGb}
	 WHERE TIMESTAMP_ACCIDENT IN
	 <foreach collection="taArray" item="item" index="index" separator="," open="(" close=")">
	 	#{item}
	 </foreach>
</update>

 

위 코드는 아래와 같이 실행된다.

UPDATE KTF_COMPARE_RESULT
   SET IDX = #{idx}
     , VIN = #{vin}
     , DATA_GB = #{dataGb}
 WHERE TIMESTAMP_ACCIDENT IN ("1", "2", "3", ...) 

 

WHERE IN 문 뒤에 "open" 값인 "(" 로 시작하여 #{item} 값과 "separator"로 설정한 "," 구분자로 값을 그대로 작성한 뒤 "close"로 ")" 닫아준다.

 

 

일반적인 리스트 VO를 넘겼을 때의 경우이다.

<update id="udtCompareList" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" separator=";" open="DECLARE BEGIN" close="; END;">
        UPDATE KTF_COMPARE_RESULT 
           SET IDX = #{item.idx}
	     , VIN = #{item.vin}
	     , DATA_GB = #{item.dataGb}
         WHERE TIMESTAMP_ACCIDENT = #{item.timestampAccident}
    </foreach>
</update>

 

 

위 코드는 아래와 같이 실행된다.

DECLARE BEGIN
	UPDATE KTF_COMPARE_RESULT
	   SET IDX = #{item.idx}
	     , VIN = #{item.vin}
	     , DATA_GB = #{item.dataGb}
	 WHERE TIMESTAMP_ACCIDENT = #{item.timestampAccident}
	;
	UPDATE KTF_COMPARE_RESULT
	   SET IDX = #{item.idx}
	     , VIN = #{item.vin}
	     , DATA_GB = #{item.dataGb}
	 WHERE TIMESTAMP_ACCIDENT = #{item.timestampAccident}
	;
	.
	.
	.
	UPDATE KTF_COMPARE_RESULT
	   SET IDX = #{item.idx}
	     , VIN = #{item.vin}
	     , DATA_GB = #{item.dataGb}
	 WHERE TIMESTAMP_ACCIDENT = #{item.timestampAccident}
; END;

 

 

자주나는 에러

Caused by: org.apache.ibatis.binding.BindingException: Parameter '__frch_item_0' not found. Available parameters are [collection, list]

 

참고로 이 에러는 보통 VO 컬럼명(파라메터 컬럼명)의 오타일 가능성이 가장 크다