How to Implement Efficient MyBatis Streaming Queries in Spring

Basic Concept

Streaming query means that after a query succeeds, instead of returning a whole collection, it returns an iterator, and the application fetches one result at a time. The benefit is reduced memory usage.

If there is no streaming query and we need to fetch ten million rows without enough memory, we would have to paginate, and pagination efficiency depends on table design; a poor design makes efficient pagination impossible. Therefore streaming query is a required feature of any database access framework.

During a streaming query the database connection remains open, so after executing a streaming query the framework does not close the connection; the application must close it after data retrieval.

MyBatis Streaming Query Interface

MyBatis provides the org.apache.ibatis.cursor.Cursor interface for streaming queries. This interface extends java.io.Closeable and java.lang.Iterable, meaning:

Cursor can be closed.

Cursor can be iterated.

In addition, Cursor offers three methods: isOpen(): checks whether the Cursor is still open before fetching data. isConsumed(): determines whether all results have been consumed. getCurrentIndex(): returns the number of rows already retrieved.

Because Cursor implements the iterator interface, using it is straightforward:

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

Building a Cursor Is Not Trivial

Consider the following Mapper:

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

The scan() method returns a Cursor, signalling MyBatis to perform a streaming query.

A Spring MVC controller can invoke the mapper as follows (irrelevant code omitted):

@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
    }
}

Although the code looks correct, executing scanFoo0() throws:

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

The exception occurs because the database connection is closed after the Mapper method returns, causing the Cursor to close as well.

To keep the connection open, three approaches are available.

Solution 1: SqlSessionFactory

Manually open a connection with SqlSessionFactory and modify the controller:

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

Here step 1 opens a SqlSession (representing a DB connection) and ensures it is closed; step 2 obtains the Mapper via the session, guaranteeing the Cursor remains open.

Solution 2: TransactionTemplate

In Spring, a TransactionTemplate can execute a transaction that keeps the connection open:

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

Step 1 creates the TransactionTemplate; step 2 runs the streaming query inside a transaction, so the connection stays open without manually creating a SqlSession.

Solution 3: @Transactional Annotation

This approach is equivalent to Solution 2 but uses the @Transactional annotation:

@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 invoked from outside the class; internal calls will still cause the same error.

These are three ways to implement MyBatis streaming queries.

Source: https://segmentfault.com/a/1190000022478915