[JPA] How to handle many-to-many link tables with additional columns

Question

Assume the following exemplary DB-Design:

USERS
-----------------
ID
NAME


ROLES
-----------------
ID
NAME


USERS_ROLES
-----------------
FK_USER_ID
FK_ROLE_ID
LOGIN
PASSWD

I map this relation with eclipse-link using xml. The users:

<many-to-many name="roles" mapped-by="users">
  <join-table name="USERS_ROLES">
    <join-column name="USER_ID" />
    <inverse-join-column name="ROLE_ID" />
  </join-table>
</many-to-many>

For the sake of completeness, the corresponding mapping of the roles:

<many-to-many name="users" mapped-by="roles">
  <join-table name="USERS_ROLES">
    <join-column name="FK_ROLE_ID" />
    <inverse-join-column name="FK_USER_ID" />
  </join-table>
</many-to-many>

Now I could also create a mapping for the link-table, but what I have to do so that I can access the properties of the linked table (mapped to the java object/class)? I need it if the users will login, choose their role of interest and enter the accordant password. With the current setup, of course, I can get all the roles the user is assigned to and vice versa, but I cannot compare against the login data, or any other properties corresponding to the relation of user and role. In short: I have no clue how to access the properties of the linked table or how I have to map this in the mapping-xml.

Thx a lot in advance!

Answer 1

It isn't an M-N though. It's 2 1-N's with an intermediate class (containing the login and password).