目录
1.场景说明
2.DUPLICATE 和REPLACE比较
3.批量插入或者更新(两种方式)
方式一：mybatis-plus的saveOrUpdateBatch方法
问题：如果操作类集成了基础类，比如封装了BaseEntity去集成，那么这样使用会出问题
方式二：on duplicate key (推荐)
4.注意
5.常见问题
插入数据时，我们经常会遇到这样的情况：
1、首先判断数据是否存在；
2、如果不存在，则插入；
3、如果存在，则更新
需求：根据表中的部分字段去判断插入或者更新
有一张表 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相同的情况下只会更新不会新增
replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。
使用saveOrUpdateBatch方法直接调用就可以了，分别在持久层实现Mapper接口，服务层接口继承 IService接口，实现类继承 ServiceImpl接口
1.持久层代码示例
说明：继承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;
/**
* 链路客户信息Mapper接口
*
* @author huachun
* @date 2023-01-31
*/
@Mapper
public interface HhChainCustomerInfoMapper extends BaseMapper<HhChainCustomerInfo> {
/**
* 使用mybatis-plus方式调用saveOrUpdateBatch不需要写这个接口
boolean saveOrUpdateBatch(@Param("entities") Collection<HhChainCustomerInfo> hhChainCustomerInfos);
*/
}
2.服务层接口示例
说明：继承 IService即可，泛型使用当前要操作类
package com.hhmt.delivery.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.hhmt.delivery.pojo.entity.HhChainCustomerInfo;
import java.util.List;
/**
* 链路客户信息Service接口
*
* @author huachun
* @date 2023-01-31
*/
public interface IHhChainCustomerInfoService extends IService<HhChainCustomerInfo> {
}
3.服务实现类示例
说明：继承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;
/**
* 链路客户信息Service业务层处理
*
* @author huachun
* @date 2023-01-31
*/
@Service
public class HhChainCustomerInfoServiceImpl extends ServiceImpl<HhChainCustomerInfoMapper, HhChainCustomerInfo> implements IHhChainCustomerInfoService {
/*@Override
public boolean saveOrUpdateBatch(Collection<HhChainCustomerInfo> entityList) {
return hhChainCustomerInfoMapper.saveOrUpdateBatch(entityList);
}*/
}
4.服务层示例
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;
/**
* 链路客户信息Controller
*
* @author huachun
* @date 2023-01-31
*/
@Api(tags = "链路客户信息")
@RestController
@RequestMapping("/chain/HhChainCustomerInfo")
public class HhChainCustomerInfoController extends BaseController {
@Autowired
private IHhChainCustomerInfoService hhChainCustomerInfoService;
@ApiOperation("批量插入或更新客户信息")
@PostMapping("/batch")
public ResultVo<Integer> addBatch(@Validated(value = Add.class) @RequestBody List<HhChainCustomerInfo> hhChainCustomerInfos) {
return toAjax(hhChainCustomerInfoService.saveOrUpdateBatch(hhChainCustomerInfos));
}
}
此时调用发现结果是成功的，数据库数据也被更新了(省略过多的测试截图)
这种方式在执行时候会通过id判断是否有内容，然后在做更新操作。从打印的sql日志可以看出
总结：
1.没有唯一键(id)回自动生成id后新增
2.有id会查询后判断
3.查询后数据有差异会调用update语句更新
示例如下：
1.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;
/**
* Entity基类
*
* @author huachun
*/
@Data
public 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<String, Object> params;
}
2.操作类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;
/**
* 链路客户信息对象 hh_chain_customer_info
*
* @author huachun
* @date 2023-01-31
*/
@EqualsAndHashCode(callSuper = true)
@Data
public class HhChainCustomerInfo extends BaseEntity {
private static final long serialVersionUID = 1L;
/**
* 主键ID
*/
@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;
/**
* 回传请求方式(1.GET 2.POST)
*/
@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在进行查询时候通过操作类属性去查询，导致了集成的父类属性也进去了，然而在表里面是没有这些字段的，所以出现了上述问题。
说明：通过sql的方式实现批量的插入或更新，这种方式需要有唯一索引，通过唯一索引去判断是否冲突，有冲突就会更新，没有冲突就会插入数据。
<!-- 批量插入或者更新 -->
<insert id="saveOrUpdateBatch" keyProperty="id" useGeneratedKeys="true">
insert into hh_chain_customer_info(id,description, create_time, update_time, service_id, name, code, req_mode,
req_api, sign_policy)
values
<foreach collection="entities" item="entity" separator=",">
(#{entity.id},#{entity.description}, #{entity.createTime}, #{entity.updateTime}, #{entity.serviceId},
#{entity.name},
#{entity.code}, #{entity.reqMode}, #{entity.reqApi}, #{entity.signPolicy})
</foreach>
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)
</insert>1.持久层代码示例
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;
/**
* 链路客户参数Mapper接口
*
* @author huachun
* @date 2023-01-31
*/
@Mapper
public interface HhChainCustomerParamsMapper extends BaseMapper<HhChainCustomerParams> {
boolean insertOrUpdateBatch(@Param("entities") Collection<HhChainCustomerParams> hhChainCustomerParams);
}
2.服务实现类
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;
/**
* 链路客户参数Service业务层处理
*
* @author huachun
* @date 2023-01-31
*/
@Service
public class HhChainCustomerParamsServiceImpl extends ServiceImpl<HhChainCustomerParamsMapper, HhChainCustomerParams> implements IHhChainCustomerParamsService {
@Autowired
private HhChainCustomerParamsMapper hhChainCustomerParamsMapper;
@Override
public boolean saveOrUpdateBatch(Collection<HhChainCustomerParams> 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 毫秒
个人感觉这样效率更改更方便，值得推荐
on udplicate key update后的内容表示，主键存在时则执行更新操作,需要注意的是insert字段中需要含有唯一性字段（主键索引或唯一索引）
原文参考 mysql插入或更新_w_t_y_y的博客-CSDN博客_mysql 插入更新
详细后续补充~