MyBatis Streaming Query (Cursor) Tutorial and Best Practices

Introduction

MyBatis streaming query is a relatively niche feature that is useful in special scenarios where large result sets need to be processed without loading everything into memory. Many developers only think of MyBatis as a one‑to‑one DAO‑mapper relationship, focusing on writing and optimizing SQL. This article reveals the hidden power of MyBatis streaming queries.

Environment Configuration

JDK version: 1.8

IDE: IntelliJ IDEA 2020.1

Spring Boot: 2.3.9.RELEASE

mybatis-spring-boot-starter: 2.1.4

What Is MyBatis Streaming Query?

When MyBatis executes a query, instead of returning a collection or a single object, it can return an iterator (Cursor) that allows the application to fetch rows one by one, preventing massive memory consumption.

Cursor Interface

The org.apache.ibatis.cursor.Cursor interface defines three abstract methods:

isOpen() : checks whether the cursor is currently open.

isConsumed() : checks whether all rows have been read.

getCurrentIndex() : returns the index of the last row read within the total result set.

public interface Cursor
extends Closeable, Iterable
{
    // 判断cursor是否正处于打开状态
    // 当返回true，则表示cursor已经开始从数据库里刷新数据了；
    boolean isOpen();
    // 判断查询结果是否全部读取完；
    // 当返回true,则表示查询sql匹配的全部数据都消费完了；
    boolean isConsumed();
    // 查询已读取数据在全部数据里的索引位置；
    // 第一条数据的索引位置为0；当返回索引位置为-1时，则表示已经没有数据可以读取；
    int getCurrentIndex();
}

Code Implementation

Streaming query keeps the SQL session open while rows are being fetched, so the developer must manually manage the SqlSession and transaction commit/rollback.

1. The DAO method returns a Cursor<Person> instead of a list.

@Mapper
public interface PersonDao {
    Cursor
selectByCursor();
    Integer queryCount();
}

<select id="selectByCursor" resultMap="personMap">
    select * from sys_person order by id desc
</select>
<select id="queryCount" resultType="java.lang.Integer">
    select count(*) from sys_person
</select>

2. Service layer opens a SqlSession, obtains the mapper, iterates the cursor, processes data in batches (e.g., 1000 rows), and commits the transaction after all rows are processed.

@Service
@Slf4j
public class PersonServiceImpl implements IPersonService {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Override
    public void getOneByAsync() throws InterruptedException {
        new Thread(() -> {
            log.info("----开启sqlSession");
            SqlSession sqlSession = sqlSessionFactory.openSession();
            try {
                PersonDao mapper = sqlSession.getMapper(PersonDao.class);
                Cursor
cursor = mapper.selectByCursor();
                Integer total = mapper.queryCount();
                List
personList = new ArrayList<>();
                int batch = 0;
                if (cursor != null) {
                    for (Person person : cursor) {
                        if (personList.size() < 1000) {
                            personList.add(person);
                        } else if (personList.size() == 1000) {
                            batch++;
                            log.info("----{}、从cursor取数据达到1000条，开始处理数据", batch);
                            Thread.sleep(1000); // simulate processing time
                            log.info("----{}、从cursor中取出的1000条数据已经处理完毕", batch);
                            personList.clear();
                            personList.add(person);
                        }
                        if (total == (cursor.getCurrentIndex() + 1)) {
                            batch++;
                            log.info("----{}、从cursor取数据达到1000条，开始处理数据", batch);
                            Thread.sleep(1000);
                            log.info("----{}、从cursor中取出的1000条数据已经处理完毕", batch);
                            personList.clear();
                        }
                    }
                    if (cursor.isConsumed()) {
                        log.info("----查询sql匹配中的数据已经消费完毕！");
                    }
                }
                sqlSession.commit();
                log.info("----提交事务");
            } catch (Exception e) {
                e.printStackTrace();
                sqlSession.rollback();
            } finally {
                if (sqlSession != null) {
                    sqlSession.close();
                    log.info("----关闭sqlSession");
                }
            }
        }).start();
    }
}

Application Scenarios

Streaming query is ideal when processing massive data sets, such as generating a payroll report for 500,000 employees. Loading all rows into JVM memory would cause high memory usage, long GC pauses, and potential OOM errors. By using Cursor, data can be processed in manageable batches.

Typical approach: partition data by month/region/department, submit each partition as a task to a thread pool, use a CountDownLatch to wait for all tasks, and within each task use MyBatis streaming to fetch and process rows batch‑wise.

Precautions

The purpose of MyBatis streaming query is to avoid OOM by returning an iterator (Cursor) that fetches rows incrementally. Developers must keep the SqlSession open while iterating, commit or rollback only after all processing is finished, and be aware that keeping the database connection alive for a long time may increase query latency.

Advantages: reduced memory footprint and GC pressure. Drawbacks: longer overall processing time, need for multithreading, and the requirement that the underlying SqlSession remain open throughout the iteration.