How to model a self-referencing Many to Many relationship?

Support
#1

I have three entities: Order, List and Family. An Order consists of multiple Lists. A list might be grouped together into a family. My Oracle legacy database tables have this :

table Order:
ordnum number primary key

table List:
id number, primary key
ord_ordnum number, foreign key to Order table
keycode varchar2(10)

The kicker is the Family table. It looks like this:

create table Family (
ordnum number,
mainkey varchar2(10), references a keycode in Lists for a particular ordnum
compkey varchar2(10), references ANOTHER keycode in Lists with the same ordnum
)

Embedded unique key for Family is all 3 fields (ordnum, mainkey, compkey). When I load a List, I would like to load all the Family entities that have the same ordnum and mainkey as the collection. I would like to add and remove compkey (which also are Lists) from this collection.

I can’t figure out the correct way to model this. Here’s are what I hope are the relevant parts of what I have:

@Table(name = "ORDERS")
@Entity(name = "merges_Orders")
@IdSequence(name = "ORDGEN")
@AttributeOverrides({
        @AttributeOverride(name = "id", column = @Column(name = "ORDNUM"))
})
public class Orders extends BaseLongIdEntity {

    @OrderBy("keycode")
    @OneToMany(mappedBy = "ordOrdnum")
    protected List<Lists> lists;

}

@Table(name = "LISTS")
@Entity(name = "merges_Lists")
public class Lists extends BaseIntegerIdEntity {

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "ORD_ORDNUM")
    protected Orders ordOrdnum;

    @OrderBy("compfc")
    @OneToMany
    @JoinTable(name="Family",
        joinColumns= {
                @JoinColumn(name = "ordOrdnum", referencedColumnName = "id.ordnum"),
                @JoinColumn(name = "keycode", referencedColumnName = "id.mainkey"),
        }
    )
    protected List<Family> family;

@MetaClass(name = "merges_FamilyCompKey")
@Embeddable
public class FamilyCompKey extends EmbeddableEntity {
    private static final long serialVersionUID = -8178030635951315049L;

    @Column(name = "COMPKEY", nullable = false, length = 10)
    protected String compkey;

    @Column(name = "MAINKEY", nullable = false, length = 10)
    protected String mainkey;

    @Column(name = "ORDNUM", nullable = false)
    protected Long ordnum;

}

public class Family extends BaseGenericIdEntity<FamilyCompKey> {

    @EmbeddedId
    protected FamilyCompKey id;

    @Lookup(type = LookupType.DROPDOWN, actions = {"lookup", "clear"})
    @MapsId("mainkey")
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns( {
            @JoinColumn(name = "ordnum", referencedColumnName = "ordOrdnum", insertable = false, updatable = false),
            @JoinColumn(name = "mainkey", referencedColumnName = "keycode")
    })
    protected Mplists mainkey;

    @Lookup(type = LookupType.DROPDOWN, actions = {"lookup", "clear"})
    @MapsId("compkey")
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns( {
            @JoinColumn(name = "ordnum", referencedColumnName = "ordOrdnum", insertable = false, updatable = false),
            @JoinColumn(name = "compkey", referencedColumnName = "keycode")
    })
    protected Mplists compkey;

    @MapsId("ordnum")
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID")
    protected Orders orders;
}

When I execute this code, I get the following warnings:

 eclipselink.metadata  - metadata_warning_reference_column_not_found [ordOrdnum, field compkey]
eclipselink.metadata   - metadata_warning_reference_column_not_found [ordOrdnum, field mainkey]
eclipselink.metadata   - metadata_warning_reference_column_not_found [id.ordnum, field family]
eclipselink.metadata   - metadata_warning_reference_column_not_found [id.mainkey, field family]

Since I can’t modify the legacy database, how do I set this up?