Mybatis + Mysql 批量操作CURD

需要确认的问题:

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>