background

The project uses the PageHlper plug-in for paging. Today, it is found that the following errors appear in many SQL query statements.

com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'it 1 LIMIT ? ', expect LIMIT, actual LIMIT pos 249, line 12, column 16, token LIMIT
at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:284)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(
at com.alibaba.druid.sql.SQLUtils.format(SQLUtils.java:255)
at com.alibaba.druid.filter.logging.LogFilter.statement_executeErrorAfter(LogFilter.java:767)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:136)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy467.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
at sun.reflect.GeneratedMethodAccessor239.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy137.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy243.getOneSomthing(Unknown Source)
at com.lingyejun.project.impl.GetOneThingServiceImpl.getOneThingFromDb(GetOneThingServiceImpl.java:23)

investigation

We checked the stack information and found a row of key information, which was intercepted by the PageHelper when querying.

at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:136)

However, no paging code is found in the SQL statement, and there are not only errors, but also several other places. It is found that the submission record has not been changed recently.

We thought it must be because of changes elsewhere. After investigating the cause, it is found that there is a code calling PageHelper After startpage, it returns directly, resulting in an error. The approximate code is as follows.

package com.lingyejun.authenticator;
import com.github.pagehelper.PageHelper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;

@Service
public class PageHelperTest {

    @Resource
    private TeacherMapper teacherMapper;

    @Resource
    private StudentMapper studentMapper;

    public void doQueryByPage(byte type) {
        PageHelper.startPage(1,10);

        if (type == 1){
            studentMapper.query();
        }else if (type ==2){
            teacherMapper.query();
        }
        // If the type is not 1 or 2, the page variable is not released and cleaned up after this method is executed
        // It will cause errors in query statements in other places, or the results are inconsistent with expectations
        return;
    }

}

principle

The PageHelper method uses the static ThreadLocal parameter, which is bound to the thread. As long as we ensure that the PageHelper method call is followed by the MyBatis query method, this is safe. Because PageHelper automatically clears the objects stored in ThreadLocal in the finally code segment.

The paging process of a PageHelper is as follows

Set page parameters
Execute query method
Check whether there is a set page parameter in ThreadLocal in the Interceptor interface
If the page parameter exists, regenerate the count sql and page sql and execute the query. The page parameter does not exist. The query result is returned directly
Execute LOCAL_PAGE.remove() clears the page parameter
However, if the thread pool is used, the current thread will not be destroyed after execution. Instead, the current thread will be stored in the pool again and marked as idle for subsequent use. In the subsequent use of this thread, because the threadlocales of the thread still has a value, although we did not set the page parameter in step 1, we can also get the page parameter in step 3 to generate count sql and page sql, which affects our normal query.  

solve

The above problem is a human bug. It does not take into account that the type is other values, that is, there is a lack of subsequent logic processing in the case of else, which will cause the PageHelper to produce a paging parameter, but it is not consumed, and this parameter will remain on this thread all the time. When this thread is used again, it may lead to methods that should not be paged to consume this paging parameter, resulting in inexplicable paging. Therefore, we can adjust and modify the corresponding logic, and change else if to else to solve this problem.

If this article is helpful to you, please give "Lingye Jun" a praise. Thank you for your support.