MySQL 批量操作

1.批量添加

sql

insert into t_student (id, name, age)
values (?,?,?),(?,?,?)

xml

<insert id="batchInsert" parameterType="java.util.List">
        insert into t_student
        (
        <include refid="Base_Column_List"/>
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.id},
            #{item.name},
            #{item.age}
            )
        </foreach>
    </insert>

mapper

int batchInsert(List<Student> list);

2.批量更新

xml

<update id="batchUpdate" parameterType="java.util.List">
    update t_student
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="name =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.name!=null and item.name != ''">
                    when id=#{item.id} then #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix=" age =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.age != null and item.age > 0">
                    when id=#{item.id} then #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    where id in 
    <foreach collection="list" separator="," item="item" index="index"  open="(" close=")">
        #{item.id}
    </foreach>
</update>

mapper

int batchUpdate(List<Student> list);

3.批量删除

xml

<delete id="batchDelete" parameterType="java.lang.String">
        delete from t_student
        where id in
        <foreach collection="array" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

mapper

int batchDelete(String[] array);