Mybatis执行SQL时多了一个limit

墨蓝 2024-04-03 11:52 64阅读 0赞

## 报错 ##

check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 5

代码如下：

mapper层方法：

List<Integer> select(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

xml代码：

<select id="select" resultType="java.lang.Integer">
        SELECT id
        FROM test
        LIMIT #{pageNum}, #{pageSize}
    </select>

完整报错如下：

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 5
    ### The error may exist in class path resource [mapper/test.xml]
    ### The error may involve defaultParameterMap
    ### The error occurred while setting parameters
    ### SQL: SELECT id  FROM test LIMIT ?, ? LIMIT ?
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 5
    ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 5，位置信息：org.
    springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    com.sun.proxy.$Proxy122.selectList(Unknown Source)
    org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
    org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
    org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
    org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    com.sun.proxy.$Proxy145.selectIdByDesc(Unknown Source)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    com.sun.proxy.$Proxy146.select(Unknown Source)
    <br>
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:635)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
    org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
    org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
    org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
    org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
    org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
    org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    java.lang.Thread.run(Thread.java:748)

## 原因 ##

查看执行流程会发现：  
![在这里插入图片描述][618a2e139d96400d9f9f7d0817e22218.png]

**Mybatis拦截器方法识别到配置中参数`supportMethodsArguments` 为`ture`时会分页处理。**

源码如下：

//是否支持接口参数来传递分页参数，默认false
    private boolean supportMethodsArguments = false;

/**
     * Mybatis拦截器方法
     *
     * @param invocation 拦截器入参
     * @return 返回执行结果
     * @throws Throwable 抛出异常
     */
    private Object _processPage(Invocation invocation) throws Throwable {
        
        final Object[] args = invocation.getArgs();
        Page page = null;
        //支持方法参数时，会先尝试获取Page
        if (supportMethodsArguments) {
        
            page = getPage(args);
        }
        //分页信息
        RowBounds rowBounds = (RowBounds) args[2];
        //支持方法参数时，如果page == null就说明没有分页条件，不需要分页查询
        if ((supportMethodsArguments && page == null)
                //当不支持分页参数时，判断LocalPage和RowBounds判断是否需要分页
                || (!supportMethodsArguments && SqlUtil.getLocalPage() == null && rowBounds == RowBounds.DEFAULT)) {
        
            return invocation.proceed();
        } else {
        
            //不支持分页参数时，page==null，这里需要获取
            if (!supportMethodsArguments && page == null) {
        
                page = getPage(args);
            }
            return doProcessPage(invocation, page, args);
        }
    }

/**
     * 对象中获取分页参数
     *
     * @param params
     * @return
     */
    public static <T> Page<T> getPageFromObject(Object params) {
        
        int pageNum;
        int pageSize;
        MetaObject paramsObject = null;
        if (params == null) {
        
            throw new NullPointerException("无法获取分页查询参数!");
        }
        if (hasRequest && requestClass.isAssignableFrom(params.getClass())) {
        
            try {
        
                paramsObject = SystemMetaObject.forObject(getParameterMap.invoke(params, new Object[]{
        }));
            } catch (Exception e) {
        
                //忽略
            }
        } else {
        
            paramsObject = SystemMetaObject.forObject(params);
        }
        if (paramsObject == null) {
        
            throw new NullPointerException("分页查询参数处理失败!");
        }
        Object orderBy = getParamValue(paramsObject, "orderBy", false);
        boolean hasOrderBy = false;
        if (orderBy != null && orderBy.toString().length() > 0) {
        
            hasOrderBy = true;
        }
        try {
        
            Object _pageNum = getParamValue(paramsObject, "pageNum", hasOrderBy ? false : true);
            Object _pageSize = getParamValue(paramsObject, "pageSize", hasOrderBy ? false : true);
            if (_pageNum == null || _pageSize == null) {
        
                Page page = new Page();
                page.setOrderBy(orderBy.toString());
                page.setOrderByOnly(true);
                return page;
            }
            pageNum = Integer.parseInt(String.valueOf(_pageNum));
            pageSize = Integer.parseInt(String.valueOf(_pageSize));
        } catch (NumberFormatException e) {
        
            throw new IllegalArgumentException("分页参数不是合法的数字类型!");
        }
        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;
    }

## 解决 ##

### 方式一： ###

> 在执行的sql中，不要使用pageSize和pageNum命名，可以换成startIndex和maxCount。

### 方式二： ###

修改配置参数为false

pagehelper.supportMethodsArguments=false

### 方式三： ###

如果不是因为配置参数问题，有可能是Page缓存问题导致：  
解决方式：  
**执行sql之前调用PgageHelper.clearPage()清理一下page缓存。**

PgageHelper.clearPage()；

![在这里插入图片描述][5e1b731011d04f1b8b5bb34319048090.png]

点赞 收藏 关注  
在心碎中认清遗憾 生命漫长也短暂

[618a2e139d96400d9f9f7d0817e22218.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/541fa6f7746642809ffbd2a727a58e2e.png
[5e1b731011d04f1b8b5bb34319048090.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/cd202254be2641fcadbbc84313cbf408.png