sprint boot + jpa 多表联查以及原生sql 日期参数传入报“ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 BINARY”错误和count计数问题。

sprint boot + jpa 多表联查以及原生sql 日期参数传入报“ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 BINARY”错误和count计数问题。

jpa在单表查询很简单，但是多表联查的时候，那就是很痛苦，尤其是A表想关联D表，无法直接关联，需要通过A关联B表，B表关联C表后才能获得D相关的数据时，就必须采用原生sql进行解决了。
而jap的@QUERY()注释使用原生sql，必须加入nativeQuery = true参数，如：

@Query(
   value = "select o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, sum(case when b.is_close = 0 then b.deal_amount else (b.stock_out_num * b.deal_amount / b.main_num) end) as totaldealamount, sum(b.order_correlation_money) as ordercorrelationmoney, o.id, o.order_status, t.code as trantype_code, cc.code as customer_category_code from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ?1 or ?2 is null) and (o.customer_id in ?3 or ?4 is null) and (o.order_type = ?5 or ?5 is null) and (o.order_code like ?6 or ?6 is null) and (o.order_date >= ?7 or ?7 is null) and (o.order_date <= ?8 or ?8 is null) and (o.sale_model in ?9 or ?10 is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code order by o.order_code desc",
   countQuery = "select count(o.id) from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ?1 or ?2 is null) and (o.customer_id in ?3 or ?4 is null) and (o.order_type = ?5 or ?5 is null) and (o.order_code like ?6 or ?6 is null) and (o.order_date >= ?7 or ?7 is null) and (o.order_date <= ?8 or ?8 is null) and (o.sale_model in ?9 or ?10 is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code",
   nativeQuery = true)
Page manyTableCorrelationFindOrderByParams(Set saleOrg, String org, Set customer, String code, String orderType, String orderCode, Date orderDate_date_start, Date orderDate_date_end, Set saleModel, String model, Pageable pageable);

但是上面代码调用后，报如下错误：

2021-12-30 16:49:09.526  WARN 27916 --- [nio-8078-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 932, SQLState: 42000
2021-12-30 16:49:09.527 ERROR 27916 --- [nio-8078-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 BINARY

2021-12-30 16:49:09.578 ERROR 27916 --- [nio-8078-exec-1] c.y.o.common.interceptor.LoggingAspect   : Exception in com.yonyou.occ.b2b.service.OrderExtService.findAllGroupByItemFinance() with cause = 'org.hibernate.exception.SQLGrammarException: could not extract ResultSet' and exception = 'could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet'

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:279)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.jpa.repository.support.CrudMethodmetadataPostProcessor$CrudMethodmetadataPopulatingMethodInterceptor.invoke(CrudMethodmetadataPostProcessor.java:138)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy264.manyTableCorrelationFindOrderByParams(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	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.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy264.manyTableCorrelationFindOrderByParams(Unknown Source)
	at com.yonyou.occ.b2b.service.OrderExtService.findAllGroupByItemFinance(OrderExtService.java:138)
	at com.yonyou.occ.b2b.service.OrderExtService$$FastClassBySpringCGLIB$$f8a5ae14.invoke()
	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.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
	at com.yonyou.ocm.common.interceptor.LoggingAspect.logAround(LoggingAspect.java:92)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	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.yonyou.occ.b2b.service.OrderExtService$$EnhancerBySpringCGLIB$$41d3febd.findAllGroupByItemFinance()
	at com.yonyou.occ.b2b.web.OrderExtController.getSaleOrder(OrderExtController.java:58)
	at com.yonyou.occ.b2b.web.OrderExtController$$FastClassBySpringCGLIB$$6bde2f5e.invoke()
	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.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
	at com.yonyou.ocm.common.interceptor.LoggingAspect.logAround(LoggingAspect.java:92)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	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.yonyou.occ.b2b.web.OrderExtController$$EnhancerBySpringCGLIB$$517768b5.getSaleOrder()
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	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:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039)
	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.doGet(frameworkServlet.java:897)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:645)
	at org.springframework.web.servlet.frameworkServlet.service(frameworkServlet.java:882)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
	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 com.yonyou.ocm.common.feign.InvocationInfoFilter.doFilter(InvocationInfoFilter.java:38)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	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:99)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	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:92)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	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.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:114)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	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:200)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:109)
	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:96)
	at org.apache.catalina.authenticator.Authenticatorbase.invoke(Authenticatorbase.java:490)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEnginevalve.invoke(StandardEnginevalve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587)
	at org.apache.tomcat.util.net.SocketProcessorbase.run(SocketProcessorbase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2167)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
	at org.hibernate.loader.Loader.doQuery(Loader.java:937)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
	at org.hibernate.loader.Loader.doList(Loader.java:2689)
	at org.hibernate.loader.Loader.doList(Loader.java:2672)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
	at org.hibernate.loader.Loader.list(Loader.java:2501)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
	at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2223)
	at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069)
	at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
	at org.hibernate.query.Query.getResultList(Query.java:132)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:201)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	... 133 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 BINARY

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at io.seata.rm.datasource.PreparedStatementProxy.lambda$executeQuery$1(PreparedStatementProxy.java:59)
	at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:69)
	at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:47)
	at io.seata.rm.datasource.PreparedStatementProxy.executeQuery(PreparedStatementProxy.java:59)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
	... 162 common frames omitted

这问题的出现搞得一脸懵逼，完全不知道为什么，因为调用如下代码，就没有出现这个错误。它们直接除了一个是原生sql，一个不是，参数及参数类型都一样：

@Query(" select h.orderCode,h.saleOrg,b.settleFinancialOrg,h.customer,h.orderType,h.orderDate,sum( case when b.isClose = 0 then b.dealAmount else (b.stockOutNum * b.dealAmount / b.mainNum) end) as totalDealAmount, sum(b.orderCorrelationMoney) as orderCorrelationMoney,h.id,h.orderStatus  from Order h join h.orderItems b where h.dr = 0 and b.dr = 0  and (h.saleOrg in ?1 or ?2 is null)   and (h.customer in ?3 or ?4 is null)  and (h.orderType = ?5 or ?5 is null)  and (h.orderCode like ?6 or ?6 is null)  and (h.orderDate >= ?7 or ?7 is null)  and (h.orderDate <= ?8 or ?8 is null)  and (h.saleModel in ?9 or ?10 is null)  and (b.isClose = 0 or b.stockOutNum > 0) and (h.orderStatus != '06') group by h.orderCode,h.saleOrg,b.settleFinancialOrg,h.customer,h.orderType,h.orderDate,h.id,h.orderStatus order by h.orderCode desc ")
Page findOrderGroupByItemFinanceOrg(Set saleOrg, String org, Set customer, String code, String orderType, String orderCode, Date orderDate_date_start, Date orderDate_date_end, Set saleModel, String model, Pageable pageable);

出现“ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 BINARY”的问题原因不明。不过解决办法倒是有，就是在方法定义入参时，给日期、时间类型的参数加上注解@Temporal(TemporalType.TIMESTAMP)，进行查询即可生效；

最后代码如下：

@Query(
    value = "select o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, sum(case when b.is_close = 0 then b.deal_amount else (b.stock_out_num * b.deal_amount / b.main_num) end) as totaldealamount, sum(b.order_correlation_money) as ordercorrelationmoney, o.id, o.order_status, t.code as trantype_code, cc.code as customer_category_code from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ?1 or ?2 is null) and (o.customer_id in ?3 or ?4 is null) and (o.order_type = ?5 or ?5 is null) and (o.order_code like ?6 or ?6 is null) and (o.order_date >= ?7 or ?7 is null) and (o.order_date <= ?8 or ?8 is null) and (o.sale_model in ?9 or ?10 is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code order by o.order_code desc",
    countQuery = "select count(o.id) from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ?1 or ?2 is null) and (o.customer_id in ?3 or ?4 is null) and (o.order_type = ?5 or ?5 is null) and (o.order_code like ?6 or ?6 is null) and (o.order_date >= ?7 or ?7 is null) and (o.order_date <= ?8 or ?8 is null) and (o.sale_model in ?9 or ?10 is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code",
    nativeQuery = true)
Page manyTableCorrelationFindOrderByParams(Set saleOrg, String org, Set customer, String code, String orderType, String orderCode, @Temporal(TemporalType.TIMESTAMP)Date orderDate_date_start, @Temporal(TemporalType.TIMESTAMP)Date orderDate_date_end, Set saleModel, String model, Pageable pageable);

在spring data jpa多表关联查询时，使用原生sql的话，很大情况下还会遇到count计数问题，即：

@Query(
   	value = "select o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, sum(case when b.is_close = 0 then b.deal_amount else (b.stock_out_num * b.deal_amount / b.main_num) end) as totaldealamount, sum(b.order_correlation_money) as ordercorrelationmoney, o.id, o.order_status, t.code as trantype_code, cc.code as customer_category_code from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ?1 or ?2 is null) and (o.customer_id in ?3 or ?4 is null) and (o.order_type = ?5 or ?5 is null) and (o.order_code like ?6 or ?6 is null) and (o.order_date >= ?7 or ?7 is null) and (o.order_date <= ?8 or ?8 is null) and (o.sale_model in ?9 or ?10 is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code order by o.order_code desc",
 	nativeQuery = true)
Page manyTableCorrelationFindOrderByParams(Set saleOrg, String org, Set customer, String code, String orderType, String orderCode, @Temporal(TemporalType.TIMESTAMP)Date orderDate_date_start, @Temporal(TemporalType.TIMESTAMP)Date orderDate_date_end, Set saleModel, String model, Pageable pageable);

后台报错: ORA-00904: “O”: 标识符无效 。经查日志发现是jpa在count计数时，使用了默认查询，这在多表联查的时候是会存在的问题，即：

select count(o) from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ? or ? is null) and (o.customer_id in ? or ? is null) and (o.order_type = ? or ? is null) and (o.order_code like ? or ? is null) and (o.order_date >= ? or ? is null) and (o.order_date <= ? or ? is null) and (o.sale_model in (?, ?) or ? is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code

这时候就需要手动在@Query添加countQuery参数，手动编写count的查询语句，即：

@Query(
	value = "select o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, sum(case when b.is_close = 0 then b.deal_amount else (b.stock_out_num * b.deal_amount / b.main_num) end) as totaldealamount, sum(b.order_correlation_money) as ordercorrelationmoney, o.id, o.order_status, t.code as trantype_code, cc.code as customer_category_code from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ?1 or ?2 is null) and (o.customer_id in ?3 or ?4 is null) and (o.order_type = ?5 or ?5 is null) and (o.order_code like ?6 or ?6 is null) and (o.order_date >= ?7 or ?7 is null) and (o.order_date <= ?8 or ?8 is null) and (o.sale_model in ?9 or ?10 is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code order by o.order_code desc",
	countQuery = "select count(o.id) from b2b_order o, b2b_order_item b, base_trantype t, base_customer c, base_customer_category cc where o.id = b.order_id and o.order_type = t.id and o.customer_id = c.id and c.customer_category_id = cc.id and nvl(o.dr, 0) = 0 and nvl(b.dr, 0) = 0 and nvl(t.dr, 0) = 0 and nvl(c.dr, 0) = 0 and nvl(cc.dr, 0) = 0 and (o.sale_org_id in ?1 or ?2 is null) and (o.customer_id in ?3 or ?4 is null) and (o.order_type = ?5 or ?5 is null) and (o.order_code like ?6 or ?6 is null) and (o.order_date >= ?7 or ?7 is null) and (o.order_date <= ?8 or ?8 is null) and (o.sale_model in ?9 or ?10 is null) and (b.is_close = 0 or b.stock_out_num > 0) and (o.order_status != '06') and b.main_num > 0 and ((t.code = 'OCC1-Cxx-01' and cc.code = '15') or t.code != 'OCC1-Cxx-01') group by o.order_code, o.sale_org_id, b.settle_financial_org_id, o.customer_id, o.order_type, o.order_date, o.id, o.order_status,t.code, cc.code",
	nativeQuery = true)
Page manyTableCorrelationFindOrderByParams(Set saleOrg, String org, Set customer, String code, String orderType, String orderCode, @Temporal(TemporalType.TIMESTAMP)Date orderDate_date_start, @Temporal(TemporalType.TIMESTAMP)Date orderDate_date_end, Set saleModel, String model, Pageable pageable);