[Oracle] Mybatis foreach Merge into 방법

시나리오

외부 데이터 API를 요청해서 5분마다 테이블에 적재하고 ID 값을 비교 같으면 UPDATE 다르면 insert 한다.

바로 오라클의 merge into로 코드를 작성했다.

오해

외부 데이터 Collection을 가져와서 mybatis의 foreach 반복을 Merge문 밖에 넣어놓고 컬렉션의 id와 merge 할 테이블의 id값을 반복문으로 비교하면서 같으면 update 없으면 insert 하면 될 거 같다고 생각해서 아래처럼 했는데 안됨.

<update id="first" parameterType="java.util.List">
    <foreach collection="list" item="item" open="" close="" index="index">
        MERGE INTO NTIC_TFM.TB_POLICE_OUTBRK_UTIC
        USING DUAL
        ON (OUTBRK_ID = #{item.incidentId})
        WHEN MATCHED THEN
        UPDATE
            SET
                OUTBRK_TY = #{item.incidenteTypeCd},
                OUTBRK_TY_CODE = #{item.incidenteSubTypeCd},
                ADRES = #{item.addressJibun},
                ADRES_CODE = #{item.addressJibunCd},
                ROAD_NM_ADRES = #{item.addressNew},
                LINK_ID = #{item.linkId},
                X_CRDNT = #{item.locationDataX},
                Y_CRDNT = #{item.locationDataY},
                SPOT_CODE = #{item.locationTypeCd},
                CRDNT_INFO = #{item.locationData},
                CNF_GRAD = #{item.incidenteTrafficCd},
                OUTBRK_GRAD = #{item.incidenteGradeCd},
                OUTBRK_SJ = #{item.incidentTitle},
                OUTBRK_AREA_CODE = #{item.incidentRegionCd},
                OCCRRNC_DT = #{item.startDate},
                END_DT = #{item.endDate},
                TFCLNE = #{item.lane},
                ROAD_NM = #{item.roadName},
                GVNF_INFO = #{item.sourceCode}
        WHEN NOT MATCHED THEN
        INSERT (
            OUTBRK_ID,
            OUTBRK_TY,
            OUTBRK_TY_CODE,
            ADRES,
            ADRES_CODE,
            ROAD_NM_ADRES,
            LINK_ID,
            X_CRDNT,
            Y_CRDNT,
            SPOT_CODE,
            CRDNT_INFO,
            CNF_GRAD,
            OUTBRK_GRAD,
            OUTBRK_SJ,
            OUTBRK_AREA_CODE,
            OCCRRNC_DT,
            END_DT,
            TFCLNE,
            ROAD_NM,
            GVNF_INFO
        )
        VALUES (
            #{item.incidentId},
            #{item.incidenteTypeCd},
            #{item.incidenteSubTypeCd},
            #{item.addressJibun},
            #{item.addressJibunCd},
            #{item.addressNew},
            #{item.linkId},
            #{item.locationDataX},
            #{item.locationDataY},
            #{item.locationTypeCd},
            #{item.locationData},
            #{item.incidenteTrafficCd},
            #{item.incidenteGradeCd},
            #{item.incidentTitle},
            #{item.incidentRegionCd},
            #{item.startDate},
            #{item.endDate},
            #{item.lane},
            #{item.roadName},
            #{item.sourceCode}
        )
    </foreach>
</update>

이렇게 하는 방식은 merge문 자체를 반복문으로 돌리는 거라 안된다.

해결

밖에 Collection 객체를 foreach로 가상의 테이블로 만들어주고 그 이후에 merge into를 실행해야 한다.

Merge into A using B on A.id = B.id

단순 물리적 테이블 A와 B를 가져와서 칼럼 값을 비교한 다음에 update 또는 insert 한다고 생각하면 된다.

변경

<update id="mergeIntoUticList" parameterType="java.util.List">
    MERGE INTO NTIC_TFM.TB_POLICE_OUTBRK_UTIC R1
    USING (
        <foreach collection="list" item="item" open="" close="" index="index" separator="UNION">
            SELECT
                #{item.incidentId} as incidentId,
                #{item.incidenteTypeCd} as incidenteTypeCd,
                #{item.incidenteSubTypeCd} as incidenteSubTypeCd,
                #{item.addressJibun} as addressJibun,
                #{item.addressJibunCd} as addressJibunCd,
                #{item.addressNew} as addressNew,
                #{item.linkId} as linkId,
                #{item.locationDataX} as locationDataX,
                #{item.locationDataY} as locationDataY,
                #{item.locationTypeCd} as locationTypeCd,
                #{item.locationData} as locationData,
                #{item.incidenteTrafficCd} as incidenteTrafficCd,
                #{item.incidenteGradeCd} as incidenteGradeCd,
                #{item.incidentTitle} as incidentTitle,
                #{item.incidentRegionCd} as incidentRegionCd,
                #{item.startDate} as startDate,
                #{item.endDate} as endDate,
                #{item.lane} as lane,
                #{item.roadName} as roadName,
                #{item.sourceCode} as sourceCode
            FROM DUAL
        </foreach>
    ) T1
    ON (R1.OUTBRK_ID = T1.incidentId)
    WHEN MATCHED THEN
    UPDATE
        SET
            R1.OUTBRK_TY = T1.incidenteTypeCd,
            R1.OUTBRK_TY_CODE = T1.incidenteSubTypeCd,
            R1.ADRES = T1.addressJibun,
            R1.ADRES_CODE = T1.addressJibunCd,
            R1.ROAD_NM_ADRES = T1.addressNew,
            R1.LINK_ID = T1.linkId,
            R1.X_CRDNT = T1.locationDataX,
            R1.Y_CRDNT = T1.locationDataY,
            R1.SPOT_CODE = T1.locationTypeCd,
            R1.CRDNT_INFO = T1.locationData,
            R1.CNF_GRAD = T1.incidenteTrafficCd,
            R1.OUTBRK_GRAD = T1.incidenteGradeCd,
            R1.OUTBRK_SJ = T1.incidentTitle,
            R1.OUTBRK_AREA_CODE = T1.incidentRegionCd,
            R1.OCCRRNC_DT = T1.startDate,
            R1.END_DT = T1.endDate,
            R1.TFCLNE = T1.lane,
            R1.ROAD_NM = T1.roadName,
            R1.GVNF_INFO = T1.sourceCode
    WHEN NOT MATCHED THEN
    INSERT (
        OUTBRK_ID,
        OUTBRK_TY,
        OUTBRK_TY_CODE,
        ADRES,
        ADRES_CODE,
        ROAD_NM_ADRES,
        LINK_ID,
        X_CRDNT,
        Y_CRDNT,
        SPOT_CODE,
        CRDNT_INFO,
        CNF_GRAD,
        OUTBRK_GRAD,
        OUTBRK_SJ,
        OUTBRK_AREA_CODE,
        OCCRRNC_DT,
        END_DT,
        TFCLNE,
        ROAD_NM,
        GVNF_INFO
    )
    VALUES (
        T1.incidentId,
        T1.incidenteTypeCd,
        T1.incidenteSubTypeCd,
        T1.addressJibun,
        T1.addressJibunCd,
        T1.addressNew,
        T1.linkId,
        T1.locationDataX,
        T1.locationDataY,
        T1.locationTypeCd,
        T1.locationData,
        T1.incidenteTrafficCd,
        T1.incidenteGradeCd,
        T1.incidentTitle,
        T1.incidentRegionCd,
        T1.startDate,
        T1.endDate,
        T1.lane,
        T1.roadName,
        T1.sourceCode
    )
</update>

 

참고

aljjabaegi.tistory.com/467

Oracle mybatis foreach merge 방법 collection merge문