I'm having problems figuring out how to delete or create rows that are on the "many" side of a relationship. Even worse, what about the case where I have a connecting table in a "many-to-many" relationship?

My table definitions are below. At a certain point in my code, I have access to a Tblpatients object (which is detached). I wish to remove all of the objects referenced by its tblhallucinationsList field and replace them with newly created objects.

I've tried many strategies, but nothing seems to work correctly.

Do I need to persist the Tblpatients object before making changes? Do I also have to explicitly persist the tblhallucinationsList obects, or is that automatic? At what point do I open a transaction? Do I do a begin/commit for each deletion and creation? Or, do I begin/commit once for the whole batch?

Can someone please provide an example of the correct way to do this? Any help would be greatly appreciated.

Thanks

Eric



CREATE TABLE tblpatients (
jhid INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1000, INCREMENT BY 1),
lastname VARCHAR(30),
firstname VARCHAR(30),
problem INT,
CONSTRAINT jhid_pk PRIMARY KEY (jhid),
FOREIGN KEY (problem) REFERENCES lstproblems (problemsid));


CREATE TABLE lsthallucinations (
hallucinationid INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 500, INCREMENT BY 1),
hallucination VARCHAR(20),
CONSTRAINT hallucinationid_pk PRIMARY KEY (hallucinationid));


CREATE TABLE tblmedvisits (
medvisitsid INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 2000, INCREMENT BY 1),
jhid INT,
doctor VARCHAR(30),
duration INT,
CONSTRAINT medvisitsid_pk PRIMARY KEY (medvisitsid),
FOREIGN KEY (jhid) REFERENCES tblpatients (jhid));


CREATE TABLE tblhallucinationsmm (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 600, INCREMENT BY 1),
jhid INT,
hallucinationID INT,
CONSTRAINT id_pk PRIMARY KEY (id),
FOREIGN KEY (jhid) REFERENCES tblpatients (jhid),
FOREIGN KEY (hallucinationID) REFERENCES lsthallucinations (hallucinationid));



CREATE TABLE lstproblems (
problemsid INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1),
problem VARCHAR(20),
CONSTRAINT problemsid_pk PRIMARY KEY (problemsid));


Entity fields for tblPatients....


private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "JHID")
private Integer jhid;
@Column(name = "LASTNAME")
private String lastname;
@Column(name = "FIRSTNAME")
private String firstname;
@OneToMany(mappedBy = "tblpatients")
private List<Tblhallucinationsmm> tblhallucinationsmmList;
@OneToMany(mappedBy = "tblpatients")
private List<Tblmedvisits> tblmedvisitsList;
@JoinColumn(name = "PROBLEM", referencedColumnName = "PROBLEMSID")
@ManyToOne
private Lstproblems lstproblems;


Entity fields for Tblhallucinationsmm....


private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "ID")
private Integer id;
@JoinColumn(name = "JHID", referencedColumnName = "JHID")
@ManyToOne
private Tblpatients tblpatients;
@JoinColumn(name = "HALLUCINATIONID", referencedColumnName = "HALLUCINATIONID")
@ManyToOne


Entity fields for lsthallucinations....


private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "HALLUCINATIONID")
private Integer hallucinationid;
@Column(name = "HALLUCINATION")
private String hallucination;
@OneToMany(mappedBy = "lsthallucinations")
private List<Tblhallucinationsmm> tblhallucinationsmmList;