bboyjing's blog

MybatisGenerator插件开发四【批量插入】

发表于 | 分类于

批量插入和分页查询一样，在项目中基本也是不可少的。这里我们要给Mapper添加两个方法：batchInsert和batchInsertSelective，区别是前者全字段插入，后者只插入给定的字段。原来的单条insertSelective是通过对象的值是不是Null来实现的，我们也可以取插入记录的第0条来实现batchInsertSelective。但是，始终感觉有点不对，所以采取了另一种方式，显式地传递要插入地字段来实现。这里就会用到了上一章节写入到Model中的表字段枚举了。

下面直接上代码。

修改Mapper接口和SqlMap文件

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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
public class BatchInsertPlugin extends PluginAdapter {
    @Override
    public boolean validate(List<String> list) {
        return true;
    }
    /**
     * 修改Mapper类
     */
    @Override
    public boolean clientGenerated(Interface interfaze, TopLevelClass topLevelClass, IntrospectedTable introspectedTable) {
        addBatchInsertMethod(interfaze, introspectedTable);
        return true;
    }
    private void addBatchInsertMethod(Interface interfaze, IntrospectedTable introspectedTable) {
        // 设置需要import的类
        Set<FullyQualifiedJavaType> importedTypes = new TreeSet<>();
        importedTypes.add(FullyQualifiedJavaType.getNewListInstance());
        importedTypes.add(new FullyQualifiedJavaType(introspectedTable.getBaseRecordType()));
        FullyQualifiedJavaType ibsreturnType = FullyQualifiedJavaType.getIntInstance();
        Method batchInsertMethod = new Method();
        // 1.设置方法可见性
        batchInsertMethod.setVisibility(JavaVisibility.PUBLIC);
        // 2.设置返回值类型 int类型
        batchInsertMethod.setReturnType(ibsreturnType);
        // 3.设置方法名
        batchInsertMethod.setName("batchInsert");
        // 4.设置参数列表
        FullyQualifiedJavaType paramType = FullyQualifiedJavaType.getNewListInstance();
        FullyQualifiedJavaType paramListType = new FullyQualifiedJavaType(introspectedTable.getBaseRecordType());
        paramType.addTypeArgument(paramListType);
        batchInsertMethod.addParameter(new Parameter(paramType, "records"));
        interfaze.addImportedTypes(importedTypes);
        interfaze.addMethod(batchInsertMethod);
        Method batchInsertSelectiveMethod = new Method();
        // 1.设置方法可见性
        batchInsertSelectiveMethod.setVisibility(JavaVisibility.PUBLIC);
        // 2.设置返回值类型 int类型
        batchInsertSelectiveMethod.setReturnType(ibsreturnType);
        // 3.设置方法名
        batchInsertSelectiveMethod.setName("batchInsertSelective");
        // 4.设置参数列表
        FullyQualifiedJavaType paramTypeSelective = FullyQualifiedJavaType.getNewListInstance();
        FullyQualifiedJavaType paramListTypeSelective = new FullyQualifiedJavaType(introspectedTable.getBaseRecordType());
        paramTypeSelective.addTypeArgument(paramListTypeSelective);
        batchInsertSelectiveMethod.addParameter(new Parameter(paramTypeSelective, "records", "@Param(\"records\")"));
        batchInsertSelectiveMethod.addParameter(new Parameter(FullyQualifiedJavaType.getStringInstance(), "columns", "@Param(\"columns\")", true));
        interfaze.addImportedTypes(importedTypes);
        interfaze.addMethod(batchInsertSelectiveMethod);
    }
    /**
     * 修改Mapper.xml
     */
    @Override
    public boolean sqlMapDocumentGenerated(Document document, IntrospectedTable introspectedTable) {
        addBatchInsertXml(document, introspectedTable);
        addBatchInsertSelectiveXml(document, introspectedTable);
        return true;
    }
    private void addBatchInsertXml(Document document, IntrospectedTable introspectedTable) {
        // <insert ...
        XmlElement insertBatchElement = new XmlElement("insert");
        insertBatchElement.addAttribute(new Attribute("id", "batchInsert"));
        insertBatchElement.addAttribute(new Attribute("parameterType", "java.util.List"));
        XmlElement valueTrimElement = new XmlElement("trim");
        valueTrimElement.addAttribute(new Attribute("prefix", " ("));
        valueTrimElement.addAttribute(new Attribute("suffix", ")"));
        valueTrimElement.addAttribute(new Attribute("suffixOverrides", ","));
        XmlElement columnTrimElement = new XmlElement("trim");
        columnTrimElement.addAttribute(new Attribute("prefix", "("));
        columnTrimElement.addAttribute(new Attribute("suffix", ")"));
        columnTrimElement.addAttribute(new Attribute("suffixOverrides", ","));
        List<IntrospectedColumn> columns = introspectedTable.getAllColumns();
        for (IntrospectedColumn introspectedColumn : columns) {
            String columnName = introspectedColumn.getActualColumnName();
            columnTrimElement.addElement(new TextElement(columnName+","));
            valueTrimElement.addElement(new TextElement("#{item." + introspectedColumn.getJavaProperty() + ",jdbcType=" + introspectedColumn.getJdbcTypeName() + "},"));
        }
        XmlElement foreachElement = new XmlElement("foreach");
        foreachElement.addAttribute(new Attribute("collection", "list"));
        foreachElement.addAttribute(new Attribute("index", "index"));
        foreachElement.addAttribute(new Attribute("item", "item"));
        foreachElement.addAttribute(new Attribute("separator", ","));
        insertBatchElement.addElement(new TextElement("insert into " + introspectedTable.getAliasedFullyQualifiedTableNameAtRuntime()));
        insertBatchElement.addElement(columnTrimElement);
        insertBatchElement.addElement(new TextElement(" values "));
        foreachElement.addElement(valueTrimElement);
        insertBatchElement.addElement(foreachElement);
        document.getRootElement().addElement(insertBatchElement);
    }
    private void addBatchInsertSelectiveXml(Document document, IntrospectedTable introspectedTable) {
        // <insert ...
        XmlElement insertBatchElement = new XmlElement("insert");
        insertBatchElement.addAttribute(new Attribute("id", "batchInsertSelective"));
        insertBatchElement.addAttribute(new Attribute("parameterType", "map"));
        XmlElement foreachColumn = new XmlElement("foreach");
        foreachColumn.addAttribute(new Attribute("collection", "columns"));
        foreachColumn.addAttribute(new Attribute("index", "index"));
        foreachColumn.addAttribute(new Attribute("item", "item"));
        foreachColumn.addAttribute(new Attribute("separator", ","));
        foreachColumn.addAttribute(new Attribute("open", "("));
        foreachColumn.addAttribute(new Attribute("close", ")"));
        foreachColumn.addElement(new TextElement("${item}"));
        insertBatchElement.addElement(new TextElement("insert into " + introspectedTable.getAliasedFullyQualifiedTableNameAtRuntime()));
        insertBatchElement.addElement(foreachColumn);
        insertBatchElement.addElement(new TextElement(" values "));
        XmlElement valueTrimElement = new XmlElement("trim");
        valueTrimElement.addAttribute(new Attribute("prefix", " ("));
        valueTrimElement.addAttribute(new Attribute("suffix", ")"));
        valueTrimElement.addAttribute(new Attribute("suffixOverrides", ","));
        XmlElement foreachColumnForValue = new XmlElement("foreach");
        foreachColumnForValue.addAttribute(new Attribute("collection", "columns"));
        foreachColumnForValue.addAttribute(new Attribute("index", "index"));
        foreachColumnForValue.addAttribute(new Attribute("item", "column"));
        List<IntrospectedColumn> columns = introspectedTable.getAllColumns();
        for (IntrospectedColumn introspectedColumn : columns) {
            String columnName = introspectedColumn.getActualColumnName();
            XmlElement check = new XmlElement("if");
            check.addAttribute(new Attribute("test", "'" + columnName + "' == column"));
            check.addElement(new TextElement("#{record." + introspectedColumn.getJavaProperty() + ",jdbcType=" + introspectedColumn.getJdbcTypeName() + "},"));
            foreachColumnForValue.addElement(check);
        }
        valueTrimElement.addElement(foreachColumnForValue);
        XmlElement foreachElement = new XmlElement("foreach");
        foreachElement.addAttribute(new Attribute("collection", "records"));
        foreachElement.addAttribute(new Attribute("index", "index"));
        foreachElement.addAttribute(new Attribute("item", "record"));
        foreachElement.addAttribute(new Attribute("separator", ","));
        foreachElement.addElement(valueTrimElement);
        insertBatchElement.addElement(foreachElement);
        document.getRootElement().addElement(insertBatchElement);
    }
}

上述代码在Mapper接口和SqlMap文件中添加了batchInsert和batchInsertSelective的支持。

查看生成文件

将插件添加到项目中，并且运行完MybatisGenerator，我们来查看下生成的相关文件：

1
2
3
4
5
6
7
public interface AdministratorMapper {
    ...
    int batchInsert(List<Administrator> records);
    int batchInsertSelective(@Param("records") List<Administrator> records, @Param("columns") String ... columns);
    ...
}
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
...
<insert id="batchInsert" parameterType="java.util.List">
    insert into administrator
    <trim prefix="(" suffix=")" suffixOverrides=",">
      id,
      mobile,
      pwd,
      role,
      deleted,
      create_time,
      update_time,
    </trim>
     values 
    <foreach collection="list" index="index" item="item" separator=",">
      <trim prefix=" (" suffix=")" suffixOverrides=",">
        #{item.id,jdbcType=BIGINT},
        #{item.mobile,jdbcType=VARCHAR},
        #{item.pwd,jdbcType=VARCHAR},
        #{item.role,jdbcType=TINYINT},
        #{item.deleted,jdbcType=INTEGER},
        #{item.createTime,jdbcType=BIGINT},
        #{item.updateTime,jdbcType=BIGINT},
      </trim>
    </foreach>
  </insert>
  <insert id="batchInsertSelective" parameterType="map">
    insert into administrator
    <foreach close=")" collection="columns" index="index" item="item" open="(" separator=",">
      ${item}
    </foreach>
     values 
    <foreach collection="records" index="index" item="record" separator=",">
      <trim prefix=" (" suffix=")" suffixOverrides=",">
        <foreach collection="columns" index="index" item="column">
          <if test="'id' == column">
            #{record.id,jdbcType=BIGINT},
          </if>
          <if test="'mobile' == column">
            #{record.mobile,jdbcType=VARCHAR},
          </if>
          <if test="'pwd' == column">
            #{record.pwd,jdbcType=VARCHAR},
          </if>
          <if test="'role' == column">
            #{record.role,jdbcType=TINYINT},
          </if>
          <if test="'deleted' == column">
            #{record.deleted,jdbcType=INTEGER},
          </if>
          <if test="'create_time' == column">
            #{record.createTime,jdbcType=BIGINT},
          </if>
          <if test="'update_time' == column">
            #{record.updateTime,jdbcType=BIGINT},
          </if>
        </foreach>
      </trim>
    </foreach>
  </insert>
...

如何使用

使用方式如下：

1
2
3
4
5
administratorMapper.batchInsertSelective(
    list,
    Administrator.COLUMNS.ID.getColumn(),
    Administrator.COLUMNS.MOBILE.getColumn()
);

本章节就到这里了。