解决mybatis使用foreach批量insert异常的问题

异常

org.springframework.jdbc.badsqlgrammarexception: 
### error updating database. cause: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'insert into t_user_role(userid,roleid)values(1,3)
		 ; 
			insert into t_user_ro' at line 3
### the error may involve defaultparametermap
### the error occurred while setting parameters
### sql: insert into t_user_role(userid,roleid)values(?,?) ;  insert into t_user_role(userid,roleid)values(?,?) ;  insert into t_user_role(userid,roleid)values(?,?)
### cause: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'insert into t_user_role(userid,roleid)values(1,3)
		 ; 
			insert into t_user_ro' at line 3
; bad sql grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'insert into t_user_role(userid,roleid)values(1,3)
		 ; 
			insert into t_user_ro' at line 3
	at org.springframework.jdbc.support.sqlerrorcodesqlexceptiontranslator.dotranslate(sqlerrorcodesqlexceptiontranslator.java:231)
	at org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:73)
	at org.mybatis.spring.mybatisexceptiontranslator.translateexceptionifpossible(mybatisexceptiontranslator.java:73)
	at org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke(sqlsessiontemplate.java:371)
	at com.sun.proxy.$proxy13.insert(unknown source)
	at org.mybatis.spring.sqlsessiontemplate.insert(sqlsessiontemplate.java:240)
	at org.apache.ibatis.binding.mappermethod.execute(mappermethod.java:51)
	at org.apache.ibatis.binding.mapperproxy.invoke(mapperproxy.java:52)
	at com.sun.proxy.$proxy14.addroles(unknown source)
	at com.atguigu.atcrowdfunding.manager.service.impl.userserviceimpl.addroles(userserviceimpl.java:139)
	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.springframework.aop.support.aoputils.invokejoinpointusingreflection(aoputils.java:317)
	at org.springframework.aop.framework.reflectivemethodinvocation.invokejoinpoint(reflectivemethodinvocation.java:190)
	at org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:157)
	at org.springframework.transaction.interceptor.transactioninterceptor$1.proceedwithinvocation(transactioninterceptor.java:98)
	at org.springframework.transaction.interceptor.transactionaspectsupport.invokewithintransaction(transactionaspectsupport.java:262)
	at org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:95)
	at org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)
	at org.springframework.aop.interceptor.exposeinvocationinterceptor.invoke(exposeinvocationinterceptor.java:92)
	at org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)
	at org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy.java:207)
	at com.sun.proxy.$proxy17.addroles(unknown source)
	at com.atguigu.atcrowdfunding.manager.controller.usercontroller.doaddroles(usercontroller.java:271)
	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.springframework.web.bind.annotation.support.handlermethodinvoker.invokehandlermethod(handlermethodinvoker.java:175)
	at org.springframework.web.servlet.mvc.annotation.annotationmethodhandleradapter.invokehandlermethod(annotationmethodhandleradapter.java:446)
	at org.springframework.web.servlet.mvc.annotation.annotationmethodhandleradapter.handle(annotationmethodhandleradapter.java:434)
	at org.springframework.web.servlet.dispatcherservlet.dodispatch(dispatcherservlet.java:945)
	at org.springframework.web.servlet.dispatcherservlet.doservice(dispatcherservlet.java:876)
	at org.springframework.web.servlet.frameworkservlet.processrequest(frameworkservlet.java:931)
	at org.springframework.web.servlet.frameworkservlet.dopost(frameworkservlet.java:833)
	at javax.servlet.http.httpservlet.service(httpservlet.java:648)
	at org.springframework.web.servlet.frameworkservlet.service(frameworkservlet.java:807)
	at javax.servlet.http.httpservlet.service(httpservlet.java:729)
	at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:291)
	at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)
	at org.springframework.web.filter.hiddenhttpmethodfilter.dofilterinternal(hiddenhttpmethodfilter.java:77)
	at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:108)
	at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:239)
	at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)
	at org.apache.tomcat.websocket.server.wsfilter.dofilter(wsfilter.java:52)
	at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:239)
	at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)
	at org.springframework.web.filter.characterencodingfilter.dofilterinternal(characterencodingfilter.java:88)
	at org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:108)
	at org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:239)
	at org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)
	at org.apache.catalina.core.standardwrappervalve.invoke(standardwrappervalve.java:219)
	at org.apache.catalina.core.standardcontextvalve.invoke(standardcontextvalve.java:106)
	at org.apache.catalina.authenticator.authenticatorbase.invoke(authenticatorbase.java:502)
	at org.apache.catalina.core.standardhostvalve.invoke(standardhostvalve.java:142)
	at org.apache.catalina.valves.errorreportvalve.invoke(errorreportvalve.java:79)
	at org.apache.catalina.valves.abstractaccesslogvalve.invoke(abstractaccesslogvalve.java:616)
	at org.apache.catalina.core.standardenginevalve.invoke(standardenginevalve.java:88)
	at org.apache.catalina.connector.coyoteadapter.service(coyoteadapter.java:518)
	at org.apache.coyote.http11.abstracthttp11processor.process(abstracthttp11processor.java:1091)
	at org.apache.coyote.abstractprotocol$abstractconnectionhandler.process(abstractprotocol.java:673)
	at org.apache.tomcat.util.net.nioendpoint$socketprocessor.dorun(nioendpoint.java:1526)
	at org.apache.tomcat.util.net.nioendpoint$socketprocessor.run(nioendpoint.java:1482)
	at java.util.concurrent.threadpoolexecutor.runworker(unknown source)
	at java.util.concurrent.threadpoolexecutor$worker.run(unknown source)
	at org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run(taskthread.java:61)
	at java.lang.thread.run(unknown source)
caused by: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'insert into t_user_role(userid,roleid)values(1,3)
		 ; 
			insert into t_user_ro' at line 3
	at sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method)
	at sun.reflect.nativeconstructoraccessorimpl.newinstance(unknown source)
	at sun.reflect.delegatingconstructoraccessorimpl.newinstance(unknown source)
	at java.lang.reflect.constructor.newinstance(unknown source)
	at com.mysql.jdbc.util.handlenewinstance(util.java:404)
	at com.mysql.jdbc.util.getinstance(util.java:387)
	at com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:941)
	at com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3870)
	at com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3806)
	at com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:2470)
	at com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2617)
	at com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2550)
	at com.mysql.jdbc.preparedstatement.executeinternal(preparedstatement.java:1861)
	at com.mysql.jdbc.preparedstatement.execute(preparedstatement.java:1192)
	at com.mchange.v2.c3p0.impl.newproxypreparedstatement.execute(newproxypreparedstatement.java:823)
	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.$proxy24.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.simpleexecutor.doupdate(simpleexecutor.java:48)
	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:358)
	... 65 more

异常分析

mapper.xml导致的错误地方

 <insert id="addroles">
 <foreach collection="data.ids" item="id" separator=";">
 insert into t_user_role(userid,roleid)values(#{userid},#{id})
 </foreach>
 </insert>

异常中说

caused by: com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'insert into t_user_role(userid,roleid)values(1,3)

首先觉得是 sql语句的问题，但是用sqlyog测试发现没有问题

后来发现是数据库对多个语句拼在一起的操作不支持问题

解决办法

在jdbcurl中加入allowmultiqueries=true即可解决

jdbc.url=jdbc:mysql://localhost:3306/atcrowdfunding?allowmultiqueries=true&rewritebatchedstatements=true&useunicode=true&characterencoding=utf8

补充：mybatis foreach嵌套 批量insert map list数据

方式一：

以teacher和students为例 ，两者为一对多关系：**

teacher 实体类

private string tid;
private string tname;
private list<student> studentlist; 
public string gettid() {
 return tid;
}
 
public void settid(string tid) {
 this.tid = tid;
}
 
public string gettname() {
 return tname;
}
 
public void settname(string tname) {
 this.tname = tname;
}
 
public list<student> getstudentlist() {
 return studentlist;
}
 
public void setstudentlist(list<student> studentlist) {
 this.studentlist = studentlist;
}

student实体类

private string sid;
private string sname;
public string getsid() {
 return sid;
}
 
public void setsid(string sid) {
 this.sid = sid;
}
 
public string getsname() {
 return sname;
}
 
public void setsname(string sname) {
 this.sname = sname;
}

应用场景 ：在老师的service层批量插入老师数据

int nums=1000;
  
 //定义teacher list
 list<teacher> tealist=new arraylist<teacher>();
 //数据是通过excel读取的 此处模拟循环读取excel row数据
 for(int rowi=0;rowi<nums;rowi++){
  teacher teacher=new teacher();
  teacher.settname("xxx");
 
  list<student> stulist=new arraylist<student>();
  student student=new student();
  //此处获取students集合
  for(xxx){
   stulist.add(student);
  }
  //把student集合和teacher绑定
  teacher.setstudentlist(stulist);
 
  //把teacher放入list
  tealist.add(teacher);
 }
 // 批量插入老师数据
 teachermapper.batchinsertteacher(tealist);
 //此时每个teacher对象都有返回的主键id值
 //老师主键 对应一个list(学生信息) map
 map<string,list<student>> stumap=new hashmap<>();
 //填入数据 老师主键一对多学生信息
 for(teacher tea:tealist){
  //非空判断 避免老师没有对应学生的情况（此处只进行逻辑处理 不考虑现实中老师没有学生）
  if(tea.getstudentlist()!=null&&tea.getstudentlist().size()>0){
   stumap.put(tea.gettid(),tea.getstudentlist());
  }
 }
 //调用学生service的批量保存学生方法
 studentservice.batchinsertstudent(stumap);

student mapper接口文件

int batchinsertstudent(@param("stumap") map<string,list<student>> stumap);

student mapper.xml文件（写法一：）

<insert id="batchinsertstudent" parametertype="java.util.map">
 insert into bs_student (sid,sname,tid) 
 values
 <foreach collection="stumap.keys" index="key" item="itemkey" separator=",">
   <foreach collection="stumap[itemkey]" index="index_list" item="list" separator="," >
    ( (select replace(uuid(),'-','') as sid) ,#{list.sname},#{itemkey} )
   </foreach>
 </foreach>
</insert>

student mapper.xml文件（写法二：）

<insert id="batchinsertstudent" >
　　insert into bs_student (sid,sname,tid)
　　<foreach collection="stumap.keys" index="index" item="itemkey" separator="union all">
　　<foreach collection="stumap[itemkey]" index="index_list" item="list" separator="union all">
　　　　　　（
　　　　　　select 
　　　　　　(select replace(uuid(),'-','') as sid)
　　　　　　,#{list.sname}
　　　　　　,#{itemkey}
　　　　　　from dual
　　　　　　）
　　</foreach>
　　</foreach>
</insert>

stumap：就是在接口中使用@param(“stumap”)标注了变量；

使用stumap.keys可以取到所有的key，遍历。

内层循环中使用stumap[itemkey]类似stumap.get(“key”)取当前key对应的value值。由于value是list所以还需要遍历。

结果保存成功。再次膜拜强大的mybatis。

至此 成功插入student数据。

方式二（推荐）：

自定义类

class enclosingtype{
 private string uuid;
 private list<elementtype> elements;
}

class elementtype{
 string a;
 string b;
 (...)
}

mapper.xml（方法一）

<mapper namespace="my.example.elementtype">
 <insert id="insertelements" parametertype="enclosingtype">
  insert into table1(enclosingtypeid,column_a,column_b)
  values
  <foreach collection="elements" index="index" item="list" separator=",">  
   ( 
   #{uuid,jdbctype=varchar}
   ,#{list.a,jdbctype=varchar}
   ,#{list.b,jdbctype=varchar}
   )
  </foreach>
 </insert>
</mapper>

mapper.xml（方法二）

<mapper namespace="my.example.elementtype">
 <insert id="insertelements" parametertype="enclosingtype">
  insert into table1(enclosingtypeid,column_a,column_b)
  <foreach collection="elements" index="index" item="list" separator="union all">
   (
   select #{uuid,jdbctype=varchar}
   ,#{list.a,jdbctype=varchar}
   ,#{list.b,jdbctype=varchar}
   from dual
   )
  </foreach>
 </insert>
</mapper>

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。