PageHelper 问题定位

问题简述：
问题现象：select * from table order by field ${orderBy} → select * from table order by ${orderBy}

问题原因：入参字段 orderBy 被 PageHelper 作为 排序 ，重写了 原 SQL 语句的 排序

解决方案与源码：

1、（推荐）通过 pagehelper.support-methods-arguments: false 禁止PageHelper使用入参进行分页排序等sql重写

2、（临时）修改入参字段，区别于 PageHelper 所使用的字段

源码追踪：
1、参数配置的代码方法，重点见 16 行 【com.github.pagehelper.page.PageParams.getPage(Object parameterObject, RowBounds rowBounds)】

/**
 * 获取分页参数
 *
 * @param parameterObject
 * @param rowBounds
 * @return
 */
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(supportMethodsArguments){  //pagehelper.support-methods-arguments: false 配置
            try {
                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;
}

2、临时修改入参字段命名的方法，见26行 【com.github.pagehelper.util.PageObjectUtil.getPageFromObject(Object params, boolean required)】

/**
 * 对象中获取分页参数
 *
 * @param params
 * @return
 */
public static <T> Page<T> getPageFromObject(Object params, boolean required) {
    int pageNum;
    int pageSize;
    MetaObject paramsObject = null;
    if (params == null) {
        throw new PageException("无法获取分页查询参数!");
    }
    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("分页查询参数处理失败!");
    }
    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("分页参数不是合法的数字类型!");
    }
    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;
}

重写OrderBy SQL 【com.github.pagehelper.parser.OrderByParser.converToOrderBySql(String sql, String orderBy)】

/**
 * convert to order by sql
 *
 * @param sql
 * @param orderBy
 * @return
 */
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
        List<OrderByElement> orderByElements = extraOrderBy(selectBody);
        String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
        if (defaultOrderBy.indexOf('?') != -1) {
            throw new RuntimeException("原SQL[" + sql + "]中的order by包含参数，因此不能使用OrderBy插件进行修改!");
        }
        //新的sql
        sql = select.toString();
    } catch (Throwable e) {
        e.printStackTrace();
    }
    return sql + " order by " + orderBy;
}