问题
同事J上了一个需求, 致使一个跟这个需求毫无关系的接口报错, 报错信息显示是由于SQL语法问题, 正常SQL应该是这样:java
select * from table where condition order by field limit from, size
可是如今倒是:git
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.10github
问题缘由
此次锅仍是本身的, 对于pagehelper的不熟悉致使的. 咱们在xml中的SQL大概是这样spring
select * from table where condition order by ${orderBy} limit from, size
咱们这里为了灵活, 排序规则是传入的, 可是"orderBy"是pagehelper的关键字, 若是参数中包含了"orderBy", pagehelper会认为须要进行排序, 因此会将SQL中的order by部分截取, 构造一个新的SQLsql
select * from table where condition limit from, size
而后在新的SQL后面拼接, 这里的源码以下, orderBy中是入参中的排序规则apache
return sql + " order by " + orderBy;
因此就形成了如今的问题, 修改的话, 只须要把xml中的SQL排序关键字修改下就能够了. 代码细节在后面~ 至于为何老代码一直没问题, 如今忽然出现问题了, 这是由于J同事发现项目中有PageInterceptor, 可是一直没有加载, 因此他此次顺便给加载进来了...缓存
排查细节
SQL是17年的代码(老项目了~), 新加的需求也跟这里没有关系, 幸亏单测能够重现问题. 查看报错堆栈, 看到查询大体通过两个组件, 先MyBatis而后sharding JDBC. 其中MyBatis会先通过一个拦截器PageInterceptor. 看到这里, 嫌疑最大的就是PageInterceptor了, 拼接SQL是MyBatis的工做, 基本能够排除sharding JDBC的问题.springboot
debug代码大概知道PageInterceptor的流程, 首先PageInterceptor会先根据入参构造一个Page对象, 而后若是Page不为null则执行分页逻辑, 在分页逻辑中就出现了拼接 order by的逻辑, 跟着代码看下.session
既然是MyBatis的拦截器, 固然先从com.github.pagehelper.PageInterceptor#intercept 入手mybatis
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会更有感受~