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子句

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的关于批量数据操作的体会

 

  1.  MyBatis的前身就是著名的Ibatis,不知何故脱离了Apache改名为MyBatis。
     MyBatis所说是轻量级的ORM框架,在网上看过一个测试报告,感觉相比于Hibernate来说,优势并不明显。

下面说一下比较有趣的现象,根据MyBatis的官方文档,在获得sqlSession时,它有为批量更新而专门准备的:

session = sessionFactory.openSession();//用于普通update
session = sessionFactory.openSession(ExecutorType.BATCH, true);//用于批量update

 一 般来说,对MYSQL数据库批量操作时速度取决于,是为每一个处理分别建立一个连接,还是为这一批处理一共建立一个连接。按MyBatis的手册说明,选 择ExecutorType.BATCH意味着,获得的sqlSession会批量执行所有更新语句。不过我测试了一下,批量插入1000条数据,发觉 ExecutorType.BATCH方式的效率居然比普通的方式差很多。我测试用的Mapper中的insert配置如下,再用for循环插入1000条记录:

复制代码
1 <insert id="insert" parameterType="sdc.mybatis.test.Student">
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>
复制代码
  1.  我不清楚原因在哪里, 就配置了MyBatis的log4j,想查看下日志。下载了log4j.jar和commons-logging.jar并配置到项目的类路径,然后在代码路径下新建文件log4j.properties,内容如下:
    复制代码
    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
    复制代码
     然后再次测试普通的sqlSession,发现日志内容中虽然插入了1000条数据,但只新建了一次连接,最后又关闭了该连接(日志如下)。也就是说 MyBatis中的普通sqlSession好像已经对批量插入默认是一次连接中完成,那么还提供ExecutorType.BATCH方式干什么,况且 该方式好像效率也不行,或者是我使用ExecutorType.BATCH方式不对??
    复制代码
    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.
    复制代码
     
  2. 最后一点是关于数据库批量插入时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 大数据量的批量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过长的限制。可以用多线程执行批处理,提高速度。

posted on 2015-08-07 15:37  duanxz  阅读(13856)  评论(1编辑  收藏  举报