mybatisCursorItemReaderBuilder使用文档 mybatis simple reuse batch

转载

killads 2024-04-29 15:01:37

文章标签 mybatis 批量修改 mybatis 批量插入 mybatis批量修改 mybatis批量插入 sql 文章分类 机器学习人工智能

Mybatis 的三种执行器

在企业开发中, 对数据库的批量操作, 是一个非常常见的操作, Mybatis提供了批量执行器, 来支持批量操作.

Mybatis sql执行器

Mybatis 支持全局修改执行器, 参数名为: defaultExecutorType. 但是笔者并不推荐这种方式,笔者建议在获取sqlSession对象时设置. Mybatis 共有三种执行器:

SIMPLE: 默认的执行器, 对每条sql进行预编译->设置参数->执行等操作
BATCH: 批量执行器, 对相同sql进行一次预编译, 然后设置参数, 最后统一执行操作
REUSE: REUSE执行器会重用预处理语句prepared statements

如何设置执行器

1.局部设置

在获取sqlSession时设置, 需要注意的时, 如果选择的是批量执行器时, 需要手工提交事务。

// 获取指定执行器的sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)

// 获取批量执行器时, 需要手动提交事务
sqlSession.commit();

// 获取指定执行器的sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)

// 获取批量执行器时, 需要手动提交事务
sqlSession.commit();

2.全局设置

可在全局配置文件中配置。

<settings>
<setting name="defaultExecutorType" value="BATCH" />
settings>

<settings>
<setting name="defaultExecutorType" value="BATCH" />
settings>

三种执行器效率测试

xml代码：

<mapper namespace="com.ywh.demo.mapper.StudentMapper">


<insert id="save" useGeneratedKeys="true" keyProperty="id">
        insert into t_student  values (null , #{name}, #{age}, #{sex}, #{birth})
insert>

mapper>

<mapper namespace="com.ywh.demo.mapper.StudentMapper">


<insert id="save" useGeneratedKeys="true" keyProperty="id">
        insert into t_student  values (null , #{name}, #{age}, #{sex}, #{birth})
insert>

mapper>

测试代码：

对于一条sql语句的执行不同的执行器没有太大的差异，所以这边采用插入10000条数据的方式。

public class TestStudentMapper {

// 批量保存方法
private void batchSave(StudentMapper mapper) {
// 初始化10000个对象
        List list = new ArrayList<>();for (int i = 0; i < 10000; i++) {
            list.add(new StudentPO("zhangsan_" + i, "M",20 + i % 10,  LocalDate.now()));
        }// 批量执行long start = System.currentTimeMillis();for (StudentPO studentPO : list) {
            mapper.save(studentPO);
        }long end = System.currentTimeMillis();// 输出执行耗时
        System.out.println("耗时:" + (end - start) + " ms!");
    }// 默认执行器@Testpublic void test_SIMPLE(){// 获取自动提交事务的Maper
        StudentMapper mapper = SqlSessionUtil.getMapperAutoTx(StudentMapper.class);// 执行批量保存
        batchSave(mapper);
    }// 重用预编译执行器@Testpublic void test_REUSE(){// 获取批量保存sqlSession
        SqlSession sqlSession = SqlSessionUtil.openSession(ExecutorType.REUSE, true);// 获取Mapper 对象
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);// 执行批量保存
        batchSave(mapper);
    }// 批量执行器@Testpublic void test_BATCH(){// 获取批量保存sqlSession
        SqlSession sqlSession = SqlSessionUtil.openSession(ExecutorType.BATCH, true);// 获取Mapper 对象
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);// 执行批量保存
        batchSave(mapper);
        sqlSession.commit();
    }
}    
public class TestStudentMapper {

// 批量保存方法
private void batchSave(StudentMapper mapper) {
// 初始化10000个对象
        List list = new ArrayList<>();for (int i = 0; i < 10000; i++) {
            list.add(new StudentPO("zhangsan_" + i, "M",20 + i % 10,  LocalDate.now()));
        }// 批量执行long start = System.currentTimeMillis();for (StudentPO studentPO : list) {
            mapper.save(studentPO);
        }long end = System.currentTimeMillis();// 输出执行耗时
        System.out.println("耗时:" + (end - start) + " ms!");
    }// 默认执行器@Testpublic void test_SIMPLE(){// 获取自动提交事务的Maper
        StudentMapper mapper = SqlSessionUtil.getMapperAutoTx(StudentMapper.class);// 执行批量保存
        batchSave(mapper);
    }// 重用预编译执行器@Testpublic void test_REUSE(){// 获取批量保存sqlSession
        SqlSession sqlSession = SqlSessionUtil.openSession(ExecutorType.REUSE, true);// 获取Mapper 对象
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);// 执行批量保存
        batchSave(mapper);
    }// 批量执行器@Testpublic void test_BATCH(){// 获取批量保存sqlSession
        SqlSession sqlSession = SqlSessionUtil.openSession(ExecutorType.BATCH, true);// 获取Mapper 对象
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);// 执行批量保存
        batchSave(mapper);
        sqlSession.commit();
    }
}

测试结果：

simple方式：从执行日志可以看出, 每次插入操作, 都会执行编译, 设置参数, 执行sql操作.

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
...
耗时:21575 ms!

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
...
耗时:21575 ms!

reuse方式：从执行日志可以看出, 只有第一次插入操作, 执行了sql编译步骤, 对其它插入操作执行了设置参数, 执行sql的操作.

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
...
耗时:19322 ms!

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- <==    Updates: 1
...
耗时:19322 ms!

batch方式：从执行日志可以看出, 只对第一次插入操作执行了sql编译操作, 对其它插入操作仅执行了设置参数操作, 最后统一执行.

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2020-07-24(Date)
...
耗时:835 ms!

[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==>  Preparing: insert into t_student values (null , ?, ?, ?, ?) 
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_0(String), 20(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_1(String), 21(Integer), M(String), 2020-07-24(Date)
[main][DEBUG][o.z.l.m.l.mapper.StudentMapper.save]- ==> Parameters: zhangsan_2(String), 22(Integer), M(String), 2020-07-24(Date)
...
耗时:835 ms!

「结论：」 在做批量操作时, 使用批量执行器, 性能会有很大的提升.

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。