Mybatis + Mysql 批量操作CURD

冷不防 2022-04-15 05:37 283阅读 0赞

# 需要确认的问题: #

1.mybatis版本号是否在3.3.1及以上(JDK1.6支持3.3.1版本,否则不支持返回主键),具体请看https://github.com/mybatis/mybatis-3/pull/547  
2.Dao/Mapper层无需填入@Parma(“list”) 因为mybatis获取时,会直接读取为list  
3.foreach 的标签中为collection=“list"即可  
4.批量更新的 separator=”;"分号,

## 批量添加 ##

/**
     * 批量添加学生信息
     * @param studentList
     * @return
     */
    int saveStudentList(List<StudentList>  studentList);
    
    <insert id="saveStudentList" parameterType="java.util.List"
            useGeneratedKeys="true" keyProperty="id">
            insert into route_rule(name,age)
            values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.name}, #{item.age})
        </foreach>
    </insert>

## 批量更新 ##

### 方法一: ###

拼接update语句,用分号隔开,但是需要在数据库连接的url中添加&allowMultiQueries=true,允许批量更新

/**
    * 批量更新学生信息
    * @param studentList
    * @return
    */
    int updateStudentListById(List<StudentList>  studentList);
    
    <update id="updateStudentListById" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            update route_rule
            <set>
                name= #{item.name},
                age= #{item.age},
            </set>
            where id=#{item.id}
        </foreach>
    </update>

### 方法二(推荐) ###

用trim标签拼接更新语句

update t_student 
    set 
    name=case when id = ? then ? end,
    name=case when id = ? then ? end,
    age=case when id = ? then ? end,
    age=case when id = ? then ? end
    where id = ? or ?

<update id="updateStudentList" parameterType="java.util.List">
        update t_student
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="name=case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.name}
                </foreach>
            </trim>
            <trim prefix="age=case" suffix="end,">
                <foreach collection="list" item="item">
                    when id=#{item.id} then #{item.age}
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="item">
                id=#{item.id}
            </foreach>
        </where>
    </update>

## 批量删除 ##

<delete id="deleteStudent" parameterType="java.util.List">
      delete from t_student
       where id IN
       <foreach item="id" collection="list" open="(" close=")" separator=",">
           #{id, jdbcType=INTEGER}
       </foreach>
    </delete>

## 批量查询多条件 ##

用UNION ALL拼接即可

<select id="selectStudent"
                parameterType="java.util.List"
                resultType="com.jd.student">
            <foreach collection="list" item="item" index="index" separator="UNION ALL">
                select id, name , age
                FROM t_student
                WHERE
                id= #{item.id}
                and gender= #{item.gender}
            </foreach>
        </select>