Mybatis批量增删改查操作数据

批量查询数据

根据ID集合批量查询数据：

mapper.xml

<select id="selectStudentListByIds" parameterType="java.util.List" resultMap="StudentResult">
    SELECT *
    FROM student
    <where>
        id IN
        <foreach item="item" index="index" collection="list"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        </where>
</select>

mapper.java

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    List<Student> selectStudentListByIds(List<Integer> list);
}

批量添加数据

向数据表中批量添加多条数据：

mapper.xml

<insert id="batchInsertStudent" parameterType="java.util.List">
    INSERT INTO student(name, gender, age, school, score) VALUES
    <foreach item="item" index="index" collection="list" separator=",">
        (#{item.name},#{item.gender},#{item.age},#{item.school},#{item.score})
    </foreach>
</insert>

mapper.java

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    void batchInsertStudent(List<Student> list);
}

批量更新数据

批量更新数据表中的多条数据：

mapper.xml

<update id="updateStudentScore" parameterType="java.util.List">
    UPDATE student
    SET score =
    CASE
    <foreach item="item" index="index" collection="list" separator=" ">
        WHEN id = #{item.id} THEN #{item.score}
    </foreach>
    ELSE score
    END
</update>

mapper.java

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    void updateStudentScore(List<StudentScore> list);
}

批量删除数据

根据ID集合批量删除数据：

mapper.xml

<delete id="deleteStudentByIds" parameterType="java.util.List">
    DELETE FROM student WHERE id IN
    <foreach item="item" index="index" collection="list"
             open="(" separator="," close=")">
        #{item}
    </foreach>
</delete>

mapper.java

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    void deleteStudentByIds(List<Integer> list);
}