Mybatis批量增删改查

一、创建t_staff员工表、及实体类

DROP TABLE IF EXISTS `t_staff`;
CREATE TABLE `t_staff`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `phone` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `gender` int(2) NULL DEFAULT NULL COMMENT '0 男  1 女',
  `staff_no` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '工号',
  `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像',
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '职称',
  `duty_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '职位',
  `dept_code` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '科室编码',
  `dept_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '科室名称',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地址',
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '状态',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `create_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '人员' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

@Data
public class Staff 
{
    private static final long serialVersionUID = 1L;

    private Long id;

    private String name;

    private String phone;

    private Integer gender;

    private String staffNo;

    private String avatar;

    private String title;

    private String dutyName;

    private String deptCode;

    private String deptName;

    private String address;

    private String status;

    private  Date  createTime;

    private  String  createBy;

}

@Mapper
public interface StaffMapper {


    int    batchInsertStaff(List<Staff>  list);


    /**
     * 批量更新方式1
     * @param list
     * @return
     */
    int   batchUpdateList1(List<Staff>  list);

    /**
     * 批量更新方式2
     * @param list
     * @return
     */
    int   batchUpdateList2(List<Staff>  list);



    /**
     * 批量删除
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteStaffByIds(Long[] ids);

    /**
     * 新增用户
     * @param staff
     * @return
     */
    int   addStaff(Staff staff);


     /**
     * 批量查询
     * @param staff
     * @return
     */
    List<Staff> selectStaffList(Staff staff);
}

二、java代码操作

1、jdbc连接添加批处理配置

&allowMultiQueries=true

2、批量插入

(1)、使用foreach批量插入

 int    batchInsertStaff(List<Staff>  list);

<insert id="batchInsertStaff"  keyColumn="id" keyProperty="id"   useGeneratedKeys="true">
        insert into t_staff(name,phone,gender,staff_no,avatar,title,duty_name,dept_code,dept_name,address,status,create_time,create_by )
        values
        <foreach collection="list" item="item" separator="," >
            (#{item.name},#{item.phone},#{item.gender},#{item.stafNo},#{item.avatar},#{item.title},#{item.dutyName},#{item.deptCode},
            #{item.deptCode},#{item.deptName},#{item.status},#{item.createTime},#{item.createBy})
        </foreach>
</insert>
    

（2）使用BatchInsert批量插入

  int   addStaff(Staff staff);

    <insert id="addStaff" parameterType="Staff" useGeneratedKeys="true" keyProperty="id">
        insert into t_staff
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">name,</if>
            <if test="phone != null">phone,</if>
            <if test="gender != null">gender,</if>
            <if test="staffNo != null">staff_no,</if>
            <if test="avatar != null">avatar,</if>
            <if test="title != null">title,</if>
            <if test="dutyName != null">duty_name,</if>
            <if test="deptCode != null">dept_code,</if>
            <if test="deptName != null">dept_name,</if>
            <if test="address != null">address,</if>
            <if test="status != null">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">#{name},</if>
            <if test="phone != null">#{phone},</if>
            <if test="gender != null">#{gender},</if>
            <if test="staffNo != null">#{staffNo},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="title != null">#{title},</if>
            <if test="dutyName != null">#{dutyName},</if>
            <if test="deptCode != null">#{deptCode},</if>
            <if test="deptName != null">#{deptName},</if>
            <if test="address != null">#{address},</if>
            <if test="status != null">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
        </trim>
    </insert>

    调用mybatis批处理代码

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Test
    public   void  batchInsert(){
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        ///Cursor<Object> objects = sqlSession.selectCursor("", Staff.class);
        StaffMapper staffMapper = sqlSession.getMapper(StaffMapper.class);
        List<Staff>  list  =  getStaffList();
        list.forEach(staffMapper::addStaff);
        sqlSession.commit();
        sqlSession.clearCache();
    }

3、批量修

（1）方式1：

  int   batchUpdateList1(List<Staff>  list);

<update id="batchUpdateList1">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update t_staff
            <set>dept_name=#{item.deptName},duty_name =#{item.dutyName} </set>
            where staff_no=#{item.staffNo}
        </foreach>
</update>

（2）方式2:

 int   batchUpdateList2(List<Staff>  list);

<update id="batchUpdateList2">
        update t_staff
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="dept_name = case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.deptName!=null and item.deptName!= ''">
                        when staff_no=#{item.staffNo} then #{item.deptName}
                    </if>
                </foreach>
            </trim>
            <trim prefix="duty_name = case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.dutyName !=null">
                        when staff_no=#{item.staffNo} then #{item.dutyName}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="item" index="index" >
            staff_no=#{item.staffNo}
        </foreach>
</update>

4、批量删除

 int deleteStaffByIds(Long[] ids)

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

5、批量查询

List<Staff> selectStaffList(Staff staff);

 <select id="selectStaffList" parameterType="cyrj.epidemic.com.work.staff.domain.Staff" resultMap="StaffResult">
         select id,  name, phone, gender, staff_no, avatar, title, duty_name,  dept_code, dept_name, state, address,  status, create_time, create_by from t_staff
        <where>  
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="phone != null  and phone != ''"> and phone = #{phone}</if>
            <if test="gender != null "> and gender = #{gender}</if>
            <if test="staffNo != null  and staffNo != ''"> and staff_no = #{staffNo}</if>
            <if test="title != null  and title != ''"> and title = #{title}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
        </where>
    </select>

调用代码

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Test
    public   void  batchSelect(){
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        Staff  staff = new Staff();
        Cursor<Staff> staffList = sqlSession.selectCursor("selectStaffList", staff);
        for (Staff next : staffList) {
            System.out.println("#----" + next.toString());
        }
    }