MyBatis BULK INSERT data into Oracle

MyBatis BULK INSERT data to Oracle Report "Java.sql.sqlexception:ora-00933:sql command does not end correctly" error resolution

Oracle BULK INSERT uses the Insert all into table (...) into table (...) VALUES (...) select * from dual; Statement to resolve, but always reported the following error

# # # The error may involve applauddaoimpl.addlist-inline### the error occurred whilesetting 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 1From dual### Cause:java.sql.SQLException:ORA-00933: SQL command did not end correctly; bad SQL grammar []; nested exception is Java.sql.SQLException:ORA-00933: SQL command did not end correctly 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. $Proxy 12.insert (Unknown Source) at Org.mybatis.spring.SqlSessionTemplate.insert (sqlsessionte Mplate.java:240) at Com.teshehui.applaud.dao.impl.ApplaudDaoImpl.addList (Applauddaoimpl.java:33)    ... 52morecaused By:java.sql.SQLException:ORA-00933: SQL command did not end correctly 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:30S) at Org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke (Preparedstatementlogger.java:55) at Com.sun.proxy. $Proxy 17.execute (Unknown Source) at Org.apache.ibatis.executor.statement.PreparedStatementHa Ndler.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:30S) at Org.apache.ibatis.plugin.Plugin.invoke (Plugin.java:59) at Com.sun.proxy. $Proxy 15.update (Unknown Source) at Org.apache.ibatis.executor.SimpleExecutor.doUpdate Executor.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:30S) at Org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke (Sqlsessiontemplate.java:358)    ... More

Google countless times, their own tracking debugging also no results, and finally in the StackOverflow mybatis question and answer zone accidentally saw the Netizen analysis (chain interview at the end), the test found resolved, the reason is unexpectedly mybatis in BULK insert Oracle needs to be explicitly specified as Usegeneratedkeys= "false" or Error ~ ~ ~

The SQL configuration for the correct MyBatis mapper is as follows:

1 <InsertID= "Addlist"ParameterType= "Java.util.List"Usegeneratedkeys= "false">2 INSERT All3         <foreachItem= "Item"Index= "Index"Collection= "List">4 Into T_applaud5         (6 ID,7 user_id,8 Business_type,9 product_id,Ten Create_time One ) VALUES A         ( - #{item.id, jdbctype=numeric}, - #{item.userid, Jdbctype=varchar}, the #{item.businesstype, Jdbctype=varchar}, - #{item.productid, Jdbctype=varchar}, - #{item.createdtime, jdbctype=numeric} -         ) +         </foreach> - SELECT 1 from DUAL +     </Insert>

Another way is to insert into table (...). (select ... from dual), union ALL (select ... from dual)

1     <InsertID= "Addlist"ParameterType= "Java.util.List"Usegeneratedkeys= "false">2 INSERT into T_applaud3         (4 ID,5 user_id,6 Business_type,7 product_id,8 Create_time9         )Ten         <foreachItem= "Item"Index= "Index"Collection= "List"Separator= "UNION All"> One         ( A SELECT - #{item.id}, - #{item.userid}, the #{item.businesstype}, - #{item.productid}, - #{item.createdtime} - From DUAL +         ) -         </foreach> +     </Insert>

Reference:

http://stackoverflow.com/questions/24956269/ Camel-2-13-1-mybatis-3-2-7-batch-insert-to-oracle-11g-table-ora-00933-sql-comma

MyBatis BULK INSERT data into Oracle