Mybatis批量操作数据总结

1.批量插入

 insert into t_train_record (add_time,emp_id,activity_id,flag)  
    values 
    <foreach collection="list" item="item" index="index" separator="," > 
        (#{item.addTime},#{item.empId},#{item.activityId},#{item.flag}) 
    </foreach>

2.批量更新

（1）
先按照id查出所有符合条件的数据，然后再进行修改

<update id="updateBatch" parameterType="java.util.List"> 
update mydata_table set status= 
<foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end"> 
when #{item.id} then #{item.status} </foreach>
 where id in
  <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id,jdbcType=BIGINT} </foreach> 
  </update>

其中when…then…是sql中的"switch" 语法。这里借助mybatis的语法来拼凑成了批量更新的sql，上面的意思就是批量更新id在updateBatch参数所传递List中的数据的status字段。还可以使用实现同样的功能,代码如下:

<update id="updateBatch" parameterType="java.util.List"> 
update mydata_table 
<trim prefix="set" suffixOverrides=","> 
<trim prefix="status =case" suffix="end,"> 
<foreach collection="list" item="item" index="index"> when id=#{item.id} then #{item.status} </foreach> 
 </trim>
 </trim> 
 where id in <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id,jdbcType=BIGINT} </foreach> 
 </update>

当然这是最简单的批量更新实现,有时候可能需要更新多个字段,那就需要将

<trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> when id=#{item.id} then #{item.status} </foreach></trim>

复制拷贝多次,更改prefix和when…then…的内容即可.而如果当需要为某个字段设置默认值的时候可以使用else

<trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> when id=#{item.id} then #{item.status} </foreach> else default_value </trim>

符合条件的更新，不符合条件的保持原数据不变

<trim prefix="status =case" suffix="end,"> 
     <foreach collection="list" item="item" index="index"> 
     <if test="item.status !=null and item.status != -1"> when id=#{item.id} then #{item.status} </if> 
     <if test="item.status == null or item.status == -1"> when id=#{item.id} then mydata_table.status //这里就是原数据 </if> </foreach>
</trim>

整体批量更新的写法如下:

<update id="updateBatch" parameterType="java.util.List">
 update mydata_table 
 <trim prefix="set" suffixOverrides=","> 
    <trim prefix="status =case" suffix="end,"> 
     <foreach collection="list" item="item" index="index">
          <if test="item.status !=null and item.status != -1"> when id=#{item.id} then #{item.status} </if> 
          <if test="item.status == null or item.status == -1"> when id=#{item.id} then mydata_table.status//原数据 </if>
      </foreach> 
   </trim>
</trim> 
where id in <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id,jdbcType=BIGINT} </foreach>
</update>