postgresql - 如何在 Spring Data JPA Entity 和通过继承相关的表之间进行映射

Question

我正在使用带有数据 JPA 的 Postgres 10.8、Java 8 和 Spring Boot 2.1.9 来将我的实体与我的数据库表进行映射。在我的数据库中，我对表进行了分区并使用了继承策略。

我的结构保持这样:



我的实体:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Table(name = "my_table", schema = "public")
public  class MyTableEntity {

    @Id
    @Column(name = "id")
    private Integer 

    @Column(name = "description")
    private String description;

    @Column(name = "created_at", updatable = false)
    @Convert(converter = LocalDateTimeConverter.class)
    private LocalDateTime createdAt;
  ...

当我尝试获取或更新某些行时，我没有问题。但是当我在 my_table 中保存一些数据时，我收到此错误:

ERROR 32759 --- [nio-8080-exec-5] p.c.l.s.web.error.ErrorControllerAdvice : Unexpected row count: 0; expected: 1; statement executed: HikariProxyPreparedStatement@2106528986 wrapping insert into public.my_table (id, description, created_at) values (10, 'Descripotion ', '2019-12-26 11:56:29.091000-03'); nested exception is org.hibernate.StaleStateException: Unexpected row count: 0; expected: 1; statement executed: HikariProxyPreparedStatement@2106528986 wrapping insert into public.my_table (id, description, created_at) values (10, 'Description', '2019-12-26 11:56:29.091000-03')

org.springframework.orm.ObjectOptimisticLockingFailureException: Unexpected row count: 0; expected: 1; statement executed: HikariProxyPreparedStatement@2106528986 wrapping insert into public.my_table (id, description, created_at) values (10, 'Description', '2019-12-26 11:56:29.091000-03'); nested exception is org.hibernate.StaleStateException: Unexpected row count: 0; expected: 1; statement executed: HikariProxyPreparedStatement@2106528986 wrapping insert into insert into public.my_table (id, description, created_at) values (10, 'Description', '2019-12-26 11:56:29.091000-03') at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:339) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:254) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:537) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:534) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:305) 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:689) at com.mechague.soft.service.impl.MyServiceImpl$$EnhancerBySpringCGLIB$$27b46df5.createItem()

我认为问题可能是由将数据重定向到子表中插入的触发器引起的，而 Spring Data 无法识别它。

有办法解决吗？

Answer 1

这个问题是在 postgresql 中使用分区时的一个已知问题，解决这个问题是他们目标的一部分 ref

因此，简而言之，hibernate 尝试通过计算受影响的行数来检查插入是否真的完成，但 hibernate 仅识别未执行插入的表“my_table”。

要解决此问题，您应该通过向实体添加以下注释来告诉 hibernate 不检查行数

@SQLInsert(sql =[YOUR_INSERT_QUERY], ResultCheckstyle = ResultCheckStyle.NONE)

另一种解决方案可能如下(但要注意性能):

修改您的触发器以返回 NEW ，从而将一行插入到
"my_table"

添加第二个触发器以删除插入的行