spring mybatis更新幾種操作
- 簡述
- 代碼實踐
- 攔截器批量操作
- 批量攔截器
- Configuration注入攔截器
- 測驗sql
- 測驗類
- 利用mybatis實作批量
- 原始碼
簡述
在日常編程中難免會有批量操作，實作物理批量操作框架有有不少，比如mybatis plus，還有提及一些比如跨資料庫操作也有操作的中間件比如阿里的ADB，還有apache開源的shardingsphere,哈哈，這都是微服務時代產生的產品，這里不多說，這里主要講解兩種實作批量更新，在學習之前，建議了解一下AOP和mybait的plugin原理intercepter，
1、mybatis攔截器實作批量操作
2、mybatis回圈，利用case when實作批量操作
代碼實踐
攔截器批量操作
1、撰寫批量操作類
在類中繼承mybatis自帶的批量操作類
package com.lgh.batch.inteceptor;
import org.apache.ibatis.executor.BatchExecutor;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.transaction.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;
/**
* 批量操作
*
* @author guohu
*/
final class BatchExecutorAdaptor extends BatchExecutor {
private Logger log = LoggerFactory.getLogger(BatchExecutorAdaptor.class);
public BatchExecutorAdaptor(Configuration configuration, Transaction transaction) {
super(configuration, transaction);
}
@Override
public int update(MappedStatement ms, Object parameter) throws SQLException {
if (parameter == null) {
super.update(ms, parameter);
}
final Object params;
if (parameter instanceof Map) {
final Map<String, Object> paramMap = (Map<String, Object>) parameter;
if (paramMap == null || paramMap.size() != 1) {
if (paramMap.size() == 2 && paramMap.get("collection") != null) {
params = paramMap.get("collection");
} else if (!paramMap.containsKey("param1")) {
return super.update(ms, parameter);
} else {
params = paramMap.get("param1");
}
} else {
params = paramMap.values().iterator().next();
}
} else if (parameter instanceof Iterable || parameter.getClass().isArray()) {
params = parameter;
} else {
params = Collections.singletonList(parameter);
}
final Iterable<?> paramIterable = toIterable(params);
try {
for (Object obj : paramIterable) {
super.update(ms, obj);
}
List<BatchResult> batchResults = doFlushStatements(false);
if (batchResults == null || batchResults.size() == 0) {
return 0;
}
return resolveUpdateResult(batchResults);
} catch (Exception e) {
log.error("batch execute", e);
doFlushStatements(true);
/**
* 批量插入，則報例外
*/
if ("INSERT".equalsIgnoreCase(ms.getSqlCommandType().name())) {
throw e;
}
return 0;
}
}
private Iterable<?> toIterable(final Object params) {
if (params == null) {
return Collections.emptyList();
}
Iterable<?> paramIterable;
if (params instanceof Iterable) {
paramIterable = (Iterable<?>) params;
} else if (params.getClass().isArray()) {
Object[] array = (Object[]) params;
paramIterable = Arrays.asList(array);
} else {
paramIterable = Collections.singletonList(params);
}
return paramIterable;
}
private int resolveUpdateResult(final List<BatchResult> batchResults) {
int result = 0;
for (BatchResult batchResult : batchResults) {
int[] updateCounts = batchResult.getUpdateCounts();
if (updateCounts == null || updateCounts.length == 0) {
continue;
}
for (int updateCount : updateCounts) {
result += updateCount;
}
}
return result;
}
}
批量攔截器
實作批量操作類之后，當然是注入攔截器啦，
package com.lgh.batch.inteceptor;
import com.lgh.batch.utils.ExecutorUtil;
import com.lgh.batch.utils.Reflection;
import org.apache.ibatis.executor.BatchExecutor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.Configuration;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.SQLException;
import java.util.Properties;
/**
* 批量操作攔截器，以Batch結束陳述句
*
* @author guohu
*/
@Intercepts({
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
})
public class BatchExecutorInterceptor implements Interceptor {
private static final Logger LOGGER = LoggerFactory.getLogger(BatchExecutorInterceptor.class);
@Override
public Object intercept(final Invocation invocation) throws Throwable {
//check argument
if (invocation.getArgs()[1] == null) {
return invocation.proceed();
}
final MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
// 是否需要批處理標識
if (!mappedStatement.getId().endsWith("Batch")) {
return invocation.proceed();
}
// 找到執行物件
final Executor targetExecutor = ExecutorUtil.getTargetExecutor((Executor) invocation.getTarget());
// 若是批處理，則不做操作
if (targetExecutor instanceof BatchExecutor) {
return invocation.proceed();
}
// 獲取組態檔
final Configuration configuration = (Configuration) Reflection.getField("configuration", targetExecutor);
// 創建批處理物件
final BatchExecutor batchExecutor = new BatchExecutorAdaptor(configuration, targetExecutor.getTransaction());
try {
return batchExecutor.update(mappedStatement, invocation.getArgs()[1]);
} catch (SQLException e) {
LOGGER.error("batch excute", e);
batchExecutor.flushStatements(true);
throw e;
}
}
@Override
public Object plugin(final Object target) {
if (!(target instanceof Executor)) {
return target;
}
if (target instanceof BatchExecutor) {
return target;
}
return Plugin.wrap(target, this);
}
@Override
public void setProperties(final Properties properties) {
}
}
Configuration注入攔截器
@Bean
public Interceptor myBatchInterceptor() {
return new BatchExecutorInterceptor();
}
測驗sql
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.lgh.mapper.UserTableMapper" >
<update id="updateBatch" parameterType="java.util.List">
update user set name=#{name} where id=#{id}
</update>
</mapper>
mapper物件
package com.lgh.mapper;
import com.lgh.entity.UserTable;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserTableMapper {
List<UserTable> findAll();
int updateBatch(List<UserTable> userTableList);
物體操作物件
package com.lgh.entity;
public class UserTable {
private String id;
private String name;
private String msg;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
}
測驗類
package com.lgh;
import com.lgh.entity.UserTable;
import com.lgh.mapper.UserTableMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class RunApplicationTest {
@Autowired
private UserTableMapper userTableMapper;
@Test
public void testSql() {
List<UserTable> list = new ArrayList<>();
UserTable table1 = new UserTable();
table1.setId("1");
table1.setName("張三1");
UserTable table2 = new UserTable();
table2.setId("2");
table2.setName("張三2");
UserTable table3 = new UserTable();
table3.setId("3");
table3.setName("張三3");
list.add(table1);
list.add(table2);
list.add(table3);
userTableMapper.updateBatch(list);
}
}
查看運行結果可知，只是執行一次update而已，實作物理批量操作
利用mybatis實作批量
<update id="updateByList" parameterType="list">
update user
set name = CASE
<foreach collection="users" item="user">
WHEN id = #{user.id} THEN #{user.name}
</foreach>
END
where id IN
<foreach collection="users" item="user" separator="," open="(" close=")">
#{user.id}
</foreach>
</update>
執行結果
2021-01-16 20:17:16.877 DEBUG 11388 --- [ main] c.l.mapper.UserTableMapper.updateByList : ==> Preparing: update user set name = CASE WHEN id = ? THEN ? WHEN id = ? THEN ? WHEN id = ? THEN ? END where id IN ( ? , ? , ? )
2021-01-16 20:17:16.921 DEBUG 11388 --- [ main] c.l.mapper.UserTableMapper.updateByList : ==> Parameters: 1(String), 張三1(String), 2(String), 張三2(String), 3(String), 張三3(String), 1(String), 2(String), 3(String)
2021-01-16 20:17:16.928 DEBUG 11388 --- [ main] c.l.mapper.UserTableMapper.updateByList : <== Updates: 3
原始碼
github
參考文獻
【sql批量】
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/250207.html
標籤：java