With Eclipselink/JPA, can I have a Foreign Composite Key that shares a field with a Primary Composite Key?

Question

My database has two entities; Company and Person. A Company can have many People, but a Person must have only one Company. The table structure looks as follows.

COMPANY
----------
owner   PK
comp_id PK
c_name

PERSON
----------------
owner    PK, FK1
personid PK
comp_id  FK1
p_fname
p_sname

It has occurred to me that I could remove PERSON.OWNER and derive it through the foreign key; however, I can't make this change without affecting legacy code.

I have modeled these as JPA-annotated classes;

@Entity
@Table(name = "PERSON")
@IdClass(PersonPK.class)
public class Person
    implements Serializable {

  @Id
  private String owner;

  @Id
  private String personid;

  @ManyToOne
  @JoinColumns(
    {@JoinColumn(name = "owner", referencedColumnName = "OWNER",
                 insertable = false, updatable = false),
     @JoinColumn(name = "comp_id", referencedColumnName = "COMP_ID",
                 insertable = true, updatable = true)})
  private Company company;

  private String p_fname;

  private String p_sname;

  ...and standard getters/setters...
}

@Entity
@Table(name = "COMPANY")
@IdClass(CompanyPK.class)
public class Company
    implements Serializable {

  @Id
  private String owner;

  @Id
  private String comp_id;

  private String c_name;

  @OneToMany(mappedBy = "company", cascade=CascadeType.ALL)
  private List people;

  ...and standard getters/setters...
}

My PersonPK and CompanyPK classes are nothing special, they just serve as a struct holding owner and the ID field, and override hashCode and equals(o).

So far so good. I come across a problem, however, when trying to deal with associations. It seems if I have an existing Company, and create a Person, and associate to the Person to the Company and persist the company, the association is not saved when the Person is inserted. For example, my main code looks like this:

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

CompanyPK companyPK = new CompanyPK();
companyPK.owner="USA";
companyPK.comp_id="1102F3";
Company company = em.find(Company.class, companyPK);

Person person = new Person();
person.setOwner("USA");
person.setPersonid("5116628123"); //some number that doesn't exist yet
person.setP_fname("Hannah");
person.setP_sname("Montana");
person.setCompany(company);
em.persist(person);

This completes without error; however in the database I find that the Person record was inserted with a null in the COMP_ID field. With EclipseLink debug logging set to FINE, the SQL query is shown as:

INSERT INTO PERSON (PERSONID,OWNER,P_SNAME,P_FNAME) VALUES (?,?,?,?)
  bind => [5116628123,USA,Montana,Hannah,]

I would have expected this to be saved, and the query to be equivalent to

INSERT INTO PERSON (PERSONID,OWNER,COMP_ID,P_SNAME,P_FNAME) VALUES (?,?,?,?,?)
  bind => [5116628123,USA,1102F3,Montana,Hannah,]

What gives? Is it incorrect to say updatable/insertable=true for one half of a composite key and =false for the other half? If I have updatable/insertable=true for both parts of the foreign key, then Eclipselink fails to startup saying that I can not use the column twice without having one set to readonly by specifying these options.

Answer 1

I had the same problem. And the solution i've found is to anotate primary key fields as non-insertable and non-updatable. Your person entity should look like this:

@Entity @Table(name = "PERSON") @IdClass(PersonPK.class) public class Person implements Serializable {

@Id @Column(insertable = false, updatable=false) private String owner;

@Id private String personid;

@Id @Column(insertable = false, updatable=false) private String comp_id;

@ManyToOne @JoinColumns({ @JoinColumn(name = "owner", referencedColumnName = "OWNER"), @JoinColumn(name = "comp_id", referencedColumnName = "COMP_ID") }) private Company company;

private String p_fname;

private String p_sname;

...and standard getters/setters... }

Answer 2

The way I did this was to annotate the fields in the PK class like I would have if they were on the entity.

Then in the entity don't have the fields the PK class holds. If you want to you can implement getters that passthrough

class Person {
  .....

private PersonPK pk;

@Id
public PersonPK getPk() { return pk; }

@Transient
public String getOwner() {
  return pk.getOwner();
}
....
}

The PersonPK needs to be serializable and annoted @Embeddable

@Embeddable
class PersonPK implements Serializable {