How to Implement Efficient MyBatis Streaming Queries in Spring MVC

Basic Concept

Streaming query returns an iterator instead of a full collection, allowing each record to be fetched one by one and reducing memory consumption. Without streaming, fetching millions of rows would require pagination, whose efficiency depends on table design.

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

MyBatis Streaming Query Interface

MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable. The interface offers: isOpen(): checks whether the cursor is still open. isConsumed(): determines if all results have been read. getCurrentIndex(): returns the number of rows already retrieved.

Because Cursor implements the iterator interface, data can be processed simply:

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

Defining a Cursor‑Returning Mapper

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

The scan() method returns a Cursor<Foo>, enabling a streaming query.

Typical Controller Usage (Problem)

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

This code throws java.lang.IllegalStateException: A Cursor is already closed because the Mapper method closes the connection after execution, causing the Cursor to close as well.

Solution 1: Use 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 -> {});
    }
}

Opening a SqlSession manually keeps the connection alive until the try‑with‑resources block ends.

Solution 2: Use 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;
    });
}

The transaction keeps the database connection open while the cursor is used.

Solution 3: Use @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 -> {});
    }
}

This approach is the most concise, but the @Transactional annotation only works when the method is invoked from outside the class; internal calls will still encounter the cursor‑closed issue.

Conclusion

MyBatis streaming queries require an open database connection throughout data retrieval. The three presented methods—manual SqlSession handling, TransactionTemplate execution, and @Transactional annotation—provide practical ways to achieve this in Spring MVC applications.