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