目錄
1.場(chǎng)景說(shuō)明
2.DUPLICATE?和REPLACE比較
3.批量插入或者更新(兩種方式)
方式一：mybatis-plus的saveOrUpdateBatch方法
問(wèn)題：如果操作類集成了基礎(chǔ)類，比如封裝了BaseEntity去集成，那么這樣使用會(huì)出問(wèn)題
方式二：on duplicate key (推薦)
4.注意
5.常見(jiàn)問(wèn)題?
1.場(chǎng)景說(shuō)明
插入數(shù)據(jù)時(shí)，我們經(jīng)常會(huì)遇到這樣的情況：
1、首先判斷數(shù)據(jù)是否存在；
2、如果不存在，則插入；
3、如果存在，則更新
需求：根據(jù)表中的部分字段去判斷插入或者更新
有一張表?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 '計(jì)劃id',
`cons` int(11) DEFAULT NULL COMMENT '消耗',
`exp` int(11) DEFAULT NULL COMMENT '曝光數(shù)',
`conv` int(11) DEFAULT NULL COMMENT '轉(zhuǎn)化數(shù)',
`click` int(11) DEFAULT NULL COMMENT '點(diǎn)擊數(shù)',
`dimension_time` varchar(32) DEFAULT NULL COMMENT '維度時(shí)間',
`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
`update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
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='監(jiān)測(cè)請(qǐng)求數(shù)據(jù)匯總';需要通過(guò)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)此時(shí)會(huì)發(fā)現(xiàn)依然會(huì)重復(fù)插入數(shù)據(jù)，需要?jiǎng)?chuàng)建一個(gè)組合索引
?添加完索引再次嘗試，code,plan_id,dimension_time相同的情況下只會(huì)更新不會(huì)新增
2.DUPLICATE?和REPLACE比較
replace into 跟 insert 功能類似，不同點(diǎn)在于：replace into 首先嘗試插入數(shù)據(jù)到表中， 1. 如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)（根據(jù)主鍵或者唯一索引判斷）則先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)。 2. 否則，直接插入新數(shù)據(jù)。
3.批量插入或者更新(兩種方式)
方式一：mybatis-plus的saveOrUpdateBatch方法
使用saveOrUpdateBatch方法直接調(diào)用就可以了，分別在持久層實(shí)現(xiàn)Mapper接口，服務(wù)層接口繼承 IService接口，實(shí)現(xiàn)類繼承 ServiceImpl接口
1.持久層代碼示例
說(shuō)明：繼承BaseMapper即可，泛型使用當(dāng)前要操作類
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方式調(diào)用saveOrUpdateBatch不需要寫(xiě)這個(gè)接口
boolean saveOrUpdateBatch(@Param("entities") Collection<HhChainCustomerInfo> hhChainCustomerInfos);
*/
}
2.服務(wù)層接口示例
說(shuō)明：繼承 IService即可，泛型使用當(dāng)前要操作類
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.服務(wù)實(shí)現(xiàn)類示例
說(shuō)明：繼承ServiceImpl即可，泛型使用持久層操作對(duì)象接口類和操作類
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業(yè)務(wù)層處理
*
* @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.服務(wù)層示例
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));
}
}
此時(shí)調(diào)用發(fā)現(xiàn)結(jié)果是成功的，數(shù)據(jù)庫(kù)數(shù)據(jù)也被更新了(省略過(guò)多的測(cè)試截圖)
?這種方式在執(zhí)行時(shí)候會(huì)通過(guò)id判斷是否有內(nèi)容，然后在做更新操作。從打印的sql日志可以看出
總結(jié)：
? ? ? ? 1.沒(méi)有唯一鍵(id)回自動(dòng)生成id后新增
? ? ? ? 2.有id會(huì)查詢后判斷
????????3.查詢后數(shù)據(jù)有差異會(huì)調(diào)用update語(yǔ)句更新
問(wèn)題：如果操作類集成了基礎(chǔ)類，比如封裝了BaseEntity去集成，那么這樣使用會(huì)出問(wèn)題
示例如下：
1.BaseEntity類(一般是實(shí)體類的公共參數(shù))
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;
/**
* 創(chuàng)建者
*/
private String createBy;
/**
* 創(chuàng)建時(shí)間
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ApiModelProperty(value = "創(chuàng)建時(shí)間", example = "2022-09-01 13:24:09")
private Date createTime;
/**
* 更新者
*/
private String updateBy;
/**
* 更新時(shí)間
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@ApiModelProperty(value = "創(chuàng)建時(shí)間", example = "2022-09-01 13:24:09")
private Date updateTime;
/**
* 備注
*/
private String remark;
/**
* 請(qǐng)求參數(shù)
*/
@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;
/**
* 鏈路客戶信息對(duì)象 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;
/**
* 服務(wù)
*/
@Excel(name = "服務(wù)")
@ApiModelProperty(value = "服務(wù)")
private Long serviceId;
/**
* 名稱
*/
@Excel(name = "名稱")
@ApiModelProperty(value = "名稱")
private String name;
/**
* 編碼
*/
@Excel(name = "編碼")
@ApiModelProperty(value = "編碼")
private String code;
/**
* 回傳請(qǐng)求方式(1.GET 2.POST)
*/
@Excel(name = "回傳請(qǐng)求方式(1.GET 2.POST)")
@ApiModelProperty(value = "回傳請(qǐng)求方式(1.GET 2.POST)")
private Integer reqMode;
/**
* 上報(bào)接口
*/
@Excel(name = "上報(bào)接口")
@ApiModelProperty(value = "上報(bào)接口")
private String reqApi;
/**
* 簽名策略
*/
@Excel(name = "簽名策略")
@ApiModelProperty(value = "簽名策略")
private Integer signPolicy;
}
此時(shí)想要進(jìn)行批量插入或者更新會(huì)出現(xiàn)以下問(wèn)題：
有請(qǐng)求id時(shí)候被認(rèn)為是更新，更新會(huì)通過(guò)id查詢判斷，問(wèn)題就出在這里。plus在進(jìn)行查詢時(shí)候通過(guò)操作類屬性去查詢，導(dǎo)致了集成的父類屬性也進(jìn)去了，然而在表里面是沒(méi)有這些字段的，所以出現(xiàn)了上述問(wèn)題。
方式二：on duplicate key (推薦)
說(shuō)明：通過(guò)sql的方式實(shí)現(xiàn)批量的插入或更新，這種方式需要有唯一索引，通過(guò)唯一索引去判斷是否沖突，有沖突就會(huì)更新，沒(méi)有沖突就會(huì)插入數(shù)據(jù)。
<!-- 批量插入或者更新 -->
<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;
/**
* 鏈路客戶參數(shù)Mapper接口
*
* @author huachun
* @date 2023-01-31
*/
@Mapper
public interface HhChainCustomerParamsMapper extends BaseMapper<HhChainCustomerParams> {
boolean insertOrUpdateBatch(@Param("entities") Collection<HhChainCustomerParams> hhChainCustomerParams);
}
2.服務(wù)實(shí)現(xiàn)類
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;
/**
* 鏈路客戶參數(shù)Service業(yè)務(wù)層處理
*
* @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);
}
}
此時(shí)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耗時(shí) ==>: 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耗時(shí) ==>: 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耗時(shí) ==>: 0 毫秒
個(gè)人感覺(jué)這樣效率更改更方便，值得推薦
4.注意
on udplicate key update后的內(nèi)容表示，主鍵存在時(shí)則執(zhí)行更新操作,需要注意的是insert字段中需要含有唯一性字段（主鍵索引或唯一索引）
原文參考?mysql插入或更新_w_t_y_y的博客-CSDN博客_mysql 插入更新文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-443280.html
5.常見(jiàn)問(wèn)題?
詳細(xì)后續(xù)補(bǔ)充~文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-443280.html
到了這里，關(guān)于批量插入或更新數(shù)據(jù)(MyBatis-plus框架)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
