Oracle mybatis 批量新增时使用序列的写法

记录下Oracle数据库在使用mybatis框架批量新增的时候使用序列的特殊写法

 

刚开始时是直接select出来之后在service中使用的，但是批量生成数据的时候会出现频繁访问数据库，所以才探索了一番。

于是有了下面的写法（我这边不是单纯用序列生成id，所以在最内层的select中需要取上别名好在外层的select中使用）

 

  <insert id="insertList" parameterType="java.util.List">
    insert into CREW_MEAL_COUPON(CMC_MEAL_CODE,
    CMC_ID, CMC_CREW_ID, CMC_OSL_ID,
    CMC_ORIGINAL_VALUE, CMC_ISSUE_TIME, CMC_EXPIRE_TIME,
    CMC_LAST_USED_TIME, CMC_REMAINED_VALUE, CMC_MANUALLY_VALUE,
    CMC_MANUALLY_USER_ID, VER_NUM, CREATE_USER_ID,
    CREATE_TIME, UPDATE_USER_ID, UPDATE_TIME,
    CMC_VALIDATE, CMC_COMMENT, CMC_MEAL_TYPE,
    CMC_FLT_ID, CMC_UN_CODE,CMC_UN_FLT_CODE,CMC_SOURCE
    )
    select concat(temp.cmcMealCode, MEAL_ID.nextval),
    temp.cmcId, temp.cmcCrewId, temp.cmcOslId,
    temp.cmcOriginalValue, temp.cmcIssueTime, temp.cmcExpireTime,
    temp.cmcLastUsedTime, temp.cmcRemainedValue, temp.cmcManuallyValue,
    temp.cmcManuallyUserId, temp.verNum, temp.createUserId,
    temp.createTime, temp.updateUserId, temp.updateTime,
    temp.cmcValidate, temp.cmcComment, temp.cmcMealType,
    temp.cmcFltId, temp.cmcUnCode, temp.cmcUnFltCode, temp.cmcSource
    from (
        <foreach collection="list" item="item" index="index" separator="union all">
            select
          #{item.cmcId,jdbcType=BIGINT} cmcId, #{item.cmcCrewId,jdbcType=BIGINT} cmcCrewId, #{item.cmcOslId,jdbcType=BIGINT} cmcOslId,
          #{item.cmcOriginalValue,jdbcType=DOUBLE} cmcOriginalValue, #{item.cmcIssueTime,jdbcType=TIMESTAMP} cmcIssueTime, #{item.cmcExpireTime,jdbcType=TIMESTAMP} cmcExpireTime,
          #{item.cmcLastUsedTime,jdbcType=TIMESTAMP} cmcLastUsedTime, #{item.cmcRemainedValue,jdbcType=DOUBLE} cmcRemainedValue, #{item.cmcManuallyValue,jdbcType=DOUBLE} cmcManuallyValue,
          #{item.cmcManuallyUserId,jdbcType=BIGINT} cmcManuallyUserId, #{item.verNum,jdbcType=BIGINT} verNum, #{item.createUserId,jdbcType=BIGINT} createUserId,
          #{item.createTime,jdbcType=TIMESTAMP} createTime, #{item.updateUserId,jdbcType=BIGINT} updateUserId, #{item.updateTime,jdbcType=TIMESTAMP} updateTime,
          #{item.cmcValidate,jdbcType=VARCHAR} cmcValidate, #{item.cmcComment,jdbcType=VARCHAR} cmcComment, #{item.cmcMealType,jdbcType=VARCHAR} cmcMealType,
          #{item.cmcMealCode,jdbcType=VARCHAR} cmcMealCode, #{item.cmcFltId,jdbcType=BIGINT} cmcFltId, #{item.cmcUnCode,jdbcType=VARCHAR} cmcUnCode,
          #{item.cmcUnFltCode,jdbcType=VARCHAR} cmcUnFltCode, #{item.cmcSource,jdbcType=VARCHAR} cmcSource
          from dual
        </foreach>
    ) temp
  </insert>

特此记录下~