Java多租户应用自定义Repository查询特定租户偶发错误求助

调用自定义Repository查询方法时出现偶发错误，仅特定租户触发，已排查数据库数据无缺失或异常。

错误日志 #

2023-11-20 10:49:40,927 ERROR c.p.v.m.ControllerAdvisor [http-nio-5050-exec-4] An error occurred while searching, please try again.
com.papertrl.vportal.exception.PaperTrlCheckedException: An error occurred while searching, please try again.
        at com.papertrl.vportal.service.impl.ExpenseServiceImpl.searchExpenseListGrid(ExpenseServiceImpl.java:839)
        at com.papertrl.vportal.service.impl.ExpenseServiceImpl$$FastClassBySpringCGLIB$$4876cb67.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
        at com.papertrl.vportal.service.impl.ExpenseServiceImpl$$EnhancerBySpringCGLIB$$9bf24dbd.searchExpenseListGrid(<generated>)
        at com.papertrl.vportal.service.impl.ExpenseServiceImpl$$FastClassBySpringCGLIB$$4876cb67.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
        at com.papertrl.vportal.service.impl.ExpenseServiceImpl$$EnhancerBySpringCGLIB$$e4b0f6ab.searchExpenseListGrid(<generated>)
        at com.papertrl.vportal.controller.ExpenseController.searchExpenseListGrid(ExpenseController.java:53)
        at com.papertrl.vportal.controller.ExpenseController$$FastClassBySpringCGLIB$$3a92de4d.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
        at com.papertrl.vportal.controller.ExpenseController$$EnhancerBySpringCGLIB$$53f8bf11.searchExpenseListGrid(<generated>)
        at sun.reflect.GeneratedMethodAccessor1085.invoke(Unknown Source)
        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:190)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:126)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:90)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:118)
        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:158)
        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 org.springframework.security.oauth2.provider.authentication.OAuth2AuthenticationProcessingFilter.doFilter(OAuth2AuthenticationProcessingFilter.java:176)
        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.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:92)
        at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:77)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        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:119)
        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:358)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        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:748)

相关代码 #

Service类 #

@Override
public ResponseEntity<Object> searchExpenseListGrid(GridSearchDtoV2 gridSearchDtoV2) throws PaperTrlCheckedException {
    try {
        GridSearchResultContainerDtoV2 resultContainerDtoV2 = new GridSearchResultContainerDtoV2();
        addRestrictionsToTheDocument(gridSearchDtoV2);
        resultContainerDtoV2.setData(expenseMasterRepository.searchExpense(gridSearchDtoV2));
             resultContainerDtoV2.setTotalRecords(expenseMasterRepository.getTotalResult(gridSearchDtoV2));
        return new ResponseEntity(resultContainerDtoV2, HttpStatus.OK);
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new PaperTrlCheckedException(ExceptionMessages.SEARCH_FAILED);
    }
}

自定义Repository方法 #

@Override
public List<ExpenseGridSearchResultDto> searchExpense(GridSearchDtoV2 gridSearchDtoV2) throws ParseException {
    String sql = "SELECT new com.papertrl.vportal.dto.grid.ExpenseGridSearchResultDto(expense.attachmentId,expense.id, expense.id,expense.reportName,"
                + "expense.createdByName,expense.createdOn,automation.automationName,expense.approvalUserName,expense.approvalGroupName,expense.totalAmount,"
                + "expense.status,expense.startFrom,expense.endDate,expense.exportStatus,expense.approvalUser,expense.approvalGroup, expense.vendorId," +
                " expense.paymentStatus, expense.balanceAmount, dept.departmentName,expense.paidAmount) "
                + "FROM VpExpenseMst expense LEFT JOIN CommonAutomationMst automation ON automation.id=expense.automationId"
                + " LEFT JOIN UmmApprovalGroup appGroup ON appGroup.id=expense.approvalGroup"
                + " LEFT JOIN CommonDepartment dept ON dept.id=expense.departmentId"
                + " LEFT JOIN VpVendorMst vendor ON vendor.id=expense.vendorId"
                + " WHERE expense.status<>'" + CommonConstants.STATUS_DELETED + "' "
                + " AND (expense.status<>'" + STATUS_DRAFT + "' OR (expense.status='" + STATUS_DRAFT + "' AND expense.createdBy=:" + PARAM_CREATED_BY_ID + ")) ";

    sql = setConditions(gridSearchDtoV2, sql);
    sql = setOrderBy(gridSearchDtoV2, sql);
    Query query = entityManager.createQuery(sql);
    setParameters(gridSearchDtoV2, query);
    query.setParameter(PARAM_CREATED_BY_ID, SecurityContextHolder.getContext().getAuthentication().getName());

    query.setFirstResult(gridSearchDtoV2.getFirst());
    query.setMaxResults(gridSearchDtoV2.getRows());
    return query.getResultList();
}

排查思路及解决方案 #

排查方向 #

1. 完善日志信息：当前仅抛出自定义异常，丢失了原始错误的根因。需要修改Service层的异常处理逻辑，记录原始异常的完整堆栈信息，才能定位具体错误类型（比如空指针、SQL语法错误、数据类型不匹配等）。
2. 验证租户隔离逻辑：检查addRestrictionsToTheDocument方法是否正确添加了租户过滤条件，确保特定租户的查询仅访问自身数据，避免跨租户数据关联时出现异常。
3. 检查特殊数据值：针对报错租户，重点排查关联表的字段值：
   • automation.automationName、dept.departmentName等字段是否存在NULL值，导致DTO构造时出现空指针或类型转换错误
   • expense.createdBy的值与SecurityContextHolder获取的用户标识是否存在格式差异（如大小写、长度、特殊字符）
4. 校验动态SQL拼接：查看setConditions和setOrderBy方法的实现，确认是否会针对特定租户的查询条件拼接出非法SQL（比如未处理空参数、特殊字符转义错误）。同时开启SQL日志打印，获取报错时执行的完整SQL语句。
5. 线程安全检查：多租户场景下，SecurityContextHolder获取用户信息可能存在线程复用导致的上下文污染，需确认请求线程的用户与租户绑定关系是否正确。

解决方案 #