Master MyBatis Streaming Queries: Keep DB Connections Open Efficiently

MyBatis streaming query returns an iterator (Cursor) instead of a collection, reducing memory usage when handling large result sets.

The Cursor interface extends java.io.Closeable and java.lang.Iterable, providing methods isOpen(), isConsumed(), and getCurrentIndex().

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

This code fails with 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:

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