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

Streaming queries return an iterator instead of a full collection, allowing applications to fetch rows one by one and significantly reduce memory usage. Without streaming, retrieving millions of rows would require pagination, whose efficiency depends heavily on table design.

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

MyBatis Streaming Query Interface

MyBatis offers the org.apache.ibatis.cursor.Cursor interface for streaming queries. It extends java.io.Closeable and java.lang.Iterable , making the cursor both closable and iterable. The interface also provides three useful methods:

isOpen() – checks if the cursor is still open.

isConsumed() – indicates whether all rows have been read.

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

Because Cursor implements the iterator pattern, fetching data is straightforward:

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

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

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: Use SqlSessionFactory

Manually open a SqlSession (which holds the 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: Use 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: Use @Transactional Annotation

Annotate the controller method with @Transactional so the framework opens a transaction automatically:

@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 not trigger the transaction.

These three approaches ensure the database connection remains open during streaming, allowing efficient processing of large result sets.