Mybatis怎么实现批量的id自增插入到oracle中

Question

因为oracle中并不支持自增id,所以使用下面这种批量的先查询id，再把id的值加1后放到id字段中去，老是报ORA-00933: SQL 命令未正确结束，请大神帮帮忙。

 <insert id="addRoleInfo" parameterType="java.util.List" useGeneratedKeys="false" >  
        <selectKey resultType="int" keyProperty="ID" order="BEFORE">  
            SELECT  MAX(ID)+1 FROM PORTAL_SYSTEM_ROLE 
        </selectKey>  
        insert into PORTAL_SYSTEM_ROLE (ID,ROLE_ID,ROLE_NAME,CREATE_TIME,UPDATE_TIME)   
        values  
        <foreach collection="list" item="item" index="index" separator="," >  
               (#{item.id,jdbcType=VARCHAR},
                #{item.roleId,jdbcType=VARCHAR},
                #{item.roleName,jdbcType=VARCHAR},
                #{item.createTime,jdbcType=TIMESTAMP},
                #{item.updateTime,jdbcType=TIMESTAMP})  
        </foreach>  
    </insert>

 org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-00933: SQL 命令未正确结束

### The error may exist in file [E:\HTML\apache-tomcat-7.0.69\webapps\4asyn\WEB-INF\classes\mybatis\RoleMapper.xml]
### The error may involve com.boco.eoms.portal.role.mapper.RoleMapper.addRoleInfo-Inline
### The error occurred while setting parameters
### SQL: insert into PORTAL_SYSTEM_ROLE (ID,ROLE_ID,ROLE_NAME,CREATE_TIME,UPDATE_TIME)         values                         (?,     ?,     ?,     ?,     ?)         ,                (?,     ?,     ?,     ?,     ?)
### Cause: java.sql.SQLException: ORA-00933: SQL 命令未正确结束

; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00933: SQL 命令未正确结束

Answer 1

你可以试试这个：
TEST_USER
SEQ_TEST_USER_ID.nextval

 <!-- 注意这里直接调用sequence的nextval函数 -->
 <insert id="insert" parameterType="User">
     insert into <include refid="TABLE_NAME" /> (ID,NAME,AGE)
         values ( <include refid="TABLE_SEQUENCE" /> ,#{name}, #{age} )
 </insert>


    或者参考下这个

        <insert id="addTrainRecordBatch" useGeneratedKeys="true" parameterType="java.util.List">  
    <selectKey resultType="long" keyProperty="id" order="AFTER">  
        SELECT  
        LAST_INSERT_ID()  
    </selectKey>  
    insert into t_train_record (add_time,emp_id,activity_id,flag)   
    values  
    <foreach collection="list" item="item" index="index" separator="," >  
        (#{item.addTime},#{item.empId},#{item.activityId},#{item.flag})  
    </foreach>  
</insert>  


    发现你的错误了吗？ 对了  insert的sql中不用写id字段。