问题

同事J上了一个需求, 导致一个跟这个需求毫无关系的接口报错, 报错信息显示是因为SQL语法问题, 正常SQL应该是这样:

select * from table where condition order by field limit from, size

但是现在却是:

select * from table where condition limit from, size order by field

不知道为什么order by跑到limit后面去了, 所以导致MySQL语法问题
项目配置
springboot 2.1.8.RELEASE
mybatis-spring-boot-starter 2.1.0
pagehelper 5.1.10

问题原因

这次锅还是自己的, 对于pagehelper的不熟悉导致的. 我们在xml中的SQL大概是这样

select * from table where condition order by ${orderBy} limit from, size

我们这里为了灵活, 排序规则是传入的, 但是"orderBy"是pagehelper的关键字, 如果参数中包含了"orderBy", pagehelper会认为需要进行排序, 所以会将SQL中的order by部分截取, 构造一个新的SQL

select * from table where condition limit from, size

然后在新的SQL后面拼接, 这里的源码如下, orderBy中是入参中的排序规则

return sql + " order by " + orderBy;

所以就造成了现在的问题, 修改的话, 只需要把xml中的SQL排序关键字修改下就可以了.
代码细节在后面~
至于为什么老代码一直没问题, 现在突然出现问题了, 这是因为J同事发现项目中有PageInterceptor, 但是一直没有加载, 所以他这次顺便给加载进来了...

排查细节

SQL是17年的代码(老项目了~), 新加的需求也跟这里没有关系, 幸好单测可以重现问题. 查看报错堆栈, 看到查询大致经过两个组件, 先MyBatis然后sharding JDBC. 其中MyBatis会先经过一个拦截器PageInterceptor. 看到这里, 嫌疑最大的就是PageInterceptor了, 拼接SQL是MyBatis的工作, 基本可以排除sharding JDBC的问题.

debug代码大概知道PageInterceptor的流程, 首先PageInterceptor会先根据入参构造一个Page对象, 然后如果Page不为null则执行分页逻辑, 在分页逻辑中就出现了拼接 order by的逻辑, 跟着代码看下.

既然是MyBatis的拦截器, 当然先从com.github.pagehelper.PageInterceptor#intercept 入手

public Object intercept(Invocation invocation) throws Throwable {
    try {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler resultHandler = (ResultHandler) args[3];
        Executor executor = (Executor) invocation.getTarget();
        CacheKey cacheKey;
        BoundSql boundSql;
        //由于逻辑关系，只会进入一次
        if (args.length == 4) {
            //4 个参数时
            boundSql = ms.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        } else {
            //6 个参数时
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }
        checkDialectExists();

        List resultList;
        //调用方法判断是否需要进行分页，如果不需要，直接返回结果
        if (!dialect.skip(ms, parameter, rowBounds)) { // 重点看这里
            //判断是否需要进行 count 查询
            if (dialect.beforeCount(ms, parameter, rowBounds)) {
                //查询总数
                Long count = count(executor, ms, parameter, rowBounds, resultHandler, boundSql);
                //处理查询总数，返回 true 时继续分页查询，false 时直接返回
                if (!dialect.afterCount(count, parameter, rowBounds)) {
                    //当查询总数为 0 时，直接返回空的结果
                    return dialect.afterPage(new ArrayList(), parameter, rowBounds);
                }
            }
            resultList = ExecutorUtil.pageQuery(dialect, executor,
                    ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
        } else {
            //rowBounds用参数值，不使用分页插件处理时，仍然支持默认的内存分页
            resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
        }
        return dialect.afterPage(resultList, parameter, rowBounds);
    } finally {
        if(dialect != null){
            dialect.afterAll();
        }
    }
}

重点看下dialect.skip(ms, parameter, rowBounds)逻辑

// com.github.pagehelper.PageHelper#skip
public boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
    if (ms.getId().endsWith(MSUtils.COUNT)) {
        throw new RuntimeException("在系统中发现了多个分页插件，请检查系统配置!");
    }
    Page page = pageParams.getPage(parameterObject, rowBounds);
    if (page == null) {
        return true;
    } else {
        //设置默认的 count 列
        if (StringUtil.isEmpty(page.getCountColumn())) {
            page.setCountColumn(pageParams.getCountColumn());
        }
        autoDialect.initDelegateDialect(ms);
        return false;
    }
}

// com.github.pagehelper.page.PageParams#getPage
// 从ThreadLocal中获取Page对象, 没有的话构造一个
public Page getPage(Object parameterObject, RowBounds rowBounds) {
    Page page = PageHelper.getLocalPage();
    if (page == null) {
        if (rowBounds != RowBounds.DEFAULT) {
            if (offsetAsPageNum) {
                page = new Page(rowBounds.getOffset(), rowBounds.getLimit(), rowBoundsWithCount);
            } else {
                page = new Page(new int[]{rowBounds.getOffset(), rowBounds.getLimit()}, rowBoundsWithCount);
                //offsetAsPageNum=false的时候，由于PageNum问题，不能使用reasonable，这里会强制为false
                page.setReasonable(false);
            }
            if(rowBounds instanceof PageRowBounds){
                PageRowBounds pageRowBounds = (PageRowBounds)rowBounds;
                page.setCount(pageRowBounds.getCount() == null || pageRowBounds.getCount());
            }
        } else if(parameterObject instanceof IPage || supportMethodsArguments){
            try {
                // 构造一个Page
                page = PageObjectUtil.getPageFromObject(parameterObject, false);
            } catch (Exception e) {
                return null;
            }
        }
        if(page == null){
            return null;
        }
        PageHelper.setLocalPage(page);
    }
    //分页合理化
    if (page.getReasonable() == null) {
        page.setReasonable(reasonable);
    }
    //当设置为true的时候，如果pagesize设置为0（或RowBounds的limit=0），就不执行分页，返回全部结果
    if (page.getPageSizeZero() == null) {
        page.setPageSizeZero(pageSizeZero);
    }
    return page;
}

//com.github.pagehelper.util.PageObjectUtil#getPageFromObject
public static <T> Page<T> getPageFromObject(Object params, boolean required) {
    if (params == null) {
        throw new PageException("无法获取分页查询参数!");
    }
    if(params instanceof IPage){
        IPage pageParams = (IPage) params;
        Page page = null;
        if(pageParams.getPageNum() != null && pageParams.getPageSize() != null){
            page = new Page(pageParams.getPageNum(), pageParams.getPageSize());
        }
        if (StringUtil.isNotEmpty(pageParams.getOrderBy())) {
            if(page != null){
                page.setOrderBy(pageParams.getOrderBy());
            } else {
                page = new Page();
                page.setOrderBy(pageParams.getOrderBy());
                page.setOrderByOnly(true);
            }
        }
        return page;
    }
    int pageNum;
    int pageSize;
    MetaObject paramsObject = null;
    if (hasRequest && requestClass.isAssignableFrom(params.getClass())) {
        try {
            paramsObject = MetaObjectUtil.forObject(getParameterMap.invoke(params, new Object[]{}));
        } catch (Exception e) {
            //忽略
        }
    } else {
        paramsObject = MetaObjectUtil.forObject(params);
    }
    if (paramsObject == null) {
        throw new PageException("分页查询参数处理失败!");
    }
    // 这里是关键, 项目中就是使用了"orderBy"关键词
    Object orderBy = getParamValue(paramsObject, "orderBy", false);
    boolean hasOrderBy = false;
    if (orderBy != null && orderBy.toString().length() > 0) {
        hasOrderBy = true;
    }
    try {
        Object _pageNum = getParamValue(paramsObject, "pageNum", required);
        Object _pageSize = getParamValue(paramsObject, "pageSize", required);
        if (_pageNum == null || _pageSize == null) {
            if(hasOrderBy){
                Page page = new Page();
                page.setOrderBy(orderBy.toString());
                page.setOrderByOnly(true);
                return page;
            }
            return null;
        }
        pageNum = Integer.parseInt(String.valueOf(_pageNum));
        pageSize = Integer.parseInt(String.valueOf(_pageSize));
    } catch (NumberFormatException e) {
        throw new PageException("分页参数不是合法的数字类型!", e);
    }
    Page page = new Page(pageNum, pageSize);
    //count查询
    Object _count = getParamValue(paramsObject, "count", false);
    if (_count != null) {
        page.setCount(Boolean.valueOf(String.valueOf(_count)));
    }
    //排序
    if (hasOrderBy) {
        page.setOrderBy(orderBy.toString());
    }
    //分页合理化
    Object reasonable = getParamValue(paramsObject, "reasonable", false);
    if (reasonable != null) {
        page.setReasonable(Boolean.valueOf(String.valueOf(reasonable)));
    }
    //查询全部
    Object pageSizeZero = getParamValue(paramsObject, "pageSizeZero", false);
    if (pageSizeZero != null) {
        page.setPageSizeZero(Boolean.valueOf(String.valueOf(pageSizeZero)));
    }
    return page;
}

以上构造完了Page对象, 其中
Object orderBy = getParamValue(paramsObject, "orderBy", false);
就是问题原因的关键.

下面开始构造SQL并执行

//com.github.pagehelper.util.ExecutorUtil#pageQuery
public static  <E> List<E> pageQuery(Dialect dialect, Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql, CacheKey cacheKey) throws SQLException {
    //判断是否需要进行分页查询
    if (dialect.beforePage(ms, parameter, rowBounds)) {
        //生成分页的缓存 key
        CacheKey pageKey = cacheKey;
        //处理参数对象
        parameter = dialect.processParameterObject(ms, parameter, boundSql, pageKey);
        //调用方言获取分页 sql; 这里就会构造问题SQL
        String pageSql = dialect.getPageSql(ms, boundSql, parameter, rowBounds, pageKey);
        BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, boundSql.getParameterMappings(), parameter);

        Map<String, Object> additionalParameters = getAdditionalParameter(boundSql);
        //设置动态参数
        for (String key : additionalParameters.keySet()) {
            pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
        }
        //执行分页查询
        return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, pageKey, pageBoundSql);
    } else {
        //不执行分页的情况下，也不执行内存分页
        return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, boundSql);
    }
}

//com.github.pagehelper.dialect.AbstractHelperDialect#getPageSql(org.apache.ibatis.mapping.MappedStatement, org.apache.ibatis.mapping.BoundSql, java.lang.Object, org.apache.ibatis.session.RowBounds, org.apache.ibatis.cache.CacheKey)

public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
    String sql = boundSql.getSql();
    Page page = getLocalPage();
    //支持 order by; 这里支持就是因为我们在sql写了orderBy关键字
    String orderBy = page.getOrderBy();
    if (StringUtil.isNotEmpty(orderBy)) {
        pageKey.update(orderBy);
        sql = OrderByParser.converToOrderBySql(sql, orderBy);
    }
    if (page.isOrderByOnly()) {
        return sql;
    }
    return getPageSql(sql, page, pageKey);
}

//com.github.pagehelper.parser.OrderByParser#converToOrderBySql
public static String converToOrderBySql(String sql, String orderBy) {
    //解析SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        //处理body-去最外层order by; 这里将order by语句截取出来
        List<OrderByElement> orderByElements = extraOrderBy(selectBody);
        String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
        if (defaultOrderBy.indexOf('?') != -1) {
            throw new PageException("原SQL[" + sql + "]中的order by包含参数，因此不能使用OrderBy插件进行修改!");
        }
        //新的sql; 这里就不包含order by部分了
        sql = select.toString();
    } catch (Throwable e) {
        log.warn("处理排序失败: " + e + "，降级为直接拼接 order by 参数");
    }
    // 这里直接拼接, 导致错误SQL
    return sql + " order by " + orderBy;
}

至此整个流程就结束了. 代码逻辑比较清晰, 大家自己debug会更有感觉~