報錯

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)

原因

查看執(zhí)行流程會發(fā)現(xiàn)：

Mybatis攔截器方法識別到配置中參數(shù)supportMethodsArguments 為ture時會分頁處理。

源碼如下：

//是否支持接口參數(shù)來傳遞分頁參數(shù)，默認false
private boolean supportMethodsArguments = false;

/**
 * Mybatis攔截器方法
 *
 * @param invocation 攔截器入參
 * @return 返回執(zhí)行結果
 * @throws Throwable 拋出異常
 */
private Object _processPage(Invocation invocation) throws Throwable {
    final Object[] args = invocation.getArgs();
    Page page = null;
    //支持方法參數(shù)時，會先嘗試獲取Page
    if (supportMethodsArguments) {
        page = getPage(args);
    }
    //分頁信息
    RowBounds rowBounds = (RowBounds) args[2];
    //支持方法參數(shù)時，如果page == null就說明沒有分頁條件，不需要分頁查詢
    if ((supportMethodsArguments && page == null)
            //當不支持分頁參數(shù)時，判斷LocalPage和RowBounds判斷是否需要分頁
            || (!supportMethodsArguments && SqlUtil.getLocalPage() == null && rowBounds == RowBounds.DEFAULT)) {
        return invocation.proceed();
    } else {
        //不支持分頁參數(shù)時，page==null，這里需要獲取
        if (!supportMethodsArguments && page == null) {
            page = getPage(args);
        }
        return doProcessPage(invocation, page, args);
    }
}

/**
 * 對象中獲取分頁參數(shù)
 *
 * @param params
 * @return
 */
public static <T> Page<T> getPageFromObject(Object params) {
    int pageNum;
    int pageSize;
    MetaObject paramsObject = null;
    if (params == null) {
        throw new NullPointerException("無法獲取分頁查詢參數(shù)!");
    }
    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("分頁查詢參數(shù)處理失敗!");
    }
    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("分頁參數(shù)不是合法的數(shù)字類型!");
    }
    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;
}

解決

方式一：

在執(zhí)行的sql中，不要使用pageSize和pageNum命名，可以換成startIndex和maxCount。

方式二：

修改配置參數(shù)為false

pagehelper.supportMethodsArguments=false

方式三：

如果不是因為配置參數(shù)問題，有可能是Page緩存問題導致：
解決方式：
執(zhí)行sql之前調用PgageHelper.clearPage()清理一下page緩存。

PgageHelper.clearPage()；

到此這篇關于Mybatis執(zhí)行SQL時多了一個limit的文章就介紹到這了,更多相關Mybatis執(zhí)行SQL多一個limit內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論