MyBatis + Oracle 实现批量新增和批量修改

浅浅的花香味﹌ 2023-10-10 11:46 75阅读 0赞

MyBatis + Oracle 实现批量新增（基于序列化自增长主键）

mapper 接口

void batchInsert(List<ASingleProject> list);

mapper.xml

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false">
      	 insert into A_SINGLE_PROJECT (KID, SINGLEPROJECTID, ZJZMJ, 
          DSGD, DSCS, DXCS, JGLX, 
          JCXS, DS_JZMJ, JKC_JZMJ, 
          DX_JZMJ, ZZ_JZMJ, BG_JZMJ, 
          SY_JZMJ, CF_JZMJ, RF_JZMJ, 
          QT_YFMJ, DSGYTS, TS_60_100, 
          TS_100_144, TS_144, TS_BS, 
          ZTS, JGZDKD, HS, XSDTS, 
          KCFTS) 
           select s_a_single_project.nextval, cd.* from (
            <foreach collection="list" item="item" index="index" separator="UNION ALL" >
            (
            select 
       	  		#{item.singleprojectid,jdbcType=DECIMAL}, 
       	  		#{item.zjzmj,jdbcType=VARCHAR}, 
          		#{item.dsgd,jdbcType=VARCHAR}, 
          		#{item.dscs,jdbcType=VARCHAR}, 
          		#{item.dxcs,jdbcType=VARCHAR}, 
          		#{item.jglx,jdbcType=VARCHAR}, 
          		#{item.jcxs,jdbcType=VARCHAR}, 
          		#{item.dsJzmj,jdbcType=VARCHAR}, 
          		#{item.jkcJzmj,jdbcType=VARCHAR}, 
          		#{item.dxJzmj,jdbcType=VARCHAR}, 
          		#{item.zzJzmj,jdbcType=VARCHAR}, 
          		#{item.bgJzmj,jdbcType=VARCHAR}, 
          		#{item.syJzmj,jdbcType=VARCHAR}, 
          		#{item.cfJzmj,jdbcType=VARCHAR}, 
          		#{item.rfJzmj,jdbcType=VARCHAR}, 
          		#{item.qtYfmj,jdbcType=VARCHAR}, 
          		#{item.dsgyts,jdbcType=VARCHAR}, 
          		#{item.ts60100,jdbcType=VARCHAR}, 
          		#{item.ts100144,jdbcType=VARCHAR}, 
          		#{item.ts144,jdbcType=VARCHAR}, 
          		#{item.tsBs,jdbcType=VARCHAR}, 
          		#{item.zts,jdbcType=VARCHAR}, 
          		#{item.jgzdkd,jdbcType=VARCHAR}, 
          		#{item.hs,jdbcType=VARCHAR}, 
          		#{item.xsdts,jdbcType=VARCHAR}, 
          		#{item.kcfts,jdbcType=VARCHAR}
          	from dual
          	)
          </foreach>
          ) cd
      </insert>

MyBatis + Oracle 实现批量修改

mapper 接口

void batchUpdate(List<ASingleProject> list);

mapper.xml

<update id="batchUpdate" parameterType="java.util.List">
      	 <foreach collection="list" index="index" item="item" open="begin" close=";end;" separator=";">
      	 	 update A_SINGLE_PROJECT
        <set>
          <if test="item.zjzmj != null">
            ZJZMJ = #{item.zjzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.dsgd != null">
            DSGD = #{item.dsgd,jdbcType=VARCHAR},
          </if>
          <if test="item.dscs != null">
            DSCS = #{item.dscs,jdbcType=VARCHAR},
          </if>
          <if test="item.dxcs != null">
            DXCS = #{item.dxcs,jdbcType=VARCHAR},
          </if>
          <if test="item.jglx != null">
            JGLX = #{item.jglx,jdbcType=VARCHAR},
          </if>
          <if test="item.jcxs != null">
            JCXS = #{item.jcxs,jdbcType=VARCHAR},
          </if>
          <if test="item.dsJzmj != null">
            DS_JZMJ = #{item.dsJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.jkcJzmj != null">
            JKC_JZMJ = #{item.jkcJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.dxJzmj != null">
            DX_JZMJ = #{item.dxJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.zzJzmj != null">
            ZZ_JZMJ = #{item.zzJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.bgJzmj != null">
            BG_JZMJ = #{item.bgJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.syJzmj != null">
            SY_JZMJ = #{item.syJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.cfJzmj != null">
            CF_JZMJ = #{item.cfJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.rfJzmj != null">
            RF_JZMJ = #{item.rfJzmj,jdbcType=VARCHAR},
          </if>
          <if test="item.qtYfmj != null">
            QT_YFMJ = #{item.qtYfmj,jdbcType=VARCHAR},
          </if>
          <if test="item.dsgyts != null">
            DSGYTS = #{item.dsgyts,jdbcType=VARCHAR},
          </if>
          <if test="item.ts60100 != null">
            TS_60_100 = #{item.ts60100,jdbcType=VARCHAR},
          </if>
          <if test="item.ts100144 != null">
            TS_100_144 = #{item.ts100144,jdbcType=VARCHAR},
          </if>
          <if test="item.ts144 != null">
            TS_144 = #{item.ts144,jdbcType=VARCHAR},
          </if>
          <if test="item.tsBs != null">
            TS_BS = #{item.tsBs,jdbcType=VARCHAR},
          </if>
          <if test="item.zts != null">
            ZTS = #{item.zts,jdbcType=VARCHAR},
          </if>
          <if test="item.jgzdkd != null">
            JGZDKD = #{item.jgzdkd,jdbcType=VARCHAR},
          </if>
          <if test="item.hs != null">
            HS = #{item.hs,jdbcType=VARCHAR},
          </if>
          <if test="item.xsdts != null">
            XSDTS = #{item.xsdts,jdbcType=VARCHAR},
          </if>
          <if test="item.kcfts != null">
            KCFTS = #{item.kcfts,jdbcType=VARCHAR},
          </if>
        </set>
        where  SINGLEPROJECTID = #{item.singleprojectid,jdbcType=DECIMAL}
      	 </foreach>
      </update>