mybatis批量插入数据到oracle
mybatis 批量插入数据到 oracle 报 ”java.sql.SQLException: ORA-00933: SQL 命令未正确结束“ 错误解决方法
oracle 批量插入使用 insert all into table(...) values(...) into table(...) values(...) select * from dual; 语句来解决，但一直报如下错误
### The error may involve ApplaudDaoImpl.addList-Inline ### The error occurred while setting parameters ### SQL: INSERT ALL INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES ( ?, ?, ?, ?, ? )INTO T_APPLAUD ( ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) INTO T_APPLAUD (ID, USER_ID, BUSINESS_TYPE, PRODUCT_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?) SELECT 1 FROM DUAL ### Cause: java.sql.SQLException: ORA-00933: SQL 命令未正确结束; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-00933: SQL 命令未正确结束
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:</span>97<span style="color: rgba(0, 0, 0, 1)">) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:</span>72<span style="color: rgba(0, 0, 0, 1)">) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:</span>80<span style="color: rgba(0, 0, 0, 1)">) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:</span>80<span style="color: rgba(0, 0, 0, 1)">) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:</span>73<span style="color: rgba(0, 0, 0, 1)">) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:</span>368<span style="color: rgba(0, 0, 0, 1)">) at com.sun.proxy.$Proxy12.insert(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:</span>240<span style="color: rgba(0, 0, 0, 1)">) at com.teshehui.applaud.dao.impl.ApplaudDaoImpl.addList(ApplaudDaoImpl.java:</span>33<span style="color: rgba(0, 0, 0, 1)">) ... </span>52<span style="color: rgba(0, 0, 0, 1)"> moreCaused by: java.sql.SQLException: ORA-00933: SQL 命令未正确结束
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:</span>112<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:</span>331<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:</span>288<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:</span>745<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:</span>216<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:</span>966<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:</span>1170<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:</span>3339<span style="color: rgba(0, 0, 0, 1)">) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:</span>3445<span style="color: rgba(0, 0, 0, 1)">) at com.jolbox.bonecp.PreparedStatementHandle.execute(PreparedStatementHandle.java:</span>138<span style="color: rgba(0, 0, 0, 1)">) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:</span>57<span style="color: rgba(0, 0, 0, 1)">) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:</span>43<span style="color: rgba(0, 0, 0, 1)">) at java.lang.reflect.Method.invoke(Method.java:</span>606<span style="color: rgba(0, 0, 0, 1)">) at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:</span>55<span style="color: rgba(0, 0, 0, 1)">) at com.sun.proxy.$Proxy17.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:</span>41<span style="color: rgba(0, 0, 0, 1)">) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:</span>66<span style="color: rgba(0, 0, 0, 1)">) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:</span>57<span style="color: rgba(0, 0, 0, 1)">) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:</span>43<span style="color: rgba(0, 0, 0, 1)">) at java.lang.reflect.Method.invoke(Method.java:</span>606<span style="color: rgba(0, 0, 0, 1)">) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:</span>59<span style="color: rgba(0, 0, 0, 1)">) at com.sun.proxy.$Proxy15.update(Unknown Source) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:</span>45<span style="color: rgba(0, 0, 0, 1)">) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:</span>100<span style="color: rgba(0, 0, 0, 1)">) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:</span>75<span style="color: rgba(0, 0, 0, 1)">) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:</span>148<span style="color: rgba(0, 0, 0, 1)">) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:</span>137<span style="color: rgba(0, 0, 0, 1)">) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:</span>57<span style="color: rgba(0, 0, 0, 1)">) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:</span>43<span style="color: rgba(0, 0, 0, 1)">) at java.lang.reflect.Method.invoke(Method.java:</span>606<span style="color: rgba(0, 0, 0, 1)">) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:</span>358<span style="color: rgba(0, 0, 0, 1)">) ... </span>55 more</pre>
谷歌无数次，自己跟踪调试也没结果，最后在 stackoverflow mybatis 问答区偶然看到了网友分析（链接见末尾），测试发现解决了，原因竟是 mybatis 批量插入 oracle 时需要显式指定为 useGeneratedKeys="false" 不然报错 ~~~
正确的 mybatis 的 mapper 的 sql 配置如下：
1 <insert id="addList" parameterType="java.util.List" useGeneratedKeys="false"> 2 INSERT ALL 3 <foreach item="item" index="index" collection="list"> 4 INTO T_APPLAUD 5 ( 6 ID, 7 USER_ID, 8 BUSINESS_TYPE, 9 PRODUCT_ID, 10 CREATE_TIME 11 ) VALUES 12 ( 13 #{item.id, jdbcType=NUMERIC}, 14 #{item.userId, jdbcType=VARCHAR}, 15 #{item.businessType, jdbcType=VARCHAR}, 16 #{item.productId, jdbcType=VARCHAR}, 17 #{item.createdTime, jdbcType=NUMERIC} 18 ) 19 </foreach> 20 SELECT 1 FROM DUAL 21 </insert>
另外一种方法是 insert into table(...) (select ... from dual) union all (select ... from dual)
1 <insert id="addList" parameterType="java.util.List" useGeneratedKeys="false"> 2 INSERT INTO T_APPLAUD 3 ( 4 ID, 5 USER_ID, 6 BUSINESS_TYPE, 7 PRODUCT_ID, 8 CREATE_TIME 9 ) 10 <foreach item="item" index="index" collection="list" separator="union all"> 11 ( 12 SELECT 13 #{item.id}, 14 #{item.userId}, 15 #{item.businessType}, 16 #{item.productId}, 17 #{item.createdTime} 18 FROM DUAL 19 ) 20 </foreach> 21 </insert>
参考：
http://stackoverflow.com/questions/24956269/camel-2-13-1-mybatis-3-2-7-batch-insert-to-oracle-11g-table-ora-00933-sql-comma