mysql各种foreach语句（包含批量增、删、改）

增，批量保存，传参如下

    <insert id="insertBatch">
        INSERT INTO cd_identity_order ( set_id, order_num, identity_id )
        VALUES
        <foreach collection="setIds" index="index" item="item" separator=",">
            (#{item},(SELECT num FROM
            (SELECT count(*) num FROM cd_identity_order WHERE set_id = #{item} ) temp) + 1,#{identityId})
        </foreach>
    </insert>

删，批量删除，传参如下

    <delete id="deleteAttendanceByAttendanceIds">
        delete from attendance where attendance_id in
        <foreach item="attendanceId" collection="array" open="(" separator="," close=")">
            #{attendanceId}
        </foreach>
    </delete>
 	//多表关联批量删除
    <delete id="deleteUserByUserName">
		delete u,ur from sys_user u,sys_user_role ur where u.user_id = ur.user_id and u.user_name in
		<foreach collection="array" item="username" open="(" separator="," close=")">
			#{username}
		</foreach>
	</delete>

改，批量更新（借助case函数）

只更新一个字段，简单Case函数，传参如下

    <update id="updateCdIdentityOrder" parameterType="java.util.Map">
        update cd_identity_order o
        <trim prefix="set" suffixOverrides=",">
            <foreach collection="list" item="this" open="order_num = case identity_id " close="end,">
                when #{this.identityId} then #{this.order}
            </foreach>
        </trim>
        <where>
            <if test="setId != null">
                o.set_id = #{setId}
            </if>
        </where>
    </update>

多个字段，Case搜索函数

<update id="updateCdIdentityOrder" parameterType="java.util.Map">
        update cd_identity_order
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="identity =case" suffix="end,">
                <foreach collection="list" item="this">
                    <if test="this.identity!=null">
                        when id=#{this.id} then #{this.identity}
                    </if>
                </foreach>
            </trim>
            <trim prefix="set_id =case" suffix="end,">
                <foreach collection="list" item="this">
                    <if test="this.setId!=null">
                        when id=#{this.id} then #{this.setId}
                    </if>
                </foreach>
            </trim>
            <trim prefix="type =case" suffix="end,">
                <foreach collection="list" item="this">
                    <if test="this.type!=null">
                        when id=#{this.id} then #{this.type}
                    </if>
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="this">
                id = #{this.id}
            </foreach>
        </where>
    </update>

例子：

        <update id="updateBatch">
        update cd_identity_order o
        <!-- suffixOverrides去除多余的后缀 -->
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="order_num =case" suffix="end,">
                <foreach collection="list" item="this">
                    when o.order_num &gt; #{this.orderNum} and o.set_id = #{this.setId} then order_num - 1
                </foreach>
                else o.order_num
            </trim>
        </trim>
        <where>
            o.set_id in
            <foreach item="this" collection="list" open="(" separator="," close=")">
                #{this.setId}
            </foreach>
        </where>
    </update>

查，利用foreach拼接多个or条件，传参如下

    <select id="selectCdShowSettingIds" resultType="java.lang.Long">
        select set_id from cd_show_setting
        <where>
        <foreach collection="list" item="item" separator="or" >
            <if test="item.setLevel != null  and item.setLevel != ''"> set_level like concat('%', #{item.setLevel}, '%')</if>
                <if test="item.setSessions != null  and item.setSessions != ''"> and set_sessions like concat('%', #{item.setSessions}, '%')</if>
        </foreach>
        </where>
    </select>