One to many association - Join tables with non primary key column in JPA

Question

I'm working on legacy system, need to read some of the info from database. Below are the table relationship

Vendor (vendorId - pk, vendorEid, name)
VendorContactBridge (bridgeId -pk, vendorEid, contactEid)
Contact (contactId -pk, contactEid, phone)

vendorEid and contactEid are not the primary key of the table but used as join column in Join table VendorContactBridge.

Vendor Entity -

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

@Id
@Column(name="VENDORID")
private BigDecimal vendorId;

@Column(name="VENDOREID")
private BigDecimal vendorEid;

@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name="VENDORCONTACTBRIDGE", 
joinColumns={@JoinColumn(name="VENDOREID", referencedColumnName="VENDOREID")},
inverseJoinColumns={@JoinColumn(name="CONTACTEID", referencedColumnName="CONTACTEID")})
private Set<Contact> vendorContact;
}

Contact Entity -

@Entity
@Table(name="CONTACT")
public class Contact implements Serializable{

@Id
@Column(name="CONTACTID")
private BigDecimal contactId;

@Column(name="CONTATEID")
private BigDecimal contactEId;

@ManyToOne
@JoinTable(name="VENDORCONTACTBRIDGE", 
joinColumns={@JoinColumn(name="CONTACTEID", referencedColumnName="CONTATEID")},
inverseJoinColumns={@JoinColumn(name="VENDOREID", referencedColumnName="VENDOREID")})
private Vendor vendor;
 }

while running the query, getting below exception

SecondaryTable JoinColumn cannot reference a non primary key.

I removed the Eager fetch which i have given in Vendor entity, i dont get any exception but it doesn't load the collection. What's wrong with association ?

Elul

Answer 1

According to the JPA 2.0 specs paragraph 11.1.21 JoinColumn annotaion on page 379

Support for referenced columns that are not primary key columns of the referenced table is optional. Applications that use such mappings will not be portable.

It seems Hibernate choose not to implement this optional part. Other implementations might. I tried it on EclipseLink but that one does not work either (it fails validation).

I see two work arounds. One is to adjust your schema to use the primary keys which would be the right thing from a database design theory perspective. However that might not be an option because of other software depending on this schema which leaves option two. Work around it by NOT moddeling the relationship in JPA just use the eid's and retrieve the relevant objects yourself.

Devolution

Option 2 : Do you mean, i should make two db call one for getting ContactEid from VendorContactBridge based on VendorEid and the again on Contact ased on ContactEid ? Is my understanding correct ? – Elul

That would be the basic principal. But it would be best to use a native query as you would be able to do a join and thus use a single sql query to retrieve the objects. – Devolution

Answer 2

I found a workaround for this problem.

See here:

• https://mcmap.net/q/590363/-is-it-possible-to-map-a-field-in-an-entity-without-defining-any-association

Dick