您的位置：首页 > 科技 > IT业 > ShardingSphere跨表查询报错

ShardingSphere跨表查询报错

2024/10/5 14:12:20 来源：https://blog.csdn.net/weixin_52116015/article/details/139596939  浏览:  次   关键词：ShardingSphere跨表查询报错

目录

    • 一、场景简介
    • 二、报错信息
    • 三、SQL
    • 四、原因
    • 五、解决
      • 方法一、调整SQL，不使用子查询
      • 方法二、将子查询的SQL独立出来，后续连接逻辑由代码处理

一、场景简介

1、使用ShardingSphere按月份进行分表

2、单月查询正常（单表）

3、跨月查询报错（跨表）

二、报错信息

### Error querying database.  Cause: java.lang.NullPointerException
### The error may exist in file [/home/xxx/devtest/robot/robot-admin-8073/webapps/RobotManagerSvr/WEB-INF/classes/mappings/RobotQuestionMapper.xml]
### The error may involve com.xxx.mapper.RobotQuestionMapper.getQuestionReportList
### The error occurred while handling results
### SQL: SELECT   t1.quesId,   t1.questionMsg,   t2.qName AS questionType,   count( t4.sId ) AS satCount,   count( t4.flag = '1' OR NULL ) AS solveCount,   count( t4.flag = '0' OR NULL ) AS unSolveCount,   ROUND(IFNULL(count( t4.flag = '1' OR NULL ) / count( t4.sId ), 0) * 100, 2) AS solvePercent,         t6.browseQty   FROM   t_robot_question AS t1   LEFT JOIN t_robot_questiontype AS t2 ON t1.qId = t2.qId   LEFT JOIN t_robot_messageReplay AS t3 ON t1.quesId = t3.quesId   LEFT JOIN t_robot_SolveRec AS t4 ON t3.recId = t4.recId   LEFT JOIN t_robot_sessionmsg AS t5 ON t3.SessionID = t5.SessionID   LEFT JOIN (             SELECT quesId, sum(IFNULL( hitQty, 0 )) AS browseQty             FROM t_robot_ques_statistics4ques             WHERE companyId = ? GROUP BY quesId         ) AS t6 ON t1.quesId = t6.quesId   WHERE   t1.companyId = ?   AND t3.CreateTime BETWEEN ? AND ?           GROUP BY   t1.quesId,   t1.questionMsg,   t2.qName         HAVING t1.quesId IS NOT NULL   ORDER BY t3.CreateTime DESC
### Cause: java.lang.NullPointerException
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.NullPointerException
### The error may exist in file [/home/xxx/devtest/robot/robot-admin-8073/webapps/RobotManagerSvr/WEB-INF/classes/mappings/RobotQuestionMapper.xml]
### The error may involve com.xxx.mapper.RobotQuestionMapper.getQuestionReportList
### The error occurred while handling results
### SQL: SELECT   t1.quesId,   t1.questionMsg,   t2.qName AS questionType,   count( t4.sId ) AS satCount,   count( t4.flag = '1' OR NULL ) AS solveCount,   count( t4.flag = '0' OR NULL ) AS unSolveCount,   ROUND(IFNULL(count( t4.flag = '1' OR NULL ) / count( t4.sId ), 0) * 100, 2) AS solvePercent,         t6.browseQty   FROM   t_robot_question AS t1   LEFT JOIN t_robot_questiontype AS t2 ON t1.qId = t2.qId   LEFT JOIN t_robot_messageReplay AS t3 ON t1.quesId = t3.quesId   LEFT JOIN t_robot_SolveRec AS t4 ON t3.recId = t4.recId   LEFT JOIN t_robot_sessionmsg AS t5 ON t3.SessionID = t5.SessionID   LEFT JOIN (             SELECT quesId, sum(IFNULL( hitQty, 0 )) AS browseQty             FROM t_robot_ques_statistics4ques             WHERE companyId = ? GROUP BY quesId         ) AS t6 ON t1.quesId = t6.quesId   WHERE   t1.companyId = ?   AND t3.CreateTime BETWEEN ? AND ?           GROUP BY   t1.quesId,   t1.questionMsg,   t2.qName         HAVING t1.quesId IS NOT NULL   ORDER BY t3.CreateTime DESC
### Cause: java.lang.NullPointerExceptionat org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)at com.sun.proxy.$Proxy99.selectList(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)at com.sun.proxy.$Proxy161.getQuestionReportList(Unknown Source)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)at com.sun.proxy.$Proxy162.getQuestionReportList(Unknown Source)at com.xxx.service.Impl.RobotQuestionServiceImpl.getQuestionReportList(RobotQuestionServiceImpl.java:656)at com.xxx.service.Impl.RobotQuestionServiceImpl$$FastClassBySpringCGLIB$$41e86926.invoke(<generated>)at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684)at com.xxx.service.Impl.RobotQuestionServiceImpl$$EnhancerBySpringCGLIB$$37dbcb67.getQuestionReportList(<generated>)at com.xxx.controller.RobotQuestionController.getQuestionReportList(RobotQuestionController.java:1094)at com.xxx.controller.RobotQuestionController$$FastClassBySpringCGLIB$$abadd88e.invoke(<generated>)at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:55)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)at com.xxx.controller.RobotQuestionController$$EnhancerBySpringCGLIB$$a3088a72.getQuestionReportList(<generated>)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:189)at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800)at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908)at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at com.xxx.filter.XssFilter.doFilter(XssFilter.java:53)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at com.xxx.jwt.JwtAuthenticationTokenFilter.doFilterInternal(JwtAuthenticationTokenFilter.java:145)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357)at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:130)at org.springframework.boot.web.servlet.support.ErrorPageFilter.access$000(ErrorPageFilter.java:66)at org.springframework.boot.web.servlet.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:105)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:123)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)at java.lang.Thread.run(Thread.java:750)

三、SQL

<select id="getQuestionReportList" resultType="com.alibaba.fastjson.JSONObject">SELECTt1.quesId,t1.questionMsg,t2.qName AS questionType,count( t4.sId ) AS satCount,count( t4.flag = '1' OR NULL ) AS solveCount,count( t4.flag = '0' OR NULL ) AS unSolveCount,ROUND(IFNULL(count( t4.flag = '1' OR NULL ) / count( t4.sId ), 0) * 100, 2) AS solvePercent,t6.browseQtyFROMt_robot_question AS t1LEFT JOIN t_robot_questiontype AS t2 ON t1.qId = t2.qIdLEFT JOIN t_robot_messageReplay AS t3 ON t1.quesId = t3.quesIdLEFT JOIN t_robot_SolveRec AS t4 ON t3.recId = t4.recIdLEFT JOIN t_robot_sessionmsg AS t5 ON t3.SessionID = t5.SessionIDLEFT JOIN (SELECT quesId, sum(IFNULL( hitQty, 0 )) AS browseQtyFROM t_robot_ques_statistics4quesWHERE companyId = #{companyId} GROUP BY quesId) AS t6 ON t1.quesId = t6.quesIdWHEREt1.companyId = #{companyId}AND t3.CreateTime BETWEEN #{beginDate} AND #{endDate}AND t4.CreateTime BETWEEN #{beginDate} AND #{endDate}AND t5.CreateTime BETWEEN #{beginDate} AND #{endDate}GROUP BYt1.quesId,t1.questionMsg,t2.qNameORDER BY t3.CreateTime DESC
</select>

四、原因

是子查询的导致（具体原因不明）

在这里插入图片描述

五、解决

方法一、调整SQL，不使用子查询

方法二、将子查询的SQL独立出来，后续连接逻辑由代码处理

1、去除子查询

在这里插入图片描述

2、将子查询封装为mapp方法

在这里插入图片描述

3、由代码完成连接

在这里插入图片描述

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

最新新闻

热搜词

未来十年职场的十大发展趋势 HUMAN经验 HTML Emoji 黑龙江“智慧林草”顶层规划方案 bose蓝牙音箱使用说明_蓝牙音箱怎么使用 蓝牙音箱使用方法介绍【详解】 英语不好可以学编程吗?

声明：本站所有新闻及新闻图片来源于其他网站，如有侵权，请及时联系我们！

客户服务 | 关于我们 | 版权声明

版权所有：

Copyright 2024 尧图网 All Rights Reserved.QQ:809451989