OneToMany Mapping - Not a key

[James Wagner]

Hi, I was just trying to work on an O/R mapping in EJB3. Unfortunately altering the database schema is not an option (or at the very least is a last resort). Basically there are a whole bunch of tables in which one of the columns is called "Attributes_ID". Though not explicitly defined in the schema, this attributes_id maps to the column "ID" in the table "Attributetext". Because multiple tables have this relationship, no foreign key can be defined within the Attributetext table.

I was wondering whether it is still possible to have a OneToMany mapping done correctly with this. In a java file defining a pojo corresponding to a table (let's call it "Feature") I include the following:

@OneToMany(fetch = FetchType.EAGER)
@JoinColumn(name="Attributes_ID")
public Collection<Attributetext> getAttributeText() {
return attributeText;
}

public void setAttributeText(Collection<Attributetext> attributeText) {
this.attributeText =attributeText;
}


The primary key of the "Feature" table is a column called "ID" and what happens with this is for each feature the attributeText row(s) with attributes_id equal to the feature's ID value are retrieved. What I want is for the attributeText row(s) with attributes_id equal to the features attributes_id value to be retrieved and put into the Collection. It seems that any example of a OneToMany mapping I can dig up, the join is being done with the primary key column in your "One" table (in this example Feature), to that column specified by "@JoinColumn" in your "Many" table (in this example AttributeText).

Is there any option that can be set to instead do the join on a non-key column in the "One" table?

Thanks sincerely

[Mike Keith]

Hi James,

First, I think I understand your situation and let me just say that we didn't actually intend to cover this kind of corner case in the spec, so JPA providers may not actually support what I am suggesting you try. Doesn't mean that you can't try it, though, because it might or might not work with the JPA provider that you are using.

Next, to have any hope there must only be one Feature that has the same collection of attributeText objects (so even though attribute_id is not the PK in the FEATURE table, it is at least unique). If this is not the case then no JPA provider can support it.

Lastly, you will need to have a ManyToOne relationship attribute from the attributeText object to each of the object types (e.g Feature) that has a attribute_id field in its table. It is on this @ManyToOne backpointer that you specify the @JoinColumn(name="ATTRIBUTE_ID", referencedColumnName="ATTRIBUTE_ID"). The @OneToMany mapping in the Feature object would just look like: @OneToMany(mappedBy="feature") where "feature is the name of the ManyToOne attribute in AttributeText.

Not sure if this will work on your provider, but you can give it a try. If it doesn't then you might have to "go native". Good luck :-)

[James Wagner]

Thanks Mike:

It seems that including the option "ReferencedColumnName" in both the OneToMany and ManyToOne definitions is what finally did the trick.