Mybatis+Oracle批量插入、更新、删除

1.批量插入

注意：<foreach>下传入对象的属性前要加上  item.  如下： #{item.username}

 <insert id="insertuser" parameterType="java.util.List" useGeneratedKeys="false" >
        insert into user
        (
            username,
            sex,
            age
        )
        <foreach close=")" collection="list" item="item" index="index" open="(" separator="union all">
            select
                #{item.username},
                #{item.sex},
                #{item.age}
            from dual 
        </foreach>
    </insert>

2.批量更新

 待验证

<update id="batchUpdate" parameterType="java.util.List">
     <foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";"> 
              update test 
                    <set>    
                            name=#{item.name},
                            age= #{item.age} 
                    </set>  
              where id = #{item.id}  
      </foreach>         
</update>

 3.Error问题解决

### Cause: java.sql.SQLSyntaxErrorException: ORA-02287: 此处不允许序号
 
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-02287: 此处不允许序号

mybatis报错代码

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper   
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"   
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.dao.Demo">
  
     
    <!-- 批量新增 -->
    <insert id="batchInsert" parameterType="com.test.dao.Demo">
         INSERT INTO DEMO_INFO
           (ID,           
            CAR_TYPE,
            DEPT_ID,
            STATUS,
            CREATED_BY,
            CREATED_DATE,
            UPDATED_BY,
            UPDATED_DATE)
    <foreach  collection="list"  separator="union all"
            item="rmi" index="index" >
            select
                 DEMO_SEQ_ID.NEXTVAL,
                #{rmi.type,jdbcType=VARCHAR},
                #{rmi.deptId,jdbcType=VARCHAR},             
                'Y',
                'sys',
                sysdate,
                'sys',
                sysdate
            from dual
        </foreach>
    </insert>
     
     
      
</mapper>

解决方法，修改后的代码

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper   
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"   
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.dao.Demo">
  
     
    <!-- 批量新增 -->
    <insert id="batchInsert" parameterType="com.test.dao.Demo">
         INSERT INTO DEMO_INFO
           (ID,
            CAR_TYPE,
            DEPT_ID,
            STATUS,
            CREATED_BY,
            CREATED_DATE,
            UPDATED_BY,
            UPDATED_DATE)
        SELECT DEMO_SEQ_ID.nextval id,
                c.carType,c.deptId,c.status,c.createdBy,c.createdDate,c.updatedBy,c.updatedDate FROM (
           <foreach  collection="list"  separator="union all"
                item="rmi" index="index" >
                select
                    #{rmi.carType,jdbcType=VARCHAR} carType,
                    #{rmi.deptId,jdbcType=VARCHAR} deptId,
                    'Y' status,
                    'sys' createdBy,
                    systimestamp createdDate,
                    'sys' updatedBy,
                    systimestamp updatedDate
                from dual
            </foreach>
        ) c
    </insert> 
     
</mapper>