MyBatis的几种批量操作
MyBatis中批量插入
方法一：
<insert id="insertbatch" parameterType="java.util.List"> <selectKey keyProperty="fetchTime" order="BEFORE" resultType="java.lang.String"> SELECT CURRENT_TIMESTAMP() </selectKey> insert into kangaiduoyaodian ( depart1, depart2, product_name, generic_name, img, product_specification, unit, approval_certificate, manufacturer, marketPrice, vipPrice, website, fetch_time, productdesc ) values <foreach collection="list" item="item" index="index" separator=","> ( #{item.depart1}, #{item.depart2}, #{item.productName}, #{item.genericName}, #{item.img}, #{item.productSpecification}, #{item.unit}, #{item.approvalCertificate}, #{item.manufacturer}, #{item.marketprice}, #{item.vipprice}, #{item.website}, #{fetchTime}, #{item.productdesc} ) </foreach> </insert>
方法二：
<insert id="batchInsertB2B" parameterType="ArrayList"> insert into xxxxtable(hkgs,hkgsjsda,office,asdf,ddd,ffff,supfullName,classtype,agent_type,remark) <foreach collection="list" item="item" index="index" separator="union all"> select #{item.hkgs,jdbcType=VARCHAR}, #{item.hkgsjsda,jdbcType=VARCHAR}, #{item.office,jdbcType=VARCHAR}, #{item.asdf,jdbcType=VARCHAR}, #{item.ddd,jdbcType=VARCHAR}, #{item.ffff,jdbcType=VARCHAR}, #{item.supfullName,jdbcType=VARCHAR},0,0, #{item.remark,jdbcType=VARCHAR} from dual </foreach> </insert>
可以考虑用union all来实现批量插入。
例如：
insert into XX_TABLE(XX,XX,XX)select 'xx','xx','xx' union all select 'xx','xx','xx' union all select 'xx','xx','xx' ...
先拼装好语句再动态传入insert into XX_TABLE(XX,XX,XX)后面部分
MyBatis中批量删除
<!-- 通过主键集合批量删除记录 --> <delete id="batchRemoveUserByPks" parameterType="java.util.List"> DELETE FROM LD_USER WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </delete>
MyBatis中in子句
1.只有一个参数
参数的类型要声明为List或Array
Sql配置如下：
<select id="selectProduct" resultMap="Map"> SELECT * FROM PRODUCT WHERE PRODUCTNO IN <foreach item="productNo" index="index" collection="参数的类型List或array"> #{productNo} </foreach> </select>
2.多个参数
首先要将多个参数写入同一个map，将map作为一个参数传入mapper
Sql配置如下：
<select id="selectProduct" resultMap="Map"> SELECT * FROM PRODUCT WHERE PRODUCTNO IN <foreach item="productNo" index="index" collection="map中集合参数的名称"> #{productNo} </foreach> </select>
MyBatis批量修改
<update id="updateOrders" parameterType="java.util.List"> update orders set state = '0' where no in <foreach collection="list" item="nos" open="(" separator="," close=")"> #{nos} </foreach> </update>
- MyBatis的前身就是著名的Ibatis，不知何故脱离了Apache改名为MyBatis。
MyBatis所说是轻量级的ORM框架，在网上看过一个测试报告，感觉相比于Hibernate来说，优势并不明显。
下面说一下比较有趣的现象，根据MyBatis的官方文档，在获得sqlSession时，它有为批量更新而专门准备的：
session = sessionFactory.openSession(ExecutorType.BATCH, true);//用于批量update
一
般来说，对MYSQL数据库批量操作时速度取决于，是为每一个处理分别建立一个连接，还是为这一批处理一共建立一个连接。按MyBatis的手册说明，选
择ExecutorType.BATCH意味着，获得的sqlSession会批量执行所有更新语句。不过我测试了一下，批量插入1000条数据，发觉
ExecutorType.BATCH方式的效率居然比普通的方式差很多。我测试用的Mapper中的insert配置如下，再用for循环插入1000条记录：
2 <!-- WARNING - @mbggenerated This element is automatically generated by
3 MyBatis Generator, do not modify. This element was generated on Mon May 09
4 11:09:37 CST 2011. -->
5 insert into student (id, name, sex,
6 address, telephone, t_id
7 )
8 values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},
9 #{sex,jdbcType=VARCHAR},
10 #{address,jdbcType=VARCHAR}, #{telephone,jdbcType=VARCHAR}, #{tId,jdbcType=INTEGER}
11 )
12 </insert>
- 我不清楚原因在哪里， 就配置了MyBatis的log4j，想查看下日志。下载了log4j.jar和commons-logging.jar并配置到项目的类路径，然后在代码路径下新建文件log4j.properties，内容如下：
然后再次测试普通的sqlSession，发现日志内容中虽然插入了1000条数据，但只新建了一次连接，最后又关闭了该连接(日志如下)。也就是说 MyBatis中的普通sqlSession好像已经对批量插入默认是一次连接中完成，那么还提供ExecutorType.BATCH方式干什么，况且 该方式好像效率也不行，或者是我使用ExecutorType.BATCH方式不对？？log4j.rootLogger=DEBUG, stdout
# SqlMap logging configuration...
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.cache.CacheModel=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientImpl=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.builder.xml.SqlMapParser=DEBUG
log4j.logger.com.ibatis.common.util.StopWatch=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
DEBUG [main] - Created connection 3502256.
DEBUG [main] - ooo Connection Opened
DEBUG [main] - ==> Executing: insert into student ( name, sex, address, telephone, t_id ) values ( ?, ?, ?, ?, ? )
DEBUG [main] - ==> Parameters: 新人0(String), male(String), addr0(String), dd(String),3(Integer)
DEBUG [main] - ==> Executing: insert into student ( name, sex, address, telephone, t_id ) values ( ?, ?, ?, ?, ? )
DEBUG [main] - ==> Parameters: 新人1(String), male(String),
...............
...............
DEBUG [main] - xxx Connection Closed
DEBUG [main] - Returned connection 3502256 to pool. - 最后一点是关于数据库批量插入时sql语句级的优化，我特意测试了两种方式，在StudentMapper中配置了两种
insert模式。第一种对应insert value1,insert value2,,,,；第二种对应insert values
(value1, value2,....)。发现后者果然比前者快很多啊。下面是两种insert模式，及测试结果对应图：
<!-- 在外部for循环调用一千次 -->
<insert id="insert" parameterType="sdc.mybatis.test.Student">
insert into student (id, name, sex,
address, telephone, t_id
)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},
#{sex,jdbcType=VARCHAR},
#{address,jdbcType=VARCHAR}, #{telephone,jdbcType=VARCHAR}, #{tId,jdbcType=INTEGER}
)
</insert>
<!-- 批量 ，传入一个长度为1000的list -->
<insert id="insertBatch">
insert into student ( <include refid="Base_Column_List"/> )
values
<foreach collection="list" item="item" index="index" separator=",">
(null,#{item.name},#{item.sex},#{item.address},#{item.telephone},#{item.tId})
</foreach>
</insert>
过Mybatis做7000+数据量的批量插入的时候报错了，error log如下：
可以看到这种异常无法捕捉，仅能看到异常指向了druid和ibatis的原码处，初步猜测是由于默认的SqlSession无法支持这个数量级的批量操作，下面就结合源码和官方文档具体看一看。
源码分析
项目使用的是spring+Mybatis，在Dao层是通过Spring提供的SqlSessionTemplate来获取SqlSession的：
@Resource(name = "sqlSessionTemplate") private SqlSessionTemplate sqlSessionTemplate; public SqlSessionTemplate getSqlSessionTemplate() { return sqlSessionTemplate; }
为了验证，接下看一下它是如何提供SqlSesion的，打开SqlSessionTemplate的源码，看一下它的构造方法：
SqlSessionTemplate.java
/** * 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这个方法：
Configuration.java
public ExecutorType getDefaultExecutorType() { return defaultExecutorType; }
可以看到它直接返回了类中的全局变量defaultExecutorType，我们再在类的头部寻找一下这个变量：
Configuration.java
protected ExecutorType defaultExecutorType = ExecutorType.SIMPLE;
找到了，Spring为我们提供的默认执行器类型为Simple，它的类型一共有三种：
package org.apache.ibatis.session; /** * @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，官方也提供了一系列重载方法：
public interface SqlSessionFactory { SqlSession openSession(); SqlSession openSession(boolean autoCommit); SqlSession openSession(Connection connection); SqlSession openSession(TransactionIsolationLevel level); SqlSession openSession(ExecutorType execType); SqlSession openSession(ExecutorType execType, boolean autoCommit); SqlSession openSession(ExecutorType execType, TransactionIsolationLevel level); SqlSession openSession(ExecutorType execType, Connection connection); Configuration getConfiguration(); }
可以观察到主要有四种参数类型，分别是
- 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,
package org.apache.ibatis.session; import java.sql.Connection; /** * @author Clinton Begin */ public enum TransactionIsolationLevel { NONE(Connection.TRANSACTION_NONE), READ_COMMITTED(Connection.TRANSACTION_READ_COMMITTED), READ_UNCOMMITTED(Connection.TRANSACTION_READ_UNCOMMITTED), REPEATABLE_READ(Connection.TRANSACTION_REPEATABLE_READ), SERIALIZABLE(Connection.TRANSACTION_SERIALIZABLE);否则它们按预期的方式来工作,并有 JDBC 支持的 5 级 (
NONE,
READ_UNCOMMITTED
READ_COMMITTED,
REPEATABLE_READ,
SERIALIZA BLE)
解决问题
回归正题，初步找到了问题原因，那我们换一中SqlSession的获取方式再试试看。
不幸的是，依旧报相同的错误，看来不仅仅是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左右，如下图所示，
转自：http://blog.csdn.net/wlwlwlwl015/article/details/50246717
总结：
mysql数据库在操作时，sql的长度是有限制的。所以在批处理时分批要合理避免出现sql过长的限制。可以用多线程执行批处理，提高速度。
