MyBatis
常用批量操作
批量新增部门
映射文件定义SQL
<sql id="key">
<!--suffixOverrides="," 可以忽略最后“,”号 -->
<trim suffixOverrides=",">
dept_name,
dept_address,
</trim>
</sql>
<insert id="insertDeptList">
insert into dept(
<include refid="key" />
) values
<foreach collection="list" item="item" separator=",">
(#{item.deptName},#{item.deptAddress})
</foreach>
</insert>
批量删除部门
映射文件定义SQL
<delete id="deleteDeptList">
delete from dept where dept_id in
<foreach collection="list" item="item" open="(" close=")"
separator=",">
#{item}
</foreach>
</delete>
批量修改员工信息
支持上有点麻烦,需要修改mybatis-config.xml文件相关数据库连接的信息(主要红色部分),以支持批量更新
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true" />
映射文件定义SQL
<update id="updateDeptList">
<!-- 多个update语句,用;号分隔开,如果是oracle数据库,一般需要加“begin”前缀,后缀“end;" -->
<foreach collection="list" item="dept" separator=";">
update dept
<set>
<if test="dept.deptName!=null">dept_name=#{dept.deptName},</if>
<if test="dept.deptAddress!=null">dept_address=#{dept.deptAddress},</if>
</set>
where dept_id=#{dept.deptId}
</foreach>
</update>