CODEWITHSUNDEEP
postgresqlhibernatespring-data-jpa
Marcos Echagüe
Marcos Echagüe

Reputation: 597

how to make a mapping between Spring Data JPA Entity and tables related by inheritance

I'm using Postgres 10.8, Java 8 and Spring Boot 2.1.9 with Data JPA to map my Entities with my database table. In my database, I partitioned my tables and use an inheritance strategy.

My structure keeps like this:

enter image description here

My Entity : 

@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;
  ...

When I try to get or update some rows, I have no problem. But when I save some data in the my_table, I get this error:

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()

I think that maybe the problem is caused by the trigger that redirects the data to insert in the child table and Spring Data does not recognize it.

Is there a way to fix it?

Upvotes: 2

Views: 1107

Answers (1)

hasnae
hasnae

Reputation: 2183

This problem is a known issue when using partitions with postgresql, it is a part of thier goals to fix this issue ref

So in simple words hibernate try to ckeck if the insert is realy done by counting the number of impacted lines, but hibernate recognize only the table "my_table" where no insert was performed.

to solve this issue you should tell hibernate to not check rows count by adding the following annotation to your entity

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

Another solution could be the following (but be careful about performance):

  • modify your trigger to return NEW , thus a line is inserted to "my_table"
  • add a second trigger to delete the inserted row

Upvotes: 2

Related Questions