通过When Case批量更新
1、pom
<!-- tk.mybatis -->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.1.5</version>
</dependency>
2、注解接口 -> MyDuplicateSelectiveMapper.java
package com.sundear.base.mybatis.tk.base;
import com.sundear.base.mybatis.tk.provider.MyDuplicateSelectiveProvider;
import org.apache.ibatis.annotations.InsertProvider;
import tk.mybatis.mapper.annotation.RegisterMapper;
import java.util.List;
/**
* @Author: xiu
* @Date: 2021/5/8 17:43
* @description 自定义，InsertListSelective
*/
@RegisterMapper
public interface MyDuplicateSelectiveMapper<T> {
/**
* 以var1第一条数据字段是否为空 进行Selective处理
*
* @param var1 list
* @return
*/
@InsertProvider(
type = MyDuplicateSelectiveProvider.class,
method = "dynamicSQL"
)
int myDuplicateSelective(List<? extends T> var1);
}
3、MyDuplicateSelectiveProvider.java
package com.sundear.base.mybatis.tk.provider;
import org.apache.ibatis.mapping.MappedStatement;
import tk.mybatis.mapper.entity.EntityColumn;
import tk.mybatis.mapper.mapperhelper.EntityHelper;
import tk.mybatis.mapper.mapperhelper.MapperHelper;
import tk.mybatis.mapper.mapperhelper.MapperTemplate;
import tk.mybatis.mapper.mapperhelper.SqlHelper;
import java.util.Iterator;
import java.util.Set;
/**
* @Author: xiu
* @Date: 2021/5/8 17:46
* @description
*/
public class MyDuplicateSelectiveProvider extends MapperTemplate {
public MyDuplicateSelectiveProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
super(mapperClass, mapperHelper);
}
public String myDuplicateSelective(MappedStatement ms) {
StringBuilder sql = myInsertListSelective(ms);
sql.append(" ON DUPLICATE KEY UPDATE ");
sql.append(" <trim suffixOverrides=\",\"> ");
Class<?> entityClass = this.getEntityClass(ms);
Set<EntityColumn> columnSet = EntityHelper.getColumns(entityClass);
columnSet.forEach((tempColumn) -> {
if (!tempColumn.isId()) {
sql.append("<if test=\"null != list[0].");
sql.append(tempColumn.getEntityField().getName());
sql.append("\">");
sql.append(tempColumn.getColumn() );
sql.append(" = values(" );
sql.append(tempColumn.getColumn() );
sql.append("),</if>");
}
});
sql.append("</trim>");
return sql.toString();
}
private StringBuilder myInsertListSelective(MappedStatement ms) {
Class<?> entityClass = this.getEntityClass(ms);
StringBuilder sql = new StringBuilder();
EntityColumn logicDeleteColumn = SqlHelper.getLogicDeleteColumn(entityClass);
sql.append("<bind name=\"listNotEmptyCheck\" value=\"@[email protected](list, '" + ms.getId() + " 方法参数为空')\"/>");
sql.append(SqlHelper.insertIntoTable(entityClass, this.tableName(entityClass), "list[0]"));
sql.append(insertColumns(entityClass, false, true, false, "list[0]", logicDeleteColumn));
sql.append(" VALUES ");
sql.append("<foreach collection=\"list\" item=\"record\" separator=\",\" >");
sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">");
Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
Iterator var6 = columnList.iterator();
EntityColumn column;
while (true) {
while (true) {
do {
if (!var6.hasNext()) {
sql.append("</trim>");
sql.append("</foreach>");
return sql;
}
column = (EntityColumn) var6.next();
} while (!column.isInsertable());
if (logicDeleteColumn != null && logicDeleteColumn == column) {
sql.append(SqlHelper.getLogicDeletedValue(column, false)).append(",");
} else {
if (column.isIdentity()) {
sql.append(SqlHelper.getIfCacheNotNull(column, column.getColumnHolder((String) null, "_cache", ",")));
} else {
sql.append(SqlHelper.getIfNotNull("list[0]", column, column.getColumnHolder("record", (String) null, ","), this.isNotEmpty()));
}
if (column.isIdentity()) {
sql.append(SqlHelper.getIfCacheIsNull(column, column.getColumnHolder() + ","));
}
}
}
}
}
private String insertColumns(Class<?> entityClass, boolean skipId, boolean notNull, boolean notEmpty, String entityName, EntityColumn logicDeleteColumn) {
StringBuilder sql = new StringBuilder();
sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">");
Set<EntityColumn> columnSet = EntityHelper.getColumns(entityClass);
Iterator var6 = columnSet.iterator();
while (true) {
EntityColumn column;
do {
do {
if (!var6.hasNext()) {
sql.append("</trim>");
return sql.toString();
}
column = (EntityColumn) var6.next();
} while (!column.isInsertable());
} while (skipId && column.isId());
if (logicDeleteColumn != null && logicDeleteColumn == column) {
sql.append(column.getColumn()).append(",");
} else {
if (notNull) {
sql.append(SqlHelper.getIfNotNull(entityName, column, column.getColumn() + ",", notEmpty));
} else {
sql.append(column.getColumn() + ",");
}
}
}
}
}
3、用我们的基础mapper继承我们上述接口 -》上篇文章已经创建过这个类
package com.sundear.base.mybatis;
import com.sundear.base.mybatis.tk.base.MyDuplicateSelectiveMapper;
import com.sundear.base.mybatis.tk.base.MyUpdateListForeachMapper;
import com.sundear.base.mybatis.tk.base.MyUpdateListWhenCaseMapper;
/**
* @Author: 秀丽
* @Date: 2021/5/8 17:41
* @description 继承自定义的方法
*/
public interface MyBaseMapper<T> extends
MyUpdateListForeachMapper<T>,
MyUpdateListWhenCaseMapper<T>,
MyDuplicateSelectiveMapper<T> {
}
4、然后我们就可以用实体类的mapper直接调用 myUpdateListWhenCaseByPrimaryKey(List<T> var1) 方法去批量更新了list了
@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
public class RunTest {
@Resource
private TbTestMapper tbTestMapper;
@Test
public void test2() {
List<TbTest> list = new ArrayList<>();
for (int i = 0; i < 4; i++) {
list.add(TbTest.builder().value(""+i).id(IdWorker.getIdWorker().nextIdStr()).build());
}
//方式一 foreach
tbTestMapper.myUpdateListForeachByPrimaryKey(list);
//方式二 when case
Example example = new Example(TbTest.class);
example.createCriteria()
.andIn(TbTest.Fields.id,list.stream().map(TbTest::getId).collect(Collectors.toList()));
tbTestMapper.myUpdateListWhenCaseByPrimaryKey(list,example);
//方式三 Duplicate
tbTestMapper.myDuplicateSelective(list);
}
}
5、三种方法效率
方式三效率最高，其次方式一
方式二为了满足通用调用方式。而且条件when是id。在大数据量下循环次数比较多，所以效率不如方式一。。可以自行测试。
方式三既可以满足update list的业务要求，也可以满足insert list的业务需求。还是比较方便的。
![Java String.format方法的简单使用[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)