Mybatis的批量更新数据实体

一、场景需求

项目开发时，有时需要对连接资源进行有效利用，同时避免不必要的程序遍历时间导致方法执行效率降低，因此对更新操作有了批量操作的需求。

二、Mybatis的批量更新的方案

实体属性

1、数据模型

public class SysUserEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    /** 主键 */
    private Integer id;
    /** 用户名 */
    private String username;
    /** 密码 */
    private String password;
    /** 真实姓名 */
    private String realName;
    /** 联系电话 */
    private String telephone;
    /** 邮箱 */
    private String mail;
}

2、表结构以及初始值

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  `real_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '真实姓名',
  `telephone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系电话',
  `mail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, 'admin', '111111', '超级管理员', '15583301101', '[e-mail]');
INSERT INTO `sys_user` VALUES (2, 'zhangsan', '111111', '张三', '15583301101', '[e-mail]');
INSERT INTO `sys_user` VALUES (3, 'lisi', '111111', '李四', '15583301101', '[e-mail]');
INSERT INTO `sys_user` VALUES (4, 'zhangtianshi', '111111', '张天师', '15583301101', '[e-mail]');
INSERT INTO `sys_user` VALUES (5, 'zhangsanfeng', '111111', '张三丰', '15583301101', '[e-mail]');
INSERT INTO `sys_user` VALUES (6, 'lishimin', '111111', '李世民', '15583301101', '[e-mail]');
INSERT INTO `sys_user` VALUES (7, 'lishikai', '111111', '李世凯', '15583301101', '[e-mail]');
INSERT INTO `sys_user` VALUES (8, 'lisikai', '111111', '李四开', '15583301101', '[e-mail]');

方案一、采用mysql连接允许多条sql执行，将update语句分次执行，使用的是mybatis的foreach语法遍历执行

1、dao层方法

/**
 * 批量更新用户信息（方式一：采用mysql连接允许多条sql执行，将update语句分次执行）
 */
int updateUserItem(List<SysUserEntity> list);

2、dao层xml语句

<!-- 批量更新用户信息 方式一 -->
<update id="updateUserItem"  parameterType="java.util.List">
	<foreach collection="list" item="item"  separator=";">
		update sys_user
		<set>
			<if test="item.username != null and item.username.trim() != ''">
				`username` = #{item.username},
			</if>
			<if test="item.password != null and item.password.trim() != ''">
				`password` = #{item.password},
			</if>
			<if test="item.realName != null and item.realName.trim() != ''">
				`real_name` = #{item.realName},
			</if>
			<if test="item.telephone != null and item.telephone.trim() != ''">
				`telephone` = #{item.telephone},
			</if>
			<if test="item.mail != null and item.mail.trim() != ''">
				`mail` = #{item.mail}
			</if>
		</set>
		where `id` = #{item.id}
	</foreach>
</update>

3、dao层方法单元测试

@Test
public void updateUserItemTest() {
    SysUserEntity sysUser = new SysUserEntity(3,"lisi", "123456", "李四",
            "12345", "[e-mail]");
    SysUserEntity sysUser2 = new SysUserEntity(6,"lishimin", "1234", "李世民",
            "66612142", "[e-mail]");
    List<SysUserEntity> list = new ArrayList<>();
    list.add(sysUser);
    list.add(sysUser2);
    sysUserDao.updateUserItem(list);
}

4、mysql访问连接属性设置开启多条语句执行

spring:
  datasource:
    #  &allowMultiQueries=true 表示允许执行多条sql
    url: jdbc:mysql://127.0.0.1:3306/learntest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true

5、测试结果

执行前：

执行数据修改数：

执行后：

方案二、采取case when语法，使用的是mybatis的 trim语法 + foreach语法执行

1、dao层方法

/**
 * 批量更新用户信息（方式二：采取case when语法）
 */
int updateUserItem2(List<SysUserEntity> list);

2、dao层xml语句

<!-- 批量更新用户信息 方式二 -->
<update id="updateUserItem2"  parameterType="java.util.List">
	update sys_user
	<trim prefix="set" suffixOverrides=",">
		<trim prefix="username = case" suffix="end,">
			<foreach collection="list" item="item">
				<if test="item.username != null and item.username.trim() != ''">
					when id=#{item.id} then #{item.username}
				</if>
			</foreach>
		</trim>
		<trim prefix="password = case" suffix="end,">
			<foreach collection="list" item="item">
				<if test="item.password != null and item.password.trim() != ''">
					when id=#{item.id} then #{item.password}
				</if>
			</foreach>
		</trim>
		<trim prefix="real_name = case" suffix="end,">
			<foreach collection="list" item="item">
				<if test="item.realName != null and item.realName.trim() != ''">
					when id=#{item.id} then #{item.realName}
				</if>
			</foreach>
		</trim>
		<trim prefix="telephone = case" suffix="end,">
			<foreach collection="list" item="item">
				<if test="item.telephone != null and item.telephone.trim() != ''">
					when id=#{item.id} then #{item.telephone}
				</if>
			</foreach>
		</trim>
		<trim prefix="mail = case" suffix="end,">
			<foreach collection="list" item="item">
				<if test="item.mail != null and item.mail.trim() != ''">
					when id=#{item.id} then #{item.mail}
				</if>
			</foreach>
		</trim>
	</trim>
	<where>
		<foreach collection="list" item="item" open="( " separator=") or (" close=" )">
			id = #{item.id}
		</foreach>
	</where>
</update>

3、dao层方法单元测试

@Test
public void updateUserItem2Test() {
    SysUserEntity sysUser = new SysUserEntity(2,"zhangsan", "123456", "张三11",
            "12121", "[e-mail]");
    SysUserEntity sysUser2 = new SysUserEntity(5,"zhangsanfeng", "1234", "李世民",
            "114114", "[e-mail]");
    List<SysUserEntity> list = new ArrayList<>();
    list.add(sysUser);
    list.add(sysUser2);
    sysUserDao.updateUserItem2(list);
}

4、测试结果

执行前：

执行数据修改数：

执行后：

源代码案例

码云地址：https://gitee.com/raoshuang/mybatis-batch-update
以上就是本次验证记录