Using MyBatis Streaming Queries with Cursor: Concepts and Implementation Options

MyBatis supports streaming queries, which return an iterator instead of a full result set, allowing applications to fetch rows one by one and reduce memory consumption.

The core of this feature is the org.apache.ibatis.cursor.Cursor interface, which extends java.io.Closeable and java.lang.Iterable . A Cursor can be closed, is iterable, and provides methods such as isOpen() , isConsumed() , and getCurrentIndex() .

Typical usage employs a try‑with‑resources block to ensure the Cursor (and underlying connection) is closed after processing:

try (Cursor<Foo> cursor = mapper.querySomeData()) {
    cursor.forEach(rowObject -> {
        // ...
    });
}

If the database connection is closed by the mapper method before the Cursor is fully consumed, a java.lang.IllegalStateException: A Cursor is already closed. error occurs. To keep the connection open, three approaches are presented.

Solution 1: SqlSessionFactory – Manually open a SqlSession (which holds the connection) and obtain the Cursor from it:

@GetMapping("foo/scan/1/{limit}")
public void scanFoo1(@PathVariable("limit") int limit) throws Exception {
    try (
        SqlSession sqlSession = sqlSessionFactory.openSession(); // 1
        Cursor<Foo> cursor = sqlSession.getMapper(FooMapper.class).scan(limit) // 2
    ) {
        cursor.forEach(foo -> { });
    }
}

Solution 2: TransactionTemplate – Execute the query inside 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); // 1
    transactionTemplate.execute(status -> { // 2
        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 -> { });
    }
}

All three methods ensure the database connection remains open while the Cursor iterates, preventing the premature‑close error and enabling efficient processing of large result sets.