Implementing MyBatis Streaming Queries in Spring: Concepts, API, and Three Practical Solutions

Basic Concept

Streaming query means that after a query is executed, instead of returning a whole collection, an iterator is returned and the application fetches one result at a time. This reduces memory consumption.

Without streaming, fetching millions of rows would require pagination, whose efficiency depends on table design; inefficient design can make pagination slow. Therefore, streaming is a necessary feature of any database access framework.

During a streaming query the database connection stays open, so the application must close the connection after all data has been read.

MyBatis Streaming Query API

MyBatis provides the org.apache.ibatis.cursor.Cursor interface for streaming queries. It extends java.io.Closeable and java.lang.Iterable , meaning a Cursor can be closed and iterated.

Cursor also offers three useful methods:

isOpen() : checks whether the Cursor is still open before fetching data.

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

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

Because Cursor implements Iterable, data can be fetched simply:

cursor.forEach(rowObject -> { ... });

Building a Cursor Is Not Trivial

Example Mapper interface:

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

Controller method using 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, which also closes the Cursor.

Solution 1: SqlSessionFactory

Manually open a SqlSession (which holds a DB connection) and obtain the Mapper from it:

@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: TransactionTemplate

Execute the query inside a Spring transaction, which keeps the connection open:

@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: @Transactional Annotation

Simply annotate the controller method with @Transactional so the method runs within a transaction:

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

Note that the @Transactional annotation only works when the method is called from outside the class; internal calls will still fail.

These three approaches ensure the database connection remains open during streaming, preventing the cursor‑closed exception.