mysql数据库的批量update，insert 在mybatis中

最新推荐文章于 2021-02-01 12:08:53 发布

yangkangtq

最新推荐文章于 2021-02-01 12:08:53 发布

阅读量504

点赞数

本文链接：https://blog.csdn.net/yangkangtq/article/details/47419663

版权

mysql数据库采用一下写法即可执行，但是数据库连接必须配置：&allowMultiQueries=true

批量插入：

    insert into jhh_licai_profit_log (user_id, from_all, from_real,
      from_privilege, addtime, addip,
      from_real_month,from_real_season,from_real_year)
    values
    <foreach collection="list" item="item" index="index" separator="," open="" close="" >

    (#{item.userId,jdbcType=INTEGER}, #{item.fromAll,jdbcType=DECIMAL}, #{item.fromReal,jdbcType=DECIMAL},
      #{item.fromPrivilege,jdbcType=DECIMAL}, #{item.addtime,jdbcType=TIMESTAMP}, #{item.addip,jdbcType=VARCHAR},

       #{item.from_real_month,jdbcType=DECIMAL},
      #{item.from_real_season,jdbcType=DECIMAL}, #{item.from_real_year,jdbcType=DECIMAL})

</foreach>

批量更新：

<update id="batchUpdate" parameterType="java.util.List">
      <foreach collection="list" item="item" index="index" open="" close="" separator=";">
                update jhh_licai_account_privilege
       <set>
           <if test="item.userId != null">
               user_id = #{item.userId,jdbcType=INTEGER},
           </if>
           <if test="item.productId != null">
               product_id = #{item.productId,jdbcType=INTEGER},
           </if>
           <if test="item.account != null">
               account = #{item.account,jdbcType=DECIMAL},
           </if>
           <if test="item.status != null">
               status = #{item.status,jdbcType=INTEGER},
           </if>
           <if test="item.addtime != null">
               addtime = #{item.addtime,jdbcType=TIMESTAMP},
           </if>
           <if test="item.addip != null">
               addip = #{item.addip,jdbcType=VARCHAR},
           </if>
           <if test="item.deleted != null">
               deleted = #{item.deleted,jdbcType=TINYINT},
           </if>
       </set>
       where id = #{item.id,jdbcType=INTEGER}
         </foreach>

    </update>

oracle数据库：（还没有测试）

 
      < 
      update 
      id= 
      "batchUpdate"  
       parameterType= 
      "java.util.List" 
      > 
     
      <foreach collection= 
      "list" 
      item= 
      "item" 
       index 
      = 
      "index" 
      open 
      = 
      "begin" 
      close 
      = 
      "end;" 
      separator= 
      ";" 
      > 
     
      update 
      test  
     
      < 
      set 
      > 
     
      test=${item.test}+1 
     
      </ 
      set 
      > 
     
      where 
      id = ${item.id} 
     
      </foreach> 
     
      </ 
      update 
      >