【精品】通用Mapper 批量更新bug解决方案

问题描述

环境：mysql8.x+mybatis3.5.13+tk.mybatis4.2.3

在使用tk.mybatis做批量更新时，程序会报错，说是执行的SQL语法错误，经研究源代码发现tk.mybatis在实现批量更新时是通过多次执行update语句实现的。这本身就不符合MySQL批量更新的语法，可以通过自定义Mapper的方式解决。

有关MySQL批量更新SQL语句的语法请参考：MySQL专有的SQL语句

解决方案

批量更新

• 接口

@RegisterMapper
public interface BatchUpdateByIdMapper<T> {/*** 批量更新* @param list* @return*/@UpdateProvider(type = BatchUpdateByIdProvider.class, method = "dynamicSQL")int batchUpdate(List<T> list);
}

• provider

public class BatchUpdateByIdProvider extends MapperTemplate {public BatchUpdateByIdProvider(Class<?> mapperClass, MapperHelper mapperHelper) {super(mapperClass, mapperHelper);}public String batchUpdate(MappedStatement statement) {//1.获取实体类对应的Class对象Class<?> entityClass = super.getEntityClass(statement);//2.获取实体类在数据库中对应的表名String tableName = super.tableName(entityClass);//3.生成update子句String update = SqlHelper.updateTable(entityClass, tableName);//4.创建StringBuilder用于拼接SQL语句的各个组成部分StringBuilder sb = new StringBuilder(update);sb.append("<trim prefix=\"set\" suffixOverrides=\",\">");//5.获取所有字段信息Set<EntityColumn> columns = EntityHelper.getColumns(entityClass);//获取主键EntityColumn pkEntityColumn = null;for (EntityColumn entityColumn : columns) {boolean isPrimaryKey = entityColumn.isId();if (isPrimaryKey) {pkEntityColumn = entityColumn;break;}}for (EntityColumn entityColumn : columns) {boolean isPrimaryKey = entityColumn.isId();//6.判断当前字段是否为主键if (!isPrimaryKey) {//7.使用非主键字段拼接SET子句String columnHolder = entityColumn.getColumnHolder("item");sb.append("<trim prefix=\"").append(entityColumn.getColumn()).append("= case\" suffix=\"end, \">");sb.append("<foreach collection=\"list\" index=\"index\" item=\"item\">");sb.append(" when ").append(pkEntityColumn.getColumn()).append(" = ").append(pkEntityColumn.getColumnHolder("item")).append(" then ").append(columnHolder);sb.append("</foreach>");sb.append("</trim>");}}sb.append("</trim>");//10.使用前面缓存的主键名、主键值拼接where子句sb.append("where ").append(pkEntityColumn.getColumn()).append(" in ");sb.append("<foreach close=\")\" collection=\"list\" item=\"item\" open=\"(\" separator=\", \">");sb.append(" #{item.").append(pkEntityColumn.getProperty()).append("}");sb.append("</foreach>");//11.将拼接好的字符串返回return sb.toString();}}

选择性批量更新

• Mapper接口

@RegisterMapper
public interface BatchUpdateSelectiveByIdMapper<T> {/*** 选择性批量更新* @param list* @return*/@UpdateProvider(type = BatchUpdateSelectiveByIdProvider.class, method = "dynamicSQL")int batchUpdateSelective(List<T> list);
}

• provider

public class BatchUpdateSelectiveByIdProvider extends MapperTemplate {public BatchUpdateSelectiveByIdProvider(Class<?> mapperClass, MapperHelper mapperHelper) {super(mapperClass, mapperHelper);}/*** 批量更新* @param statement* @return*/public String batchUpdateSelective(MappedStatement statement) {//1.获取实体类对应的Class对象Class<?> entityClass = super.getEntityClass(statement);//2.获取实体类在数据库中对应的表名String tableName = super.tableName(entityClass);//3.生成update子句String update = SqlHelper.updateTable(entityClass, tableName);//4.创建StringBuilder用于拼接SQL语句的各个组成部分StringBuilder sb = new StringBuilder(update);sb.append("<trim prefix=\"set\" suffixOverrides=\",\">");//5.获取所有字段信息Set<EntityColumn> columns = EntityHelper.getColumns(entityClass);//获取主键EntityColumn pkEntityColumn = null;for (EntityColumn entityColumn : columns) {boolean isPrimaryKey = entityColumn.isId();if (isPrimaryKey) {pkEntityColumn = entityColumn;break;}}for (EntityColumn entityColumn : columns) {boolean isPrimaryKey = entityColumn.isId();//6.判断当前字段是否为主键if (!isPrimaryKey) {//7.使用非主键字段拼接SET子句String columnHolder = entityColumn.getColumnHolder("item");sb.append("<trim prefix=\"").append(entityColumn.getColumn()).append("= case\" suffix=\"end, \">");sb.append("<foreach collection=\"list\" index=\"index\" item=\"item\">");sb.append("<if test=\"item.").append(entityColumn.getProperty()).append(" != null\"> ");sb.append(" when ").append(pkEntityColumn.getColumn()).append(" = ").append(pkEntityColumn.getColumnHolder("item")).append(" then ").append(columnHolder);sb.append(" </if>");sb.append("</foreach>");sb.append("</trim>");}}sb.append("</trim>");//10.使用前面缓存的主键名、主键值拼接where子句sb.append("where ").append(pkEntityColumn.getColumn()).append(" in ");sb.append("<foreach close=\")\" collection=\"list\" item=\"item\" open=\"(\" separator=\", \">");sb.append(" #{item.").append(pkEntityColumn.getProperty()).append("}");sb.append("</foreach>");//11.将拼接好的字符串返回return sb.toString();}}

测试代码

@Test
void batchUpdate(){List<Subject> list = List.of(Subject.builder().id(13L).name("111").build(),Subject.builder().id(14L).name("222").build(),Subject.builder().id(15L).name("333").build(),Subject.builder().id(16L).name("444").build());subjectMapper.batchUpdateSelective(list);
}