Nesting one-to-one relationship in already owned entity

Question

I'm working on a project with multiple user data mapped to a user by one-to-one relationships in the entities. I'm in the process of adding another entity which has a one-to-one relationship with another entity that is already in a bidirectional one-to-one relationship with the owning user data entity.

For clarification let's assume we have the following owning entity:

@Entity(name = "user_data")
public class UserDataEntity {
    @Id
    @GeneratedValue
    private int id;

    @OneToOne(mappedBy = "userData", cascade = CascadeType.ALL)
    private OtherDataEntity otherData;

    // other one-to-one relations

    // other fields, getter, setter etc.
}

Then we have the OtherDataEntity:

@Entity(name = "other_data")
public class OtherDataEntity {
    @Id
    private int id;

    @MapsId
    @OneToOne(fetch = FetchType.LAZY, optional = false)
    private UserDataEntity userData;
}

Also there is a unique constrain added via liquibase:

<changeSet>
    <addForeignKeyConstraint
        baseColumnNames="user_data_id"
        baseTableName="other_data"
        constraintName="someID"
        deferrable="false"
        initiallyDeferred="false"
        onDelete="NO ACTION"
        onUpdate="NO ACTION"
        referencedColumnNames="id"
        referencedTableName="user_data"
        validate="true"/>
</changeSet>

This works just fine but now I want to add another entity which is logically related to OtherDataEntity by a one-to-one relationship.

So I naively added the AdditionalDataEntity to OtherDataEntity like this:

@Entity(name = "other_data")
public class OtherDataEntity {
    @Id
    private int id;

    @MapsId
    @OneToOne(fetch = FetchType.LAZY, optional = false)
    private UserDataEntity userData;

    @OneToOne(mappedBy = "otherData", cascade = CascadeType.ALL)
    private AdditionalDataEntity additionalData;
}

@Entity(name = "additional_data")
public class AdditionalDataEntity {
    @Id
    private int id;

    @MapsId
    @OneToOne(fetch = FetchType.LAZY, optional = false)
    private OtherDataEntity otherData;

}

I've then added a similar unique constrain on this entity with liquibase

<changeSet>
    <addForeignKeyConstraint
        baseColumnNames="user_data_id"
        baseTableName="additional_data"
        constraintName="someId"
        deferrable="false"
        initiallyDeferred="false"
        onDelete="NO ACTION"
        onUpdate="NO ACTION"
        referencedColumnNames="user_data_id"
        referencedTableName="other_data"
        validate="true"/>
</changeSet>

For both entities there is a user_data_id column present in the corresponding tables but I'm getting an error once I try to get, create or update a dataset:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2322) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:956) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:327) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.Loader.loadEntity(Loader.java:2440) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:77) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:61) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.doLoad(AbstractEntityPersister.java:4521) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4511) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:571) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:539) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:208) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:327) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:108) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:74) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:118) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl.fireLoadNoChecks(SessionImpl.java:1226) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1215) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl.access$2100(SessionImpl.java:201) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.doLoad(SessionImpl.java:2830) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.lambda$load$1(SessionImpl.java:2807) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.perform(SessionImpl.java:2763) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2807) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl.get(SessionImpl.java:1028) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultMergeEventListener.entityIsDetached(DefaultMergeEventListener.java:306) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:172) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:70) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:833) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:820) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311) ~[spring-orm-5.3.14.jar:5.3.14]
    at jdk.proxy2/jdk.proxy2.$Proxy175.merge(Unknown Source) ~[na:na]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:627) ~[spring-data-jpa-2.6.0.jar:2.6.0]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:289) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:529) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:638) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.14.jar:5.3.14]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:163) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138) ~[spring-data-commons-2.6.0.jar:2.6.0]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.14.jar:5.3.14]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.14.jar:5.3.14]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.14.jar:5.3.14]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.14.jar:5.3.14]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.14.jar:5.3.14]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.14.jar:5.3.14]
    ... 155 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: column additionaldata1_.other_data_user_data_id does not exist
  Position: 1224
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674) ~[postgresql-42.3.1.jar:42.3.1]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364) ~[postgresql-42.3.1.jar:42.3.1]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354) ~[postgresql-42.3.1.jar:42.3.1]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484) ~[postgresql-42.3.1.jar:42.3.1]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404) ~[postgresql-42.3.1.jar:42.3.1]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:162) ~[postgresql-42.3.1.jar:42.3.1]
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) ~[postgresql-42.3.1.jar:42.3.1]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.6.3.Final.jar:5.6.3.Final]
    ... 213 common frames omitted

The questions I have now is:

Is this a valid approach and I'm doing something wrong or is the nesting of this style not working at all?

Answer 1

I found a solution to this error. Solution process:

1. The Java entity class does not correspond to the data type of the database field, no problem

2. If the table name is different from that in the database, add the table name again

3. The value of name corresponding to column does not correspond to the case of database field

4. The configuration file is not connected to the database correctly

The above common error causes have been ruled out, there is no problem

The cause of the error is that the database to which the table is built is different from the database to which the configuration file is connected, and the address of the database to which the table is built is not checked carefully

Reproduced here.