批量添加，删除，修改数据 基于MySQL SSM

添加

<insert id="functionName" parameterType="java.util.List">
  INSERT INTO table_name
  (    field1,field2,field3  )
  VALUE
  <foreach collection="list" item="item" index="index" separator=",">
   (
    #{item.field1},#{item.field2}

    <if test="item.field3 != null">,#{item.field3}</if>

    <if test="item.field3 == null">,null</if>//这里这个部分是为了告诉大家，某些字段如果不穿值得话我的处理方式，

<if test="item.field3 != null">,#{item.field3}</if>//因为这个语句在 mysql上的是这样的：   

//           INSERT INTO table_name ( field1,field2,field3) VALUE('','',''),('','','')

   )
   </foreach>
  </insert>

 

修改

<update id="functionName" parameterType="java.util.List">
  UPDATE effect_info
  <trim prefix="SET" suffixOverrides=",">
      <trim prefix="field = CASE id" suffix="END,">                       
          <foreach collection="list" item="item" index="index">
          <if test="item.field != null">
               WHEN #{item.id} THEN #{item.field}
          </if>
          </foreach>
      </trim>
      <trim prefix="profile = CASE effect_id" suffix="END,">
          <foreach collection="list" item="item" index="index">
          <if test="item.profile != null">
               WHEN #{item.effectId} THEN #{item.profile}
          </if>
          </foreach>
      </trim>
  </trim>
WHERE id IN
      <foreach collection="list" item="item" index="no" open="(" separator="," close=")">
          #{item.id}
       </foreach>
  </update>

主要运用的是mysql  when then 语法

UPDATE table_name SET    field = case id

       WHEN #{id1} THEN #{field1}

       WHEN #{id2} THEN #{field2}

       END

WHERE id IN(id1,id2)

 

删除

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

DELETE FROM table_name WHERE id IN(id1,id2,id3)