MyBatis+Oracle+Sequence

转载

mob604756f4ef89 2021-09-17 17:23:00

文章标签 oracle java 批量插入批量更新 文章分类 Java 后端开发

【MyBatis + Oracle + Sequence】插入单条

<insert id="insertFfbz" parameterType="com.resources.novelcoronavirus.domain.FuFeiBingZhongPO">
<selectKey resultType="String" order="BEFORE" keyProperty="ID">
    SELECT appgm.SEQ_YB_FFBZ.NEXTVAL FROM DUAL
</selectKey>
  INSERT INTO appgm.yb_ffbz (
    ID,
    BZJSMLI,
    ABZJSBZMLDM,
    ABZJSBZMC,
    XDSSCZDM,
    XDSSCZMC,
    YXBZ,
    WYJLH,
    SJCJSJ,
    SJGXSJ,
    BBH,
    BZNH,
    BZ,
    BBMC,
    ZLZNYM,
    ZLZNDZDA
  ) VALUES (
    #{ID, jdbcType=VARCHAR},
    #{BZJSMLI, jdbcType=VARCHAR},
    #{ABZJSBZMLDM, jdbcType=VARCHAR},
    #{ABZJSBZMC, jdbcType=VARCHAR},
    #{XDSSCZDM, jdbcType=VARCHAR},
    #{XDSSCZMC, jdbcType=VARCHAR},
    #{YXBZ, jdbcType=VARCHAR},
    #{WYJLH, jdbcType=VARCHAR},
    #{SJCJSJ, jdbcType=VARCHAR},
    #{SJGXSJ, jdbcType=VARCHAR},
    #{BBH, jdbcType=VARCHAR},
    #{BZNH, jdbcType=VARCHAR},
    #{BZ, jdbcType=VARCHAR},
    #{BBMC, jdbcType=VARCHAR},
    #{ZLZNYM, jdbcType=VARCHAR},
    #{ZLZNDZDA, jdbcType=VARCHAR}
  )
</insert>

【MyBatis + Oracle + Sequence】批量插入

<insert id="batchInsertFfbz" useGeneratedKeys="true" parameterType="java.util.List">
<selectKey resultType="String" order="BEFORE" keyProperty="ID">
    SELECT appgm.SEQ_YB_FFBZ.NEXTVAL FROM DUAL
</selectKey>
  INSERT INTO appgm.yb_ffbz (
    ID,
    BZJSMLI,
    ABZJSBZMLDM,
    ABZJSBZMC,
    XDSSCZDM,
    XDSSCZMC,
    YXBZ,
    WYJLH,
    SJCJSJ,
    SJGXSJ,
    BBH,
    BZNH,
    BZ,
    BBMC,
    ZLZNYM,
    ZLZNDZDA
  ) SELECT appgm.SEQ_YB_FFBZ.NEXTVAL, t.* FROM (
<foreach collection="list" index="i" item="it" separator="UNION ALL">
      SELECT
        #{it.BZJSMLI, jdbcType=VARCHAR},
        #{it.ABZJSBZMLDM, jdbcType=VARCHAR},
        #{it.ABZJSBZMC, jdbcType=VARCHAR},
        #{it.XDSSCZDM, jdbcType=VARCHAR},
        #{it.XDSSCZMC, jdbcType=VARCHAR},
        #{it.YXBZ, jdbcType=VARCHAR},
        #{it.WYJLH, jdbcType=VARCHAR},
        #{it.SJCJSJ, jdbcType=VARCHAR},
        #{it.SJGXSJ, jdbcType=VARCHAR},
        #{it.BBH, jdbcType=VARCHAR},
        #{it.BZNH, jdbcType=VARCHAR},
        #{it.BZ, jdbcType=VARCHAR},
        #{it.BBMC, jdbcType=VARCHAR},
        #{it.ZLZNYM, jdbcType=VARCHAR},
        #{it.ZLZNDZDA, jdbcType=VARCHAR}
      FROM
        DUAL
</foreach>
  ) t
</insert>

【MyBatis + Oracle】批量更新

<update id="batchUpdateFfbz" parameterType="java.util.List">
<foreach collection="list" index="i" item="it" open="begin" close=";end;" separator=";">
    UPDATE
      appgm.yb_ffbz
    SET
      BZJSMLI = #{it.BZJSMLI, jdbcType=VARCHAR},
      ABZJSBZMLDM = #{it.ABZJSBZMLDM, jdbcType=VARCHAR},
      ABZJSBZMC = #{it.ABZJSBZMC, jdbcType=VARCHAR},
      XDSSCZDM = #{it.XDSSCZDM, jdbcType=VARCHAR},
      XDSSCZMC = #{it.XDSSCZMC, jdbcType=VARCHAR},
      YXBZ = #{it.YXBZ, jdbcType=VARCHAR},
      WYJLH = #{it.WYJLH, jdbcType=VARCHAR},
      SJCJSJ = #{it.SJCJSJ, jdbcType=VARCHAR},
      SJGXSJ = #{it.SJGXSJ, jdbcType=VARCHAR},
      BBH = #{it.BBH, jdbcType=VARCHAR},
      BZNH = #{it.BZNH, jdbcType=VARCHAR},
      BZ = #{it.BZ, jdbcType=VARCHAR},
      BBMC = #{it.BBMC, jdbcType=VARCHAR},
      ZLZNYM = #{it.ZLZNYM, jdbcType=VARCHAR},
      ZLZNDZDA = #{it.ZLZNDZDA, jdbcType=VARCHAR}
    WHERE
      BZJSMLI = #{it.BZJSMLI}
</foreach>
</update>