Mybatis里Mapper.xml加强

批量插入
insert into user(user_id,name) values (1,'test0'),(2,'test1');

<!-- 批量插入 -->
<insert id="batchInsert" parameterType="java.util.List">
insert into
user(
user_id,name
)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.userId},#{item.name})
</foreach>
</insert>

批量删除
delete from user where user_id in(1,2);
<!-- 批量删除 -->
<delete id="batchDel" parameterType="java.util.List">
delete from user
where
user_id in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</delete>

批量更新
①：循环更新，连接数据库N次，效率低
update user set name = 'test0' where user_id=1;update user set name = 'test1' where user_id =2;

<!-- 批量更新 -->
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update user
<set>
name=#{name}
</set>
where user_id = #{item.userId}
</foreach>
</update>

②：循环更新，连接数据库一次，效率较高（case when的两种写法）
写法一：
update user set name = case user_id when 1 then 'test0' when 2 then 'test1' else name end where user_id in(1,2)

<update id="batchUpdate" parameterType="java.util.List">
update user set name=
<foreach collection="list" item="item" index="index" separator=" " open="case user_id" close="end">
when #{item.userId} then #{item.name}
</foreach>
where user_id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.user_id}
</foreach>
</update>

写法二：
update user set name = case when user_id = 1 then 'test0' when user_id = 2 then 'test1' else name end where user_id in(1,2)

<update id="batchUpdate" parameterType="java.util.List">
update user
                 set name =
              <foreach collection="list" item="item" index="index" separator=" " open="case " close="else name end">
when user_id = #{item.userId} then #{item.name}
</foreach>
             where id in
                 <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
                 #{item.userId}
                 </foreach>
</update>