JPA Entity mapping with no foreign keys available

Question

Hi,

I dont really get to work with entity beans, but they are staring at me right now.

We have a couple of tables that are related to each other, but there are no foreign keys, and we cannot add any. For this question, the database cannot change, it's just not possible, but as a different solution we might create views. Anyways...

I have 3 tables. LOCATION , LINKS and ENDPOINT and for extra salt, we a a LINKS_TYPE table.

LOCATION table has a primary key LOCATIONID which is a string containing the location id.

LINKS table has a LINKID as primary key a column LINK_ATTR_VALUE_A which contains ENDPOINT's primary key a column LINK_ATTR_VALUE_B which contains LOCATION's primary key. a column LINKTYPEID which contains a LINKS_TYPE primary key

ENDPOINT table has a primary key POINTID

LINKS_TYPE has primary key LINKTYPEID a column LINKTYPEA (text string defining the table name it's linked to) a column LINKTYPEB (text string defining the table name it's linked to)

Now even though LINKS_TYPE is mentioned, I dont need to worry about that now, because there are no other links in this instance of the database.

I would like to define a member in my LOCATION entity 'List endPoints' Which would be a @OneToMany from my understanding. Do keep in mind, there is no foreign key to help here, and there wont ever be.

This is the mapping I defined...

@OneToMany ( cascade=CascadeType.ALL)
@JoinTable ( name = "ENDPOINT",
             joinColumns = @JoinColumn ( 
                                name = "LINK_ATTR_VALUE_B"
                            ),
             inverseJoinColumns = 
                            @JoinColumn ( 
                                name = "LINK_ATTR_VALUE_A"
             )
            )
private List<EndPoint> endPoints;

It's very likely from this you might realize I have no clue what I'm doing :D But the documentation aint too great, and Ive ordered a book for help with ejb 3, but I just dont have the time to finish the book before this mapping :D

We are using TopLink with jdeveloper 11g and that weblogic server thing and oracle 10g as database.

When using a serviceFacade client to query, everything did seem right (since then workspace died and I have to recreate the project to get the client working). It generates the perfect query in my opinion to retrieve the right data. Yet, it ends with no results.

I'm willing to give as much info as possible, just not sure what is needed. But I know my mapping is most probably wrong, and it's because I do not understand the mapping.

Could someone help me?

Thank you.

Answer 1

Your LINKS table looks like many-to-many mapping table between LOCATION and ENDPOINT rather than one-to-many. The big question here is whether it has any additional columns aside from LINKID, LINK_ATTR_VALUE_A and LINK_ATTR_VALUE_B that you listed?

If it does, then you would have to map it as a separate entity:
Location would have a collection of Links mapped as bi-directional one-to-many
Link would have many-to-one relationships with both Location and EndPoint

If, OTOH, LINKS has no other columns AND you're willing to forgo its primary key (which is neither needed nor can be mapped for many-to-many join table) then you can map it as many-to-many collection of EndPoints on Location.

If you can clarify your question I'll update my answer to include the actual mapping if you need it.

Answer 2

Here is the mapping I ended on.

@OneToMany(cascade=CascadeType.ALL)
@JoinTable(name = "LINK",
    joinColumns = {
            @JoinColumn(name="LINK_ATTR_VALUE_B")
        },
           inverseJoinColumns = {
            @JoinColumn(name="LINK_ATTR_VALUE_A")
           }
)
private List<EndPoint> endPoints;

There just isnt truly any values needed right now in the LINK table. But when it' time, our DBA's will need to create materialized views for us or something.

But when attemting the mapping, I initialy kept the LINK, rather than going straight to the endPoint. I was being returned 5000+ links where there should only be 133. So again there is a mapping I dont understand,but I'll leave that for later.

At the moment our database only contains 1 link type. This will change, and I really which there was a way for me to add an additional where clause to the mapping, so I could have different attribute mappings for different types.

I'm in a typing mood today :-D