Mastering MyBatis Streaming Queries: Keep Connections Open and Avoid Cursor Errors

Streaming queries return an iterator rather than a full collection, allowing lower memory consumption; without them, large result sets require pagination, which can be inefficient depending on table design.

The MyBatis framework provides the org.apache.ibatis.cursor.Cursor interface for streaming queries, which extends java.io.Closeable and java.lang.Iterable. A Cursor is closeable (closing it also closes the DB connection) and iterable.

Cursor offers three useful methods:

isOpen() : checks if the Cursor is open before fetching data.

isConsumed() : determines whether all results have been retrieved.

getCurrentIndex() : returns the number of rows already fetched.

Using a Cursor is straightforward:

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

However, the Cursor’s underlying connection remains open, so the application must close it after use. Building a Cursor can be tricky, and a common error is “Cursor is already closed” because the Mapper method closes the connection after execution.

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();
        Cursor<Foo> cursor = sqlSession.getMapper(FooMapper.class).scan(limit)
    ) {
        cursor.forEach(foo -> { });
    }
}

Solution 2: TransactionTemplate

Execute the streaming query within 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 method with @Transactional to run it in a transaction, ensuring the connection stays open. Note that the annotation only works on external calls:

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

These three methods enable reliable MyBatis streaming queries by maintaining an open database connection throughout data retrieval.