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());
}
}

最后

以上就是风中歌曲为你收集整理的Mybatis批量增删改查的全部内容，希望文章能够帮你解决Mybatis批量增删改查所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。