Mastering MyBatis Streaming Queries: Reduce Memory Usage and Avoid Cursor Closure Errors

What is Streaming Query?

Streaming query returns an iterator instead of a full result set, allowing the application to fetch one row at a time.

Benefits of Streaming Query

It reduces memory usage; without it, fetching millions of rows would require pagination, which depends on table design and may be inefficient. Streaming queries keep the DB connection open, so the application must close it after use.

MyBatis Streaming Query Interface

MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable. The cursor can be closed (which also closes the DB connection) and iterated.

Key methods:

isOpen() : checks if the cursor is open before fetching data.

isConsumed() : determines whether all results have been read.

getCurrentIndex() : returns the number of rows already retrieved.

Example of using a cursor with try‑with‑resources:

try (Cursor cursor = mapper.querySomeData()) {
    cursor.forEach(rowObject -> {
        // process row
    });
}

Building a Cursor

Define a mapper method that returns Cursor<Foo>:

@Mapper
public interface FooMapper {
    @Select("select * from foo limit #{limit}")
    Cursor<Foo> scan(@Param("limit") int limit);
}

Controller method calling the mapper:

@GetMapping("foo/scan/0/{limit}")
public void scanFoo0(@PathVariable("limit") int limit) throws Exception {
    try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
        cursor.forEach(foo -> {});
    }
}

This code throws java.lang.IllegalStateException: A Cursor is already closed because the mapper method closes the connection after execution.

Solutions to Keep the Connection Open

Solution 1: Use SqlSessionFactory

@GetMapping("foo/scan/1/{limit}")
public void scanFoo1(@PathVariable("limit") int limit) throws Exception {
    try (
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Cursor<Foo> cursor = sqlSession.getMapper(FooMapper.class).scan(limit)
    ) {
        cursor.forEach(foo -> {});
    }
}

Solution 2: Use TransactionTemplate

@GetMapping("foo/scan/2/{limit}")
public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
    TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
    transactionTemplate.execute(status -> {
        try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
            cursor.forEach(foo -> {});
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    });
}

Solution 3: Use @Transactional Annotation

@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 -> {});
    }
}

The @Transactional approach is concise but only works when the method is invoked from outside the class.

These three methods enable proper MyBatis streaming queries without running out of memory.