mysql游标

mysql分页查询的时候有深度翻页的问题，如果我们需要查询全部数据的时候，不可能一次查询全部，这个时候就可以使用游标，游标的原理其实就是顺序查询，只是每次查询到一些数据后会先返回给我们，然后继续往后查询。这样解决了等待问题，也解决了内存爆炸问题。下面通过mybatis介绍一下游标的使用。

使用方式

@Component
public class PersonResultHandler implements ResultHandler<Person> {
    @Override
    public void handleResult(ResultContext<? extends Person> resultContext) {
        System.out.println(resultContext.isStopped());
        System.out.println(resultContext.getResultObject());
    }
}

@Mapper
public interface PersonMapper {
    Person getById(@Param("id") Long id);

    int create(Person person);

    void findAll(ResultHandler<Person> resultHandler);
}


    <select id="findAll" resultType="com.example.bootmybatis.entity.Person">
        select
        `id` as `id`,
        `name` as `name`,
        `desc` as `desc`,
        `nationality_id` as `nationalityId`
        from person
    </select>

思路

mybatis提供了两个接口，可以通过游标查询。

    void select(String var1, Object var2, ResultHandler var3);

    void select(String var1, ResultHandler var2);

根据this.method.returnsVoid() && this.method.hasResultHandler()可以知道，如果接口返回值为void并且包含resultHandler接口，则可以使用游标查询。

    public Object execute(SqlSession sqlSession, Object[] args) {
        Object param;
        Object result;
        switch(this.command.getType()) {
        case INSERT:
            param = this.method.convertArgsToSqlCommandParam(args);
            result = this.rowCountResult(sqlSession.insert(this.command.getName(), param));
            break;
        case UPDATE:
            param = this.method.convertArgsToSqlCommandParam(args);
            result = this.rowCountResult(sqlSession.update(this.command.getName(), param));
            break;
        case DELETE:
            param = this.method.convertArgsToSqlCommandParam(args);
            result = this.rowCountResult(sqlSession.delete(this.command.getName(), param));
            break;
        case SELECT:
            if (this.method.returnsVoid() && this.method.hasResultHandler()) {
                this.executeWithResultHandler(sqlSession, args);
                result = null;
            } else if (this.method.returnsMany()) {
                result = this.executeForMany(sqlSession, args);
            } else if (this.method.returnsMap()) {
                result = this.executeForMap(sqlSession, args);
            } else if (this.method.returnsCursor()) {
                result = this.executeForCursor(sqlSession, args);
            } else {
                param = this.method.convertArgsToSqlCommandParam(args);
                result = sqlSession.selectOne(this.command.getName(), param);
            }
            break;
        case FLUSH:
            result = sqlSession.flushStatements();
            break;
        default:
            throw new BindingException("Unknown execution method for: " + this.command.getName());
        }

        if (result == null && this.method.getReturnType().isPrimitive() && !this.method.returnsVoid()) {
            throw new BindingException("Mapper method '" + this.command.getName() + " attempted to return null from a method with a primitive return type (" + this.method.getReturnType() + ").");
        } else {
            return result;
        }
    }