JPA & Hibernate - Composite primary key with foreign key

Question

So I have two tables which I would like to have in Spring memory to query. I have successfully managed to model the 'Drugs' table in an entiry called Medicine, however now need to model the 'drugInteraction' table - which will have the combination of drug_id(PK called id in medicine table), and the drug_name in the drugInteraction table, as a combined primary key.

Schemas as used in python:

cursor.execute("CREATE TABLE drugs(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), description TEXT, toxicity TEXT)")


cursor.execute("CREATE TABLE drugInteractions (drug_id INT NOT NULL, name VARCHAR(90), description TEXT, PRIMARY KEY(drug_id, name), FOREIGN KEY (drug_id) REFERENCES drugs (id))")

Some example data for the drugInteractions table:

drug_id     name       description

1       "Abciximab"         "The risk or severity of bleeding can be increased when Abciximab is combined with Lepirudin."
1       "Aceclofenac"        "The risk or severity of bleeding and hemorrhage can be increased when Aceclofenac is combined with Lepirudin."
1.      "Acemetacin"         "The risk or severity of bleeding and hemorrhage can be increased when Lepirudin is combined with Acemetacin."

Some example data for the drugs table:

id.       name.       description

1       "Lepirudin"      "Lepirudin is identical to...."
2       "Cetuximab"      "Cetuximab is an epidermal growth..."

Here's what I have for the Medicine.java:

package com.example.configbackendspring;

import net.minidev.json.JSONObject;

import javax.persistence.*;

@Entity
@Table(name = "drugs")
public class Medicine {
    @Id
    @GeneratedValue
    @Column(name = "id")
    private Integer id;
    @Column(name = "name")
    private String name;
    @Column(name = "description")
    private String description;
    @Column(name = "toxicity")
    private String toxicity;

    public Medicine(int id, String name, String description, String toxicity) {
        this.id=id;
        this.name=name;
        this.description=description;
        this.toxicity=toxicity;
    }


    public Medicine(){}


    public int getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getToxicity() {
        return toxicity;
    }

    public void setToxicity(String toxicity) {
        this.toxicity = toxicity;
    }

    public JSONObject toJSONObject(){
        JSONObject object = new JSONObject();
        JSONObject medicineObject = new JSONObject();
        medicineObject.appendField("name", this.name);
        medicineObject.appendField("description", this.description);
        medicineObject.appendField("toxicity", this.toxicity);
        medicineObject.appendField("id", this.id);
        object.appendField("medicine", medicineObject);
        return object;

    }
}

And this is what I have for the drugInteraction.java ...which does not work

package com.example.configbackendspring;

import net.minidev.json.JSONObject;

import javax.persistence.*;
import javax.resource.cci.Interaction;
import java.io.Serializable;


@Entity
@Table(name = "drugInteractions")
public class DrugInteraction {


    @EmbeddedId
    private InteractionId interactionId;

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


    public DrugInteraction(int drug_id, String name, String description) {
        this.interactionId.drug_name = name;
        this.interactionId.drug_id = drug_id;
        this.description=description;

    }


    public DrugInteraction(){}


    public Integer getId() {
        return interactionId.drug_id;
    }


    public String getName() {
        return interactionId.drug_name;
    }


    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }


    public JSONObject toJSONObject(){
        JSONObject object = new JSONObject();
        JSONObject interactionObject = new JSONObject();
        interactionObject.appendField("name", interactionId.drug_name);
        interactionObject.appendField("description", this.description);
        interactionObject.appendField("drug_id", interactionId.drug_id);
        object.appendField("drugInteraction", interactionObject);
        return object;

    }
}

This is InteractionId.java

package com.example.configbackendspring;

import lombok.*;

import java.io.Serializable;

@RequiredArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@ToString
@EqualsAndHashCode
public class InteractionId implements Serializable
{

//    public InteractionId(int drug_id, String drug_name){
//        this.drug_id=drug_id;
//        this.drug_name=drug_name;
//
//    }


    @NonNull
    public int drug_id;

    @NonNull
    public String drug_name;
}

My current issue is that I can't figure out how to link the foreign key id in drugs with the composite key. The above code is compiling but the database is empty so the import must be failing somewhere

Please, can someone advise as to how I would change the files to model the above behaviour? I am having trouble figuring out how I would model the composite ID with foreign key

Answer 1

You have multiple things wrong. If you are using @EmbeddedId then the you ID class needs to be an embeddable.

@Embeddable
public class InteractionId {
    @Column(name="name")
    String name;
    Long drugId; //type should be same as for ID field on Medicine

    //equals and hashcode etc.
}

You also need a relationship from DrugInteraction to Medicine annotated with MapsId:

@Entity
@Table(name = "drugInteractions")
public class DrugInteraction {

    @EmbeddedId
    private InteractionId interactionId;

    @MapsId("drugId")//value corresponds to property in the ID class
    @ManyToOne
    @JoinColumn(name = "drug_id")
    private Medicine medicine;
}

To save a new instance:

DrugInteraction di = new DrugInteraction();
Medicine medicine = //an existing medicine
di.setName("Some Name");
di.setMedicine(medicine);
//save

As an alternative, can also do this using an IDClass rather than EmbeddedId:

//not an embeddable
public class InteractionId {
    String name;
    Long drugId; //type should be same as for ID field on Medicine

    //equals and hashcode etc.
}

and change the mappings:

@Entity
@Table(name = "drugInteractions")
@IdClass(InteractionId.class) //specify the ID class
public class DrugInteraction {

    @Id
    private String name;

    @Id
    @ManyToOne
    @JoinColumn(name = "drug_id")
    private Medicine medicine;
}