lupestro (7) [Avatar] Offline
#1
Chapter 8 left a few things unclear about the behavior of JPA with multi-key entities, particularly where the keys are in fact foreign keys to other tables.

Suppose I have a multi-way join table where the entire row is the unique index, and the table consists of three foreign keys to other tables. Classic case - (work, participant, role). The "work" has various "participants" that play one or more roles - writer, editor, producer, director, performer. Works involve multiple participants and the same participant can play different roles in the same work or in different works. Roles could just be an enumeration, but making them a table makes it possible to add new roles as we discover them and keeps the database self-describing. So we have a three-way-join table of unique rows where all three columns are foreign keys.

In most tables, foreign keys never show up in entities. Instead the object containing the key is included and JPA figures it out from the annotations. In fact, for a many-to-many join between two objects, we don't need to model the join table at all. However, since we cannot simply specify a three-way join table via annotations alone and thereby keep the table out of the model, the next-most-natural thing would be to make a ParticipantRoleInWork entity containing a Work object, a Participant object, and a Role object, specifying that each of their primary keys participates in this entity's key.

In my Work entity, I will want a Set<ParticipantRoleInWork> so I can see who participated and what their role was. In my Participant entity, I will want a Set<ParticipantRoleInWork> so I can what they participated in and what their role was. To add a Work, I just create a Work and a number of Participants, create the needed ParticipantRoleInWork entities and stuff them into the relevant Sets. If I have set up my cascading properly and I persist the Work object, in a perfect world, JPA does the rest. Magic!

However, the text makes it pretty clear that JPA1 doesn't support any annotation to let us make a multi-key out of the IDs of the foreign objects in the ParticipantRoleInWork object. (Does JPA2 make this possible?) Enter the embeddable PK object, with each of the foreign key id fields in it. I can add an embeddable PK object to my entity as an embedded ID. But now what fields do I need to populate in the ParticipantRoleInWork object so that JPA can do its magic - just the Work, Participant, and Role members? Just the key fields in the PK object? Do I need to get rid of the Work, Participant, and Role members from my entity altogether and leave it empty except for the PK? Can I cascade through this? If so, where do I put the cascade annotations?

You see the problem? The book didn't go into any of this and it is hard to extrapolate it from what was stated.

As an aside, I could give these auto-generated id keys of their own to sidestep the ambiguities but it seems like overkill. It certainly isn't something I would do for such a table if I weren't using an ORM, and having ORM limitations influence the database design seems like the tail wagging the dog.

Lupestro
reza_rahman (456) [Avatar] Offline
#2
Re: Question about JPA with multi-key entities involving foreign keys...
Lupestro,

Not sure I quite follow this. Could you use a real-world application case? Does @JoinColumns (http://java.sun.com/javaee/5/docs/api/javax/persistence/JoinColumns.html) solve this issue? On first blush I would say simply use a surrogate key for the "join table".

Thanks,
Reza
lupestro (7) [Avatar] Offline
#3
Re: Question about JPA with multi-key entities involving foreign keys...
Many thanks, Reza,

> Not sure I quite follow this. Could you use a real-world application case?

Actually, this case was from my attempts to build an on-line catalog of my library of books, music, and video that I can reference from my Blackberry smilie. Whether this is a real-world goal or not is debatable, but I'd like to think it is and it does let me showcase my command of the various technologies used relatively easily.

> Does @JoinColumns (http://java.sun.com/javaee/5/docs/api/javax/persistence/JoinColumns.html) solve this issue?

It might, but then, in the sample given in the Javadoc, what would Address look like in terms of fields and annotations?

> On first blush I would say simply use a surrogate key for the "join table".

Yeah, except that all of the foreign key references are radiating outward from this table, so no table in the database would ever reference that surrogate key. Maybe it will help if I lay out the SQL. I've changed the names slightly because the nature of one of these works is a little more complex, but this has all of the interesting parts for this problem.

CREATE TABLE IF NOT EXISTS Works (
id INT NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
... more fields here...,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS Participants (
id INT NOT NULL AUTO_INCREMENT,
full_name TEXT NOT NULL,
... more fields here...,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS RoleTypes (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS Roles (
work_id INT NOT NULL,
participant_id INT NOT NULL,
role_type INT NOT NULL,
PRIMARY KEY (work_id, participant_id, role_type),
FOREIGN KEY (work_id) REFERENCES Works(id),
FOREIGN KEY (participant_id) REFERENCES Participants(id),
FOREIGN KEY (role_type) REFERENCES RoleTypes(id)
);

Thus Barbra Streisand (participant) is director, actress, producer, and writer (role types) for the movie "Yentl" (work) and a performer (role type) on the album "Pins and Needles" (work).

Does this make things clearer?

The Roles table is purely a relationships table, but it is a mutual relationship around three different kinds of data rather than two. All of its relationships radiate outward. It is never referenced by other tables.

I'll need an @Entity class for each of these:
- The RoleType class is really simple - just an integer Id field and a String field.
- The Work class will have an integer Id field and a Set<Role> field called participantRoles, among others.
- The Participant class will have an integer Id field and a Set<Role> field called workRoles, among others.
- The Role class will have three members, a Work, a Participant, and a RoleType.
No table contains any IDs for other tables so far, just object references for the entities.
Am I doing well so far?

I can declare workRoles and participantRoles from the Work and Participant classes as @ManyToOne. But the serious questions begin with the Role class...

- Do I need a RolePK class with the three keys (just the IDs) in it?
- Or should Role not contain Work, Participant or RoleType fields but just the RolePK field?
Assuming I need both...
- What annotations do I place on the ids in the RolePK class?
- What annotations do I place on the work, participant, and roleType fields of the Role class?
And now the big questions...
- If I want to insert a new role into the database, which fields do need to I fill in before calling persist()?
- How about if I query for a list of all the Role objects? What fields get filled in?

Do you see where I'm getting confused? The description in the book didn't quite go quite far enough to help me to understand what I would need to do to perform CRUD operations on entities with multi-field keys, especially when these entities are referenced in collections from other entities. It gets even more interesting when I introduce cascades into the mix and I'm trying to store roles and participants as a side effect of a call to persist(work).

Hopefully this is clearer than my first attempt.

Regards,
Lupestro
lupestro (7) [Avatar] Offline
#4
Re: Question about JPA with multi-key entities involving foreign keys...
I dug into Section 2 of JSR-317 this morning hoping to find some enlightenment. smilie I found a couple of helpful things there so my whole question has boiled down, I think, to something much simpler.

The first insight is that since the references all run from the Role to the Participant, Work, and RoleType tables, the Role must be the _owning entity_ for all three of these relationships. [JSR Section 2.9, bullet 2 of first set of bullets.] This is also spelled out in chapter 8 of the book.

The second insight is that all em.persist() cascades must flow from the owning entity to the owned entities. Thus, I cannot cascade from Work or Participant to Role, but I can cascade from Role to either Work or Participant (or both). This makes sense but wasn't spelled out in section 9.3.1 of the book. Maybe it was spelled out elsewhere and I missed it.

This, in turn, means that if Work is the owned entity in several of these kinds of relationships, it would have to be em.persist()ed first and then the owning entities for each of these would be persist()ed in turn. I can't set up the whole structure of interlocking relationships with cascades defined and em.persist() one root object to cascade the whole thing by flood. Maybe something in 9.3 would help to set expectations about what can and cannot be cascaded.

The third insight came from an example in the JSR - 2.4.1.2 Example 1 case (b), which gave clear guidance on how what I am trying to do should look.

@Entity
public class Employee {
@Id long empId;
String name;
...
}

@Embeddable
public class DependentId {
String name;
long empPK; // corresponds to PK type of Employee
}

@Entity
public class Dependent {
@EmbeddedId DependentId id;
...
@MappedById("empPK") // maps to empPK attribute of embedded id
@ManyToOne Employee emp;
}

It would be useful if section 8.3 of the next edition of the book had an example showing the use of foreign keys in composite key objects, since it is such a common occurrence. It is a far richer example than the one in 7.2.3 of the book, but couldn't really be introduced pedagogically until chapter 8. Chapter 8 does a good job of revisiting a lot of the situations from Chapter 7 in the context of tables, columns, and relationships. This is one more that deserves a clear treatment.

The final insight is that the @Embeddable PK object exists only to keep state and isn't itself persistent. The JSR has a caveat about letting JPA update the composite key and not touching it by hand [JSR Section 2.4 last paragraph before 2.4.1] but I can't see how I would persist() such an entity if I couldn't fill in its key fields.

That leaves just one very small unanswered question. When I want to persist() a Dependent in the above example, I presume that I would need to populate id.name since it doesn't appear elsewhere. However, would I populate just emp, just id.empPK, or both? The relevant information for the INSERT appears in both places.

Regards,
Lupestro
reza_rahman (456) [Avatar] Offline
#5
Re: Question about JPA with multi-key entities involving foreign keys...
Lupestro,

Thanks for the suggestion. Since we are de-emphasizing JPA 2 in favor of EJB 3.1 and CDI for the possible next edition, I doubt we'll have room for this use-case.

Thanks,
Reza