第Mybatis執(zhí)行SQL時多了一個limit的問題及解決方法目錄報錯原因解決方式一：方式二：方式三：

報錯

checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenearLIMIT10atline5

代碼如下：

mapper層方法：

ListIntegerselect(@Param("pageNum")IntegerpageNum,@Param("pageSize")IntegerpageSize);

xml代碼：

selectid="select"resultType="java.lang.Integer"

SELECTid

FROMtest

LIMIT#{pageNum},#{pageSize}

/select

完整報錯如下：

;badSQLgrammar[];nestedexceptioniscom.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenearLIMIT10atline5，位置信息：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)

xy.$Proxy122.selectList(UnknownSource)

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)

xy.$Proxy145.selectIdByDesc(UnknownSource)

sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

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.ReflectiveMethodIceed(ReflectiveMethodInvocation.java:157)

org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)

org.springframework.aop.framework.ReflectiveMethodIceed(ReflectiveMethodInvocation.java:179)

org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)

xy.$Proxy146.select(UnknownSource)

br

sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

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.FrameworkScessRequest(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.ApplicationFilterCernalDoFilter(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.AbstractProcessorLcess(AbstractProcessorLight.java:66)

org.apache.coyote.AbstractProtocol$ConnectionHcess(AbstractProtocol.java:861)

.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)

.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ā)現：

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

源碼如下：

//是否支持接口參數來傳遞分頁參數，默認false

privatebooleansupportMethodsArguments=false;

/**

*Mybatis攔截器方法

*@paraminvocation攔截器入參

*@return返回執(zhí)行結果

*@throwsThrowable拋出異常

privateObject_processPage(Invocationinvocation)throwsThrowable{

finalObject[]args=invocation.getArgs();

Pagepage=null;

//支持方法參數時，會先嘗試獲取Page

if(supportMethodsArguments){

page=getPage(args);

//分頁信息

RowBoundsrowBounds=(RowBounds)args[2];

//支持方法參數時，如果page==null就說明沒有分頁條件，不需要分頁查詢

if((supportMethodsArgumentspage==null)

//當不支持分頁參數時，判斷LocalPage和RowBounds判斷是否需要分頁

||(!supportMethodsArgumentsSqlUtil.getLocalPage()==nullrowBounds==RowBounds.DEFAULT)){

returnceed();

}else{

//不支持分頁參數時，page==null，這里需要獲取

if(!supportMethodsArgumentspage==null){

page=getPage(args);

returndoProcessPage(invocation,page,args);

/**

*對象中獲取分頁參數

*@paramparams

*@return

publicstaticTPageTgetPageFromObject(Objectparams){

intpageNum;

intpageSize;

MetaObjectparamsObject=null;

if(params==null){

thrownewNullPointerException("無法獲取分頁查詢參數!");

if(hasRequestrequestClass.isAssignableFrom(params.getClass())){

try{

paramsObject=SystemMetaObject.forObject(getParameterMap.invoke(params,newObject[]{}));

}catch(Exceptione){

//忽略

}else{

paramsObject=SystemMetaObject.forObject(params);

if(paramsObject==null){

thrownewNullPointerException("分頁查詢參數處理失敗!");

ObjectorderBy=getParamValue(paramsObject,"orderBy",false);

booleanhasOrderBy=false;

if(orderBy!=nullorderBy.toString().length()0){

hasOrderBy=true;

try{

Object_pageNum=getParamValue(paramsObject,"pageNum",hasOrderByfalse:true);

Object_pageSize=getParamValue(paramsObject,"pageSize",hasOrderByfalse:true);

if(_pageNum==null||_pageSize==null){

Pagepage=newPage();

page.setOrderBy(or