問題
同事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會更有感受~