Master MyBatis Streaming Queries: Reduce Memory Usage with Cursors

What is streaming query?

Streaming query returns an iterator instead of a full collection, allowing each row to be fetched one by one, which reduces memory consumption.

When dealing with tens of millions of rows without enough memory, pagination may be inefficient; therefore a streaming query is essential for a database access framework.

During a streaming query the database connection stays open, so the application must close it after processing.

MyBatis streaming query interface

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

Cursor also offers three methods: isOpen() – checks if the cursor is still open. isConsumed() – determines whether all results have been read. getCurrentIndex() – returns the number of rows already retrieved.

Example Mapper:

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

Controller usage:

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

The above code throws java.lang.IllegalStateException: A Cursor is already closed. because the Mapper method closes the connection after execution.

Solutions to keep the connection open

Solution 1: SqlSessionFactory

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

Solution 2: TransactionTemplate

Execute the query inside 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 so the query runs within a transaction.

@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 @Transactional only works when the method is called from outside the class.