using foreach to do batch insert with mybatis

Question

I am using mybatis and i would like to insert an ArrayList to some table.
all right using foreach in mapper, well this ends up with oracle exception ORA_00933 .
this is the mybatis mapper:

<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

one more mapper configuration

<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

blackwizard · Accepted Answer · 2016-11-15 11:10:38Z

9

Insert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:

some database such as Oracle here does not support.
in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.

Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insert statement in a Java Foreach loop. The most important thing is the session Executor type.

SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
    session.insert("insertStatement", model);
}
session.flushStatements();

I event think that here it will be enough to use ExecutorType.REUSE without flushing statements.

Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.

answered Nov 15, 2016 at 11:10

blackwizard

2,0541 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shijie xu Over a year ago

What performance improvement can be made from BatchSqlSession?

Diego Martinoia · Accepted Answer · 2015-09-18 10:39:35Z

0

Try extracting the foreach (and change the separator):

<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 think that your current code creates a new role of values for each element, but a single insert statement (which is not what you want, you want an insert for each element)

answered Sep 18, 2015 at 10:39

Diego Martinoia

4,6821 gold badge20 silver badges36 bronze badges

3 Comments

wqzhou Over a year ago

Thanks. I have edit the question, you can find out the difference between the sql statements

Diego Martinoia Over a year ago

@wqzhou 1) you are still using "," as separator. I'm unsure, but usually ";" is used for sql commands 2) your values are "?" . Are you sure you are inserting the right stuff?

wqzhou Over a year ago

but it exactly caused "java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符"

free斩 · Accepted Answer · 2016-01-05 05:58:46Z

0

Oracle does not support

insert into xxx values (xxx,xxx),(xxx,xxx)

maybe you can use insert all like this

    <insert id="batchInsert">
    INSERT ALL
    <foreach collection="list" item="model">
        INTO
        SYS_ROLES_PERMISSIONGROUP (role_id, permissiongroup_id)
        VALUES
        (#{model.role_id}, #{model.permissiongroup_id})
    </foreach>
   </insert>

answered Jan 5, 2016 at 5:58

free斩

4511 gold badge6 silver badges18 bronze badges

Collectives™ on Stack Overflow

using foreach to do batch insert with mybatis

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related