JPA OneToMany using columns from different tables

I have the following tables in my relational DB.

 CREATE TABLE manufacturer (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100),
  code varchar(50)
);

 CREATE TABLE product (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  manufacturer_id int(10)
  name varchar(100),
  revision_id int(10)
  CONSTRAINT product_man_fk FOREIGN KEY (manufacturer_id) REFERENCES manufacturer (id) ON DELETE CASCADE
);

 CREATE TABLE revision (
  id int(10),
  code varchar(50),
  CONSTRAINT revision_uq UNIQUE(id, code)
);

Please note that id is not a PK nor a unique column in revision, only a combination of id and code is unique.

Now, in terms of JPA, I need to have only one Revision within the class Product (not a collection of them) and I need to map it as follows:

class Product {
...
    @ManyToOne
    @JoinColumn(name="manufacturer_id")
    private Manufacturer manufacturer;

    @ManyToOne
    @JoinColumns({
      @JoinColumn(name="revision_id", referencedColumnName="id"),
      @JoinColumn(name="manufacturer.code", referencedColumnName="code")
    })
    private Revision revision;
}

This doesn't work obviously, as manufacturer.code is not a column of table product.
Is there any way to somehow pass a field from another parent entity to a different @ManyToOne annotation for mapping?

Thanks in advance

In JPA, you cannot directly reference a field from one entity to another entity's relationship mapping annotation. However, you can achieve the desired mapping by introducing an additional mapping entity.

1. Create a new mapping entity (e.g. ProductRevisionMapping), which represents the mapping between Product and Revision. This entity will have a composite primary key consisting of product_id (foreign key to Product) and revision_id (foreign key to Revision).

@Entity
@Table(name = "product_revision_mapping")
public class ProductRevisionMapping implements Serializable {

    @Id
    @ManyToOne
    @JoinColumn(name = "product_id")
    private Product product;

    @Id
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "revision_id", referencedColumnName = "id"),
        @JoinColumn(name = "revision_code", referencedColumnName = "code")
    })
    private Revision revision;
}

2. Modify the Product entity to have a one-to-one mapping with ProductRevisionMapping.

@Entity
@Table(name = "product")
public class Product {

    // ...

    @OneToOne(mappedBy = "product")
    private ProductRevisionMapping productRevisionMapping;


    public Revision getRevision() {
        if (productRevisionMapping != null) {
            return productRevisionMapping.getRevision();
        }
        return null;
    }

    public void setRevision(Revision revision) {
        if (productRevisionMapping == null) {
            productRevisionMapping = new ProductRevisionMapping();
            productRevisionMapping.setProduct(this);
        }
        productRevisionMapping.setRevision(revision);
    }
}

With this modification, you can have a one-to-one relationship between Product and Revision, using the ProductRevisionMapping as an intermediary mapping entity.

> Make sure to handle the saving and updating of Product and Revision entities appropriately to maintain the consistency of the mapping.