mybatis-plus的批量新增/批量更新以及問題

更新時(shí)間：2023年04月10日 14:21:18 作者：Filwaod

這篇文章主要介紹了Mybatis-Plus實(shí)現(xiàn)批量新增與批量更新以及出現(xiàn)的問題，文章中有詳細(xì)的代碼示例，感興趣的同學(xué)可以參考一下

第一種就不說了，重復(fù)的IO連接與斷開效率極低，性能很差，不考慮
第二種使用多線程進(jìn)行批量插入/修改，時(shí)間會(huì)大大降低，但還會(huì)有頻繁建立斷開IO，性能不好
第三種其實(shí)就是在拼sql，但是不同業(yè)務(wù)要拼不同的sql，復(fù)用性很差
第四種本質(zhì)也是拼sql，但是通過簡(jiǎn)單的配置就可以達(dá)到不同業(yè)務(wù)的復(fù)用

1.代碼中foreach insert/update

 for(int i=0;i<insertList.size();i++){
     offerMapper.insert(offerDO);
 }

更新同理

2.多線程foreach insert/update

工作中也使用過多線程批量更新，新增同理

//定義線程池
private static final Long KEEP_ALIVE_TIME = 60L;
private static final int APS = Runtime.getRuntime().availableProcessors();
private static final ThreadPoolExecutor THREAD_POOL_EXECUTOR = new ThreadPoolExecutor(
        APS * 2,
        APS * 4,
        KEEP_ALIVE_TIME,
        TimeUnit.SECONDS,
        new LinkedBlockingDeque<>(256),
        new ThreadFactoryBuilder().setNameFormat("分揀出庫(kù)-pool-%d").build(),
        new ThreadPoolExecutor.CallerRunsPolicy()
);

//使用
try {
    taskExecute(list, yearList);
} catch (Exception e) {
    log.error("分揀出庫(kù)更新失?。簕}", e);
}

// 處理單個(gè)任務(wù)數(shù)據(jù)（year是分庫(kù)分表用的）
private void taskExecute(List<SortingOutboundProductDetailDO> list, List<Integer> yearList) throws Exception {
    if (CollectionUtils.isEmpty(list)) {
        return;
    }

    final CountDownLatch latch = new CountDownLatch(list.size());
    for (SortingOutboundProductDetailDO data : list) {

        THREAD_POOL_EXECUTOR.submit(() -> {
            try {
                //更新從表
                sortingOutboundProductDetailMapper.update(null,
                        new LambdaUpdateWrapper<SortingOutboundProductDetailDO>()
                                .eq(SortingOutboundProductDetailDO::getId, data.getId())
                                .in(SortingOutboundProductDetailDO::getYear, yearList)
                                .set(SortingOutboundProductDetailDO::getOutboundNumber, data.getOutboundNumber())
                );
            } finally {
                if (latch != null) {
                    latch.countDown();
                }
            }
        });
    }
    latch.await();
}

3.mybatis xml中foreach

批量新增

//不用關(guān)注里面的業(yè)務(wù)代碼
private int insert(OfferSaveRequest request){
    List<OfferDO> insertOffer = request.getOfferList().stream().map(obj -> {
        OfferDO offerDO = new OfferDO();
        offerDO.setId(IdWorker.getId());
        offerDO.setFirstSubjectId(request.getFirstSubjectId());
        offerDO.setWarehouseNum(request.getWarehouseNum());
        offerDO.setExpressCompany(obj.getExpressCompany());
        offerDO.setExpressCompanyName(obj.getExpressCompanyName());
        offerDO.setArea(obj.getArea());
        offerDO.setExpensesItemName(obj.getExpensesItemName());
        offerDO.setUnit(obj.getUnit());
        offerDO.setFees(obj.getFees());
        offerDO.setDescription(obj.getDescription());
        offerDO.setTransportType(generateTransportType(obj.getExpensesItemName()));
        offerDO.setCreateTime(new Date());
        offerDO.setCreateUserId(1L);
        offerDO.setCreateUserName("管理員");
        return offerDO;
    }).collect(Collectors.toList());
    return offerMapper.batchInsert(insertOffer);
}

xml

<insert id="batchInsert" parameterType="com.model.OfferDO">
    INSERT INTO offer(
    id,
    first_subject_id,
    warehouse_num,
    express_company,
    express_company_name,
    area,
    expenses_item_name,
    unit,
    fees,
    description,
    create_time,
    create_user_id,
    create_user_name
    )
    values
    <foreach collection="offerList" separator="," item="offer">
    (
    #{offer.id},
    #{offer.firstSubjectId},
    #{offer.warehouseNum},
    #{offer.expressCompany},
    #{offer.expressCompanyName},
    #{offer.area},
    #{offer.expensesItemName},
    #{offer.unit},
    #{offer.fees},
    #{offer.description},
    #{offer.createTime},
    #{offer.createUserId},
    #{offer.createUserName}
    )
    </foreach>
</insert>

批量修改

//不用關(guān)注里面的業(yè)務(wù)代碼
List<OfferSaveRequest.Offer> updateList = request.getOfferList().stream()
    .filter(obj -> obj.getId() != null).collect(Collectors.toList());
if (updateList.size() > 0) {
    List<OfferDO> updateOffer = updateList.stream().map(obj -> {
        OfferDO offerDO = new OfferDO();
        offerDO.setId(obj.getId());
        offerDO.setArea(obj.getArea());
        offerDO.setFees(obj.getFees());
        offerDO.setDescription(obj.getDescription());
        offerDO.setUpdateTime(new Date());
        offerDO.setUpdateUserId(1L);
        offerDO.setUpdateUserName("管理員");
        return offerDO;
    }).collect(Collectors.toList());
    offerMapper.batchUpdate(updateOffer);
}

xml

<update id="batchUpdate" parameterType="com.model.OfferDO">
    <foreach collection="offerList" item="offer" separator=";">
        update
        offer
        set
        <if test="offer.area!=null and offer.area!=''">
            area=#{offer.area},
        </if>
        <if test="offer.fees!=null">
            fees=#{offer.fees},
        </if>
        <if test="offer.description!=null and offer.description!=''">
            description=#{offer.description},
        </if>
        update_time=#{offer.updateTime},
        update_user_id=#{offer.updateUserId},
        update_user_name=#{offer.updateUserName}
        where
        id = #{offer.id}
    </foreach>
</update>

批量修改還需要在配置文件中配置&allowMultiQueries=true，否則報(bào)錯(cuò)

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowMultiQueries=true

4.mybatis-plus擴(kuò)展

1.創(chuàng)建sql注入器

/**
 * 自定義方法SQL注入器
 * 【注意】這個(gè)類名，可以隨便命名
 */
public class MyInjector extends DefaultSqlInjector  {

    /**
     * 如果只需增加方法，保留MyBatis plus自帶方法，
     * 可以先獲取super.getMethodList()，再添加add
     */
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        // 注意：此SQL注入器繼承了DefaultSqlInjector(默認(rèn)注入器)，調(diào)用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自帶方法
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new InsertBatchMethod());
        methodList.add(new UpdateBatchMethod());
        return methodList;
    }
 
}

2.注入容器

@Configuration
@MapperScan("com.yida.mapper")
public class MybatisPlusPageConfig {

    @Bean
    public MyInjector myInjector(){
        return new MyInjector();
    }
}

3.定義通用mapper

/**
 * 公共mapper
 * 要實(shí)現(xiàn)批量新增/修改 繼承此類
 *
 * @param <T>
 */
public interface CommonMapper<T> extends BaseMapper<T> {

    /**
     * 自定義批量插入
     * 如果要自動(dòng)填充，@Param(xx) xx參數(shù)名必須是 list/collection/array 3個(gè)的其中之一
     */
    int insertBatch(@Param("list") List<T> list);

    /**
     * 自定義批量更新，條件為主鍵
     * 如果要自動(dòng)填充，@Param(xx) xx參數(shù)名必須是 list/collection/array 3個(gè)的其中之一
     */
    int updateBatch(@Param("list") List<T> list);
}

4.新增/修改

/**
 * 批量新增
 */
@Slf4j
public class InsertBatchMethod extends AbstractMethod {
    /**
     * insert into user(id, name, age) values (1, "a", 17), (2, "b", 18);
     <script>
     insert into user(id, name, age) values
     <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
          #{item.id}, #{item.name}, #{item.age}
     </foreach>
     </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldSql = prepareFieldSql(tableInfo);
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
        log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        // 第三個(gè)參數(shù)必須和RootMapper的自定義方法名一致
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);
    }
  
    private String prepareFieldSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        fieldSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> {
            //新增時(shí)修改字段不填充
            if (!("update_time".equals(x.getColumn()))
                    &&!("update_user_id".equals(x.getColumn()))
                    &&!("update_user_name".equals(x.getColumn()))){
                fieldSql.append(x.getColumn()).append(",");
            }
        });
        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
        fieldSql.insert(0, "(");
        fieldSql.append(")");
        return fieldSql.toString();
    }
  
    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        tableInfo.getFieldList().forEach(x -> {
            if (!("updateTime".equals(x.getProperty()))
                    &&!("updateUserId".equals(x.getProperty()))
                    &&!("updateUserName".equals(x.getProperty()))){
                valueSql.append("#{item.").append(x.getProperty()).append("},");
            }
        });
        valueSql.delete(valueSql.length() - 1, valueSql.length());
        valueSql.append("</foreach>");
        return valueSql.toString();
    }
}

/**
 * 批量更新方法實(shí)現(xiàn)，條件為主鍵，選擇性更新
 */
@Slf4j
public class UpdateBatchMethod extends AbstractMethod {
    /**
     * update user set name = "a", age = 17 where id = 1;
     * update user set name = "b", age = 18 where id = 2;
     <script>
        <foreach collection="list" item="item" separator=";">
            update user
            <set>
                <if test="item.name != null and item.name != ''">
                    name = #{item.name,jdbcType=VARCHAR},
                </if>
                <if test="item.age != null">
                    age = #{item.age,jdbcType=INTEGER},
                </if>
            </set>
            where id = #{item.id,jdbcType=INTEGER}
        </foreach>
     </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sql = "<script>\n<foreach collection=\"list\" item=\"item\" separator=\";\">\nupdate %s %s where %s=#{%s} %s\n</foreach>\n</script>";
        String additional = tableInfo.isWithVersion() ? tableInfo.getVersionFieldInfo().getVersionOli("item", "item.") : "" + tableInfo.getLogicDeleteSql(true, true);
        String setSql = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "item", "item.");
        String sqlResult = String.format(sql, tableInfo.getTableName(), setSql, tableInfo.getKeyColumn(), "item." + tableInfo.getKeyProperty(), additional);
        log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        // 第三個(gè)參數(shù)必須和RootMapper的自定義方法名一致
        return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);
    }
  
}

5.使用，將原有的繼承BaseMapper的方法，改寫為繼承CommonMapper，后續(xù)批量操作，直接使用新增的兩個(gè)方法進(jìn)行處理即可。

public interface OfferMapper extends CommonMapper<OfferDO> {
}

新增：offerMapper.insertBatch(insertOffer)
更新：offerMapper.updateBatch(updateOffer)

not null問題

在實(shí)際使用中發(fā)現(xiàn)一個(gè)問題，這個(gè)批量插入是在項(xiàng)目啟動(dòng)后就進(jìn)行拼接好的sql，然后調(diào)用的時(shí)候，進(jìn)行值得替換，例

<script>
INSERT INTO express (id,express_name,express_code,state,create_time,create_user_id,create_user_name) VALUES <foreach collection="list" item="et" separator=",">
(#{id},#{expressName},#{expressCode},#{state},#{createTime},#{createUserId},#{createUserName})
</foreach>
</script>

發(fā)現(xiàn)是全量新增，這樣也就產(chǎn)生了一個(gè)問題，當(dāng)只想新增一部分?jǐn)?shù)據(jù)，剩下的一部分?jǐn)?shù)據(jù)更新進(jìn)去時(shí)，有時(shí)會(huì)有問題，這取決于你數(shù)據(jù)庫(kù)中字段設(shè)置是可以為null還是不可以為null。
當(dāng)數(shù)據(jù)庫(kù)中字段設(shè)置為not null，而新增的時(shí)候傳一個(gè)null，就會(huì)觸發(fā)數(shù)據(jù)庫(kù)的not null校驗(yàn)，報(bào)錯(cuò)

然后就查資料，改代碼，測(cè)試，然后官網(wǎng)上是這樣說的

參考：https://blog.csdn.net/weixin_45505313/article/details/121574166

Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or 
INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column
data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value
for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row
inserts because the server does not examine the result set from the SELECT to see whether it returns
a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column.
Instead, the statement fails with an error.)

在聲明為NOT NULL的列中插入NULL。對(duì)于多行INSERT語句或
插入…SELECT語句時(shí)，該列被設(shè)置為該列的隱式默認(rèn)值
數(shù)據(jù)類型。數(shù)值類型為0，字符串類型為空字符串(")，值為" 0 "
用于日期和時(shí)間類型。插入…SELECT語句的處理方式與多行語句相同
插入，因?yàn)榉?wù)器不檢查SELECT的結(jié)果集，看它是否返回
單行。(對(duì)于單行INSERT，當(dāng)NULL插入到NOT NULL列時(shí)，不會(huì)出現(xiàn)警告。
相反，語句失敗并報(bào)錯(cuò)。)

也就是說mysql允許批量插入時(shí)，向not null字段插入null值，mysql會(huì)給其賦一個(gè)隱藏值
但是在我實(shí)測(cè)下發(fā)現(xiàn)并不行，然后又開始查資料，被我發(fā)現(xiàn)了這個(gè)

那么我就查了一下我的數(shù)據(jù)庫(kù)模式

select @@sql_mode;

結(jié)果：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

可以看出我的數(shù)據(jù)庫(kù)模式為嚴(yán)格模式，怪不得官網(wǎng)說可以插入null，而我的代碼一直報(bào)錯(cuò)，排坑之旅任重道遠(yuǎn)

解決方案：
1.關(guān)閉數(shù)據(jù)庫(kù)的嚴(yán)格模式（公司的數(shù)據(jù)庫(kù)沒有權(quán)限，這個(gè)直接pass掉）
2.手動(dòng)拼批量插入的sql，拼成如下樣子，然后一次請(qǐng)求執(zhí)行，這樣只進(jìn)行了一次數(shù)據(jù)庫(kù)連接，也可以實(shí)現(xiàn)批量插入的效果，但是不知道這種多個(gè)INSERT語句與單個(gè)INSERT和多個(gè)VALUES的性能怎么樣

INSERT INTO `oss` VALUES (1, -1, '', '測(cè)試用文件.docx', '', '', 0, '', '2022-12-08 16:21:33', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (2, -1, '', '測(cè)試用文件.docx', '', '', 0, '', '2022-12-08 16:32:32', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (3, -1, '', '測(cè)試用文件.docx', '', '', 0, '', '2022-12-08 16:33:17', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (4, -1, '', '測(cè)試用文件.docx', '', '', 0, '', '2022-12-08 16:44:30', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (5, -1, '', '測(cè)試用文件.docx', '', '', 0, '', '2022-12-08 16:45:28', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (6, -1, '', '合同導(dǎo)入測(cè)試.doc','', '', 0, '', '2022-12-08 16:47:03', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (7, -1, '', '合同導(dǎo)入測(cè)試.doc','', '', 0, '', '2022-12-08 16:48:03', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (8, -1, '', '測(cè)試用文件.docx', '', '', 1, '', '2022-12-08 16:49:35', 1, '系統(tǒng)管理員', NULL, -1, '');
INSERT INTO `oss` VALUES (9, -1, '', '新建文本文檔.doc','', '', 0, '', '2022-12-08 17:12:36', 1, '系統(tǒng)管理員', NULL, -1, '');

以上就是mybatis-plus的批量新增/批量更新以及問題的詳細(xì)內(nèi)容，更多關(guān)于mybatis-plus的批量新增/和更新的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: