MyBatis Cursor with Spring Boot

Question

I'm trying to use a MyBatis Cursor with Spring Boot to iterate a large query:

Mapper:

@Mapper
@Repository
interface UserMapper {

    @Select("SELECT * FROM huge_user_table")
    Cursor<User> getUsers();

Consumer:

@Component
public class UserProcessor {
    @Autowired private UserMapper userMapper;

    public boolean process() throws IOException {
        Cursor<User> users = userMapper.getUsers();
        //users.isOpen() == false
        for (User user : users) {
            //Never iterates
            System.out.println(user.getId());
        }

When I get my cursor back though it is closed, with no records returned.

Am I missing something?

Answer 1

Most probably the problem is that you try to use Cursor outside of transaction. This is not supported.

Cursor is basically a wrapper around JDBC ResultSet. In order to fetch values from Cursor the underlying ResultSet should be opened. If you do not have a transaction for the whole duration of the loop then spring will open the connection when the query is executed (in your case for the duration of getUsers method). After the method is finished the connection is closed and therefore the ResultSet used by Cursor is closed as well.

Add @Transactional to process and this should fix the issue.