MyBatis-Plus 批量保存的操作方法
前言
在項目開發(fā)中,需要插入批量插入20多萬條數(shù)據(jù),通過日志觀察,發(fā)現(xiàn)在調(diào)用MyBatis-Plus中的saveBatch()方法性能非常的差,本篇文章主要分享一下saveBatch()的原理以及使用的注意事項
原理
我們通過源碼的形式進(jìn)行解析saveBatch()方法的原理
@Transactional(rollbackFor = Exception.class)
default boolean saveBatch(Collection<T> entityList) {
//DEFAULT_BATCH_SIZE 默認(rèn)是1000
return saveBatch(entityList, DEFAULT_BATCH_SIZE);
} @Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {
String sqlStatement = getSqlStatement(SqlMethod.INSERT_ONE);
//分批執(zhí)行SQL
return executeBatch(entityList, batchSize, (sqlSession, entity) -> sqlSession.insert(sqlStatement, entity));
}我們看下saveBatch是怎么批量執(zhí)行的
public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
Assert.isFalse(batchSize < 1, "batchSize must not be less than one");
return !CollectionUtils.isEmpty(list) && executeBatch(entityClass, log, sqlSession -> {
int size = list.size();
int i = 1;
for (E element : list) {
//數(shù)據(jù)最終保存在StatementImpl.batchArgs中,用于批量保存
consumer.accept(sqlSession, element);
if ((i % batchSize == 0) || i == size) {
//批量保存StatementImpl.batchArgs中數(shù)據(jù)
sqlSession.flushStatements();
}
i++;
}
});
}通過flushStatements()方法我們可以看到最終調(diào)用的是StatementImpl中的executeBatchInternal()方法。注意:代碼過長,下面方法做了刪減。
protected long[] executeBatchInternal() throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
if (this.connection.isReadOnly()) {
throw new SQLException(Messages.getString("PreparedStatement.25") + Messages.getString("PreparedStatement.26"),
MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT);
}
if (this.query.getBatchedArgs() == null || this.query.getBatchedArgs().size() == 0) {
return new long[0];
}
// we timeout the entire batch, not individual statements
int batchTimeout = getTimeoutInMillis();
setTimeoutInMillis(0);
resetCancelledState();
try {
statementBegins();
clearWarnings();
// 如果配置rewriteBatchedStatements 開啟多SQL執(zhí)行
if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {
if (getQueryInfo().isRewritableWithMultiValuesClause()) {
return executeBatchWithMultiValuesClause(batchTimeout);
}
if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null
&& this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) {
return executePreparedBatchAsMultiStatement(batchTimeout);
}
}
return executeBatchSerially(batchTimeout);
} finally {
this.query.getStatementExecuting().set(false);
clearBatch();
}
}
}我們再看下insert做了什么事情
public int insert(String statement, Object parameter) {
return update(statement, parameter);
} public int update(String statement, Object parameter) {
try {
dirty = true;
MappedStatement ms = configuration.getMappedStatement(statement);
return executor.update(ms, wrapCollection(parameter));
} catch (Exception e) {
throw ExceptionFactory.wrapException("Error updating database. Cause: " + e, e);
} finally {
ErrorContext.instance().reset();
}
} public int update(MappedStatement ms, Object parameter) throws SQLException {
ErrorContext.instance().resource(ms.getResource()).activity("executing an update").object(ms.getId());
if (closed) {
throw new ExecutorException("Executor was closed.");
}
clearLocalCache();
return doUpdate(ms, parameter);
}重點方法在doUpdate(ms,parameter). 完成SQL的拼裝
@Override
public int doUpdate(MappedStatement ms, Object parameterObject) throws SQLException {
final Configuration configuration = ms.getConfiguration();
final StatementHandler handler = configuration.newStatementHandler(this, ms, parameterObject, RowBounds.DEFAULT, null, null);
final BoundSql boundSql = handler.getBoundSql();
final String sql = boundSql.getSql();
final Statement stmt;
// 數(shù)據(jù)的SQL語句必須完全一致,包括表名和列
if (sql.equals(currentSql) && ms.equals(currentStatement)) {
int last = statementList.size() - 1;
stmt = statementList.get(last);
applyTransactionTimeout(stmt);
handler.parameterize(stmt);// fix Issues 322
BatchResult batchResult = batchResultList.get(last);
batchResult.addParameterObject(parameterObject);
} else {
Connection connection = getConnection(ms.getStatementLog());
stmt = handler.prepare(connection, transaction.getTimeout());
handler.parameterize(stmt); // fix Issues 322
currentSql = sql;
currentStatement = ms;
statementList.add(stmt);
batchResultList.add(new BatchResult(ms, sql, parameterObject));
}
handler.batch(stmt);
return BATCH_UPDATE_RETURN_VALUE;
}以上就是saveBatch的原理。
總結(jié)
1: 想要批量執(zhí)行操作 數(shù)據(jù)庫鏈接參數(shù)加上rewriteBatchedStatements=true
rewriteBatchedStatements參數(shù)需要保證5.1.13以上版本的驅(qū)動才能實現(xiàn)高性能的批量插入
2: 根據(jù)doUpdate(ms,parameter). 完成SQL的拼裝的原理可以得出,如果批量插入的數(shù)據(jù),有些數(shù)據(jù)字段值為null,不會批量查詢,而是單獨拼裝一個SQL執(zhí)行。
例如:
public class Student {
private String name;
private String address;
}100個Student,其中 20個name=null,其中 50個address==null。通過日志我們看下這種不會批量插入。
到此這篇關(guān)于MyBatis-Plus 批量保存方法的文章就介紹到這了,更多相關(guān)MyBatis-Plus 批量保存內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SpringBoot?整合Security權(quán)限控制的初步配置
這篇文章主要為大家介紹了SpringBoot?整合Security權(quán)限控制的初步配置實例,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2022-11-11
JavaSE網(wǎng)絡(luò)原理之UDP和TCP原理詳解
TCP、UDP都是屬于運(yùn)輸層的協(xié)議,提供端到端的進(jìn)程之間的邏輯通信,而IP協(xié)議是提供主機(jī)間的邏輯通信,應(yīng)用層規(guī)定應(yīng)用進(jìn)程在通信時所遵循的協(xié)議,這篇文章主要介紹了JavaSE網(wǎng)絡(luò)原理之UDP和TCP原理的相關(guān)資料,需要的朋友可以參考下2026-01-01
springboot 2.x整合mybatis實現(xiàn)增刪查和批量處理方式
這篇文章主要介紹了springboot 2.x整合mybatis實現(xiàn)增刪查和批量處理方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2021-09-09

