How to Keep MyBatis Streaming Queries Alive: Three Practical Solutions

Basic Concept

Streaming queries return an iterator instead of a full result set, allowing applications to fetch rows one by one and dramatically reduce memory consumption. 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 the connection itself after processing the data.

MyBatis Streaming Query Interface

MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable. Therefore a Cursor is both closeable and iterable.

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

Consider the following Mapper interface:

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

The scan() method returns a Cursor, turning the query into a streaming operation.

A Spring MVC controller can invoke this mapper:

@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 reason is that the Mapper method closes the database connection after execution, which also closes the Cursor.

To keep the connection open, three solutions are available:

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 streaming query within a Spring transaction, which keeps the connection alive:

@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 that Spring opens a transaction (and thus a connection) for the duration of the method:

@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 @Transactional only works when the method is called from outside the class; internal calls will not trigger the transaction proxy.

These three approaches ensure that MyBatis streaming queries operate correctly without the cursor being closed prematurely.