其他
流式查询:MyBatis千万级数据查询解决方案,避免OOM
来源:segmentfault.com/a/1190000022478915
# 基本概念
# MyBatis 流式查询接口
Cursor 是可关闭的;
Cursor 是可遍历的。
isOpen():用于在取数据之前判断 Cursor 对象是否是打开状态。只有当打开时 Cursor 才能取数据;
isConsumed():用于判断查询结果是否全部取完。
getCurrentIndex():返回已经获取了多少条数据
cursor.forEach(rowObject -> {...});
# 但构建 Cursor 的过程不简单
@Mapper
public interface FooMapper {
@Select("select * from foo limit #{limit}")
Cursor<Foo> scan(@Param("limit") int limit);
}
@GetMapping("foo/scan/0/{limit}")
public void scanFoo0(@PathVariable("limit") int limit) throws Exception {
try (Cursor<Foo> cursor = fooMapper.scan(limit)) { // 1
cursor.forEach(foo -> {}); // 2
}
}
java.lang.IllegalStateException: A Cursor is already closed.
# 方案一:SqlSessionFactory
@GetMapping("foo/scan/1/{limit}")
public void scanFoo1(@PathVariable("limit") int limit) throws Exception {
try (
SqlSession sqlSession = sqlSessionFactory.openSession(); // 1
Cursor<Foo> cursor =
sqlSession.getMapper(FooMapper.class).scan(limit) // 2
) {
cursor.forEach(foo -> { });
}
}
# 方案二:TransactionTemplate
@GetMapping("foo/scan/2/{limit}")
public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
TransactionTemplate transactionTemplate =
new TransactionTemplate(transactionManager); // 1
transactionTemplate.execute(status -> { // 2
try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
cursor.forEach(foo -> { });
} catch (IOException e) {
e.printStackTrace();
}
return null;
});
}
# 方案三:@Transactional 注解
@GetMapping("foo/scan/3/{limit}")
@Transactional
public void scanFoo3(@PathVariable("limit") int limit) throws Exception {
try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
cursor.forEach(foo -> { });
}
}