Mybatis 语句

批量操作：

Mybatis 插入和删除批处理操作

springMVC 接收数组参数，mybatis 接收数组参数，mybatis批量插入/批量删除案例

SQL中的case when then else end用法

批量 insert：

<insert id="batchSaveOrUpdate" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO favorite_group(id, name, user_id) VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id}, #{item.name}, #{item.userId})
    </foreach>
</insert>
```  

最终的语句类似：  

```  
insert into favorite_group(id, name, user_id) values (1, 1, 1), (2, 2, 2), (3, 3, 3)

批量 insert 或 update：

<insert id="batchSaveOrUpdate" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO favorite_group(id, name, user_id) VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id}, #{item.name}, #{item.userId})
    </foreach>
    ON DUPLICATE KEY UPDATE
    name = CASE id
    <foreach collection="list" item="item" index="index">
        WHEN #{item.id} THEN #{item.name}
    </foreach>
    END
</insert>

如果有多个字段要改动，END 后面加逗号分隔。

批量 delete：

<delete id="batchDelete" parameterType="long">
    DELETE FROM favorite_group where id IN
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{groupIds}
    </foreach>
</delete>

最终的语句类似于：

delete from favorite_group where id in (1, 2, 3)