解决Mybatis 大数据量的批量insert问题

前言

通过mybatis做7000+数据量的批量插入的时候报错了，error log如下：

 , 
('g61010352', 
'610103199208291214', 
'学生52', 
'g61010350',
'610103199109920192',
'学生50',
'07',
'01',
'0104',
' ',
,
' ',
' ',
current_timestamp,
current_timestamp
) 

被中止，呼叫 getnextexception 以取得原因。

at org.postgresql.jdbc2.abstractjdbc2statement$batchresulthandler.handleerror(abstractjdbc2statement.java:2743)
 at org.postgresql.core.v3.queryexecutorimpl.execute(queryexecutorimpl.java:411)
 at org.postgresql.jdbc2.abstractjdbc2statement.executebatch(abstractjdbc2statement.java:2892)
 at com.alibaba.druid.filter.filterchainimpl.statement_executebatch(filterchainimpl.java:2596)
 at com.alibaba.druid.wall.wallfilter.statement_executebatch(wallfilter.java:473)
 at com.alibaba.druid.filter.filterchainimpl.statement_executebatch(filterchainimpl.java:2594)
 at com.alibaba.druid.filter.filteradapter.statement_executebatch(filteradapter.java:2474)
 at com.alibaba.druid.filter.filtereventadapter.statement_executebatch(filtereventadapter.java:279)
 at com.alibaba.druid.filter.filterchainimpl.statement_executebatch(filterchainimpl.java:2594)
 at com.alibaba.druid.proxy.jdbc.statementproxyimpl.executebatch(statementproxyimpl.java:192)
 at com.alibaba.druid.pool.druidpooledpreparedstatement.executebatch(druidpooledpreparedstatement.java:559)
 at org.apache.ibatis.executor.batchexecutor.doflushstatements(batchexecutor.java:108)
 at org.apache.ibatis.executor.baseexecutor.flushstatements(baseexecutor.java:127)
 at org.apache.ibatis.executor.baseexecutor.flushstatements(baseexecutor.java:120)
 at org.apache.ibatis.executor.baseexecutor.commit(baseexecutor.java:235)
 at org.apache.ibatis.executor.cachingexecutor.commit(cachingexecutor.java:112)
 at org.apache.ibatis.session.defaults.defaultsqlsession.commit(defaultsqlsession.java:196)
 at org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke(sqlsessiontemplate.java:390)
 ... 39 more

可以看到这种异常无法捕捉，仅能看到异常指向了druid和ibatis的原码处，初步猜测是由于默认的sqlsession无法支持这个数量级的批量操作，下面就结合源码和官方文档具体看一看。

源码分析

项目使用的是spring+mybatis，在dao层是通过spring提供的sqlsessiontemplate来获取sqlsession的：

@resource(name = "sqlsessiontemplate")
private sqlsessiontemplate sqlsessiontemplate;
public sqlsessiontemplate getsqlsessiontemplate() 
{
 return sqlsessiontemplate;
}

为了验证，接下看一下它是如何提供sqlsesion的，打开sqlsessiontemplate的源码，看一下它的构造方法：

  /**
 * constructs a spring managed sqlsession with the {@code sqlsessionfactory}
 * provided as an argument.
 *
 * @param sqlsessionfactory
 */
 public sqlsessiontemplate(sqlsessionfactory sqlsessionfactory) {
 this(sqlsessionfactory, sqlsessionfactory.getconfiguration().getdefaultexecutortype());
 }

接下来再点开getdefaultexecutortype这个方法：

 public executortype getdefaultexecutortype() {
 return defaultexecutortype;
 }

可以看到它直接返回了类中的全局变量defaultexecutortype，我们再在类的头部寻找一下这个变量：

protected executortype defaultexecutortype = executortype.simple;

找到了，spring为我们提供的默认执行器类型为simple，它的类型一共有三种：

/**
 * @author clinton begin
 */
public enum executortype {
 simple, reuse, batch
}

仔细观察一下，发现有3个枚举类型，其中有一个batch是否和批量操作有关呢？我们看一下mybatis官方文档中对这三个值的描述：

- executortype.simple: 这个执行器类型不做特殊的事情。它为每个语句的执行创建一个新的预处理语句。

- executortype.reuse: 这个执行器类型会复用预处理语句。

- executortype.batch:这个执行器会批量执行所有更新语句,如果 select 在它们中间执行还会标定它们是 必须的,来保证一个简单并易于理解的行为。

可以看到我的使用的simple会为每个语句创建一个新的预处理语句，也就是创建一个preparedstatement对象，即便我们使用druid连接池进行处理，依然是每次都会向池中put一次并加入druid的cache中。这个效率可想而知，所以那个异常也有可能是insert timeout导致等待时间超过数据库驱动的最大等待值。

好了，已解决问题为主，根据分析我们选择通过batch的方式来创建sqlsession，官方也提供了一系列重载方法：

sqlsession opensession()
sqlsession opensession(boolean autocommit)
sqlsession opensession(connection connection)
sqlsession opensession(transactionisolationlevel level)
sqlsession opensession(executortype exectype,transactionisolationlevel level)
sqlsession opensession(executortype exectype)
sqlsession opensession(executortype exectype, boolean autocommit)
sqlsession opensession(executortype exectype, connection connection)

可以观察到主要有四种参数类型，分别是

- connection connection
- executortype exectype
- transactionisolationlevel level
- boolean autocommit

官方文档中对这些参数也有详细的解释：

sqlsessionfactory 有六个方法可以用来创建 sqlsession 实例。通常来说,如何决定是你 选择下面这些方法时:

transaction (事务): 你想为 session 使用事务或者使用自动提交(通常意味着很多 数据库和/或 jdbc 驱动没有事务)?

connection (连接): 你想 mybatis 获得来自配置的数据源的连接还是提供你自己

execution (执行): 你想 mybatis 复用预处理语句和/或批量更新语句(包括插入和 删除)?

所以根据需求选择即可，由于我们要做的事情是批量insert，所以我们选择sqlsession opensession(executortype exectype, boolean autocommit)

顺带一提关于transactionisolationlevel也就是我们经常提起的事务隔离级别，官方文档中也介绍的很到位：

mybatis 为事务隔离级别调用使用一个 java 枚举包装器, 称为 transactionisolationlevel, 否则它们按预期的方式来工作,并有 jdbc 支持的 5 级

none,
read_uncommitted
read_committed,
repeatable_read,
serializa ble)

解决问题

回归正题，初步找到了问题原因，那我们换一中sqlsession的获取方式再试试看。

testing… 2minutes later…

不幸的是，依旧报相同的错误，看来不仅仅是executortype的问题，那会不会是一次commit的数据量过大导致响应时间过长呢？上面我也提到了这种可能性，那么就再分批次处理试试，也就是说，在同一事务范围内，分批commit insert batch。具体看一下dao层的代码实现：

 @override
 public boolean insertcrossevaluation(list<crossevaluation> members)
   throws exception {
  // todo auto-generated method stub
  int result = 1;
  sqlsession batchsqlsession = null;
  try {
   batchsqlsession = this.getsqlsessiontemplate()
     .getsqlsessionfactory()
     .opensession(executortype.batch, false);// 获取批量方式的sqlsession
   int batchcount = 1000;// 每批commit的个数
   int batchlastindex = batchcount;// 每批最后一个的下标
   for (int index = 0; index < members.size();) {
    if (batchlastindex >= members.size()) {
     batchlastindex = members.size();
     result = result * batchsqlsession.insert("mutualevaluationmapper.insertcrossevaluation",members.sublist(index, batchlastindex));
     batchsqlsession.commit();
     system.out.println("index:" + index+ " batchlastindex:" + batchlastindex);
     break;// 数据插入完毕，退出循环
    } else {
     result = result * batchsqlsession.insert("mutualevaluationmapper.insertcrossevaluation",members.sublist(index, batchlastindex));
     batchsqlsession.commit();
     system.out.println("index:" + index+ " batchlastindex:" + batchlastindex);
     index = batchlastindex;// 设置下一批下标
     batchlastindex = index + (batchcount - 1);
    }
   }
   batchsqlsession.commit();
  } 
  finally {
   batchsqlsession.close();
  }
  return tools.getboolean(result);
 }

再次测试，程序没有报异常，总共7728条数据 insert的时间大约为10s左右，如下图所示，

总结

简单记录一下mybatis批量insert大数据量数据的解决方案，仅供参考，tne end。

补充：mybatis批量插入报错：','附近有错误

mybatis批量插入的时候报错，报错信息‘，'附近有错误

mapper.xml的写法为

<insert id="insertbybatch">
  insert into user_log (user_id, op_type, content, ip, op_id, op_time) values 
  <foreach collection="userids" item="userid" open="(" close=")" separator=",">
   (#{rateid}, #{optype}, #{content}, #{ipstr}, #{userid}, #{optime},
  </foreach>
 </insert>

打印的sql语句

insert into user_log (user_id, op_type, content, ip, op_id, op_time) values  ( 
 (?, ?, ?, ?, ?, ?)  ,  (?, ?, ?, ?, ?, ?)  )

调试的时候还是把sql复制到navicate中进行检查，就报了上面的错。这个错看起来毫无头绪，然后就自己重新写insert语句，发现正确的语句应该为

insert into user_log (user_id, op_type, content, ip, op_id, op_time) values   (?, ?, ?, ?, ?, ?)  ,  (?, ?, ?, ?, ?, ?)

比之前的sql少了外面的括号，此时运行成功，所以mapper.xml中应该把opern=”(” close=”)”删除即可。

多说一句，批量插入的时候也可以把要插入的数据组装成list<实体>，这样就不用传这么多的参数了。

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