Using MyBatis Cursor for Streaming Queries and Managing Database Connections in Spring

Hello, I am a top architect. A streaming query means that after a query succeeds, it returns an iterator instead of a whole collection, and the application fetches one result at a time.

The advantage of streaming queries is reduced memory consumption.

If you do not use streaming and need to fetch, for example, ten million rows without enough memory, you are forced to paginate, and pagination efficiency depends on table design; a poorly designed table cannot perform efficient pagination. Therefore streaming is a required feature of any database access framework.

During a streaming query the database connection remains open, so after the query the framework does not close the connection; the application must close it after all data have been retrieved.

MyBatis Streaming Query Interface

MyBatis provides the org.apache.ibatis.cursor.Cursor interface for streaming queries. This interface extends java.io.Closeable and java.lang.Iterable , which means:

Cursor is closable – closing the Cursor also closes the underlying database connection.

Cursor is iterable.

In addition, Cursor offers three useful methods:

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

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

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

Because Cursor implements the iterator interface, using it is straightforward:

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

Using try‑resource ensures the Cursor (and thus the connection) is automatically closed.

Building a Cursor Is Not Trivial

Consider the following Mapper definition:

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

And a Spring MVC controller that calls it:

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

When scanFoo0(int) runs, it throws java.lang.IllegalStateException: A Cursor is already closed. because the Mapper method finishes, the framework closes the connection, and the Cursor is consequently closed.

To solve this, the database connection must stay open while the Cursor is being consumed. Three viable approaches are presented below.

Solution 1: SqlSessionFactory

Manually open a SqlSession (which holds a 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 -> {});
    }
}

Here the session is opened (keeping the connection alive) and closed automatically by the try‑with‑resources block.

Solution 2: TransactionTemplate

Execute the query inside a Spring transaction, which also 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;
    });
}

The transaction ensures the underlying connection remains open for the duration of the callback.

Solution 3: @Transactional Annotation

Simply annotate the controller method with @Transactional :

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

This is the most concise solution, but note that Spring only applies the annotation when the method is invoked from outside the class; internal calls will not trigger the transaction.

These three approaches demonstrate how to correctly perform MyBatis streaming queries while keeping the database connection alive.