mybatis各种批量执行留念（Oracle）

merge into批量执行

参数为List<?>

<update id="batchAddDepartment" parameterType="java.util.List">
        MERGE INTO T_SYS_DEPARTMENT t1
        USING (
        <foreach collection="list" item="item" separator="union">
            select
            #{item.departmentId} AS department_id,#{item.departmentName} AS department_name,#{item.workStatus} AS
            work_status,#{item.departmentLeader} AS department_leader,#{item.departmentChief} AS department_chief
            from dual
        </foreach>
        ) t2 ON (t1.department_id=t2.department_id)
        WHEN MATCHED THEN
        UPDATE SET t1.department_name=t2.department_name,t1.work_status=t2.work_status,t1.department_leader=t2.department_leader,t1.department_chief=t2.department_chief
        WHEN NOT MATCHED THEN
        INSERT (t1.department_id,t1.department_name,t1.work_status,t1.department_leader,t1.department_chief) VALUES
        (t2.department_id,t2.department_name,t2.work_status,t2.department_leader,t2.department_chief)
    </update>

参数为Map，Map中含有List的批量insert

传入参数格式大致如下：

Map<String,Object> map = new HashMap<>();
map.put("roleList",list);
map.put("user_id","1");

sql如下：

 <insert id="insertUserRole" parameterType="java.util.Map">
        <selectKey keyProperty="roleId" order="BEFORE" resultType="int">
            SELECT (nvl(max(ID),0) + 1) FROM T_USER_ROLE
        </selectKey>
        INSERT INTO t_user_role
        <foreach collection="roleList" item="item" index="index" separator="union">
            SELECT (#{roleId}+#{index}),'EBTS',#{user_id},#{item},sysdate,NULL FROM dual
        </foreach>
    </insert>

批量删除

<delete id="batchDelDepartment" parameterType="java.util.List">
        DELETE FROM T_SYS_DEPARTMENT
        WHERE
        <foreach collection="list" item="id" open="DEPARTMENT_ID IN(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

参考：http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html