MyBatis Streaming Query: Cursor Interface and Practical Implementations

MyBatis streaming query returns an iterator (Cursor) instead of a full collection, which reduces memory consumption when processing large result sets.

The Cursor interface ( org.apache.ibatis.cursor.Cursor ) extends java.io.Closeable and java.lang.Iterable , making it both closeable and iterable, and provides three useful methods: isOpen() , isConsumed() , and getCurrentIndex() .

Using the Cursor is straightforward; for example:

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

A typical Mapper definition looks like this:

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

In a Spring MVC controller you might write:

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

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

To keep the connection open, three approaches are presented:

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

TransactionTemplate : Execute the query inside a Spring transaction, which keeps the connection alive. @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; }); }

@Transactional annotation : Annotate the controller method with @Transactional so the framework opens a transaction for the duration of the 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.

These three solutions enable safe and efficient streaming of large query results with MyBatis.