Implementing MyBatis Streaming Queries with Cursor and Keeping Database Connections Open

Streaming queries return an iterator instead of a full result set, allowing each row to be fetched on demand and reducing memory consumption. Without streaming, retrieving millions of rows would require pagination, whose efficiency depends heavily on table design.

When a streaming query is executed, the database connection remains open, so the application must close it after processing the data.

MyBatis offers the org.apache.ibatis.cursor.Cursor interface for streaming queries; it extends java.io.Closeable and java.lang.Iterable , making it both closable and iterable. The interface provides three useful methods:

isOpen() : checks whether the cursor is still open.

isConsumed() : determines if all rows have been read.

getCurrentIndex() : returns the number of rows already retrieved.

Using the cursor is straightforward, e.g.:

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

Below is a typical Mapper definition that returns a Cursor<Foo> :

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

A Spring MVC controller can consume the cursor as follows:

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

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

To keep the connection open, three approaches are presented:

Solution 1: SqlSessionFactory

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

Solution 2: 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); // 1
    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 controller method with @Transactional so the method 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 the @Transactional approach only works when the method is called from outside the same class.

These three solutions enable proper use of MyBatis streaming queries without premature cursor closure.