How to Implement Efficient MyBatis Streaming Queries in Spring MVC

Basic Concepts

Streaming queries return an iterator instead of a full result set, allowing applications to fetch one record at a time and thus reduce memory usage.

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

During a streaming query the database connection stays open, so the application must close it after consuming all 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:

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

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

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

But building a Cursor is not simple

Consider the following Mapper definition:

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

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

A Spring MVC controller can invoke this mapper as follows:

@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 compiles, but at runtime it throws:

java.lang.IllegalStateException: A Cursor is already closed.

The exception occurs because the Mapper method closes the database connection after execution, which also closes the Cursor.

To keep the connection open, you can choose one of three approaches.

Solution 1: SqlSessionFactory

Manually open a session 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 within a Spring transaction:

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

@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 annotation only works when the method is invoked from outside the class; internal calls will still trigger the same error.

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