2023年8月3日发(作者：)
mybatis-plus关于savebatch,saveorupdatebatch遇到的坑。。。⼀.背景 最近mybatis-plus框架的更新，让我们基础开发中如虎添翼。其中基本的增删改查，代码⽣成器想必⼤家⽤着那叫⼀个爽。本⼈在使⽤中，也遇到⼀些坑。⽐如savebatch,saveorupdatebatch，看着这不是批量新增，批量新增或更新嘛，看着api进⾏开发，感觉也太好⽤啦。开发完⼀测试，速度跟蜗⽜⼀样，针对⼤数据量真是⽆法忍受。在控制台上发现，怎么名义上是批量插⼊，还是⼀条⼀条的进⾏插⼊，难怪速度龟速。⼆.解决办法 查阅⽹上资料，⼤体有两种解决⽅案： （1）.使⽤mybatis的xml,⾃⼰进⾏sql语句编写。该⽅法⼀个缺点是如果表的字段较多，有个⼏⼗个字段，写批量新增，批量新增修改的sql语句真是个噩梦。
INSERT INTO t
(id, age)
VALUES
(3, 28), (4, 29)
ON DUPLICATE KEY UPDATE id = VALUES(id), age = VALUES(age);(2)mybatis-plus 新添加了⼀个sql注⼊器，通过sql注⼊器可以实现批量新增，批量新增修改功能。⼀次注⼊，随时使⽤，使⽤极其⽅便。缺点就是项⽬启动时候，会进⾏sql注⼊器注册，稍微影响启动速度。三.sql注⼊器实现批量更新，批量新增或更新功能(1)⾃定义mapper接⼝，继承BaseMapper，定义实现的⽅法。import pper;import ;import ;/** * 根Mapper，给表Mapper继承⽤的，可以⾃定义通⽤⽅法 * {@link BaseMapper} * {@link ce} * {@link eImpl} */public interface RootMapper extends BaseMapper { /** * ⾃定义批量插⼊ * 如果要⾃动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之⼀ */ int insertBatch(@Param("list") List list); /** * ⾃定义批量新增或更新 * 如果要⾃动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之⼀ */ int mysqlInsertOrUpdateBath(@Param("list") List list);}（2）批量插⼊、批量新增或更新具体⽅法实现批量插⼊具体⽅法实现如下：import ctMethod;import nfo;import 4j;import enerator;import Statement;import rce;/** * 批量插⼊⽅法实现 */@Slf4jpublic class InsertBatchMethod extends AbstractMethod { /** * insert into user(id, name, age) values (1, "a", 17), (2, "b", 18); */ @Override public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) { final String sql = ""; final String fieldSql = prepareFieldSql(tableInfo); final String valueSql = prepareValuesSql(tableInfo); final String sqlResult = (sql, leName(), fieldSql, valueSql); ("sqlResult----->{}", sqlResult); SqlSource sqlSource = SqlSource(configuration, sqlResult, modelClass); // 第三个参数必须和RootMapper的⾃定义⽅法名⼀致 return ertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null); } private String prepareFieldSql(TableInfo tableInfo) { StringBuilder fieldSql = new StringBuilder(); (Column()).append(","); ldList().forEach(x -> { (umn()).append(","); }); (() - 1, ()); (0, "("); (")"); return ng(); } private String prepareValuesSql(TableInfo tableInfo) { final StringBuilder valueSql = new StringBuilder(); (""); ("#{item.").append(Property()).append("},"); ldList().forEach(x -> ("#{item.").append(perty()).append("},")); (() - 1, ()); (""); return ng(); }}批量插⼊或更新具体⽅法如下：import ctMethod;import nfo;import enerator;import Statement;import rce;import rce;import Utils;public class MysqlInsertOrUpdateBath extends AbstractMethod { @Override public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) { final String sql = ""; final String tableName = leName(); final String filedSql = prepareFieldSql(tableInfo); final String modelValuesSql = prepareModelValuesSql(tableInfo); final String duplicateKeySql =prepareDuplicateKeySql(tableInfo); final String sqlResult = (sql, tableName, filedSql, modelValuesSql,duplicateKeySql); //n("savaorupdatesqlsql="+sqlResult); SqlSource sqlSource = SqlSource(configuration, sqlResult, modelClass); return ertMappedStatement(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(!y(Column())) { (Column()).append("=values(").append(Column()).append("),"); } ldList().forEach(x -> { (umn()) .append("=values(") .append(umn()) .append("),"); }); (() - 1, ()); return ng(); } /** * 准备属性名 * @param tableInfo * @return */ private String prepareFieldSql(TableInfo tableInfo) { StringBuilder fieldSql = new StringBuilder(); (Column()).append(","); ldList().forEach(x -> { (umn()).append(","); }); (() - 1, ()); (0, "("); (")"); return ng(); } private String prepareModelValuesSql(TableInfo tableInfo){ final StringBuilder valueSql = new StringBuilder(); (""); if(!y(Property())) { ("#{item.").append(Property()).append("},"); } ldList().forEach(x -> ("#{item.").append(perty()).append("},")); (() - 1, ()); (""); return ng(); }}（3）sql注⼊器实现import ctMethod;import tSqlInjector;import ;/** * ⾃定义⽅法SQL注⼊器 */public class CustomizedSqlInjector extends DefaultSqlInjector { /** * 如果只需增加⽅法，保留mybatis plus⾃带⽅法， * 可以先获取hodList()，再添加add */ @Override public List getMethodList(Class mapperClass) { List methodList = hodList(mapperClass); (new InsertBatchMethod()); (new UpdateBatchMethod()); (new MysqlInsertOrUpdateBath()); return methodList; }}（4）在⾃⼰想使⽤的mapper上继承⾃定义的 pper;import Pds;import ;import ;import ;import ent;@Componentpublic interface InfMpmPdsMapper extends RootMapper { IPage selectPageList(Page page, @Param("infMpmPds") InfMpmPds infMpmPds);}(5)在controller或serviceImpi中引⼊mapper,使⽤⾃定义的⽅法。>>>>>>>>>>>>>>>>>>>>引⼊mapper>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>⽅法使⽤>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>完成上述步骤，就可以运⾏项⽬进⾏测试看看，数据提升是不是⼏个数量级。
2023年8月3日发(作者：)
mybatis-plus关于savebatch,saveorupdatebatch遇到的坑。。。⼀.背景 最近mybatis-plus框架的更新，让我们基础开发中如虎添翼。其中基本的增删改查，代码⽣成器想必⼤家⽤着那叫⼀个爽。本⼈在使⽤中，也遇到⼀些坑。⽐如savebatch,saveorupdatebatch，看着这不是批量新增，批量新增或更新嘛，看着api进⾏开发，感觉也太好⽤啦。开发完⼀测试，速度跟蜗⽜⼀样，针对⼤数据量真是⽆法忍受。在控制台上发现，怎么名义上是批量插⼊，还是⼀条⼀条的进⾏插⼊，难怪速度龟速。⼆.解决办法 查阅⽹上资料，⼤体有两种解决⽅案： （1）.使⽤mybatis的xml,⾃⼰进⾏sql语句编写。该⽅法⼀个缺点是如果表的字段较多，有个⼏⼗个字段，写批量新增，批量新增修改的sql语句真是个噩梦。
INSERT INTO t
(id, age)
VALUES
(3, 28), (4, 29)
ON DUPLICATE KEY UPDATE id = VALUES(id), age = VALUES(age);(2)mybatis-plus 新添加了⼀个sql注⼊器，通过sql注⼊器可以实现批量新增，批量新增修改功能。⼀次注⼊，随时使⽤，使⽤极其⽅便。缺点就是项⽬启动时候，会进⾏sql注⼊器注册，稍微影响启动速度。三.sql注⼊器实现批量更新，批量新增或更新功能(1)⾃定义mapper接⼝，继承BaseMapper，定义实现的⽅法。import pper;import ;import ;/** * 根Mapper，给表Mapper继承⽤的，可以⾃定义通⽤⽅法 * {@link BaseMapper} * {@link ce} * {@link eImpl} */public interface RootMapper extends BaseMapper { /** * ⾃定义批量插⼊ * 如果要⾃动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之⼀ */ int insertBatch(@Param("list") List list); /** * ⾃定义批量新增或更新 * 如果要⾃动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之⼀ */ int mysqlInsertOrUpdateBath(@Param("list") List list);}（2）批量插⼊、批量新增或更新具体⽅法实现批量插⼊具体⽅法实现如下：import ctMethod;import nfo;import 4j;import enerator;import Statement;import rce;/** * 批量插⼊⽅法实现 */@Slf4jpublic class InsertBatchMethod extends AbstractMethod { /** * insert into user(id, name, age) values (1, "a", 17), (2, "b", 18); */ @Override public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) { final String sql = ""; final String fieldSql = prepareFieldSql(tableInfo); final String valueSql = prepareValuesSql(tableInfo); final String sqlResult = (sql, leName(), fieldSql, valueSql); ("sqlResult----->{}", sqlResult); SqlSource sqlSource = SqlSource(configuration, sqlResult, modelClass); // 第三个参数必须和RootMapper的⾃定义⽅法名⼀致 return ertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null); } private String prepareFieldSql(TableInfo tableInfo) { StringBuilder fieldSql = new StringBuilder(); (Column()).append(","); ldList().forEach(x -> { (umn()).append(","); }); (() - 1, ()); (0, "("); (")"); return ng(); } private String prepareValuesSql(TableInfo tableInfo) { final StringBuilder valueSql = new StringBuilder(); (""); ("#{item.").append(Property()).append("},"); ldList().forEach(x -> ("#{item.").append(perty()).append("},")); (() - 1, ()); (""); return ng(); }}批量插⼊或更新具体⽅法如下：import ctMethod;import nfo;import enerator;import Statement;import rce;import rce;import Utils;public class MysqlInsertOrUpdateBath extends AbstractMethod { @Override public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo) { final String sql = ""; final String tableName = leName(); final String filedSql = prepareFieldSql(tableInfo); final String modelValuesSql = prepareModelValuesSql(tableInfo); final String duplicateKeySql =prepareDuplicateKeySql(tableInfo); final String sqlResult = (sql, tableName, filedSql, modelValuesSql,duplicateKeySql); //n("savaorupdatesqlsql="+sqlResult); SqlSource sqlSource = SqlSource(configuration, sqlResult, modelClass); return ertMappedStatement(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(!y(Column())) { (Column()).append("=values(").append(Column()).append("),"); } ldList().forEach(x -> { (umn()) .append("=values(") .append(umn()) .append("),"); }); (() - 1, ()); return ng(); } /** * 准备属性名 * @param tableInfo * @return */ private String prepareFieldSql(TableInfo tableInfo) { StringBuilder fieldSql = new StringBuilder(); (Column()).append(","); ldList().forEach(x -> { (umn()).append(","); }); (() - 1, ()); (0, "("); (")"); return ng(); } private String prepareModelValuesSql(TableInfo tableInfo){ final StringBuilder valueSql = new StringBuilder(); (""); if(!y(Property())) { ("#{item.").append(Property()).append("},"); } ldList().forEach(x -> ("#{item.").append(perty()).append("},")); (() - 1, ()); (""); return ng(); }}（3）sql注⼊器实现import ctMethod;import tSqlInjector;import ;/** * ⾃定义⽅法SQL注⼊器 */public class CustomizedSqlInjector extends DefaultSqlInjector { /** * 如果只需增加⽅法，保留mybatis plus⾃带⽅法， * 可以先获取hodList()，再添加add */ @Override public List getMethodList(Class mapperClass) { List methodList = hodList(mapperClass); (new InsertBatchMethod()); (new UpdateBatchMethod()); (new MysqlInsertOrUpdateBath()); return methodList; }}（4）在⾃⼰想使⽤的mapper上继承⾃定义的 pper;import Pds;import ;import ;import ;import ent;@Componentpublic interface InfMpmPdsMapper extends RootMapper { IPage selectPageList(Page page, @Param("infMpmPds") InfMpmPds infMpmPds);}(5)在controller或serviceImpi中引⼊mapper,使⽤⾃定义的⽅法。>>>>>>>>>>>>>>>>>>>>引⼊mapper>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>⽅法使⽤>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>完成上述步骤，就可以运⾏项⽬进⾏测试看看，数据提升是不是⼏个数量级。
发布评论