目录
环境
jdk8
mybatis 3.5.2
mysql-connector-java 8.0.22
使用方式
mapper:
<?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.demo.mapper.MenuMapper">
<select id="cursor" resultType="com.demo.entity.Menu" parameterType="com.demo.entity.Menu" fetchSize="-2147483648">
select menu_id as menuId,menu_name menuName from t_menu where menu_id < #{menuId}
</select>
</mapper>
package com.demo.mapper;
import com.demo.entity.Menu;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.session.RowBounds;
public interface MenuMapper {
Cursor<Menu> cursor();
}
servcie
package com.demo.service;
import com.demo.entity.Menu;
import com.demo.mapper.MenuMapper;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Iterator;
@Service
public class MenuService {
@Autowired
private MenuMapper menuMapper;
@Autowired
private SqlSessionFactory sessionFactory;
public void menu(){
SqlSession sqlSession = sessionFactory.openSession(ExecutorType.REUSE);
MenuMapper mapper = sqlSession.getMapper(MenuMapper.class);
RowBounds rowBounds = new RowBounds(0,10);
Cursor<Menu> cursor = mapper.cursor();
Iterator<Menu> iterator = cursor.iterator();
while (iterator.hasNext()) {
Menu next = iterator.next();
System.out.println(next.getMenuId() + next.getMenuName());
}
sqlSession.close();
}
}
使用说明
(1)在MySQL数据库下,声明fetchSize="-2147483648"(即Integer.MIN_VALUE),这是MySQL特有判断方式,参考:com.mysql.cj.jdbc.StatementImpl#createStreamingResultSet;
protected boolean createStreamingResultSet() {
return ((this.query.getResultType() == Type.FORWARD_ONLY) && (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY)
&& (this.query.getResultFetchSize() == Integer.MIN_VALUE));
}
(2)service创建ExecutorType.REUSE类型的SqlSession。
当使用ExecutorType.BATCH类型的SqlSession,在查询完数据后,关闭了结果集,无法再从服务端取数据。参考:org.apache.ibatis.executor.BatchExecutor#doQueryCursor
同理ExecutorType.SIMPLE类型的SqlSession也关闭了,而ExecutorType.REUSE没有关闭,可以持续从服务端获取数据,进行处理。xml没有配置fetchSize="-2147483648",这是伪Cursor,数据是一次性加载到内存,此种方式,可以不需关注ExecutorType,但有内存过大风险。原因参考:jdbc开启mysql流式处理