• mybatis-plus批量更新太慢，如何解决？

    最近使用mybatis-plus的 saveOrUpdateBath 和saveBath接口执行特别慢，数据量大时往往需要十几分钟，打开日志查看原来批量操作也是循环单条数据插入的，那有没有批量更新的办法呢？？

    mybatis-plus 提供了一个自定义方法sql注入器DefaultSqlInjector我们可以通过继DefaultSqlInjector来加入自定义的方法达到批量插入的效果。

    1.  
    2. import com.baomidou.mybatisplus.core.injector.AbstractMethod;
    3. import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
    4. import org.springframework.stereotype.Component;
    5.  
    6. import java.util.List;
    7.  
    8. /**
    9.  * @Description: 自定义方法SQL注入器
    10.  * @Title: CustomizedSqlInjector
    11.  * @Package com.highgo.edu.common.batchOperation
    12.  * @Author:
    13.  * @Copyright 
    14.  * @CreateTime: 2022/11/3 16:21
    15.  */
    16. @Component
    17. public class CustomizedSqlInjector  extends DefaultSqlInjector {
    18.     /**
    19.      * 如果只需增加方法，保留mybatis plus自带方法，
    20.      * 可以先获取super.getMethodList()，再添加add
    21.      */
    22.     @Override
    23.     public List getMethodList(Class mapperClass) {
    24.         List methodList = super.getMethodList(mapperClass);
    25.         methodList.add(new InsertBatchMethod());
    26.        // methodList.add(new UpdateBatchMethod());
    27.         methodList.add(new MysqlInsertOrUpdateBath());
    28.         methodList.add(new PGInsertOrUpdateBath());
    29.         return methodList;
    30.     }
    31.  
    32. }

    同时我们需要继承BaseMapper 定义

    1.  
    2. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    3. import org.apache.ibatis.annotations.Param;
    4.  
    5. import java.util.List;
    6. /**
    7.  * @description:自定义接口覆盖BaseMapper，解决mybatis-plus 批量操作慢的问题
    8.  * @author:
    9.  * @date: 2022/11/3 15:14
    10.  * @param: null
    11.  * @return:
    12.  **/
    13. public interface RootMapper extends BaseMapper {
    14.     /**
    15.      * @description:批量插入
    16.      * @author:
    17.      * @date: 2022/11/3 15:13
    18.      * @param: [list]
    19.      * @return: int
    20.      **/
    21.     int insertBatch(@Param("list") List list);
    22.     /**
    23.      * @description:批量插入更新
    24.      * @author:
    25.      * @date: 2022/11/3 15:14
    26.      * @param: [list]
    27.      * @return: int
    28.      **/
    29.     int mysqlInsertOrUpdateBatch(@Param("list") List list);
    30.  
    31.     int pgInsertOrUpdateBatch(@Param("list") List list);
    32. }

    在需要使用批量更新插入的mapper上使用自定义的RootMapper

    如下图

    1. import com.XX.edu.common.batchOperation.RootMapper;
    2. import com.XX.edu.exam.model.TScore;
    3. import org.springframework.stereotype.Repository;
    4.  
    5. /**
    6.  * @Entity com.XX.edu.exam.model.TScore
    7.  */
    8. @Repository
    9. public interface TScoreMapper extends RootMapper {
    10.  
    11. }

    下面我们来定义批量插入的方法：

    1. package com.XX.edu.common.batchOperation;
    2.  
    3. import com.baomidou.mybatisplus.annotation.IdType;
    4. import com.baomidou.mybatisplus.core.enums.SqlMethod;
    5. import com.baomidou.mybatisplus.core.injector.AbstractMethod;
    6. import com.baomidou.mybatisplus.core.metadata.TableInfo;
    7. import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
    8. import org.apache.commons.lang3.StringUtils;
    9. import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
    10. import org.apache.ibatis.executor.keygen.KeyGenerator;
    11. import org.apache.ibatis.executor.keygen.NoKeyGenerator;
    12. import org.apache.ibatis.mapping.MappedStatement;
    13. import org.apache.ibatis.mapping.SqlSource;
    14. import org.slf4j.Logger;
    15. import org.slf4j.LoggerFactory;
    16.  
    17. /**
    18.  * @Description: 批量插入的方法
    19.  * @Title: InsertBatchMethod
    20.  * @Package com.XX.edu.common.batchOperation
    21.  * @Author:
    22.  * @CreateTime: 2022/11/3 15:16
    23.  */
    24. public class InsertBatchMethod extends AbstractMethod {
    25.     Logger logger = LoggerFactory.getLogger(getClass());
    26.  
    27.     @Override
    28.     public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) {
    29.         final String sql = "";
    30.         final String fieldSql = prepareFieldSql(tableInfo);
    31.         final String valueSql = prepareValuesSql(tableInfo);
    32.         final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
    33.         logger.debug("sqlResult----->{}", sqlResult);
    34.         SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
    35.         KeyGenerator keyGenerator = new NoKeyGenerator();
    36.         SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
    37.         String keyProperty = null;
    38.         String keyColumn = null;
    39.         // 表包含主键处理逻辑,如果不包含主键当普通字段处理
    40.         if (StringUtils.isNotEmpty(tableInfo.getKeyProperty())) {
    41.             if (tableInfo.getIdType() == IdType.AUTO) {
    42.                 /* 自增主键 */
    43.                 keyGenerator = new Jdbc3KeyGenerator();
    44.                 keyProperty = tableInfo.getKeyProperty();
    45.                 keyColumn = tableInfo.getKeyColumn();
    46.             } else {
    47.                 if (null != tableInfo.getKeySequence()) {
    48.                     keyGenerator = TableInfoHelper.genKeyGenerator(sqlMethod.getMethod(),tableInfo, builderAssistant);
    49.                     keyProperty = tableInfo.getKeyProperty();
    50.                     keyColumn = tableInfo.getKeyColumn();
    51.                 }
    52.             }
    53.         }
    54.         // 第三个参数必须和RootMapper的自定义方法名一致
    55.         return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, keyGenerator, keyProperty, keyColumn);
    56.     }
    57.  
    58.     /**
    59.      * @description: 拼接字段值
    60.      * @author:
    61.      * @date: 2022/11/3 15:20
    62.      * @param: [tableInfo]
    63.      * @return: java.lang.String
    64.      **/
    65.     private String prepareValuesSql(TableInfo tableInfo) {
    66.         final StringBuilder valueSql = new StringBuilder();
    67.         valueSql.append("");
    68.         //valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
    69.         tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
    70.         valueSql.delete(valueSql.length() - 1, valueSql.length());
    71.         valueSql.append("");
    72.         return valueSql.toString();
    73.     }
    74.  
    75.     /**
    76.      * @description:拼接字段
    77.      * @author:
    78.      * @date: 2022/11/3 15:20
    79.      * @param: [tableInfo]
    80.      * @return: java.lang.String
    81.      **/
    82.     private String prepareFieldSql(TableInfo tableInfo) {
    83.         StringBuilder fieldSql = new StringBuilder();
    84.         //fieldSql.append(tableInfo.getKeyColumn()).append(",");
    85.         tableInfo.getFieldList().forEach(x -> {
    86.             fieldSql.append(x.getColumn()).append(",");
    87.         });
    88.         fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
    89.         fieldSql.insert(0, "(");
    90.         fieldSql.append(")");
    91.         return fieldSql.toString();
    92.     }
    93. }

    继续定义批量插入更新的抽象方法

    1. package com.XX.edu.common.batchOperation;
    2.  
    3. import com.baomidou.mybatisplus.core.injector.AbstractMethod;
    4. import com.baomidou.mybatisplus.core.metadata.TableInfo;
    5. import org.apache.ibatis.executor.keygen.NoKeyGenerator;
    6. import org.apache.ibatis.mapping.MappedStatement;
    7. import org.apache.ibatis.mapping.SqlSource;
    8.  
    9. /**
    10.  * @Description: 批量插入更新
    11.  * @Title: InsertOrUpdateBath
    12.  * @Package com.XX.edu.common.batchOperation
    13.  * @Author:
    14.  * @Copyright 
    15.  * @CreateTime: 2022/11/3 15:23
    16.  */
    17. public abstract class InsertOrUpdateBathAbstract extends AbstractMethod {
    18.     @Override
    19.     public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) {
    20.         final  SqlSource sqlSource = prepareSqlSource(tableInfo, modelClass);
    21.         // 第三个参数必须和RootMapper的自定义方法名一致
    22.         return this.addInsertMappedStatement(mapperClass, modelClass, prepareInsertOrUpdateBathName(), sqlSource, new NoKeyGenerator(), null, null);
    23.  
    24.     }
    25.  
    26.     protected abstract SqlSource prepareSqlSource(TableInfo tableInfo, Class modelClass);
    27.     
    28.     protected abstract String prepareInsertOrUpdateBathName();
    29.  
    30. }

    继承上面的抽象类----mysql版本（本版本未测试 根据自己需求修改）

    1. package com.XX.edu.common.batchOperation;
    2.  
    3. import com.baomidou.mybatisplus.core.metadata.TableInfo;
    4. import org.apache.ibatis.mapping.SqlSource;
    5. import org.springframework.util.StringUtils;
    6.  
    7. /**
    8.  * @Description: 批量插入更新
    9.  * @Title: InsertOrUpdateBath
    10.  * @Package com.XX.edu.common.batchOperation
    11.  * @Author:
    12.  * @Copyright 
    13.  * @CreateTime: 2022/11/3 15:23
    14.  */
    15. public class MysqlInsertOrUpdateBath extends InsertOrUpdateBathAbstract {
    16.  
    17.     @Override
    18.     protected SqlSource prepareSqlSource(TableInfo tableInfo, Class modelClass) {
    19.         final String sql = "";
    20.         final String tableName = tableInfo.getTableName();
    21.         final String filedSql = prepareFieldSql(tableInfo);
    22.         final String modelValuesSql = prepareModelValuesSql(tableInfo);
    23.         final String duplicateKeySql = prepareDuplicateKeySql(tableInfo);
    24.         final String sqlResult = String.format(sql, tableName, filedSql, modelValuesSql, filedSql, duplicateKeySql);
    25.         //String.format(sql, tableName, filedSql, modelValuesSql, duplicateKeySql);
    26.         //System.out.println("savaorupdatesqlsql="+sqlResult);
    27.         return languageDriver.createSqlSource(configuration, sqlResult, modelClass);
    28.     }
    29.  
    30.  
    31.     @Override
    32.     protected String prepareInsertOrUpdateBathName() {
    33.         return "mysqlInsertOrUpdateBath";
    34.     }
    35.  
    36.     String prepareDuplicateKeySql(TableInfo tableInfo) {
    37.         final StringBuilder duplicateKeySql = new StringBuilder();
    38.         if (!StringUtils.isEmpty(tableInfo.getKeyColumn())) {
    39.             duplicateKeySql.append(tableInfo.getKeyColumn()).append("=values(").append(tableInfo.getKeyColumn()).append("),");
    40.         }
    41.  
    42.         tableInfo.getFieldList().forEach(x -> {
    43.             duplicateKeySql.append(x.getColumn())
    44.                     .append("=values(")
    45.                     .append(x.getColumn())
    46.                     .append("),");
    47.         });
    48.         duplicateKeySql.delete(duplicateKeySql.length() - 1, duplicateKeySql.length());
    49.         return duplicateKeySql.toString();
    50.     }
    51.  
    52.     String prepareModelValuesSql(TableInfo tableInfo) {
    53.         final StringBuilder valueSql = new StringBuilder();
    54.         valueSql.append("");
    55.         if (!StringUtils.isEmpty(tableInfo.getKeyProperty())) {
    56.             valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
    57.         }
    58.         tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
    59.         valueSql.delete(valueSql.length() - 1, valueSql.length());
    60.         valueSql.append("");
    61.         return valueSql.toString();
    62.     }
    63.  
    64.     /**
    65.      * @description:准备属性名
    66.      * @author:
    67.      * @date: 2022/11/3 15:25
    68.      * @param: [tableInfo]
    69.      * @return: java.lang.String
    70.      **/
    71.     String prepareFieldSql(TableInfo tableInfo) {
    72.         StringBuilder fieldSql = new StringBuilder();
    73.         fieldSql.append(tableInfo.getKeyColumn()).append(",");
    74.         tableInfo.getFieldList().forEach(x -> {
    75.             fieldSql.append(x.getColumn()).append(",");
    76.         });
    77.         fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
    78.         fieldSql.insert(0, "(");
    79.         fieldSql.append(")");
    80.         return fieldSql.toString();
    81.     }
    82. }

    继承上面的抽象类----postgresql版本（已测试完成，其中id使用序列自增）

    1. package com.XX.edu.common.batchOperation;
    2.  
    3. import com.baomidou.mybatisplus.core.metadata.TableInfo;
    4. import org.apache.ibatis.mapping.SqlSource;
    5. import org.slf4j.Logger;
    6. import org.slf4j.LoggerFactory;
    7. import org.springframework.util.StringUtils;
    8.  
    9. /**
    10.  * @Description: 批量插入更新
    11.  * @Title: InsertOrUpdateBath
    12.  * @Package com.XX.edu.common.batchOperation
    13.  * @Author:
    14.  * @Copyright 
    15.  * @CreateTime: 2022/11/3 15:23
    16.  */
    17. public class PGInsertOrUpdateBath extends InsertOrUpdateBathAbstract {
    18.     Logger logger = LoggerFactory.getLogger(getClass());
    19.     @Override
    20.     protected SqlSource prepareSqlSource(TableInfo tableInfo, Class modelClass) {
    21.         final String sql = "";
    22.         final String tableName = tableInfo.getTableName();
    23.         final String filedSql = prepareFieldSql(tableInfo);
    24.         final String modelValuesSql = prepareModelValuesSql(tableInfo);
    25.         final String duplicateKeySql = prepareDuplicateKeySql(tableInfo);
    26.         final String sqlResult = String.format(sql, tableName, filedSql, modelValuesSql, duplicateKeySql);
    27.         logger.info("sql=={}",sqlResult);
    28.         return languageDriver.createSqlSource(configuration, sqlResult, modelClass);
    29.     }
    30.  
    31.  
    32.     @Override
    33.     protected String prepareInsertOrUpdateBathName() {
    34.         return "pgInsertOrUpdateBatch";
    35.     }
    36.  
    37.  
    38.     private String prepareDuplicateKeySql(TableInfo tableInfo) {
    39.         final StringBuilder duplicateKeySql = new StringBuilder();
    40.         if (!StringUtils.isEmpty(tableInfo.getKeyColumn())) {
    41.             duplicateKeySql.append(tableInfo.getKeyColumn()).append("=excluded.").append(tableInfo.getKeyColumn()).append(",");
    42.         }
    43.  
    44.         tableInfo.getFieldList().forEach(x -> {
    45.             duplicateKeySql.append(x.getColumn())
    46.                     .append("=excluded.")
    47.                     .append(x.getColumn())
    48.                     .append(",");
    49.         });
    50.         duplicateKeySql.delete(duplicateKeySql.length() - 1, duplicateKeySql.length());
    51.         return duplicateKeySql.toString();
    52.     }
    53.  
    54.     private String prepareModelValuesSql(TableInfo tableInfo) {
    55.         final StringBuilder valueSql = new StringBuilder();
    56.         valueSql.append("");
    57.         if (!StringUtils.isEmpty(tableInfo.getKeyProperty())) {
    58.             valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
    59.         }
    60.         tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
    61.         valueSql.delete(valueSql.length() - 1, valueSql.length());
    62.         valueSql.append("");
    63.         return valueSql.toString();
    64.     }
    65.  
    66.     /**
    67.      * @description:准备属性名
    68.      * @author:
    69.      * @date: 2022/11/3 15:25
    70.      * @param: [tableInfo]
    71.      * @return: java.lang.String
    72.      **/
    73.     private String prepareFieldSql(TableInfo tableInfo) {
    74.         StringBuilder fieldSql = new StringBuilder();
    75.         if (!StringUtils.isEmpty(tableInfo.getKeyProperty())) {
    76.             fieldSql.append(tableInfo.getKeyColumn()).append(",");
    77.         }
    78.  
    79.         tableInfo.getFieldList().forEach(x -> {
    80.             fieldSql.append(x.getColumn()).append(",");
    81.         });
    82.         fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
    83.         fieldSql.insert(0, "(");
    84.         fieldSql.append(")");
    85.         return fieldSql.toString();
    86.     }
    87. }

     到此定义结束，下面开始使用

    1. @Service
    2. public class TNewExerciseServiceImpl extends ServiceImpl
    3.         implements TNewExerciseService {
    4.     Logger logger = LoggerFactory.getLogger(getClass());
    5. //引入mapper 
    6. @Autowired
    7. TScoreMapper scoreMapper;
    8. //这样就可以批量新增更新操作了
    9. public void test(List collect){
    10.  scoreMapper.pgInsertOrUpdateBatch(collect);
    11. }
    12.  
    13. }

    但是如果collect数据量太大会出现异常
    “Tried to send an out-of-range integer as a 2-byte value: 87923”
    是因为pg对于sql语句的参数数量是有限制的，最大为32767。

    看pg源码

    1. public void sendInteger2(int val) throws IOException {
    2.         if (val >= -32768 && val <= 32767) {
    3.             this.int2Buf[0] = (byte)(val >>> 8);
    4.             this.int2Buf[1] = (byte)val;
    5.             this.pgOutput.write(this.int2Buf);
    6.         } else {
    7.             throw new IOException("Tried to send an out-of-range integer as a 2-byte value: " + val);
    8.         }
    9.     }

    从源代码中可以看到pgsql使用2个字节的integer，故其取值范围为[-32768, 32767]。

    这意味着sql语句的参数数量，即行数*列数之积必须小于等于32767.

    比如，总共有17个字段，因为最大是32767，这样最多允许32767/ 17 大约是1 927个，所以要分批操作，或有能力的童鞋可以自己修改pg的驱动呦

    分批插入代码如下：

    1.     /**
    2.      * @description:
    3.      * @author:
    4.      * @date: 2022/11/4 14:57
    5.      * @param: [list, fieldCount:列数]
    6.      * @return: void
    7.      **/
    8.     public void detachSaveOrUpdate_score(List list, int fieldCount) {
    9.         int numberBatch = 32767; //每一次插入的最大数
    10.         //每一次插入的最大行数 ， 向下取整
    11.         int v = ((Double) Math.floor(numberBatch / (fieldCount * 1.0))).intValue();
    12.         double number = list.size() * 1.0 / v;
    13.         int n = ((Double) Math.ceil(number)).intValue(); //向上取整
    14.         for (int i = 0; i < n; i++) {
    15.             int end = v * (i + 1);
    16.             if (end > list.size()) {
    17.                 end = list.size(); //如果end不能超过最大索引值
    18.             }
    19.             scoreMapper.pgInsertOrUpdateBatch(list.subList(v * i, end)); //插入数据库
    20.             logger.info("更新一次~~~{}-{}", v * i, end);
    21.         }
    22.     }

    完成收工~~~

  • 相关阅读:
    Kotlin协程：flowOn与线程切换
    13-面试官：synchronized有几种用法？
    正则表达式
    ANR问题分析的一般套路
    Git使用大全解
    Day804.原型模式与享元模式 -Java 性能调优实战
    【python】ModuleNotFoundError: No module named ‘skimage‘...
    谈对数据库索引的认识
    防止雪崩问题
    Typescript语言基础
  • 原文地址：https://blog.csdn.net/feixiangdexiaozhidan/article/details/127685532