Mybatis实现批量插入或者可覆盖更新

本文讲述 Mybatis 通过 on duplicate key update 实现批量插入或覆盖更新。

使用要点

（1） 表要求必须有主键或唯一索引才能起效果，否则 insert 或 update 无效；

（2） 注意语法 on duplicate key update 后面应为需要更新字段。

（3） 相较于 replace into（insert加强版，不存在时insert，存在时先delete后insert）虽然也能达到批量更新目的，但因为删除和添加需要重复维护索引，所以大批量比 on duplicate key update 性能要差，小量可忽略，自选为主。

以 article表为例：

CREATE TABLE `article` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '文章的唯一ID',
  `author` varchar(50) DEFAULT NULL COMMENT '作者',
  `title` varchar(100) DEFAULT NULL COMMENT '标题',
  `content` longtext COMMENT '文章的内容',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8

单条的插入或者更新

<insert id="insertOrUpdate" keyColumn="id" keyProperty="id" parameterType="com.itjing.generator.entity.Article" useGeneratedKeys="true">
    insert into article
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            id,
        </if>
        author,
        title,
        content,
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id,jdbcType=INTEGER},
        </if>
        #{author,jdbcType=VARCHAR},
        #{title,jdbcType=VARCHAR},
        #{content,jdbcType=LONGVARCHAR},
    </trim>
    on duplicate key update 
    <trim suffixOverrides=",">
        <if test="id != null">
            id = #{id,jdbcType=INTEGER},
        </if>
        author = #{author,jdbcType=VARCHAR},
        title = #{title,jdbcType=VARCHAR},
        content = #{content,jdbcType=LONGVARCHAR},
    </trim>
</insert>

单条的插入或者更新（根据有效字段）

<insert id="insertOrUpdateSelective" keyColumn="id" keyProperty="id" parameterType="com.itjing.generator.entity.Article" useGeneratedKeys="true">
    insert into article
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            id,
        </if>
        <if test="author != null">
            author,
        </if>
        <if test="title != null">
            title,
        </if>
        <if test="content != null">
            content,
        </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id,jdbcType=INTEGER},
        </if>
        <if test="author != null">
            #{author,jdbcType=VARCHAR},
        </if>
        <if test="title != null">
            #{title,jdbcType=VARCHAR},
        </if>
        <if test="content != null">
            #{content,jdbcType=LONGVARCHAR},
        </if>
    </trim>
    on duplicate key update 
    <trim suffixOverrides=",">
        <if test="id != null">
            id = #{id,jdbcType=INTEGER},
        </if>
        <if test="author != null">
            author = #{author,jdbcType=VARCHAR},
        </if>
        <if test="title != null">
            title = #{title,jdbcType=VARCHAR},
        </if>
        <if test="content != null">
            content = #{content,jdbcType=LONGVARCHAR},
        </if>
    </trim>
</insert>

批量插入

<insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
    insert into article
    (author, title, content)
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.author,jdbcType=VARCHAR}, #{item.title,jdbcType=VARCHAR}, #{item.content,jdbcType=LONGVARCHAR}
        )
    </foreach>
</insert>

批量插入或者更新（如果更新时字段值有效则更新）

<insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
    insert into article
    (author, title, content)
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.author,jdbcType=VARCHAR}, #{item.title,jdbcType=VARCHAR}, #{item.content,jdbcType=LONGVARCHAR}
        )
    </foreach>
    on duplicate key update
    id = IF(ISNULL(VALUES(id)),id,VALUES(id)),
    author = IF(ISNULL(VALUES(author)),author,VALUES(author)),
    title = IF(ISNULL(VALUES(title)),title,VALUES(title)),
    content = IF(ISNULL(VALUES(content)),content,VALUES(content))
</insert>

比如利用 自动生成工具生成的 insertOrUpdate 的 on duplicate key update后面的内容为：

id = #{id,jdbcType=INTEGER},
author = #{author,jdbcType=VARCHAR},
title = #{title,jdbcType=VARCHAR},
content = #{content,jdbcType=LONGVARCHAR},

我们可以利用这段内容通过 nodepad3 替换成我们需要的内容。

在 nodepad3 中输入 ctrl + H 进入替换页面。

然后使用正则表达式将其替换成我们需要的内容：

(.+)   # 匹配某行全部内容

$1 = IF(ISNULL(VALUES($1)),$1,VALUES($1)),  # 替换后内容，$1为之前匹配内容

如下则为我们需要的内容，注意最后没有逗号：

id = IF(ISNULL(VALUES(id)),id,VALUES(id)),
author = IF(ISNULL(VALUES(author)),author,VALUES(author)),
title = IF(ISNULL(VALUES(title)),title,VALUES(title)),
content = IF(ISNULL(VALUES(content)),content,VALUES(content))

将上述内容放到 on duplicate key update 后面即可。

这样的话，如果更新时，插入值为 null ，则使用原值。

如果在字段多的情况下，可以使用上述方式去生成替换，字段少的话手动也是可以的。

本文只是本人突发奇想做个记录，留个笔记。