spring - 新的@JoinTable 在 nativeQuery 中导致 org.hibernate.exception.SQLGrammarException，我们不知道
问题描述
这让我们的团队摸不着头脑，我们不知道如何进行。
概述 我们有两种订单类型，CashIn和CashOut，它们具有单独的实体类，并且我们有一个CustomerDetails实体。为了避免不必要的重复，我设置了一个JoinTable以在需要时查找正确的CustomerDetails。
每个类中有更多条目，但这就是下面与此问题相关的内容。
现金类
@NamedEntityGraph(name = "Order.CustomerDetails", attributeNodes = @NamedAttributeNode("customerDetails"))
public class Order extends BaseEntity {
@OneToOne(cascade = CascadeType.ALL)
@JoinTable(name = "order_customer_details",
joinColumns = { @JoinColumn(name = "cashin_order_id", referencedColumnName = "id") },
inverseJoinColumns = { @JoinColumn(name = "customer_details_id", referencedColumnName = "id") }
)
private CustomerDetails customerDetails;
}
CashOut 类
public class CashOut extends BaseEntity {
/**
* The old approach was no good.
*/
@OneToOne(cascade = CascadeType.ALL)
@JoinTable(name = "order_customer_details",
joinColumns = { @JoinColumn(name = "cashout_order_id", referencedColumnName = "id") },
inverseJoinColumns = { @JoinColumn(name = "customer_details_id", referencedColumnName = "id") }
)
private CustomerDetails customerDetails;
}
类 CustomerDetails
@Entity
@Table(name = "order_details")
public class CustomerDetails {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id",unique=true, nullable = false)
private Long id;
@Column(name = "name", length = 50)
private String name;
}
连接表
@Entity
@Table(name = "order_customer_details")
public class OrderCustomerDetails extends BaseEntity {
/**
* OneToOne OrderCustomerDetails Bridge
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", columnDefinition = "int")
private Long id;
@Column(name = "cashin_order_id", columnDefinition = "bigint")
private Long cashInOrderId;
@Column(name = "cashout_order_id", columnDefinition = "bigint")
private Long cashOutOrderId;
@Column(name = "customer_details_id", columnDefinition = "bigint")
private Long customerDetailsId;
@CreationTimestamp
@Column(name = "created_date", nullable = false)
private Date createdDate;
}
所有这些都按预期工作，当检查CashIn或CashOut订单时，它会在JoinTable中查找正确的条目并获取正确的CustomerDetails条目。
但
在我们预定的nativeQuery通话中
@Transactional(readOnly = true)
@Query(nativeQuery = true,
value = "SELECT o.* FROM orders o JOIN vendors v on o.vendor_id = v.id" +
" where date_add(o.last_update_date, interval v.order_notification_delay second) < now() AND o.id > 10000")
List<Order> findPaidOrdersRequiringNotification();
使用CashIn示例返回
2019-11-08 12:43:15,404 WARN [task-scheduler-4] org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: S0022
2019-11-08 12:43:15,405 ERROR [task-scheduler-4] org.hibernate.engine.jdbc.spi.SqlExceptionHelper: Column 'customer_details_id' not found.
2019-11-08 12:43:21,310 ERROR [task-scheduler-4] org.springframework.integration.handler.LoggingHandler: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [SELECT o.* FROM orders o JOIN vendors v on o.vendor_id = v.id where date_add(o.last_update_date, interval v.order_notification_delay second) < now() AND o.id > 10000]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:488)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy143.findPaidOrdersRequiringNotification(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:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:97)
at m6.controller.merchant.SortManipulatingAspect.enableIgnoreCaseSorting(SortManipulatingAspect.java:22)
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.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy143.findPaidOrdersRequiringNotification(Unknown Source)
at m6.task.orderpaid.OrderPaidService.processPaidOrders(OrderPaidService.java:52)
at m6.task.orderpaid.OrderPaidService$$FastClassBySpringCGLIB$$307fb413.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
at m6.task.orderpaid.OrderPaidService$$EnhancerBySpringCGLIB$$3843faf7.processPaidOrders(<generated>)
at m6.task.orderpaid.OrderPaidTask.perform(OrderPaidTask.java:34)
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.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
at java.util.concurrent.FutureTask.run(FutureTask.java)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.loader.Loader.doList(Loader.java:2620)
at org.hibernate.loader.Loader.doList(Loader.java:2600)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
at org.hibernate.loader.Loader.list(Loader.java:2424)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:121)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
... 57 more
Caused by: java.sql.SQLException: Column 'customer_details_id' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1078)
at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2736)
at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:63)
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:238)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:224)
at org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:169)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2790)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1729)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1655)
at org.hibernate.loader.Loader.getRow(Loader.java:1544)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:727)
at org.hibernate.loader.Loader.processResultSet(Loader.java:972)
at org.hibernate.loader.Loader.doQuery(Loader.java:930)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
at org.hibernate.loader.Loader.doList(Loader.java:2617)
... 80 more
经过几天的测试，我可以确定的是，如果查询找到订单，它将呕吐。如果没有命中，它会愉快地继续没有错误。
在我们的案例中，唯一可能不同的是我在JoinTable中有空值
CREATE TABLE `order_customer_details` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`cashin_order_id` bigint(20) unsigned DEFAULT NULL,
`cashout_order_id` bigint(20) unsigned DEFAULT NULL,
`customer_details_id` bigint(20) unsigned DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16583 DEFAULT CHARSET=utf8 COMMENT='One to One table to link orders to customer details';
在nativeQueryMySQL Workbench 中运行良好。
我不知道如何继续。有人有什么建议让我检查吗？
解决方案
问题不在于查询本身，而在于您从中获得的结果。您正在运行一个本机查询，该查询仅检索表的列，order但
"SELECT o.* FROM orders o ...将这些结果映射到一个更复杂的类，您的Order实体包含复杂的连接，因为查询是本机的，hibernate 不会执行所有连接和魔术它通常在幕后进行。这就是为什么查询可以在 MySQL Workbench 上运行但不能在您的应用程序上运行的原因。
这意味着，如果您希望您的本机查询按预期工作，您将必须在查询上手动执行所有这些连接，然后指示休眠它应该如何使用 SqlResultSetMapping 或类似的东西将这些结果映射到您的类。
希望这可以帮助。
推荐阅读
- snowflake-cloud-data-platform - 如何计算变体列中嵌套数组中浮点数的平均值
- google-apps-script - 如何在创建新工作表时自动合并 Google 表格中的单元格
- oracle - 从基于事件的小时表中查看 Oracle 小时数据
- java - Cucumber - Java - 标识符中的非 ASCII 字符
- pandas - koalas 列赋值不支持类型ndarray
- python - 制作图像时如何重新排列像素
- kubernetes - Terraform 应用抛出错误“等待插件启动时超时”
- azure - 用于在资源组参与者中自动添加用户组的 Azure 策略
- javascript - 周开始数组 javascript 直到 X 天
- node.js - 如何引用节点模块中包含的 nunjucks 模板？