oracle mybatis 批量插入遇到的问题

浏览数：228 / 时间：2015年06月12日

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>