mybatis_Oracle_批量更新_批量插入_批量插入或更新_批量删除

批量插入

    <insert id="dataBatchInsert" parameterType="java.util.List" >

        INSERT INTO table_name 
        (
        id, column1,column2,column3,column4
        )
        <foreach collection="list" item="item" index="index" open="(" close=")" separator="union">
            SELECT
            #{item.id,jdbcType=BIGINT} , #{item.column1,jdbcType=VARCHAR} ,
            #{item.column2,jdbcType=TIMESTAMP} , #{item.column3,jdbcType=INTEGER} , #{item.column3,jdbcType=NUMERIC}
            FROM dual
        </foreach>
    </insert>

批量更新

 

    <update id="dataBatchUpdateData">
        <foreach collection="list" item="item" index="index" open="BEGIN" close=";END;" separator=";" >
            UPDATE table_name
            <set>
                column1 = #{item.column1},column2 = #{item.column2},column3 = #{item.column3},
                column4= #{item.column4}

            </set>
            WHERE id = #{item.id}
        </foreach>

    </update>

批量删除

 

    <delete id="dataBatchDelete" >

        delete from table_name
        where id in
        <foreach collection="list" item="item" index="index" open="(" close=")">
                 #{item.id}
        </foreach>

    </delete>

 

批量Merge

<insert id="dataBatchSaveOrUpdate" parameterType="java.util.List" >

        MERGE INTO table_name t1
        USING (
        <foreach collection="list" item="item" index="index" separator="union all" >
            ( SELECT
            #{item.id,jdbcType=BIGINT} id,
            #{item.column1,jdbcType=VARCHAR} column1, #{item.column2,jdbcType=TIMESTAMP} column2,
            #{item.column3,jdbcType=INTEGER} column3, #{item.column4,jdbcType=NUMERIC} column4
            FROM dual
            )
        </foreach>
        ) t2
        ON ( t1.id = t2.id )
        WHEN MATCHED THEN
        UPDATE SET
        t1.column1 = t2.column1, t1.column2 = t2.column2,t1.column3 = t2.column3,t1.column4 = t2.column4
        WHEN NOT MATCHED THEN
        INSERT
        (
         id,column1,column2,column3,column4
        )
        VALUES
        (
        t2.id,t2.column1,t2.column2,t2.column3,t2.column4
        )

    </insert>