How to Implement Efficient MyBatis Streaming Queries in Spring

Basic Concept

Streaming query returns an iterator instead of a collection, allowing the application to fetch one row at a time, which reduces memory usage.

Without streaming, fetching millions of rows would require pagination, whose efficiency depends on table design; therefore streaming is essential for a database access framework.

During streaming, the DB connection stays open, so the framework does not close it; the application must close the connection after processing.

MyBatis Streaming Query Interface

MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable.

Cursor is closeable.

Cursor is iterable.

Cursor also offers three 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 the cursor is straightforward because it implements the iterator interface.

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

But Building a Cursor Is Not Simple

Example Mapper interface:

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

Controller method using the mapper:

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

This code throws java.lang.IllegalStateException: A Cursor is already closed. because the DB connection is closed after the mapper method returns.

To keep the connection open, three solutions are presented.

Solution 1: SqlSessionFactory

Manually open a SqlSession (which holds a DB 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: 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

Add the @Transactional annotation to the controller 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 the annotation only works when the method is called from outside the class; internal calls will still fail.

These are three ways to implement MyBatis streaming queries safely.