MyBatis批量新增和更新

妖狐艹你老母 2021-08-29 21:52 462阅读 0赞

之前有开发任务一个接口里面有大量的数据新增和更新操作，导致十分缓慢。使用了批量操作之后速度有明显提升，几乎百倍千倍的速度提升。

**1、批量插入：**

1）mapper.xml：

<insert id="addWordsByList" parameterType="java.util.List">
        insert into words (word_no, value, filed_class, 
          pinyin, synonym, create_date, 
          update_date, operator_no, src_channel, 
          latest_operation, versions, file_index, 
          charac_class, weight)
        values 
        <foreach collection="list" item="item" index="index" separator="," >
            (#{item.wordNo},#{item.value},#{item.filedClass},#{item.pinyin},
            #{item.synonym},#{item.createDate},#{item.updateDate},#{item.operatorNo},
            #{item.srcChannel},#{item.latestOperation},#{item.versions},#{item.fileIndex},
            #{item.characClass},#{item.weight})
        </foreach>
    </insert>

2）接口：

public void addWordsByList(List<Words> wordsList);

**2、批量更新：**

批量更新必须在添加如下数据库连接配置：&allowMultiQueries=true，否则会报SQL格式错误；

jdbc:MySQL://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true

1）mapper.xml:

<update id="updateWordsByList"  parameterType="java.util.List">  
        <foreach collection="list" item="item" index="index" separator=";">
           update words
           <set >
              <if test="item.value != null" >
                value = #{item.value,jdbcType=VARCHAR},
              </if>
              <if test="item.filedClass != null" >
                filed_class = #{item.filedClass,jdbcType=VARCHAR},
              </if>
              <if test="item.pinyin != null" >
                pinyin = #{item.pinyin,jdbcType=VARCHAR},
              </if>
              <if test="item.synonym != null" >
                synonym = #{item.synonym,jdbcType=VARCHAR},
              </if>
              <if test="item.createDate != null" >
                create_date = #{item.createDate,jdbcType=TIMESTAMP},
              </if>
              <if test="item.updateDate != null" >
                update_date = #{item.updateDate,jdbcType=TIMESTAMP},
              </if>
              <if test="item.operatorNo != null" >
                operator_no = #{item.operatorNo,jdbcType=VARCHAR},
              </if>
              <if test="item.srcChannel != null" >
                src_channel = #{item.srcChannel,jdbcType=VARCHAR},
              </if>
              <if test="item.latestOperation != null" >
                latest_operation = #{item.latestOperation,jdbcType=VARCHAR},
              </if>
              <if test="item.versions != null" >
                versions = #{item.versions,jdbcType=BIGINT},
              </if>
              <if test="item.fileIndex != null" >
                file_index = #{item.fileIndex,jdbcType=BIGINT},
              </if>
              <if test="item.characClass != null" >
                charac_class = #{item.characClass,jdbcType=VARCHAR},
              </if>
              <if test="item.weight != null" >
                weight = #{item.weight,jdbcType=INTEGER},
              </if>
            </set>
            where word_no = #{item.wordNo,jdbcType=BIGINT}
        </foreach>       
    </update>

2）接口：

public void updateWordsByList(List<Words> wordsList);

此外，还有一种批量更新，如：update table set name="abc" where id in (...);

对于这种情况mybatis配置如下：

<updateid="updateBatch"parameterType="java.util.List">
        update mydata_table 
        set  status=
        <foreach collection="list" item="item" index="index" 
            separator=" " open="case ID" close="end">
            when #{item.id} then #{item.status}
        </foreach>
        where id in
        <foreach collection="list" index="index" item="item" 
            separator="," open="(" close=")">
            #{item.id,jdbcType=BIGINT}
        </foreach>
    </update>

参考：[https://blog.csdn.net/lu1024188315/article/details/78758943][https_blog.csdn.net_lu1024188315_article_details_78758943]

[https_blog.csdn.net_lu1024188315_article_details_78758943]: https://blog.csdn.net/lu1024188315/article/details/78758943