• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

JPA Multiple Foreign Key Join

 
Ranch Hand
Posts: 167
Netbeans IDE C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.







 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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 Nicholes
Ranch Hand
Posts: 167
Netbeans IDE C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Nicholes
Ranch Hand
Posts: 167
Netbeans IDE C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Michelle Nicholes
Ranch Hand
Posts: 167
Netbeans IDE C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Nicholes
Ranch Hand
Posts: 167
Netbeans IDE C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
sunglasses are a type of coolness prosthetic. Check out the sunglasses on this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic