logo头像
Snippet 博客主题

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

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

微信打赏

赞赏是不耍流氓的鼓励