Nick1407 的个人博客

一、批量新增
传入list参数：

   <insert id="insertSynDate" parameterType="java.util.List" useGeneratedKeys="true"  keyProperty="id">
        insert into `cau_ent_store`
        (
        `sin_id`,
        `goods_id`
        )
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.sinId},
            #{item.goodsId}
            )
        </foreach>
    </insert>

二、批量修改
传入list参数，where条件sin_id过滤值，当case的sin_id值等于when后item.sinId值的时候，该列就会set入then后的item.storeId值

  <update id="updataSynData" parameterType="java.util.List">
        update cau_ent_store
        set store_id = case sin_id
        <foreach collection="list" item="item">
            when #{item.sinId} then #{item.storeId}
        </foreach>
        end,
        goods_id=case sin_id
        <foreach collection="list" item="item">
            when #{item.sinId} then #{item.goodsId}
        </foreach>
        end
        where sin_id in
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.sinId}
        </foreach>

    </update>

三、批量删除

<delete id="deleteMoreEmp" parameterType="int[]">

		<!-- delete from emp where empno in(7789,7790) -->
		<!-- forEach : 用来循环 collection : 用来指定循环的数据的类型 可以填的值有：array,list,map item 
			: 循环中为每个循环的数据指定一个别名 index : 循环中循环的下标 open : 开始 close : 结束 separator : 数组中元素之间的分隔符 -->
		delete from emp where empno in
		<foreach collection="array" item="arr" index="no" open="("
			separator="," close=")">
			#{arr}
		</foreach>
</delete>

四、动态传参：传入map参数（字段名，表名动态传入）
1、新增

 <insert id="insert"  parameterType="java.util.Map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">

        <selectKey resultType="int" order="AFTER" keyProperty="id">

            SELECT LAST_INSERT_ID() as id

        </selectKey>
        <foreach collection="tableName" item="item" index="index" open="" separator="," close="">
        insert into ${item}
        </foreach>
        <foreach collection="key" item="item" index="index" open="(" separator="," close=")">
            ${item}
        </foreach>
        values
        <foreach collection="value" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </insert>

2、查询

  <select id="queryIsExsit"  parameterType="java.util.Map" resultType="Integer">

        <foreach collection="tableName" item="item" index="index" open="" separator="," close=" ">

                    select  t.id from ${item} t

        </foreach>
        <where>
        <foreach item="item" index="count" collection="flag" open=" "
                 separator="and" close="">
            ${item}=
            <foreach item="item" index="index" collection="flagValue" open=" "
                     separator=" " close="">
                <if test="index==count">
                    #{item}
                </if>
            </foreach>
        </foreach>
        </where>
    </select>

3、修改

    <update id="updateDate" parameterType="java.util.Map" >
        <foreach collection="tableName" item="item" index="index" open="" separator="," close=" ">
            update  ${item} t SET
        </foreach>
        <foreach item="key"  collection="key" separator="," index="count" >
           ${key}=
            <foreach item="item" index="index" collection="value" open=" "
                     separator=" " close="">
                <if test="index==count">
                    #{item}
                </if>
            </foreach>
        </foreach>
        <where>
            <foreach collection="tableId" item="item" index="index" open="" separator="," close=" ">
                t.id=#{item}
            </foreach>
        </where>
    </update>