mybatis中Oracle数据库的批量增删改操作小结

来源：互联网发布：h5 css js手机开发编辑：程序博客网时间：2024/06/04 19:47

下面写的主要是mybatis操作Oracle数据库的批量增删改。小弟今天第一次发博文，也是刚进入IT行业，如有错误，欢迎各位批评指正。

1.Oracle数据库批量增加

<insert id="insertBatch" parameterType="java.util.List">
        insert into USER_INFO (
           ID,
           EMPLOYEE_NO,
           LOGIN_NAME,
           EMPLOYEE_NAME,
           BATCH_NUM,
           UPDATE_USER,
           UPDATE_TIME,
           CREATE_TIME,
           CREATE_USER
       )
        select A.* FROM (
        <foreach collection="list" item="item" index="index"
            separator="union all">
             select
             #{item.id,jdbcType=VARCHAR},
           #{item.employeeNo,jdbcType=VARCHAR},
           #{item.loginName,jdbcType=VARCHAR},
           #{item.employeeName,jdbcType=VARCHAR},
           #{item.batchNum,jdbcType=VARCHAR},
           #{item.updateUser,jdbcType=VARCHAR},
           #{item.updateTime,jdbcType=TIMESTAMP},
           #{item.createTime,jdbcType=TIMESTAMP},
           #{item.createUser,jdbcType=VARCHAR}
             from
             dual
        </foreach>
        ) A
</insert>
2.Oracle数据库批量修改

<update id="updateBatch" parameterType="java.util.List">
   <foreach collection="list" item="item" index="index" open="begin" close=";end;"
   separator=";">
   update CUST_CREDIT_INFO
   <trim prefix="set" suffixOverrides=",">
      CREDIT_BALANCE=#{item.creditBalance ,jdbcType=DECIMAL},
      USE_AMOUNT=#{item.useAmount ,jdbcType=DECIMAL},
      UPDATE_USER=#{item.updateUser ,jdbcType=VARCHAR},
      UPDATE_TIME=#{item.updateTime ,jdbcType=TIMESTAMP},
   </trim>
   where CONTRACT_NUMBER = #{item.contractNumber}
   </foreach>
</update>

3.Oracle数据库的批量删除

<delete id = "deleteBatch" parameterType = "java.util.List">
       delete from USER_INFO where id in
       <foreach collection="list" index="index" item = "item" open="(" separator="," close=")">
         #{item}
       </foreach>
    </delete>

1 0