oracle mybatis 批量插入遇到的問題

標籤：

1、這是一個很蛋疼的問題，用<insert>的話會一直報sql語法錯誤，我這裡改用<update>測試OK，用<select>貌似也行

2、批量插入帶oracle序列遞增遇到的錯誤：java.sql.SQLSyntaxErrorException: ORA-02287: 此處不允許序號

查資料說有很多限制

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the
following constructs:
■ A subquery in a DELETE, SELECT, or UPDATE statement
■ A query of a view or of a materialized view
■ A SELECT statement with the DISTINCT operator
■ A SELECT statement with a GROUP BY clause or ORDER BY clause
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
■ The WHERE clause of a SELECT statement
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
■ The condition of a CHECK constrain

在mybatis <foreach>外邊再套一層解決

    <update id="insertBatch" parameterType="java.util.Map" >        insert into T_CARD_MAGNETIC_DATA (RECORD_NO, ORDER_NO, ISSUE_ORG_CODE,        MANUFACTURER_RECORD_NO, CARD_NO, CARD_BIN,        CARD_TYPE, FIRST_STRIPE, SECOND_STRIPE,        THIRD_STRIPE, FACE_VALUE, VALID_DATE,        SERVICE_CODE, CARD_CVN, INIT_PWD,        CREATE_UID, CREATE_TIME, SYN_FLAG,        SYN_FROM_TIME, SYN_TO_TIME)        select seq.nextval,A.* from(        <foreach collection="cards" item="item" index="index" separator="union all">            select            #{cardOrder.orderNo,jdbcType=VARCHAR}, #{cardOrder.issueOrgCode,jdbcType=VARCHAR},            #{cardOrder.manufacturerRecordNo,jdbcType=DECIMAL}, #{item.cardNo,jdbcType=VARCHAR},            #{cardOrder.cardBin,jdbcType=VARCHAR},            #{cardOrder.cardType,jdbcType=VARCHAR}, #{firstStripe,jdbcType=VARCHAR}, #{secondStripe,jdbcType=VARCHAR},            #{thirdStripe,jdbcType=VARCHAR}, #{cardOrder.faceValue,jdbcType=DECIMAL},            #{cardOrder.validDate,jdbcType=DATE},            #{serviceCode,jdbcType=CHAR}, #{cardCvn,jdbcType=CHAR}, #{item.initPwd,jdbcType=VARCHAR},            #{createUid,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{synFlag,jdbcType=CHAR},            #{synFromTime,jdbcType=TIMESTAMP}, #{synToTime,jdbcType=TIMESTAMP}            from dual        </foreach>        )A    </update>

 

oracle mybatis 批量插入遇到的問題