How to Implement Efficient MyBatis Streaming Queries in Spring Boot

Streaming query returns an iterator instead of a full collection, reducing memory usage.

When the database connection is closed after the mapper method, the MyBatis Cursor also closes, causing errors; therefore the connection must stay open during iteration.

MyBatis streaming query interface

MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable. The cursor is closable and iterable and offers three methods: isOpen() – checks if the cursor is open. isConsumed() – checks if all results have been read. getCurrentIndex() – returns the number of rows already fetched.

Typical usage:

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

Problem when building a Cursor

A mapper method returning Cursor<Foo> works, but the connection is closed after the method returns, leading to

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

Solutions to keep the connection open

Solution 1: SqlSessionFactory

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

Solution 2: TransactionTemplate

Execute the query inside a Spring TransactionTemplate so the connection stays 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

Add @Transactional to the controller method; the transaction keeps the connection open, but the annotation only works when the method is invoked from another bean.

@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 approaches enable proper MyBatis streaming queries in Spring applications.