一、背景
在mybatis框架中,我们针对大量数据操作,会采用批量操作的方式,让我们一起总结一些常见的批量操作形式吧,以下从增,改操作来叙述。
1、环境:
jdk1.8、maven
pom.xml
<!-- Mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>com.github.miemiedev</groupId>
<artifactId>mybatis-paginator</artifactId>
<version>1.2.15</version>
</dependency>
<!-- MySql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
2、表
CREATE TABLE `t_company_user_rel` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`company_id` bigint unsigned NOT NULL COMMENT '公司ID',
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='用户与公司关系表';
CREATE TABLE `t_member` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`file_number` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '文件编号',
`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
`sex` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别',
`id_card` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '身份证',
`phone_number` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电话号码',
`reg_time` date DEFAULT NULL COMMENT '注册时间',
`password` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '密码',
`email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电子邮箱',
`birthday` date DEFAULT NULL COMMENT '生日',
`remark` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8mb3;
3、 domain
public class CompanyUserRel implements Serializable {
/**
* 主键ID
*
* @mbg.generated
*/
private Long id;
/**
* 公司ID
*
* @mbg.generated
*/
private Long companyId;
/**
* 用户ID
*
* @mbg.generated
*/
private Long userId;
/**
* 创建时间
*
* @mbg.generated
*/
private Date createTime;
/**
* 更新时间
*
* @mbg.generated
*/
private Date updateTime;
private static final long serialVersionUID = 1L;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getCompanyId() {
return companyId;
}
public void setCompanyId(Long companyId) {
this.companyId = companyId;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getSimpleName());
sb.append(" [");
sb.append("Hash = ").append(hashCode());
sb.append(", id=").append(id);
sb.append(", companyId=").append(companyId);
sb.append(", userId=").append(userId);
sb.append(", createTime=").append(createTime);
sb.append(", updateTime=").append(updateTime);
sb.append(", serialVersionUID=").append(serialVersionUID);
sb.append("]");
return sb.toString();
}
}
public class Member implements Serializable {
private Integer id;
private String fileNumber;
private String name;
private String sex;
private String idCard;
private String phoneNumber;
private Date regTime;
private String password;
private String email;
private Date birthday;
private String remark;
private static final long serialVersionUID = 1L;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getFileNumber() {
return fileNumber;
}
public void setFileNumber(String fileNumber) {
this.fileNumber = fileNumber;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getIdCard() {
return idCard;
}
public void setIdCard(String idCard) {
this.idCard = idCard;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public Date getRegTime() {
return regTime;
}
public void setRegTime(Date regTime) {
this.regTime = regTime;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getSimpleName());
sb.append(" [");
sb.append("Hash = ").append(hashCode());
sb.append(", id=").append(id);
sb.append(", fileNumber=").append(fileNumber);
sb.append(", name=").append(name);
sb.append(", sex=").append(sex);
sb.append(", idCard=").append(idCard);
sb.append(", phoneNumber=").append(phoneNumber);
sb.append(", regTime=").append(regTime);
sb.append(", password=").append(password);
sb.append(", email=").append(email);
sb.append(", birthday=").append(birthday);
sb.append(", remark=").append(remark);
sb.append(", serialVersionUID=").append(serialVersionUID);
sb.append("]");
return sb.toString();
}
}
二、增
Mybatis在插入单条数据的时候有两种方式返回自增主键：
1、对于支持生成自增主键的数据库：增加 useGenerateKeys和keyProperty ，标签属性。
2、不支持生成自增主键的数据库：使用selectKey标签。
从官网资料可以看出Mybatis是支持批量插入时返回自增主键的。
但是在开发过程中如果遇到返回自增id失效情况，可以从以下方面排查问题：
1、Mybatis版本至少3.3.1以上。
2、在Mapper文件中，方法参数不能使用@Param注解。
3、Mapper.xml文件中，标签上指定参数类型（parameterType=“java.util.List”），设置useGeneratedKeys=“true”，keyProperty=“id”（list中元素的主键）。
1、除主键之外,无其它唯一索引限制的批量插入
<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
insert into t_member (file_number, name, sex,
id_card, phone_number, reg_time,
password, email, birthday,
remark)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.fileNumber,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, #{item.sex,jdbcType=VARCHAR},
#{item.idCard,jdbcType=VARCHAR}, #{item.phoneNumber,jdbcType=VARCHAR}, #{item.regTime,jdbcType=DATE},
#{item.password,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR}, #{item.birthday,jdbcType=DATE},
#{item.remark,jdbcType=VARCHAR})
</foreach>
</insert>
2、唯一键与主键同时存在的批量插入
<insert id="batchInsertOrUpdate" parameterType="java.util.List">
insert into t_company_user_rel (company_id, user_id, create_time,update_time) values
<foreach collection="list" separator="," item="item">
(#{item.companyId,jdbcType=BIGINT}, #{item.userId,jdbcType=BIGINT}, #{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP})
</foreach>
ON DUPLICATE KEY UPDATE
company_id=VALUES(company_id),update_time=VALUES(update_time)
</insert>
三、改
1、case...when 组合更新
<update id="batchCaseWhenUpdate" parameterType="java.util.List">
UPDATE t_member
<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="password =case" suffix="end,">
<foreach collection="list" item="item">
when id=#{item.id} then #{item.password}
</foreach>
</trim>
</trim>
WHERE id IN
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
2、sql批量更新
<update id="batchSqlUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" separator=";">
UPDATE t_member
SET name=#{item.name},password=#{item.password}
WHERE id=#{item.id}
</foreach>
</update>
注意: 组装多个update语句，这种方式需要设置jdbc连接 allowMultiQueries=true
