mybatis+mysql 批量插入、删除、更新

mybatis+mysql 批量插入、删除、更新
批量插入

public int insertBatchStudent(List<Student> students);

<insert id="insertBatchStudent" parameterType="java.util.List" useGeneratedKeys="true">
    <selectKey resultType="Long" keyProperty="id" order="AFTER">
        SELECT LAST_INSERT_ID()
    </selectKey>
    insert into student(stu_no,stu_name,age,sex,address)
    values
    <foreach collection="list" item="stu" index="index" separator=",">
        (#{stu.stuNo},
        #{stu.stuName},
        #{stu.age},
        #{stu.sex},
        #{stu.address})
    </foreach>
</insert>

根据数组批量删除

public int deleteStudentByIds(String[] ids);

<delete id="deleteStudentByIds" parameterType="String">
    delete from student where id in
    <foreach item="id" collection="array" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

根据集合批量删除

public int deleteStudentByIds(List<String> ids);

<delete id="deleteStudentByIds" parameterType="java.util.List">
    delete from student where id in
    <foreach item="id" collection="list" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

批量更新

public int updateBatchStudent(List<Student> students);

<update id="updateBatchStudent" parameterType="java.util.List">
    update student set
    stu_no =
    <foreach collection="list" item="stu" index="index" separator=" " open="case id" close="end">
        when #{stu.id} then #{stu.stuNo}
    </foreach>
    ,stu_name =
    <foreach collection="list" item="stu" index="index" separator=" " open="case id" close="end">
        when #{stu.id} then #{stu.stuName}
    </foreach>
    ,age =
    <foreach collection="list" item="stu" index="index" separator=" " open="case id" close="end">
        when #{stu.id} then #{stu.age}
    </foreach>
    ,sex =
    <foreach collection="list" item="stu" index="index" separator=" " open="case id" close="end">
        when #{stu.id} then #{stu.sex}
    </foreach>
    ,address =
    <foreach collection="list" item="stu" index="index" separator=" " open="case id" close="end">
        when #{stu.id} then #{stu.address}
    </foreach>
    where id in
    <foreach collection="list" item="stu" index="index" separator="," open="(" close=")">
        #{stu.id}
    </foreach>
</update>