Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Problem with Foreign Composite Key sharing a field with a Primary Composite Key?
Problem with Foreign Composite Key sharing a field with a Primary Composite Key? [message #390044] Fri, 10 July 2009 15:21 Go to next message
Philihp Busby is currently offline Philihp Busby
Messages: 2
Registered: July 2009
Junior Member
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 IGNORE 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 IGNORE 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.
Re: Problem with Foreign Composite Key sharing a field with a Primary Composite Key? [message #390281 is a reply to message #390044] Mon, 13 July 2009 10:28 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

This should work, you can make half the foreign key read-only. If you
remove the insertable/updateable=false, or move the false to be the second
JoinColumn does it work? (you may need to add an @Column with
insertable/updateable=false for the @Id owner).

If it does, then please log a bug for this.

Another workaround would be to add another basic variable for the other
foreign key, or use a DescriptorCustomizer to fix the OneToOneMapping's
foreign key fields.

---
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Re: Problem with Foreign Composite Key sharing a field with a Primary Composite Key? [message #390284 is a reply to message #390281] Mon, 13 July 2009 11:15 Go to previous messageGo to next message
Philihp Busby is currently offline Philihp Busby
Messages: 2
Registered: July 2009
Junior Member
Reordering the columns has no affect.

Removing the ", insertable=false, updatable=false" from the owner causes
the insert person query to fail:

company = new Company();
company.setOwner("USA");
company.setComp_id("2000034");
company.setC_name("Spacely Sprockets");
em.persist(company);

Person person = new Person();
person.setOwner("USA");
person.setPersonid("20000A2");
person.setP_fname("George");
person.setP_sname("Jetson");
person.setCompany(company);
em.persist(person);

javax.servlet.ServletException: javax.persistence.RollbackException:
Exception [EclipseLink-4002] (Eclipse Persistence Services -
1.1.2.v20090612-r4475):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00957: duplicate column name

Error Code: 957
Call: INSERT IGNORE INTO PERSON (PERSONID, OWNER, P_FNAME, P_SNAME, comp_id,
owner) VALUES (?, ?, ?, ?, ?, ?)
bind => [20000A2, USA, George, Jetson, 2000034, USA]

I will file a bug.
Re: Problem with Foreign Composite Key sharing a field with a Primary Composite Key? [message #390292 is a reply to message #390284] Thu, 16 July 2009 09:52 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The duplicate column name seems to be because you are using different case
in your column, you must be consistent in your case in your @Column or
@JoinColumn.

---
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Previous Topic:Thrown exceptions are useless
Next Topic:Unstable database connection causes EL to deadlock
Goto Forum:
  


Current Time: Thu Jul 24 13:50:55 EDT 2014

Powered by FUDForum. Page generated in 0.03118 seconds