Master MyBatis Streaming Queries: Reduce Memory Usage with Cursors

Introduction

MyBatis streaming query is a less‑known but powerful technique that returns a Cursor iterator instead of loading the whole result set into memory, which helps avoid OOM in large data processing.

What is MyBatis streaming query?

When MyBatis executes a select, it can return a Cursor that can be iterated to fetch rows one by one, keeping the database connection open.

Cursor interface

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

Implementation

Configure JDK 1.8, Spring Boot 2.3.9, mybatis‑spring‑boot‑starter 2.1.4, etc.

Define a mapper that returns Cursor<Person> and a normal count query.

@Mapper
public interface PersonDao {
    Cursor<Person> 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>

In the service layer, open a SqlSession, obtain the mapper, fetch the Cursor, and process data in batches (e.g., 1000 rows) while keeping the session open. Commit the transaction after all batches are processed and finally close the session.

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

    public void getOneByAsync() throws InterruptedException {
        new Thread(() -> {
            SqlSession sqlSession = sqlSessionFactory.openSession();
            try {
                PersonDao mapper = sqlSession.getMapper(PersonDao.class);
                Cursor<Person> cursor = mapper.selectByCursor();
                Integer total = mapper.queryCount();
                List<Person> personList = new ArrayList<>();
                int i = 0;
                for (Person person : cursor) {
                    if (personList.size() < 1000) {
                        personList.add(person);
                    } else {
                        i++;
                        // process batch
                        personList.clear();
                        personList.add(person);
                    }
                    if (total == cursor.getCurrentIndex() + 1) {
                        // last batch processing
                    }
                }
                if (cursor.isConsumed()) {
                    // all data consumed
                }
                sqlSession.commit();
            } catch (Exception e) {
                sqlSession.rollback();
            } finally {
                sqlSession.close();
            }
        }).start();
    }
}

Use cases

The technique is useful when processing massive data sets, such as generating payroll reports for 500,000 employees, where loading all rows at once would exhaust JVM memory.

Precautions

Keep the SqlSession open while iterating; otherwise the cursor will be closed.

Process data in manageable batches to limit memory consumption.

Be aware that streaming may increase overall processing time and requires careful transaction handling.