sql效率 Mysql支持一条sql语句插入多条数据。但是mybatis-Plus中默认提供的saveBatch、updateBatchById方法并不能算是真正的批量语句，而是遍历实体集合执行INSERT_ONE、UPDATE_BY_ID
Mysql支持一条sql语句插入多条数据。但是mybatis-Plus中默认提供的saveBatch、updateBatchById方法并不能算是真正的批量语句，而是遍历实体集合执行INSERT_ONE、UPDATE_BY_ID语句。
mybatis-plus虽然做了分批请求、一次提交的处理。但如果jdbc不启用配置rewriteBatchedStatements，那么批量提交的sql到了mysql就还是一条一条执行，mysql并不会将这些sql重写为insert多值插入，相比一条sql批量插入，性能上会差点。
rewriteBatchedStatements文档
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-perfORMance-extensions.html#cj-conn-prop_rewriteBatchedStatements
##sql格式(不止这些字段) <script> <foreach collection="list" item="item" separator=";"> insert into user (name, age) values ("张三",17) </foreach> </script>##sql格式(不止这些字段)insert into user (name, age) values ("张三", 17), ("李四", 18);##sql格式(不止这些字段) <script> <foreach collection="list" item="item" separator=";">update user set name = "张三", age = 17 where id = 1 </foreach></script>##sql格式(不止这些字段)update userset name= ( case id when 1 then '张三' when 2 then '李四' else nameend), age= ( case id when 1 then 16 when 2 then 26 else ageend)where id in ( 1 , 2 )综合比较效率
| 10000条数据 | mybatis-plus | 自定义sql |
|---|---|---|
| 批量插入 | 3.6s | 1.8s |
| 批量更新 | 3.1s | 5.8s |
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;public class InsertBatchMethod extends AbstractMethod { @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 = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql); SqlSource sqlSource = languageDriver.createSqlSource( configuration, sqlResult, modelClass); //加入Configuration中的Map mappedStatements, //这个mappedStatements保存我们xml中写的各种标签 //key为全限定类名方法名、value为对应元数据信息 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("" ); 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(""); return valueSql.toString(); }}public class UpdateBatchMethod extends AbstractMethod { @Override public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) { final String sql = ""; final String valueSql = prepareValuesSql(tableInfo); final String sqlResult = String.format(sql, tableInfo.getTableName(), valueSql); SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass); return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource); } private String prepareValuesSql(TableInfo tableInfo) { final StringBuilder valueSql = new StringBuilder(); valueSql.append("\n" ); tableInfo.getFieldList().forEach(x -> { valueSql.append(").append(x.getColumn()).append(" =(case id\" suffix=\"end),\">\n"); valueSql.append("\n" ); valueSql.append(").append(x.getProperty()).append("!=null\">\n"); valueSql.append("when #{item.id} then #{item.").append(x.getProperty()).append("}\n"); valueSql.append(" \n"); valueSql.append("\n"); valueSql.append("else ").append(x.getColumn()); valueSql.append(" \n"); }); valueSql.append("\n"); return valueSql.toString(); }}import com.baomidou.mybatisplus.core.injector.AbstractMethod;import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;import org.springframework.context.annotation.Configuration;import java.util.List;@Configurationpublic class CustomizedSqlInjector extends DefaultSqlInjector { @Override public List<AbstractMethod> getMethodList(Class<?> mapperClass) { List<AbstractMethod> methodList = super.getMethodList(mapperClass); methodList.add(new InsertBatchSomeColumn()); methodList.add(new InsertBatchMethod()); methodList.add(new UpdateBatchMethod()); return methodList; }}import com.baomidou.mybatisplus.core.mapper.BaseMapper;import org.apache.ibatis.annotations.Param;import java.util.List;public interface BaseEntityMapper<T> extends BaseMapper<T> { int insertBatch(@Param("list") List<T> list); Integer insertBatchSomeColumn(List<T> list); int updateBatch(@Param("list") List<T> list);}import com.test.demo.model.User;import org.apache.ibatis.annotations.Mapper;@Mapperpublic interface UserMapper extends BaseEntityMapper<User> {}...userMapper.insertBatch(userList);...--结束END--
本文标题: Mybatis-Plus的SQL注入器实现批量插入/修改，效率比较
本文链接: https://www.lsjlt.com/news/375951.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0