Mybatis Plus快速重构真批量sql入库操作

2024-09-08 02:52
文章标签 sql 操作 快速 批量 重构 mybatis plus database 入库

本文主要是介绍Mybatis Plus快速重构真批量sql入库操作，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Mybatis快速重构真批量sql入库操作

基本思路

重构mybatis默认方法saveBatch和saveOrUpdateBatch的实现

基本步骤
  1. 真批量保存实现类InsertBatchMethod
  2. 真批量更新实现类MysqlInsertOrUpdateBath
  3. 注册InsertBatchMethod和MysqlInsertOrUpdateBath到EasySqlInjector
  4. 注册EasySqlInjector到Mybatis配置类
  5. 实现自定义RootMapper
  6. 要实现真批量操作的实体类的Mapper继承RootMapper
  7. 自定义MyServiceImpl实现类
  8. 实体类ServiceImpl继承MyServiceImpl
  9. 批量方法的使用
详细步骤

  1. 真批量保存实现类InsertBatchMethod

    import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;/*** 批量插入方法实现*/
@Slf4j
public class InsertBatchMethod extends AbstractMethod {@Overridepublic 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);// 第三个参数必须和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 -> {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 -> valueSql.append("#{item.").append(x.getProperty()).append("},"));valueSql.delete(valueSql.length() - 1, valueSql.length());valueSql.append("</foreach>");return valueSql.toString();}
}

  2. 真批量更新实现类MysqlInsertOrUpdateBath

    import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.springframework.util.StringUtils;public class MysqlInsertOrUpdateBath extends AbstractMethod {@Overridepublic MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {final String sql = "<script>insert into %s %s values %s ON DUPLICATE KEY UPDATE %s</script>";final String tableName = tableInfo.getTableName();final String filedSql = prepareFieldSql(tableInfo);final String modelValuesSql = prepareModelValuesSql(tableInfo);final String duplicateKeySql =prepareDuplicateKeySql(tableInfo);final String sqlResult = String.format(sql, tableName, filedSql, modelValuesSql,duplicateKeySql);//System.out.println("savaorupdatesqlsql="+sqlResult);SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);return this.addInsertMappedStatement(mapperClass, modelClass, "mysqlInsertOrUpdateBath", sqlSource, new NoKeyGenerator(), null, null);}/*** 准备ON DUPLICATE KEY UPDATE sql* @param tableInfo* @return*/private String prepareDuplicateKeySql(TableInfo tableInfo) {final StringBuilder duplicateKeySql = new StringBuilder();if(!StringUtils.isEmpty(tableInfo.getKeyColumn())) {duplicateKeySql.append(tableInfo.getKeyColumn()).append("=values(").append(tableInfo.getKeyColumn()).append("),");}tableInfo.getFieldList().forEach(x -> {duplicateKeySql.append(x.getColumn()).append("=values(").append(x.getColumn()).append("),");});duplicateKeySql.delete(duplicateKeySql.length() - 1, duplicateKeySql.length());return duplicateKeySql.toString();}/*** 准备属性名* @param tableInfo* @return*/private String prepareFieldSql(TableInfo tableInfo) {StringBuilder fieldSql = new StringBuilder();fieldSql.append(tableInfo.getKeyColumn()).append(",");tableInfo.getFieldList().forEach(x -> {fieldSql.append(x.getColumn()).append(",");});fieldSql.delete(fieldSql.length() - 1, fieldSql.length());fieldSql.insert(0, "(");fieldSql.append(")");return fieldSql.toString();}private String prepareModelValuesSql(TableInfo tableInfo){final StringBuilder valueSql = new StringBuilder();valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");if(!StringUtils.isEmpty(tableInfo.getKeyProperty())) {valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");}tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));valueSql.delete(valueSql.length() - 1, valueSql.length());valueSql.append("</foreach>");return valueSql.toString();}
}

  3. 注册InsertBatchMethod和MysqlInsertOrUpdateBath到EasySqlInjector

    import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.AlwaysUpdateSomeColumnById;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import org.springframework.stereotype.Component;
import java.util.List;@Component
public class EasySqlInjector extends DefaultSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass) {List<AbstractMethod> methodList = super.getMethodList(mapperClass);// 真批量插入接口methodList.add(new InsertBatchSomeColumn());// 总是更新字段，不忽略nullmethodList.add(new AlwaysUpdateSomeColumnById(tableFieldInfo ->!tableFieldInfo.getColumn().equals("DELETED")&& !tableFieldInfo.getColumn().equals("ID")&& !tableFieldInfo.getColumn().equals("CREATE_TIME")&& !tableFieldInfo.getColumn().equals("CREATE_USER")&& !tableFieldInfo.getColumn().equals("CREATE_USER_NAME")));// 真批量更新methodList.add(new InsertBatchMethod());methodList.add(new MysqlInsertOrUpdateBath());return methodList;}
}

  4. 注册EasySqlInjector到Mybatis配置类

    @Slf4j
@Configuration
@EnableNacosConfig
@EnableConfigurationProperties
@MapperScan("com.**.dao")
public class OPAConfiguration implements EnvironmentAware,BeanFactoryAware{@Beanpublic xxxMybatisContext xxxMybatisContext(xxxProperties xxxProperties, MybatisPlusProperties properties) throws Exception {xxxMybatisContext xxxMybatisContext=new xxxMybatisContext();// TODO 使用 MybatisSqlSessionFactoryBean 而不是 SqlSessionFactoryBeanMybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean();factory.setDataSource(this.dataSource);factory.setVfs(SpringBootVFS.class);factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/xxx/xxx/xxx/mapping/*Mapper.xml"));// TODO 此处必为非 NULLGlobalConfig globalConfig = properties.getGlobalConfig();GlobalConfig newGlobalConfig=new GlobalConfig();BeanUtils.copyProperties(globalConfig, newGlobalConfig);newGlobalConfig.setMetaObjectHandler(new xxxMetaObjectHandler());newGlobalConfig.setSqlInjector(new EasySqlInjector());factory.setGlobalConfig(newGlobalConfig);SqlSessionFactory sqlSessionFactory=factory.getObject();newGlobalConfig.setSqlSessionFactory(sqlSessionFactory);xxxMybatisContext.setSessionFactory(sqlSessionFactory);xxxMybatisContext.setSqlSessionTemplate(new SqlSessionTemplate(sqlSessionFactory));ClassPathMapperScanner scanner = new ClassPathMapperScanner((BeanDefinitionRegistry) beanFactory);scanner.setSqlSessionTemplate(xxxMybatisContext.getSqlSessionTemplate());//   scanner.setBeanNameGenerator(new xxxBeanNameGenerator());scanner.registerFilters();scanner.scan(StringUtils.tokenizeToStringArray("com.xxx.xxx.xxx.mapping", ConfigurableApplicationContext.CONFIG_LOCATION_DELIMITERS));return xxxMybatisContext;}}

  5. 实现自定义RootMapper

    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;/*** 根Mapper，给表Mapper继承用的，可以自定义通用方法* {@link BaseMapper}* {@link com.baomidou.mybatisplus.extension.service.IService}* {@link com.baomidou.mybatisplus.extension.service.impl.ServiceImpl}*/
public interface RootMapper<T> extends BaseMapper<T> {/*** 自定义批量插入* 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一*/int insertBatch(@Param("list") List<T> list);/*** 自定义批量新增或更新* 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一*/int mysqlInsertOrUpdateBath(@Param("list") List<T> list);}

  6. 要实现真批量操作的实体类的Mapper继承RootMapper

    @Mapper
public interface BudgetVSActualMapper extends RootMapper<BudgetVSActual> {

  7. 自定义MyServiceImpl实现类

    import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.*;
import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.extension.toolkit.SqlHelper;
import com.pwc.sdc.OPA.mapping.RootMapper;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.ResolvableType;
import org.springframework.transaction.annotation.Transactional;
import java.io.Serializable;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;public  class MyServiceImpl<M extends RootMapper<T>, T> implements IService<T> {protected Log log = LogFactory.getLog(this.getClass());@Autowiredprotected M baseMapper;protected Class<T> entityClass = this.currentModelClass();protected Class<T> mapperClass = this.currentMapperClass();public MyServiceImpl() {}public M getBaseMapper() {return this.baseMapper;}public Class<T> getEntityClass() {return this.entityClass;}/** @deprecated */@Deprecatedprotected boolean retBool(Integer result) {return SqlHelper.retBool(result);}protected Class<T> currentMapperClass() {return (Class)this.getResolvableType().as(MyServiceImpl.class).getGeneric(new int[]{0}).getType();}protected Class<T> currentModelClass() {return (Class)this.getResolvableType().as(MyServiceImpl.class).getGeneric(new int[]{1}).getType();}protected ResolvableType getResolvableType() {return ResolvableType.forClass(ClassUtils.getUserClass(this.getClass()));}/** @deprecated */@Deprecatedprotected SqlSession sqlSessionBatch() {return SqlHelper.sqlSessionBatch(this.entityClass);}/** @deprecated */@Deprecatedprotected void closeSqlSession(SqlSession sqlSession) {SqlSessionUtils.closeSqlSession(sqlSession, GlobalConfigUtils.currentSessionFactory(this.entityClass));}/** @deprecated */@Deprecatedprotected String sqlStatement(SqlMethod sqlMethod) {return SqlHelper.table(this.entityClass).getSqlStatement(sqlMethod.getMethod());}@Transactional(rollbackFor = {Exception.class})public boolean saveBatch(Collection<T> entityList, int batchSize) {if(CollectionUtils.isEmpty(entityList)){return true;}int maxNum=2000;int limit = countStep(entityList.size(), maxNum);List<List<T>> subList = new ArrayList<>();Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {subList.add(entityList.stream().skip(i * maxNum).limit(maxNum).collect(Collectors.toList()));});for (List<T> t : subList) {this.baseMapper.insertBatch(t);}return true;}protected String getSqlStatement(SqlMethod sqlMethod) {return SqlHelper.getSqlStatement(this.mapperClass, sqlMethod);}@Transactional(rollbackFor = {Exception.class})public boolean saveOrUpdate(T entity) {if (null == entity) {return false;} else {TableInfo tableInfo = TableInfoHelper.getTableInfo(this.entityClass);Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!", new Object[0]);String keyProperty = tableInfo.getKeyProperty();Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!", new Object[0]);Object idVal = ReflectionKit.getFieldValue(entity, tableInfo.getKeyProperty());return !StringUtils.checkValNull(idVal) && !Objects.isNull(this.getById((Serializable)idVal)) ? this.updateById(entity) : this.save(entity);}}@Transactional(rollbackFor = {Exception.class})public boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize) {if(CollectionUtils.isEmpty(entityList)){return true;}int maxNum=2000;int limit = countStep(entityList.size(), maxNum);List<List<T>> subList = new ArrayList<>();Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {subList.add(entityList.stream().skip(i * maxNum).limit(maxNum).collect(Collectors.toList()));});for (List<T> t : subList) {this.baseMapper.mysqlInsertOrUpdateBath(t);}return true;}private static Integer countStep(Integer size, Integer maxNum) {return (size + maxNum - 1) / maxNum;}@Transactional(rollbackFor = {Exception.class})public boolean updateBatchById(Collection<T> entityList, int batchSize) {String sqlStatement = this.getSqlStatement(SqlMethod.UPDATE_BY_ID);return this.executeBatch(entityList, batchSize, (sqlSession, entity) -> {MapperMethod.ParamMap<T> param = new MapperMethod.ParamMap();param.put("et", entity);sqlSession.update(sqlStatement, param);});}public T getOne(Wrapper<T> queryWrapper, boolean throwEx) {return throwEx ? this.baseMapper.selectOne(queryWrapper) : SqlHelper.getObject(this.log, this.baseMapper.selectList(queryWrapper));}public Map<String, Object> getMap(Wrapper<T> queryWrapper) {return (Map)SqlHelper.getObject(this.log, this.baseMapper.selectMaps(queryWrapper));}public <V> V getObj(Wrapper<T> queryWrapper, Function<? super Object, V> mapper) {return SqlHelper.getObject(this.log, this.listObjs(queryWrapper, mapper));}/** @deprecated */@Deprecatedprotected boolean executeBatch(Consumer<SqlSession> consumer) {return SqlHelper.executeBatch(this.entityClass, this.log, consumer);}protected <E> boolean executeBatch(Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {return SqlHelper.executeBatch(this.entityClass, this.log, list, batchSize, consumer);}protected <E> boolean executeBatch(Collection<E> list, BiConsumer<SqlSession, E> consumer) {return this.executeBatch(list, 1000, consumer);}

  8. 实体类ServiceImpl继承MyServiceImpl

    public class BudgetVSActualServiceImpl extends MyServiceImpl<BudgetVSActualMapper, BudgetVSActual> implements BudgetVSActualService {

  9. 批量方法的使用

    @Autowired
private IService<T> iService;//批量保存数据
iService.saveBatch(DataList);
//批量更新数据
iService.saveOrUpdateBatch(DataList);

这篇关于Mybatis Plus快速重构真批量sql入库操作的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1146908。

相关文章

Java字符串操作技巧之语法、示例与应用场景分析

Java字符串操作技巧之语法、示例与应用场景分析

《Java字符串操作技巧之语法、示例与应用场景分析》在Java算法题和日常开发中,字符串处理是必备的核心技能,本文全面梳理Java中字符串的常用操作语法,结合代码示例、应用场景和避坑指南,可快速掌握字... 目录引言1. 基础操作1.1 创建字符串1.2 获取长度1.3 访问字符2. 字符串处理2.1 子字
阅读更多...
数据库面试必备之MySQL中的乐观锁与悲观锁

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》：本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁（一）原理（二）应用场景（三）示例代码三、悲观锁（一）原理（二）应用场景（三）示例
阅读更多...
SQL表间关联查询实例详解

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括：左连接（leftjoin）、右连接（rightjoin）、全连接（fulljoin）、内连接（innerjoin）、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表
阅读更多...
SQL server配置管理器找不到如何打开它

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一：桌面图标进入方法二：运行窗口进入方法三：查找文件路径方法四：检查 SQL Server 安
阅读更多...
MySQL 中的 LIMIT 语句及基本用法

MySQL 中的 LIMIT 语句及基本用法

《MySQL中的LIMIT语句及基本用法》LIMIT语句用于限制查询返回的行数,常用于分页查询或取部分数据,提高查询效率,：本文主要介绍MySQL中的LIMIT语句,需要的朋友可以参考下... 目录mysql 中的 LIMIT 语句1. LIMIT 语法2. LIMIT 基本用法(1) 获取前 N 行数据(
阅读更多...
Python 中的 with open文件操作的最佳实践

Python 中的 with open文件操作的最佳实践

《Python中的withopen文件操作的最佳实践》在Python中,withopen()提供了一个简洁而安全的方式来处理文件操作,它不仅能确保文件在操作完成后自动关闭,还能处理文件操作中的异... 目录什么是 with open()？为什么使用 with open()？使用 with open() 进行
阅读更多...
MySQL 分区与分库分表策略应用小结

MySQL 分区与分库分表策略应用小结

《MySQL分区与分库分表策略应用小结》在大数据量、复杂查询和高并发的应用场景下,单一数据库往往难以满足性能和扩展性的要求,本文将详细介绍这两种策略的基本概念、实现方法及优缺点,并通过实际案例展示如... 目录mysql 分区与分库分表策略1. 数据库水平拆分的背景2. MySQL 分区策略2.1 分区概念
阅读更多...
MySQL高级查询之JOIN、子查询、窗口函数实际案例

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》：本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN（连接查询）1.1 内连接（INNER JOIN）1.2 左连接（LEFT JOI
阅读更多...
MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2
阅读更多...
MySQL中动态生成SQL语句去掉所有字段的空格的操作方法

MySQL中动态生成SQL语句去掉所有字段的空格的操作方法

《MySQL中动态生成SQL语句去掉所有字段的空格的操作方法》在数据库管理过程中,我们常常会遇到需要对表中字段进行清洗和整理的情况,本文将详细介绍如何在MySQL中动态生成SQL语句来去掉所有字段的空... 目录在mysql中动态生成SQL语句去掉所有字段的空格准备工作原理分析动态生成SQL语句在MySQL
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2