關(guān)于批量插入或更新數(shù)據(jù)(MyBatis-plus框架)

更新時(shí)間：2023年09月18日 17:03:14 作者：春卷-huachun

這篇文章主要介紹了關(guān)于批量插入或更新數(shù)據(jù)(MyBatis-plus框架),具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

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不需要寫這個(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 毫秒