aspose file tools*
The moose likes Object Relational Mapping and the fly likes JPA Multiple Foreign Key Join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA Multiple Foreign Key Join" Watch "JPA Multiple Foreign Key Join" New topic
Author

JPA Multiple Foreign Key Join

Michelle Streeter
Ranch Hand

Joined: Sep 22, 2011
Posts: 87

I have class A, class B, and Class C. B is a child of A and C is a child of B. The classes listed below are the ones created by JPA. But I got an error on the reference from C to B as generated. "Referenced column name must be specified when there are multiple join columns" So then after several changes to the C class to the second list of C below and it compiled. But when I published to Glassfish, I get an error and JPA didnt like it. "Internal Exception: Exception [EclipseLink-7333] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.ValidationException
Exception Description: The reference column name [Aid] mapped on the element [field b] does not correspond to a valid field on the mapping reference.
" Could someone please help here? I made one change to the B table to better reflect the real table. instead of using id for Bid in C it had a Bid in B. JPA generated the exact same B Class. So I then added a reference to Bid in hopes of helping but it didnt fix the issue.







Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2434
    
  28



This part doesn't look right to me. Can you post your physical table for C. Do you have an Aid column in C table?. Really, you don't need Aid, just putting Bid in C should be enough
Michelle Streeter
Ranch Hand

Joined: Sep 22, 2011
Posts: 87

If you read what I said, that one was the original. I then later changed the structure of B to better reflect what was in the real issue and then I posted what was generated in the second set of code for C. BTW, I didnt design the original tables and so I have to live with their design. A references a Scenario. B references a resource, C references schedule items. So, you pick a scenario then you pick a resource to see the scheduled items for that scenario and resource.

Below are the table structures





Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2434
    
  28

Michelle Streeter wrote:If you read what I said, that one was the original. I then later changed the structure of B to better reflect what was in the real issue and then I posted what was generated in the second set of code for C.

It will help if you use code tags for your code, so it's more readable.
Michelle Streeter wrote:
BTW, I didnt design the original tables and so I have to live with their design. A references a Scenario. B references a resource, C references schedule items. So, you pick a scenario then you pick a resource to see the scheduled items for that scenario and resource.

Below are the table structures

A:
id int Unchecked
name nchar(10) Checked


B:
id int Unchecked
Aid int Unchecked
Bid int Unchecked
name nchar(10) Checked

C:
id int Unchecked
Aid int Unchecked
Bid int Unchecked
name nchar(10) Checked


What are your FK relationships? In table B, why do you have Bid? how do you populate B.Bid?

I would model thes tables as

Michelle Streeter
Ranch Hand

Joined: Sep 22, 2011
Posts: 87

Again, the design is on production tables which I did not design and I am not allowed to change.

The problem with your code is the list of b's would be a list of all b's and not just b's of type a.

I didnt use code tags because that was not code. It was a data structure.

Lets say A contains

1 S1
2 S2

Lets say B contains
1 S1 Room1
2 S1 Room2
3 S2 Room1
4 S2 Room3

C contains
1 S1 Room1 8am
2 S1 Room1 9am
3 S1 Room2 8am
4 S2 Room2 9am
5 S2 Room3 8am

The bs for S1 would contain Room1 and Room2
Your cs for Room2 would contain both S1 and S2's rooms which is why you need your cs to be both Aid and Bid hence why multiple field foreign keys. The join needs to be on both fields.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Your model seems very confused.

In JPA a ManyToOne always references the Id of the target object, and the ID of an object must always be unique.

In B you have the id as generated, so will always be unique. So C should only have a foreign key to the id in B, that is enough to define the relationship, and it should not be using the Aid.

If your id in B is not unique (then it makes no sense to have it generated), but is only unique within the Aid, then you need to put @Id on the id and the ManyToOne to a, and also define an @IdClass.
Then in C you would be able to use both the Aid and the Bid in the ManyToOne, otherwise it just makes no sense.

If you don't care if your model makes sense or not, you can still define the ManyToOne using Aid and Bid, but you need to define this using a DescriptorCustomizer in EclipseLink and customize the ManyToOneMapping, you cannot use the JPA annotations, as JPA does not allow this.

TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Michelle Streeter
Ranch Hand

Joined: Sep 22, 2011
Posts: 87

I am not sure how many times I must state I can not change the structure of the tables. The tables are owned by a different organization and must remain as they are. I am only trying to enhance what crap the other people forced on us.

The app is for a school. Scheduling classrooms. It also has a forecast tool which makes copies of the existing data into scenarios. So the A table represents the scenarios. The B table represents the copy classrooms or resources for this scenario which I suspect they made the id field to make each recored unique and the Bid field to hold the real B id data since this id field is used for lookups to look up tables and since there can be more than one scenario. The C table represents actual classes that were copied and can be modifies for this scenario. The scenarios allow an adminstrator to rearrange the classes to optimize the use of classrooms and to minimize the time to completion of the classes. The app does not display the data in a birds eye format and so i am writing an app that does. Since the tables are for their app and mine is only an enhancement. I can not change them. Hence why I am trying to map JPA with a composite key. BTW, I have read some posts which have solved composite key mapping in different situations like mine but not like mine. And they have succeeded in theirs. So I suspect there is a way. I just have not found it for my situation.

Its nice for you to state what I would need to do. A descriptor customerizer? Wouldnt it been nice if you had given me an example just in case I might have needed it?






Michelle Streeter
Ranch Hand

Joined: Sep 22, 2011
Posts: 87

You will not believe the answer. It was seriously simple. Actually, I am shocked I didnt get an error but it worked. All I had to do is modify the B class with a Column to Aid

 
 
subject: JPA Multiple Foreign Key Join