Master MyBatis Streaming Queries: Avoid Cursor Closure Errors with Three Simple Solutions

Streaming query concept

A streaming query returns a java.util.Iterator (or any Iterable) instead of materialising the whole result set in memory. This reduces memory consumption when processing large tables (e.g., millions of rows). The database connection remains open for the duration of the iteration, so the application must explicitly close the connection after the stream is consumed.

MyBatis streaming API

MyBatis provides org.apache.ibatis.cursor.Cursor<T>, which extends both java.io.Closeable and java.lang.Iterable<T>. Consequently:

Cursor can be used in a try‑with‑resources block and will be closed automatically.

Cursor can be traversed with enhanced for loops or forEach lambda expressions.

Additional useful methods: isOpen() – returns true while the underlying JDBC ResultSet is still open. isConsumed() – indicates whether all rows have been read. getCurrentIndex() – the number of rows already fetched.

Typical usage:

cursor.forEach(row -> {
    // process row
});

Why a Cursor may be closed prematurely

When a mapper method returns a Cursor, MyBatis closes the JDBC Connection as soon as the mapper method finishes. If the connection is closed, the returned Cursor is also closed, leading to the runtime exception:

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

The following example reproduces the problem:

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

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

Because the mapper method finishes before the forEach loop reads any rows, the underlying connection is closed and the cursor becomes unusable.

Solution 1 – Manually open a SqlSession

Open a SqlSession explicitly, which holds the JDBC connection for the lifetime of the session. Use the session to obtain the mapper and the cursor.

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

Both the SqlSession and the Cursor are closed automatically by the try‑with‑resources block, guaranteeing that the connection stays open while rows are streamed.

Solution 2 – Execute within a Spring TransactionTemplate

Wrap the streaming operation in a Spring transaction. The transaction manager obtains a connection that remains open for the duration of the transaction.

@GetMapping("foo/scan/2/{limit}")
public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
    TransactionTemplate txTemplate = new TransactionTemplate(transactionManager);
    txTemplate.execute(status -> {
        try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
            cursor.forEach(foo -> {});
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    });
}

The mapper can be injected directly; Spring supplies the connection via the transaction.

Solution 3 – Annotate the method with @Transactional

Mark the controller method with @Transactional. Spring opens a transaction (and thus a connection) before invoking the method and commits/rolls back after it returns.

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

Important: @Transactional only takes effect when the method is called from another Spring bean. Self‑invocation (a method calling another @Transactional method within the same class) will bypass the proxy and the transaction will not be started.

Summary

MyBatis streaming queries require the underlying JDBC connection to stay open while the Cursor is consumed. The three practical approaches above—manual SqlSession, TransactionTemplate, and @Transactional —ensure the connection remains alive, preventing the "Cursor is already closed" exception.