pagehelper/PageInterceptor causes MyBatis to execute SQL problems

question
Colleague J met a requirement, which caused an error to be reported in an interface that had nothing to do with the requirement. The error message was due to a SQL syntax problem. Normal SQL should look like this:

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

But now it is:

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

I don’t know why order by goes after limit, which causes MySQL syntax problems. Project configuration springboot 2.1.8.RELEASE mybatis-spring-boot-starter 2.1.0 pagehelper 5.1.10

problem causes
This time I still have my fault, due to my unfamiliarity with pagehelper. Our SQL in xml is probably like this

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

For the sake of flexibility, the sorting rules are passed in here, but “orderBy” is the keyword of pagehelper. If the parameter contains “orderBy”, pagehelper will think that sorting is needed, so it will change the order by in SQL Partially intercept and construct a new SQL

select * from table where condition limit from, size

Then splice it behind the new SQL. The source code here is as follows. orderBy is the sorting rule in the input parameters.

return sql + " order by " + orderBy;

So this has caused the current problem. If you want to modify it, you only need to modify the SQL sort keyword in the xml. The code details are later~ As for why the old code has always been fine, but now there is a problem suddenly, it is because J A colleague discovered that there was a PageInterceptor in the project, but it had not been loaded, so he loaded it in this time…

Troubleshooting details
SQL is a 17-year-old code (an old project~), and the newly added requirements have nothing to do with it. Fortunately, a single test can reproduce the problem. Looking at the error stack, we can see that the query roughly passes through two components, first MyBatis and then sharding JDBC. Among them, MyBatis will first pass through an interceptor, PageInterceptor. Seeing this, the most suspicious one is PageInterceptor. Splicing SQL is MyBatis’s job, which can basically eliminate the problem of sharding JDBC.

The debug code roughly knows the process of PageInterceptor. First, PageInterceptor will construct a Page object based on the input parameters. Then if the Page is not null, the paging logic will be executed. In the paging logic, the splicing order by logic will appear. Follow the code to see.

Since it is the interceptor of MyBatis, of course we should start with 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;
        //Due to logical relationship, it will only be entered once
        if (args. length == 4) {
            //4 parameters
            boundSql = ms.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        } else {
            //6 parameters
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }
        checkDialectExists();

        List resultList;
        //Call the method to determine whether paging is needed. If not, return the result directly.
        if (!dialect.skip(ms, parameter, rowBounds)) { // Focus here
            //Determine whether count query is required
            if (dialect.beforeCount(ms, parameter, rowBounds)) {
                //Query total number
                Long count = count(executor, ms, parameter, rowBounds, resultHandler, boundSql);
                //Process the total number of queries, continue paging queries when true is returned, and return directly when false
                if (!dialect.afterCount(count, parameter, rowBounds)) {
                    //When the total number of queries is 0, empty results are returned directly.
                    return dialect.afterPage(new ArrayList(), parameter, rowBounds);
                }
            }
            resultList = ExecutorUtil.pageQuery(dialect, executor,
                    ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
        } else {
            //rowBounds uses parameter values. When not using the paging plug-in, the default memory paging is still supported.
            resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
        }
        return dialect.afterPage(resultList, parameter, rowBounds);
    } finally {
        if(dialect != null){
            dialect.afterAll();
        }
    }
}

Focus on the logic of 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("Multiple paging plug-ins were found in the system, please check the system configuration!");
    }
    Page page = pageParams.getPage(parameterObject, rowBounds);
    if (page == null) {
        return true;
    } else {
        //Set the default count column
        if (StringUtil.isEmpty(page.getCountColumn())) {
            page.setCountColumn(pageParams.getCountColumn());
        }
        autoDialect.initDelegateDialect(ms);
        return false;
    }
}

// com.github.pagehelper.page.PageParams#getPage
// Get the Page object from ThreadLocal, if not, construct one
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);
                //When offsetAsPageNum=false, reasonable cannot be used due to PageNum issues, so it will be forced to false here.
                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 {
                //Construct a Page
                page = PageObjectUtil.getPageFromObject(parameterObject, false);
            } catch (Exception e) {
                return null;
            }
        }
        if(page == null){
            return null;
        }
        PageHelper.setLocalPage(page);
    }
    //Page rationalization
    if (page.getReasonable() == null) {
        page.setReasonable(reasonable);
    }
    //When set to true, if pagesize is set to 0 (or RowBounds limit=0), paging will not be performed and all results will be returned.
    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("Unable to obtain paging query parameters!");
    }
    if(params instanceof IPage){
        IPage pageParams = (IPage) params;
        Page page = null;
        if(pageParams.getPageNum() != null & amp; & amp; 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 & amp; & amp; requestClass.isAssignableFrom(params.getClass())) {
        try {
            paramsObject = MetaObjectUtil.forObject(getParameterMap.invoke(params, new Object[]{}));
        } catch (Exception e) {
            //neglect
        }
    } else {
        paramsObject = MetaObjectUtil.forObject(params);
    }
    if (paramsObject == null) {
        throw new PageException("Paging query parameter processing failed!");
    }
    // Here is the key, the "orderBy" keyword is used in the project
    Object orderBy = getParamValue(paramsObject, "orderBy", false);
    boolean hasOrderBy = false;
    if (orderBy != null & amp; & amp; 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 parameter is not a legal numeric type!", e);
    }
    Page page = new Page(pageNum, pageSize);
    //count query
    Object _count = getParamValue(paramsObject, "count", false);
    if (_count != null) {
        page.setCount(Boolean.valueOf(String.valueOf(_count)));
    }
    //Sort
    if (hasOrderBy) {
        page.setOrderBy(orderBy.toString());
    }
    //Page rationalization
    Object reasonable = getParamValue(paramsObject, "reasonable", false);
    if (reasonable != null) {
        page.setReasonable(Boolean.valueOf(String.valueOf(reasonable)));
    }
    //Query all
    Object pageSizeZero = getParamValue(paramsObject, "pageSizeZero", false);
    if (pageSizeZero != null) {
        page.setPageSizeZero(Boolean.valueOf(String.valueOf(pageSizeZero)));
    }
    return page;
}

The above completes the construction of the Page object, in which Object orderBy = getParamValue(paramsObject, “orderBy”, false); is the key to the cause of the problem.

Next, start constructing SQL and execute it

//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 {
    //Determine whether paging query is required
    if (dialect.beforePage(ms, parameter, rowBounds)) {
        //Generate paging cache key
        CacheKey pageKey = cacheKey;
        //process parameter object
        parameter = dialect.processParameterObject(ms, parameter, boundSql, pageKey);
        //Call the dialect to get the paging sql; here the problem SQL will be constructed
        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);
        //Set dynamic parameters
        for (String key : additionalParameters.keySet()) {
            pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
        }
        //Execute paging query
        return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, pageKey, pageBoundSql);
    } else {
        //When paging is not performed, memory paging is not performed either.
        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();
    //Support order by; This is supported because we wrote the orderBy keyword in sql
    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) {
    //Parse SQL
    Statement stmt = null;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        SelectBody selectBody = select.getSelectBody();
        //Process the body-go to the outermost order by; here intercept the order by statement
        List<OrderByElement> orderByElements = extraOrderBy(selectBody);
        String defaultOrderBy = PlainSelect.orderByToString(orderByElements);
        if (defaultOrderBy.indexOf('?') != -1) {
            throw new PageException("order by in the original SQL[" + sql + "] contains parameters, so it cannot be modified using the OrderBy plug-in!");
        }
        //New sql; this does not include the order by part
        sql = select.toString();
    } catch (Throwable e) {
        log.warn("Failed to process sorting: " + e + ", downgraded to direct splicing order by parameter");
    }
    // Direct splicing here leads to wrong SQL
    return sql + " order by " + orderBy;
}

At this point, the whole process is over. The code logic is relatively clear, and you will feel better when you debug it by yourself~

The article quotes pagehelper/PageInterceptor causing MyBatis to execute SQL problems_51CTO blog_pagehelper causes mybatis errors

This article is reprinted content, and we respect the copyright of the original author. If there are any content errors or infringement issues, the original author is welcome to contact us to correct the content or delete the article.