mybatis批量数据处理

数据库ORACLE11g

foreach select-union

使用mybatis的foreach拼接sql，具体代码如下

@Mapper
public interface TestMapper{
    int persistOrderSelectUnion(List<OrderDto> list);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wekri.TestMapper">
    <insert id="persistOrderSelectUnion" parameterType="list">
            insert into tb_order(id,name,phone,age,gender)
            SELECT SEQ_ORDER.nextval,t.* FROM (
            <foreach collection="list" item="orderDto" index="index" separator="union all">
                SELECT
                #{orderDto.name},#{orderDto.phone},#{orderDto.age},#{orderDto.gender}
                FROM dual
            </foreach>
            ) t
        </insert>
</mapper>

最后会拼接成sql：

insert into tb_order(id,name,phone,age,gender)
    SELECT SEQ_ORDER.nextval,t.* FROM (
        SELECT ?,?,?,? FROM dual UNION ALL 
        SELECT ?,?,?,? FROM dual UNION ALL 
        SELECT ?,?,?,? FROM dual UNION ALL 
        SELECT ?,?,?,? FROM dual
    ) t

这样会带来一个问题，如果list太大，会导致sql过长，绑定的变量过多。
之前有同事一次绑定几十万个变量，导致数据库（ORACLE 11.2.0.3）宕了:

Description
When an application has more than 65535 bind variables in a single statement
then a dump can occur in/under opiaba, which can potentially cause an SGA
memory corruption that will crash the instance.

在mysql中也会有类似的问题。

推荐使用batch。

batch

先介绍一下枚举类```org.apache.ibatis.session.ExecutorType``,有三个枚举：
“SIMPLE”, 在执行bookDao.save(book)时，就相当于JDBC的stmt.execute(sql)；
“REUSE”, 在执行bookDao.save(book)时，相当于JDBC重用一条sql，再通过stmt传入多项参数值，然后执行stmt.executeUpdate()或stmt.executeBatch();
“BATCH”, 在执行bookDao.save(book)时，相当于JDBC语句的 stmt.addBatch(sql)，即仅仅是将执行SQL加入到批量计划。

默认的ExecutorType为SIMPLE，在org.apache.ibatis.session.Configuration 中：

protected ExecutorType defaultExecutorType = ExecutorType.SIMPLE;

使用BATCH时不会进行sql拼接，大数据量操作时效率更高。

下面介绍一下项目中局部使用BATCH的方法：

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);

其中后四种可以设置ExecutorType。

enum Action {
    INSERT("插入"),
    UPDATE("更新"),
    DELETE("删除");
    private String name;

    Action(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }
}
    
public <T> void batchExecute(String mybatisSQLId, List<T> list, Action action) {
    SqlSessionFactory sessionFactory = SpringContextUtil.getBean(SqlSessionFactory.class);
    SqlSession session = null;
    try {
        session = sessionFactory.openSession(ExecutorType.BATCH, false);

        for (int i = 0; i < list.size(); i++) {
            switch (action) {
                case INSERT:
                    session.insert(mybatisSQLId, list.get(i));
                    break;
                case UPDATE:
                    session.update(mybatisSQLId, list.get(i));
                    break;
                case DELETE:
                    session.delete(mybatisSQLId, list.get(i));
                    break;
                default:
                    break;
            }
        }

        session.commit();
        session.clearCache();
    } catch (Exception e) {
        session.rollback();
        logger.error("batchCommit error!", e);
        throw e;
    } finally {
        if (null != session) {
            session.close();
        }
    }
}

大家可根据情况自行再封装。