how to map between two tables with @EmbeddedId s?

Question

So what I have here is a diagram that looks like this, which can be found in this Answer Here.

 +---------------+     +-------------------+
 | PRODUCTS      |-----< PRODUCT_VARIANTS  |
 +---------------+     +-------------------+
 | #product_id   |     | #product_id       |
 |  product_name |     | #variant_id       |
 +---------------+     |  sku_id           |
         |             +-------------------+
         |                       |
+--------^--------+     +--------^--------+
| PRODUCT_OPTIONS |-----< VARIANT_VALUES  |
+-----------------+     +-----------------+
| #product_id     |     | #product_id     |
| #option_id      |     | #variant_id     |
+--------v--------+     | #option_id      |
         |              |  value_id       |
+-----------------+     +--------v--------+
| OPTIONS         |              |
+-----------------+              |
| #option_id      |              |
|  option_name    |              |
+-----------------+              |
         |                       |
 +-------^-------+               |
 | OPTION_VALUES |---------------+
 +---------------+
 | #option_id    |
 | #value_id     |
 |  value_name   |
 +---------------+

I managed to implement the whole diagram in my Spring app, except the table VARIANT_VALUES

The problem is that the design suggests that the tables PRODUCT_OPTIONS, OPTION_VALUES and PRODUCT_VARIANTS have compound Ids (Primary Keys), which I implemented using @EmbeddedId on three of them.

like follows ex. //PRODUCT_VARIANTS

@Data
@NoArgsConstructor
@Entity
@Table(name = "product_variants")
public class ProductVariant {

    @EmbeddedId
    private ProductVariantId productVariantId;

    @NotNull
    private Double price;

    @MapsId("productId")
    @ManyToOne(fetch = FetchType.LAZY)
    private Product product;

    //other properties
}

and the //ProductVariantId

@Data
@Embeddable
public class ProductVariantId implements Serializable {

    @EqualsAndHashCode.Include
    private Long productVariantId;
    @EqualsAndHashCode.Include
    private Long productId;
}

now I need to map those three tables (PRODUCT_OPTIONS, OPTION_VALUES and PRODUCT_VARIANTS) again with VARIANT_VALUES

so I tried this approach //VariantValue

@Data
@NoArgsConstructor
@Entity
@Table(name = "variant_values")
public class VariantValue {

    @EmbeddedId
    private VariantValueId variantValueId;

    @MapsId("productVariantId")
    @ManyToOne(fetch = FetchType.LAZY)
    private ProductVariant productVariant;

    @MapsId("productOptionId")
    @ManyToOne(fetch = FetchType.LAZY)
    private ProductOption productOption;

    @MapsId("optionValueId")
    @ManyToOne(fetch = FetchType.LAZY)
    private OptionValue optionValue;

}

//VariantValueId

@Embeddable
@Data
public class VariantValueId implements Serializable {


    @EqualsAndHashCode.Include
    @Embedded
    private ProductVariantId productVariantId;

    @EqualsAndHashCode.Include
    @Embedded
    private ProductOptionId productOptionId;

    @EqualsAndHashCode.Include
    @Embedded
    private OptionValueId optionValueId;
}

but it ended up creating a table with each and every Id in all of the three tables,

When I add an @Embdded to VariantValueId it generates the two columns and I could not find any way to exclude one value and use the other,

so the generated table now looks something like this //variant_values

product_option_option_option_id [PK]
product_option_ptoduct_product_id [PK]
product_variant_ptoduct_product_id [PK]
product_variant_ptoduct_variant_id [PK]
product_value_option_option_id [PK]
product_value_option_value_id [PK]

so how can I implement this to only have product_id [PK], variant_id [PK], option_id [PK] and value_id as the design suggests?

Edit 1

I tried Christian Beikov's Approach

@Data
@NoArgsConstructor
@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "product_variants")
public class ProductVariant extends UserDateAudit {

    @EmbeddedId
    @AttributeOverrides({
            @AttributeOverride(name = "productVariantId", column = @Column(name = "variant_id")),
            @AttributeOverride(name = "productId", column = @Column(name = "product_id"))
    })
    private ProductVariantId productVariantId;


    @NotNull
    private Double price;

    //many to many with product entity, option entity and optionValue entity
    @MapsId("productId")
    @ManyToOne(fetch = FetchType.LAZY)
    private Product product;

    @OneToMany(mappedBy = "productVariant", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private Set<VariantValue> variantValues;
    
}

@Data
@Embeddable
public class ProductVariantId implements Serializable {
    
    private Long productVariantId;
    private Long productId;
}

@Data
@NoArgsConstructor
@Entity
@Table(name = "variant_values")
public class VariantValue {
    @EmbeddedId
    @AttributeOverrides({
            @AttributeOverride(name = "productVariantId", column = @Column(name = "variant_id")),
            @AttributeOverride(name = "productId", column = @Column(name = "product_id"))
            @AttributeOverride(name = "optionId", column = @Column(name = "option_id")),
            @AttributeOverride(name = "valueId", column = @Column(name = "value_id"))
    })
    private VariantValueId variantValueId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
            @JoinColumn(name = "variant_id", referencedColumnName = "variant_id", insertable = false, updatable = false),
            @JoinColumn(name = "product_id", referencedColumnName = "product_id", insertable = false, updatable = false)
    })
    private ProductVariant productVariant;

    //other mappings

i got the following error

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaAuditingHandler': Cannot resolve reference to bean 'jpaMappingContext' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaMappingContext': Invocation of init method failed; nested exception is org.hibernate.MappingException: Unable to find column with logical name: product_id in product_variants
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:342) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:113) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:691) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:197) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1356) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1206) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:571) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:531) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:944) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:925) ~[spring-context-5.3.1.jar:5.3.1]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:588) ~[spring-context-5.3.1.jar:5.3.1]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144) ~[spring-boot-2.4.0.jar:2.4.0]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767) ~[spring-boot-2.4.0.jar:2.4.0]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) ~[spring-boot-2.4.0.jar:2.4.0]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426) ~[spring-boot-2.4.0.jar:2.4.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:326) ~[spring-boot-2.4.0.jar:2.4.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1309) ~[spring-boot-2.4.0.jar:2.4.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1298) ~[spring-boot-2.4.0.jar:2.4.0]
    at com.ecommerce.ECommerceApplication.main(ECommerceApplication.java:10) ~[classes/:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[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:564) ~[na:na]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.4.0.jar:2.4.0]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jpaMappingContext': Invocation of init method failed; nested exception is org.hibernate.MappingException: Unable to find column with logical name: product_id in product_variants
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1788) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:609) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:531) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.1.jar:5.3.1]
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:330) ~[spring-beans-5.3.1.jar:5.3.1]
    ... 27 common frames omitted
Caused by: org.hibernate.MappingException: Unable to find column with logical name: product_id in product_variants
    at org.hibernate.cfg.Ejb3JoinColumn.checkReferencedColumnsType(Ejb3JoinColumn.java:854) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.cfg.BinderHelper.createSyntheticPropertyReference(BinderHelper.java:255) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.cfg.ToOneFkSecondPass.doSecondPass(ToOneFkSecondPass.java:101) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processEndOfQueue(InFlightMetadataCollectorImpl.java:1823) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processFkSecondPassesInOrder(InFlightMetadataCollectorImpl.java:1767) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processSecondPasses(InFlightMetadataCollectorImpl.java:1655) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:286) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255) ~[hibernate-core-5.4.23.Final.jar:5.4.23.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.1.jar:5.3.1]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.1.jar:5.3.1]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.1.jar:5.3.1]
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:832) ~[na:na]

Christian Beikov · Accepted Answer · 2020-11-25T14:22:11.073

Try something like this instead:

@Data
@NoArgsConstructor
@Entity
@Table(name = "product_variants")
public class ProductVariant {

    @EmbeddedId
    @AttributeOverrides({
        @AttributeOverride(name = "productVariantId", column = @Column(name = "variant_id")),
        @AttributeOverride(name = "productId", column = @Column(name = "product_id"))
    })
    private ProductVariantId productVariantId;

    @NotNull
    private Double price;

    @MapsId("productId")
    @ManyToOne(fetch = FetchType.LAZY)
    private Product product;

    //other properties
}

@Data
@Embeddable
public class ProductVariantId implements Serializable {

    @EqualsAndHashCode.Include
    private Long productVariantId;
    @EqualsAndHashCode.Include
    private Long productId;
}

@Data
@NoArgsConstructor
@Entity
@Table(name = "variant_values")
public class VariantValue {

    @EmbeddedId
    @AttributeOverrides({
        @AttributeOverride(name = "productVariantId", column = @Column(name = "variant_id")),
        @AttributeOverride(name = "productId", column = @Column(name = "product_id")),
        @AttributeOverride(name = "optionId", column = @Column(name = "option_id")),
        @AttributeOverride(name = "valueId", column = @Column(name = "value_id"))
    })
    private VariantValueId variantValueId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
      @JoinColumn(name = "variant_id", referencedColumnName = "variant_id", insertable = false, updatable = false),
      @JoinColumn(name = "product_id", referencedColumnName = "product_id", insertable = false, updatable = false)
    })
    private ProductVariant productVariant;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
      @JoinColumn(name = "option_id", referencedColumnName = "option_id", insertable = false, updatable = false),
      @JoinColumn(name = "product_id", referencedColumnName = "product_id", insertable = false, updatable = false)
    })
    private ProductOption productOption;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
      @JoinColumn(name = "option_id", referencedColumnName = "option_id", insertable = false, updatable = false),
      @JoinColumn(name = "value_id", referencedColumnName = "value_id", insertable = false, updatable = false)
    })
    private OptionValue optionValue;

}

@Embeddable
@Data
public class VariantValueId implements Serializable {


    @EqualsAndHashCode.Include
    private Long productVariantId;
    @EqualsAndHashCode.Include
    private Long productId;
    @EqualsAndHashCode.Include
    private Long optionId;
    @EqualsAndHashCode.Include
    private Long valueId;
}

Edit:

Try this then:

@Data
@NoArgsConstructor
@Entity
@Table(name = "product_variants")
public class ProductVariant {

    @EmbeddedId
    @AttributeOverrides({
        @AttributeOverride(name = "productVariantId", column = @Column(name = "variant_id")),
        @AttributeOverride(name = "productId", column = @Column(name = "product_id"))
    })
    private ProductVariantId productVariantId;

    @NotNull
    private Double price;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColum(name = "product_id", insertable = false, updatable = false)
    private Product product;

    //other properties
}

Thanks a lot, I tried your approach and I got an exception, I updated the question to include the stack trace. — Omar Abdelhady, Nov 25 '20 at 11:04
I got that error before and the solution was to add the embeddedId itself to the "VariantValueId", so I'm not sure what's wrong, please in you want to view something else in the code just tell me to update my answer — Omar Abdelhady, Nov 25 '20 at 12:19
Thank you very very much. that really was it, helped me a lot, thank you. — Omar Abdelhady, Nov 25 '20 at 15:22

Qwer Izuken · Answer 2 · 2020-11-24T21:46:02.150

1

You can force JPA to map properties of embedded ids in your columns using @AttributeOverride annotation

@Embedded
@AttributeOverrides({
        @AttributeOverride(name = "productVariantId", column = @Column(name = "variant_id")),
        @AttributeOverride(name = "productId", column = @Column(name = "product_id"))
})
private ProductVariantId productVariantId;

And do the same for ProductOptionId and OptionValueId in VariantValueId class.

edited Nov 24 '20 at 21:46

answered Nov 24 '20 at 20:43

Qwer Izuken

599
2
6

thanks, but this still creates the exact same schema (and same names as well) and my problem is not the naming of the created schema's columns, my problem is it does create some extra repeated columns that I don't intend to create. – Omar Abdelhady Nov 25 '20 at 05:35

how to map between two tables with @EmbeddedId s?

2 Answers2