我正在使用 mybatis，我想将 ArrayList 插入到某个表中。
好吧，在映射器中使用 foreach ，这最终会导致 Oracle 异常 ORA_00933 。
这是 mybatis 映射器：
<insert id="batchInsert" parameterType="java.util.List">
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values
<foreach collection="list" item="model" index="index" separator=",">
(#{model.role_id}, #{model.permissiongroup_id})
</foreach>
</insert>
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
### The error may involve com.gaotime.platform.system.mapper.RolePermissiongroupMapper.batchInsert-Inline
### The error occurred while setting parameters
### SQL: insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?) , (?, ?) , (?, ?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:95)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)
at com.sun.proxy.$Proxy5.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:236)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy15.batchInsert(Unknown Source)
at com.gaotime.platform.system.service.RolePermissiongroupService.batchInsert(RolePermissiongroupService.java:18)
at com.gaotime.platform.system.action.RolePermissiongroupAction.execute(RolePermissiongroupAction.java:54)
at com.gaotime.platform.handler.MqMessageHandler.handle(MqMessageHandler.java:20)
at unitask.ums.activemq.HandlerThread.run(HandlerThread.java:51)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
at com.sun.proxy.$Proxy27.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)
Please help me out, thanks
多一个映射器配置
<insert id="batchInsert" parameterType="java.util.List">
<foreach collection="list" item="model" index="index" separator=",">
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values
(#{model.role_id}, #{model.permissiongroup_id})
</foreach>
</insert>
I got this message
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
### The error may involve com.gaotime.platform.system.mapper.RolePermissiongroupMapper.batchInsert-Inline
### The error occurred while setting parameters
### SQL: insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?) , insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?) , insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values (?, ?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
here is the static insert statement
<insert id="batchInsert" parameterType="java.util.List">
<!-- <foreach collection="list" item="model" index="index" separator=";">
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values
(#{model.role_id,jdbcType=NUMERIC}, #{model.permissiongroup_id,jdbcType=NUMERIC})
</foreach> -->
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values(5,5);
insert into SYS_ROLES_PERMISSIONGROUP
(role_id, permissiongroup_id)
values(6,6)
</insert>
and the exception
19:00:21,531 DEBUG Thread-11 RolePermissiongroupMapper.batchInsert:139 - ==> Preparing: insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values(5,5); insert into SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id) values(6,6)
19:00:21,535 DEBUG Thread-11 RolePermissiongroupMapper.batchInsert:139 - ==> Parameters:
19:00:21,553 DEBUG Thread-11 impl.NewPooledConnection:430 - com.mchange.v2.c3p0.impl.NewPooledConnection@699238ad handling a throwable.
java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
For update, I still need help. Come over
google group
InsertMybatis 内部foreach不是批次，这是一个单一的（可能会变得巨大的）SQL 语句，并且带来了缺点：
- 有些数据库比如Oracle这里不支持。
- 在相关情况下：将需要插入大量记录，并且将达到数据库配置的限制（默认情况下每个语句大约 2000 个参数），如果语句本身变得太大，最终可能会出现数据库堆栈错误。
不得在 mybatis XML 中对集合进行迭代。
只需执行一个简单的Insert中的声明Java foreach loop.
最重要的是会话执行者类型.
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
session.insert("insertStatement", model);
}
session.flushStatements();
我认为在这里就足够使用执行器类型.REUSE没有冲洗语句。
与默认不同执行器类型.SIMPLE，该语句将准备一次并为每个要插入的记录执行。
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
