Mybatis结合Oracle的foreach insert批量插入

最近在做将mysql数据库项目迁移到oracle数据库项目中，发现某些sql存在不兼容的情况，比如批量插入在mysql中写法如下，mybatis配置文件

xxxMapper.xml文件：

 

	<insert id="batchInsert" parameterType="List">
		INSERT INTO USER_ANSWER (
			USER_ANSWER_ID,USER_SERVEY_ID,QUESTION_ID,OPTION_ID,ADD_DATE
		)
		VALUES
		<foreach collection="list" item="record" index="index" separator=",">
			(#{record.USER_ANSWER_ID}, #{record.USER_SERVEY_ID}, #{record.QUESTION_ID}, #{record.OPTION_ID}, #{record.ADD_DATE})
		</foreach>
	</insert>

 

 

        但是运行起来后就一直报错，报错信息如下：

 

### SQL: INSERT INTO USER_ANSWER (    USER_ANSWER_ID,USER_SERVEY_ID,QUESTION_ID,OPTION_ID,ADD_DATE   )   VALUES         (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)    ,     (?, ?, ?, ?, ?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

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

 

        把SQL复制出来在PL/SQL中运行也是报同样的错，如上也可以看出，使用批量插入执行的SQL语句等价于：

 INSERT INTO USER_ANSWER (
USER_ANSWER_ID,USER_SERVEY_ID,QUESTION_ID,OPTION_ID,ADD_DATE
)VALUES (?,?,?,?,?),(?,?,?,?,?)

 而在oracle中用insert into xxx values (xxx,xxx),(xxx,xxx) 这种语法是通不过的 。

发现这只适用于MySQL，不适用于Oracle，因此把xml文件修改一下：

	<insert id="batchInsert" parameterType="List">
		INSERT INTO USER_ANSWER (
			USER_ANSWER_ID,USER_SERVEY_ID,QUESTION_ID,OPTION_ID,ADD_DATE
		)
		SELECT A.*
		FROM (
			<foreach collection="list" item="record" index="index" separator="UNION ALL">
				SELECT
				#{record.USER_ANSWER_ID}, #{record.USER_SERVEY_ID}, #{record.QUESTION_ID}, #{record.OPTION_ID},
				#{record.ADD_DATE}
				FROM dual
			</foreach>
		)A
	</insert>

 

        运行通过。在Oracle的版本中，有几点需要注意的：

        1.SQL中没有VALUES；

        2.<foreach>标签中的(selece ..... from dual)；

        3.<foreach>标签中的separator的属性为"UNION ALL"，将查询合并结果集。