一、前言
关于使用mybatis报"org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3"错误异常,详情日志如下
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3@b@ at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:76) ~[mybatis-spring-1.2.3-1.2.3.jar:1.2.3]@b@ at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399) ~[mybatis-spring-1.2.3-1.2.3.jar:1.2.3]@b@ at com.sun.proxy.$Proxy37.selectOne(Unknown Source) ~[?:?]@b@ at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:165) ~[mybatis-spring-1.2.3-1.2.3.jar:1.2.3]@b@ at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:69) ~[mybatis-3.3.0-3.3.0.jar:3.3.0]@b@ at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) ~[mybatis-3.3.0-3.3.0.jar:3.3.0]@b@ at com.sun.proxy.$Proxy58.getPageCount(Unknown Source) ~[?:?]@b@ at com.xwood.portal.info.service.impl.NewInfoServiceImpl.getPageTotal(NewInfoServiceImpl.java:56) ~[bin/:?]@b@ at com.xwood.portal.info.service.impl.NewInfoServiceImpl.getPageTotal(NewInfoServiceImpl.java:1) ~[bin/:?]@b@ at com.xwood.portal.info.web.controller.NewInfoInfoController.searchEntry(NewInfoInfoController.java:57) [bin/:?]@b@ at com.xwood.portal.info.web.controller.NewInfoInfoController$$FastClassBySpringCGLIB$$3b8de65a.invoke(<generated>) [spring-core-3.2.13.RELEASE-3.2.13.RELEASE.jar:?]@b@ at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [spring-core-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:700) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:91) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at com.xwood.portal.info.aop.LogAppendAdvice.logController(LogAppendAdvice.java:47) [bin/:?]@b@ at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_11]@b@ at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_11]@b@ at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_11]@b@ at java.lang.reflect.Method.invoke(Method.java:483) ~[?:1.8.0_11]@b@ at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:633) [spring-aop-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at com.xwood.portal.info.web.controller.NewInfoInfoController$$EnhancerBySpringCGLIB$$bdd0cc97.searchEntry(<generated>) [spring-core-3.2.13.RELEASE-3.2.13.RELEASE.jar:?]@b@ at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_11]@b@ at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_11]@b@ at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_11]@b@ at java.lang.reflect.Method.invoke(Method.java:483) ~[?:1.8.0_11]@b@ at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) [spring-web-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) [spring-web-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) [spring-webmvc-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:743) [spring-webmvc-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:672) [spring-webmvc-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:82) [spring-webmvc-3.2.13.RELEASE-3.2.13.RELEASE.jar:3.2.13.RELEASE]@b@ at com.xwood.portal.info.web.DefWebRequestHandler.handleRequest(DefWebRequestHandler.java:61) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.DefaultFilterChain.doFinal(DefaultFilterChain.java:64) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.DefaultFilterChain.doFilter(DefaultFilterChain.java:57) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.common.filter.XssSecurityFilter.doFilter(XssSecurityFilter.java:49) [bin/:?]@b@ at com.xwood.portal.info.web.filter.DefaultFilterChain.doFilter(DefaultFilterChain.java:52) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.impl.PostJsonToFormFilter.doFilter(PostJsonToFormFilter.java:38) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.DefaultFilterChain.doFilter(DefaultFilterChain.java:52) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.DefWebDispatcher.handleRequest(DefWebDispatcher.java:134) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.context.JARContextBean.handleWebRequest.context.ontextBean.java:170) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.AppWebRequestHandler.handleRequest(AppWebRequestHandler.java:44) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.DefaultFilterChain.doFinal(DefaultFilterChain.java:64) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.DefaultFilterChain.doFilter(DefaultFilterChain.java:57) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.impl.CommonWebFilter.doFilter(CommonWebFilter.java:46) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.filter.DefaultFilterChain.doFilter(DefaultFilterChain.java:52) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.web.DefAppWebDispatcher.dispatch(DefAppWebDispatcher.java:80) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.AppContextBean.handleWebRequest(AppContextBean.java:182) [App-5.2.6.jar:5.2.6]@b@ at com.xwood.portal.info.protocol.web.AppServlet.service(AppServlet.java:63) [App-5.2.6.jar:5.2.6]@b@ at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:684) [jetty-servlet-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1496) [jetty-servlet-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at com.xwood.portal.info.protocol.jetty.JettyResourceFilter.doFilter(JettyResourceFilter.java:73) [App-5.2.6.jar:5.2.6]@b@ at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1467) [jetty-servlet-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:501) [jetty-servlet-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:229) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1086) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:429) [jetty-servlet-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1020) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.Server.handle(Server.java:370) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:494) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:982) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:1043) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:865) [jetty-http-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240) [jetty-http-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82) [jetty-server-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:696) [jetty-io-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:53) [jetty-io-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608) [jetty-util-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543) [jetty-util-8.1.16.v20140903-8.1.16.jar:8.1.16.v20140903]@b@ at java.lang.Thread.run(Thread.java:745) [?:1.8.0_11]@b@Caused by: org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 3@b@ at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77) ~[mybatis-3.3.0-3.3.0.jar:3.3.0]@b@ at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_11]@b@ at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_11]@b@ at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_11]@b@ at java.lang.reflect.Method.invoke(Method.java:483) ~[?:1.8.0_11]@b@ at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386) ~[mybatis-spring-1.2.3-1.2.3.jar:1.2.3]@b@ ... 77 more
二、解决方法
1.原因分析 - mybatis配置文件getPageCount因group by返回多条记录,对于NewInfoDAO.java的方法返回是java.lang.Integer整型类型
<?xml version="1.0" encoding="UTF-8" ?> @b@<!DOCTYPE mapper @b@ PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" @b@ "http://mybatis.org/dtd/mybatis-3-mapper.dtd">@b@<mapper namespace="com.xwood.portal.info.dao.NewInfoDAO"> @b@ @b@ <!-- 获取总记录数 -->@b@ <select id="getPageCount" resultType="java.lang.Integer" parameterType="java.util.Map">@b@ SELECT COUNT(1) FROM PMS_NEWINFO_INFO @b@ <where> DATA_STATUS ='1' @b@ @b@ <if test="startCreateDate != null and startCreateDate != ''">@b@ and CREATED_DATE >= to_char(to_date(#{startCreateDate},'yyyy-mm-dd'),'yyyymmdd')@b@ </if>@b@ <if test="endCreateDate != null and endCreateDate != ''">@b@ and CREATED_DATE <= to_char(to_date(#{endCreateDate}, 'yyyy-mm-dd'),'yyyymmdd')@b@ </if>@b@ </where>@b@ group by FOLDER_NAME,WEB_SITE_ID@b@ </select>@b@ @b@</mapper>
public interface NewInfoDAO extends BaseMapper {@b@@b@ @b@ /**@b@ * 获取分页总记录数@b@ * @param parameterObj 条件入参@b@ * @throws Exception@b@ * @return@b@ */@b@ public Integer getPageCount(Map<?,?> parameterObj) throws Exception;@b@ @b@ @b@}
2. 解决方法 - 删除group by FOLDER_NAME,WEB_SITE_ID,这样对应返回NewInfoDAO的getPageCount方法的整型数据
<?xml version="1.0" encoding="UTF-8" ?> @b@<!DOCTYPE mapper @b@ PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" @b@ "http://mybatis.org/dtd/mybatis-3-mapper.dtd">@b@<mapper namespace="com.xwood.portal.info.dao.NewInfoDAO"> @b@ @b@ <!-- 获取总记录数 -->@b@ <select id="getPageCount" resultType="java.lang.Integer" parameterType="java.util.Map">@b@ SELECT COUNT(1) FROM PMS_NEWINFO_INFO @b@ <where> DATA_STATUS ='1' @b@ @b@ <if test="startCreateDate != null and startCreateDate != ''">@b@ and CREATED_DATE >= to_char(to_date(#{startCreateDate},'yyyy-mm-dd'),'yyyymmdd')@b@ </if>@b@ <if test="endCreateDate != null and endCreateDate != ''">@b@ and CREATED_DATE <= to_char(to_date(#{endCreateDate}, 'yyyy-mm-dd'),'yyyymmdd')@b@ </if>@b@ </where> @b@ </select> @b@ @b@</mapper>