批量插入或更新数据(MyBatis-plus框架)
目录
方式一：mybatis-plus的saveOrUpdateBatch方法
问题：如果操作类集成了基础类，比如封装了BaseEntity去集成，那么这样使用会出问题
1.场景说明
插入数据时，我们经常会遇到这样的情况：
首先判断数据是否存在；
如果不存在，则插入；
如果存在，则更新
需求：根据表中的部分字段去判断插入或者更新
有一张表 hh_adx_monitor_summary
ddl:
CREATE TABLE `hh_adx_monitor_summary` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `code` varchar(6) DEFAULT NULL COMMENT '链路编码', `plan_id` varchar(32) DEFAULT NULL COMMENT '计划id', `cons` int(11) DEFAULT NULL COMMENT '消耗', `exp` int(11) DEFAULT NULL COMMENT '曝光数', `conv` int(11) DEFAULT NULL COMMENT '转化数', `click` int(11) DEFAULT NULL COMMENT '点击数', `dimension_time` varchar(32) DEFAULT NULL COMMENT '维度时间', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `idx_hh_adx_monitor_summary_cpd` (`code`,`plan_id`,`dimension_time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 COMMENT='监测请求数据汇总';需要通过code,plan_id,dimension_time判断插入或者更新
INSERT INTO hh_adx_monitor_summary ( CODE, plan_id, cons, exp, conv, click, dimension_time)VALUES( '001001', '1', 6, 3, 0, 0, '20220823' ) ON DUPLICATE KEY UPDATE CODE =VALUES( CODE ),plan_id =VALUES( plan_id ),cons =VALUES( cons ),exp =VALUES( exp ),conv =VALUES( conv ),click =VALUES( click ),dimension_time =VALUES(dimension_time)此时会发现依然会重复插入数据，需要创建一个组合索引
添加完索引再次尝试，code,plan_id,dimension_time相同的情况下只会更新不会新增
2.DUPLICATE 和REPLACE比较
replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。
3.批量插入或者更新(两种方式)
方式一：mybatis-plus的saveOrUpdateBatch方法
使用saveOrUpdateBatch方法直接调用就可以了，分别在持久层实现Mapper接口，服务层接口继承 IService接口，实现类继承 ServiceImpl接口
持久层代码示例
说明：继承BaseMapper即可，泛型使用当前要操作类
package com.hhmt.delivery.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.hhmt.delivery.pojo.entity.HhChainCustomerInfo;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import java.util.Collection;import java.util.List;@Mapperpublic interface HhChainCustomerInfoMapper extends BaseMapper { } 服务层接口示例
说明：继承 IService即可，泛型使用当前要操作类
package com.hhmt.delivery.service;import com.baomidou.mybatisplus.extension.service.IService;import com.hhmt.delivery.pojo.entity.HhChainCustomerInfo;import java.util.List;public interface IHhChainCustomerInfoService extends IService { } 服务实现类示例
说明：继承ServiceImpl即可，泛型使用持久层操作对象接口类和操作类
package com.hhmt.delivery.service.impl;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import com.hhmt.delivery.mapper.HhChainCustomerInfoMapper;import com.hhmt.delivery.pojo.entity.HhChainCustomerInfo;import org.springframework.stereotype.Service;import java.util.List;@Servicepublic class HhChainCustomerInfoServiceImpl extends ServiceImpl implements IHhChainCustomerInfoService { } 服务层示例
package com.hhmt.delivery.controller;import com.hhmt.delivery.core.controller.BaseController;import com.hhmt.delivery.core.domain.model.ResultVo;import com.hhmt.delivery.core.page.TableDataInfo;import com.hhmt.delivery.pojo.entity.HhChainCustomerInfo;import com.hhmt.delivery.pojo.model.query.HhChainCustomerInfoQuery;import com.hhmt.delivery.pojo.model.vo.HhChainCustomerInfoVo;import com.hhmt.delivery.service.IHhChainCustomerInfoService;import com.hhmt.delivery.valiadtion.Add;import com.hhmt.delivery.valiadtion.Update;import io.swagger.annotations.Api;import io.swagger.annotations.ApiOperation;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.validation.annotation.Validated;import org.springframework.web.bind.annotation.DeleteMapping;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.PutMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@Api(tags = "链路客户信息")@RestController@RequestMapping("/chain/HhChainCustomerInfo")public class HhChainCustomerInfoController extends BaseController { @Autowired private IHhChainCustomerInfoService hhChainCustomerInfoService; @ApiOperation("批量插入或更新客户信息") @PostMapping("/batch") public ResultVo addBatch(@Validated(value = Add.class) @RequestBody List hhChainCustomerInfos) { return toAjax(hhChainCustomerInfoService.saveOrUpdateBatch(hhChainCustomerInfos)); } } 此时调用发现结果是成功的，数据库数据也被更新了(省略过多的测试截图)
这种方式在执行时候会通过id判断是否有内容，然后在做更新操作。从打印的sql日志可以看出
总结：
1.没有唯一键(id)回自动生成id后新增
2.有id会查询后判断
3.查询后数据有差异会调用update语句更新
问题：如果操作类集成了基础类，比如封装了BaseEntity去集成，那么这样使用会出问题
示例如下：
BaseEntity类(一般是实体类的公共参数)
package com.hhmt.delivery.core.domain.model;import com.fasterxml.jackson.annotation.JsonFormat;import com.fasterxml.jackson.annotation.JsonIgnore;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import java.io.Serializable;import java.util.Date;import java.util.Map;@Datapublic class BaseEntity implements Serializable { private static final long serialVersionUID = 1L; private String searchValue; private String createBy; @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") @ApiModelProperty(value = "创建时间", example = "2022-09-01 13:24:09") private Date createTime; private String updateBy; @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") @ApiModelProperty(value = "创建时间", example = "2022-09-01 13:24:09") private Date updateTime; private String remark; @JsonIgnore private Map params;} 操作类HhChainCustomerInfo继承了BaseEntity
package com.hhmt.delivery.pojo.entity;import com.hhmt.delivery.annotation.Excel;import com.hhmt.delivery.constant.VerificationTips;import com.hhmt.delivery.core.domain.model.BaseEntity;import com.hhmt.delivery.valiadtion.Update;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.EqualsAndHashCode;import javax.validation.constraints.NotNull;@EqualsAndHashCode(callSuper = true)@Datapublic class HhChainCustomerInfo extends BaseEntity { private static final long serialVersionUID = 1L; @NotNull(groups = Update.class, message = VerificationTips.EMPTY_TIPS) @ApiModelProperty(value = "${comment}") private Long id; @Excel(name = "描述") @ApiModelProperty(value = "描述") private String description; @Excel(name = "服务") @ApiModelProperty(value = "服务") private Long serviceId; @Excel(name = "名称") @ApiModelProperty(value = "名称") private String name; @Excel(name = "编码") @ApiModelProperty(value = "编码") private String code; @Excel(name = "回传请求方式(1.GET 2.POST)") @ApiModelProperty(value = "回传请求方式(1.GET 2.POST)") private Integer reqMode; @Excel(name = "上报接口") @ApiModelProperty(value = "上报接口") private String reqApi; @Excel(name = "签名策略") @ApiModelProperty(value = "签名策略") private Integer signPolicy;}此时想要进行批量插入或者更新会出现以下问题：
有请求id时候被认为是更新，更新会通过id查询判断，问题就出在这里。plus在进行查询时候通过操作类属性去查询，导致了集成的父类属性也进去了，然而在表里面是没有这些字段的，所以出现了上述问题。
方式二：on duplicate key (推荐)
说明：通过sql的方式实现批量的插入或更新，这种方式需要有唯一索引，通过唯一索引去判断是否冲突，有冲突就会更新，没有冲突就会插入数据。
insert into hh_chain_customer_info(id,description, create_time, update_time, service_id, name, code, req_mode, req_api, sign_policy) values (#{entity.id},#{entity.description}, #{entity.createTime}, #{entity.updateTime}, #{entity.serviceId}, #{entity.name}, #{entity.code}, #{entity.reqMode}, #{entity.reqApi}, #{entity.signPolicy}) on duplicate key update description = values(description), create_time = values(create_time), update_time = values(update_time), service_id = values(service_id), name = values(name), code = values(code), req_mode = values(req_mode), req_api = values(req_api), sign_policy = values(sign_policy) 持久层代码示例
package com.hhmt.delivery.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.hhmt.delivery.pojo.entity.HhChainCustomerParams;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import java.util.Collection;import java.util.List;@Mapperpublic interface HhChainCustomerParamsMapper extends BaseMapper { boolean insertOrUpdateBatch(@Param("entities") Collection hhChainCustomerParams);} 服务实现类
package com.hhmt.delivery.service.impl;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import com.hhmt.delivery.core.utils.DateUtils;import com.hhmt.delivery.mapper.HhChainCustomerParamsMapper;import com.hhmt.delivery.pojo.entity.HhChainCustomerParams;import com.hhmt.delivery.service.IHhChainCustomerParamsService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.Collection;import java.util.List;@Servicepublic class HhChainCustomerParamsServiceImpl extends ServiceImpl implements IHhChainCustomerParamsService { @Autowired private HhChainCustomerParamsMapper hhChainCustomerParamsMapper; @Override public boolean saveOrUpdateBatch(Collection entityList) { return hhChainCustomerParamsMapper.insertOrUpdateBatch(entityList); }} 此时sql日志：
拦截的sql ==>: com.hhmt.delivery.mapper.HhChainCustomerInfoMapper.saveOrUpdateBatch:insert into hh_chain_customer_info(id,description, create_time, update_time, service_id, name, code, req_mode, req_api, sign_policy) values (1621028577047281666,, , , , '111111111111', , , , ) on duplicate key update description = values(description), create_time = values(create_time), update_time = values(update_time), service_id = values(service_id), name = values(name), code = values(code), req_mode = values(req_mode), req_api = values(req_api), sign_policy = values(sign_policy)
sql耗时 ==>: 14 毫秒
类型 ==> INSERT
拦截的sql ==>: com.hhmt.delivery.mapper.HhChainCustomerInfoMapper.saveOrUpdateBatch:insert into hh_chain_customer_info(id,description, create_time, update_time, service_id, name, code, req_mode, req_api, sign_policy) values (1621028577047281666,, , , , 'dsfasdfadf', , , , ) on duplicate key update description = values(description), create_time = values(create_time), update_time = values(update_time), service_id = values(service_id), name = values(name), code = values(code), req_mode = values(req_mode), req_api = values(req_api), sign_policy = values(sign_policy)
sql耗时 ==>: 0 毫秒
类型 ==> INSERT
拦截的sql ==>: com.hhmt.delivery.mapper.HhChainCustomerInfoMapper.saveOrUpdateBatch:insert into hh_chain_customer_info(id,description, create_time, update_time, service_id, name, code, req_mode, req_api, sign_policy) values (,, , , , 'dsfasdfadf', , , , ) on duplicate key update description = values(description), create_time = values(create_time), update_time = values(update_time), service_id = values(service_id), name = values(name), code = values(code), req_mode = values(req_mode), req_api = values(req_api), sign_policy = values(sign_policy)
sql耗时 ==>: 0 毫秒
个人感觉这样效率更改更方便，值得推荐
4.注意
on udplicate key update后的内容表示，主键存在时则执行更新操作,需要注意的是insert字段中需要含有唯一性字段（主键索引或唯一索引）
原文参考 mysql插入或更新_w_t_y_y的博客-CSDN博客_mysql 插入更新
5.常见问题
详细后续补充~
来源地址：https://blog.csdn.net/hauchun/article/details/126488062
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/36.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
