How to Implement Efficient MyBatis Streaming Queries in Spring

Basic Concept

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

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

During a streaming query the database connection remains open, so the framework does not close the connection automatically; the application must close it after processing.

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:

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 whether all results have been read. getCurrentIndex(): returns the number of rows already retrieved.

Using the iterator nature of Cursor is straightforward: cursor.forEach(rowObject -> { ... }); Example Mapper:

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

Calling scan() from a Spring MVC controller without keeping the connection open leads to the error java.lang.IllegalStateException: A Cursor is already closed. because the Mapper method closes the connection after execution.

Solution 1: 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: TransactionTemplate

Execute the streaming 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

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: The @Transactional approach only works when the method is invoked from outside the class; internal calls will not trigger the transaction and may still cause the cursor‑closed error.

These three methods provide reliable ways to perform MyBatis streaming queries while ensuring the underlying database connection stays open for the duration of data processing.