【精品】通用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);
}
1
2
3
4
5
6
7
8
9
10

• 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("");

        //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(").append(entityColumn.getColumn()).append("= case\" suffix=\"end, \">");

                sb.append("");

                sb.append(" when ")
                        .append(pkEntityColumn.getColumn()).append(" = ").append(pkEntityColumn.getColumnHolder("item"))
                        .append(" then ").append(columnHolder);

                sb.append("");

                sb.append("");
            }
        }
        sb.append("");

        //10.使用前面缓存的主键名、主键值拼接where子句
        sb.append("where ").append(pkEntityColumn.getColumn()).append(" in ");
        sb.append("");
        sb.append(" #{item.").append(pkEntityColumn.getProperty()).append("}");
        sb.append("");

        //11.将拼接好的字符串返回
        return sb.toString();
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

选择性批量更新

• Mapper接口

@RegisterMapper
public interface BatchUpdateSelectiveByIdMapper<T> {
    /**
     * 选择性批量更新
     * @param list
     * @return
     */
    @UpdateProvider(type = BatchUpdateSelectiveByIdProvider.class, method = "dynamicSQL")
    int batchUpdateSelective(List<T> list);
}
1
2
3
4
5
6
7
8
9
10

• 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("");

        //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(").append(entityColumn.getColumn()).append("= case\" suffix=\"end, \">");

                sb.append("");

                sb.append(").append(entityColumn.getProperty()).append(" != null\"> ");

                sb.append(" when ")
                        .append(pkEntityColumn.getColumn()).append(" = ").append(pkEntityColumn.getColumnHolder("item"))
                        .append(" then ").append(columnHolder);

                sb.append(" ");

                sb.append("");

                sb.append("");
            }
        }
        sb.append("");

        //10.使用前面缓存的主键名、主键值拼接where子句
        sb.append("where ").append(pkEntityColumn.getColumn()).append(" in ");
        sb.append("");
        sb.append(" #{item.").append(pkEntityColumn.getProperty()).append("}");
        sb.append("");

        //11.将拼接好的字符串返回
        return sb.toString();
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

测试代码

@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);
}
1
2
3
4
5
6
7
8
9
10