How to Perform Streaming Queries with MyBatis Cursor and Keep the Database Connection Open

Streaming queries return an iterator instead of a full result set, allowing applications to fetch rows one by one and significantly reduce memory consumption.

Without streaming, retrieving millions of rows would require pagination, whose performance depends heavily on table design; therefore, streaming is a crucial feature for any database access framework.

During a streaming query the database connection remains open, so the application must explicitly close the connection after processing the data.

MyBatis Streaming Query Interface

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 is closeable.

Cursor is iterable.

Cursor also offers three useful methods:

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

isConsumed() : determines if all rows have been read.

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

Because Cursor implements the iterator interface, consuming data is straightforward:

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

Building a Cursor Is Not Trivial

Example Mapper:

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

The scan() method returns a Cursor, signalling MyBatis to perform a streaming query.

Example Spring MVC controller method:

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

This code throws java.lang.IllegalStateException: A Cursor is already closed. because the Mapper method closes the connection after execution, causing the Cursor to close as well.

Solution 1: SqlSessionFactory

Manually open a SqlSession to keep the connection alive:

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

Solution 2: TransactionTemplate

Execute the streaming query within 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); // 1
    transactionTemplate.execute(status -> {
        // 2
        try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
            cursor.forEach(foo -> {});
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    });
}

Solution 3: @Transactional Annotation

Annotate the controller method with @Transactional to run it in 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 invoked from outside the class; internal calls will still encounter the closed‑Cursor issue.

These three approaches ensure the database connection stays open for the duration of the streaming query, allowing safe and efficient processing of large result sets.