java - Mybatis批量插入异常:ORA-00933: SQL command not properly ended

Question

我的项目使用Springboot和Mybatis，我使用批量插入语法来保存数据我使用了oracle数据库

<sql id="site_columns">
    ID, SITE_URL, BRM_HGSSITE_ID, BRM_SITE_STATUS, BPS_HGSSITE_ID, SITE_STATUS, OP_HGSSITE_ID, BILLING_PLATFORM, BLIS_EFFECTIVE_FROM,
    BLIS_EFFECTIVE_TO, BRM_EFFECTIVE_FROM, ISSUE_TYPE, COMMENTS, CSM_FIRST_NAME, CSM_LAST_NAME, CSM_EMAIL, CREATE_TIME
</sql>

<insert id="batchSaveVerifiedSitesFromFile" parameterType="siteSummaryInfoBean">
    INSERT INTO MR_VERIFIED_SITE_LIST(<include refid="site_columns"/>) VALUES
    <foreach collection="list" item="site" separator=",">
        (MR_VERIFIED_SITE_LIST_SEQ.nextval, #{site.webexurl}, #{site.brm_hgssiteid}, #{site.brm_sitestatus},
        #{site.bps_hgssiteid}, #{site.sitestatus},
        #{site.op_hgssiteid}, #{site.billingplatform,jdbcType=VARCHAR}, #{site.blis_effectiveFrom,jdbcType=DATE},
        #{site.blis_effectiveTo,jdbcType=DATE}, #{site.brm_effectiveFrom,jdbcType=DATE},
        #{site.issueType,jdbcType=VARCHAR}, #{site.comments,jdbcType=VARCHAR},
        #{site.csmfirstname,jdbcType=VARCHAR}, #{site.csmlastname,jdbcType=VARCHAR},
        #{site.csmemail,jdbcType=VARCHAR},
        greatest(nvl(#{site.blis_effectiveTo,jdbcType=DATE},sysdate),
        nvl(#{site.brm_effectiveFrom,jdbcType=DATE},sysdate)))
    </foreach>
</insert>

Answer 1

Oracle不支持多行插入语句。

您可以编写一个insert into ... select ... union all select ... 语句as seen in a different answer .

这样，在 foreach 循环中，分隔符将是 union all，循环体将是 select ... from Dual。