问题描述
我有以下方法给我一个例外。
private double doFullCalculation(Iterable<OfferToCatalog> startOfferCatalogs, Iterable<OfferToCatalog> endOfferCatalogs){
double fullWeeksSales=0d;
int startWeeks=0;
int startIteration =0;
for(OfferToCatalog startOfferToCatalog: startOfferCatalogs){
List<OfferWeeks> startOfferWeeks = offerWeeksRepository.getWeeksForCatalogNumber(startOfferToCatalog.getOfferToCatalogKey().getBusinessUnitNumber()+""+startOfferToCatalog.getOfferToCatalogKey().getCatalogNumber()+""+startOfferToCatalog.getOfferToCatalogKey().getCatalogVersion());
startIteration++;
if(startIteration==1){
startWeeks=startOfferWeeks.size();
}
fullWeeksSales += addStartWeeks(startWeeks, startOfferWeeks);
}
for(OfferToCatalog endOfferToCatalog: endOfferCatalogs){
List<OfferWeeks> endOfferWeeks = offerWeeksRepository.getWeeksForCatalogNumber(endOfferToCatalog.getOfferToCatalogKey().getBusinessUnitNumber()+""+endOfferToCatalog.getOfferToCatalogKey().getCatalogNumber()+""+endOfferToCatalog.getOfferToCatalogKey().getCatalogVersion());
fullWeeksSales += addEndWeeks(startWeeks, endOfferWeeks);
}
return fullWeeksSales;
}
我似乎只在第二个for循环（获取endOfferWeeks的部分）中出现错误。 它调用的方法与startOfferWeeks for循环中的方法完全相同，但是出现以下异常：
java.sql.SQLException: An undefined column name was detected.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:408)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:380)
at com.ibm.as400.access.JDServerRow.findField(JDServerRow.java:482)
at com.ibm.as400.access.AS400JDBCResultSet.findColumn(AS400JDBCResultSet.java:557)
at com.ibm.as400.access.AS400JDBCResultSet.getBigDecimal(AS400JDBCResultSet.java:2239)
at weblogic.jdbc.wrapper.ResultSet_com_ibm_as400_access_AS400JDBCResultSet.getBigDecimal(Unknown Source)
at org.hibernate.type.descriptor.sql.DecimalTypeDescriptor$2.doExtract(DecimalTypeDescriptor.java:62)
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:254)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:250)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:230)
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:331)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2283)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1527)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1455)
at org.hibernate.loader.Loader.getRow(Loader.java:1355)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:611)
at org.hibernate.loader.Loader.doQuery(Loader.java:829)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2542)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:96)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:61)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:95)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:85)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:337)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy131.getWeeksForCatalogNumber(Unknown Source)
at com.oriental.oft.service.impl.OfferServiceImpl.doFullCalculation(OfferServiceImpl.java:340)
at com.oriental.oft.service.impl.OfferServiceImpl.processFTF(OfferServiceImpl.java:297)
at com.oriental.oft.service.impl.OfferServiceImpl.getForecastJSON(OfferServiceImpl.java:588)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy133.getForecastJSON(Unknown Source)
at com.oriental.oft.web.controller.AuthenticationController.getForecastJSON(AuthenticationController.java:76)
at com.oriental.oft.web.controller.AuthenticationController$$FastClassByCGLIB$$7a6d41ea.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
at com.oriental.oft.web.controller.AuthenticationController$$EnhancerByCGLIB$$803ec396.getForecastJSON(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3592)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2202)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2108)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1432)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
>
我已经看过有关此问题的其他文章，但是这似乎有所不同，因为查询在第一次执行时似乎运行良好。 我只是在此方法的第二部分中遇到了问题。 我已经查看了作为参数传递的内容，并且在使用DB Visualizer时查询运行良好。
如果有用的话，这就是我的存储库的外观。
public interface OfferWeeksRepository extends CrudRepository<OfferWeeks, String>,
JpaRepository<OfferWeeks, String>, JpaSpecificationExecutor<OfferWeeks> {
@Query(value="select a.FCLYERWK, cast(sum(a.RMSALES) as decimal(18,5)) " +
"from DWALCTNFL as a " +
"inner join ( " +
"select b.ITMNBR, b.CATPFX " +
"from OECATPAG b " +
"where (b.PAGNBR >= 0 and b.PAGNBR < 600) AND " +
"(b.PHOSIZ+b.TXTSIZ+b.WHTSPCSIZ) > 0 AND " +
"Rtrim(CAST(b.BUSUNTNBR as varchar(5)))||Rtrim(b.CATNBR)||Rtrim(b.CATVER) = :catalogNumber " +
"group by b.itmnbr, b.catpfx ) as c " +
"on (a.ITMNBR=c.ITMNBR and a.ITMPFX=c.CATPFX) " +
"where trim(a.ITMCATNBR) = :catalogNumber " +
"group by a.FCLYERWK " +
"order by a.FCLYERWK ", nativeQuery=true)
List<OfferWeeks> getWeeksForCatalogNumber(@Param("catalogNumber") String catalogNumber);
}
我期望从查询中得到的是：
201344 239.70000
201345 6045.34500
201346 8885.38200
201347 19087.47600
201348 18076.79600
201349 22518.06900
201350 24897.04300
201351 21699.63600
201352 12532.93400
201401 13964.03500
201402 10197.32900
201403 7687.81300
201404 3980.19600
201405 4828.38000
201406 3592.61600
201407 2069.02900
201408 1612.69500
201409 3280.60700
201410 77.40000
201411 25.65700
201412 847.53000
201413 594.59100
201414 391.50000
201415 2292.53200
201416 0.00000
1楼
我能够找出问题所在。 在“通过”失败的方法时，这是查询唯一返回数据的时间。 由于第二列是数据的总和，因此如果没有我这样声明，它就不会返回命名列。
我的实体映射正在寻找第二列的命名列，但是由于在结果集中未命名该列，所以我的程序不知道如何处理数据。
@Entity
@Table(name = "OECATPAG")
public class OfferWeeks implements Serializable{
String week;
BigDecimal sales;
@Id
@Column(name = "FCLYERWK", columnDefinition = "varchar2(40)")
public String getWeek() {
return week;
}
public void setWeek(String week) {
this.week = week;
}
@Column(name = "RMSALES", columnDefinition = "decimal(18,9)")
public BigDecimal getSales() {
return sales;
}
public void setSales(BigDecimal sales) {
this.sales = sales;
}
}