MyBatis Streaming Query (Cursor) – Keeping the Database Connection Open

Streaming query means that after a query is executed the database returns an iterator instead of a full result set, allowing rows to be fetched one by one and reducing memory consumption.

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

The Cursor interface offers three useful methods:

isOpen() – checks whether the cursor is still open before fetching data.

isConsumed() – determines whether all rows have been read.

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

Because the cursor keeps the database connection open, the application must close the cursor (and thus the connection) after use.

Example Mapper returning a Cursor:

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

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

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

Three ways to keep the connection open:

Solution 1 – SqlSessionFactory

@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

@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

@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 invoked from outside the class; internal calls will still encounter the closed‑cursor error.