EclipseLink: is it possible to add @OneToMany relationship to class on an @EmbeddedId property as foreign key?

Question

I want to do a Jaas login module using JPA to store my AuthUser and AuthUserRole. I'll focus on the JPA side on this question.

Here is what I would do in the Database (not at all legitimate SQL code but hopefully comprehensive):

TABLE AuthUser( INT uid, VARCHAR password )
PRIMARY KEY (uid)

TABLE AuthUserRole( INT uid, INT role )
PRIMARY KEY (uid , role)
FOREIGN KEY (uid) REFERENCE AuthUser.uid

It makes sense to me, one role can only be assigned to a user once.

Here is what I attempted to do in Java, not showing username and email in AuthUser:

@Entity
public class AuthUser {
    @Id
    private int uid;

    private String password;

    @OneToMany
    private Set<AuthUserRole> roles;
}

@Entity
public class AuthUserRole {

    @Embeddedid
    private AuthUserRolePK authUserRolePK;
}

@Embeddable
public class AuthUserRolePK {
    public int uid;
    public int role;
}

Eclipselink does the mapping just fine, which means it works, but not the way I wanted. It makes a third table named *authuser_authuserrole* that holds the (AuthUser_uid , uid, role) columns. No need to mention AuthUser_uid and uid is identical.

The trivial answer would be:

@Entity
public class AuthUser {
    @Id
    private int uid;

    private String password;

    @OneToMany
    @JoinColumn(name="authUserRolePK.uid", referencedColumnName="uid")
    private Set<AuthUserRole> roles;
}

But EclipseLink cryes that when @Embeddedid is used, all primary key columns have to be mentioned.

Q: How do I make Eclipselink map my entities like the database schema I mentioned? Should I rather use @IdClass? I could see the result of a database --> entities mapping but that's too easy :)

Thank you for your answers!

Answer 1

Three tables is the typical way to do this actually, your approach is simply lacking a little bit of JPA finesse.

Typically you have three tables associated as follows:

AuthUser        AuthUser_Role (association)       Role
frank     ----         frank,admin       -----    admin

This is in fact what Eclipselink was trying to map for you, but in general, you don't create the AuthUser_Role mapping yourself. Instead, you create a field on AuthUser like:

@ManyToMany
Set<Roles> userRoles

And (optionally) on Role like:

@ManyToMany(mappedBy="userRoles")
Set<AuthUser> usersWithRole;

Then EclipseLink takes care of the join table for you, and all you need to worry about is the userRoles field, which will update the join.

You can extend this to create the join manually for say roles that start and end on a set date, etc, but for all but the most complex projects, that's usually not necessary, and can often be accomplished in a different way. For compliance purposes, it's easier to use one of the ELUG extensions to keep and access a history of changes for example, which is the most common reason I've seen for adding extra meta-data to the join information.

---

Alternatively, if you REALLY want to do this with only two tables, make the AuthUserRole the primary side, and the AuthUser the passive side.

@Entity
public class AuthUser {
    @Id
    private int uid;

    private String password;

    @OneToMany(mappedBy="user")
    private Set<AuthUserRole> roles;
}

@Entity
public class AuthUserRole {

    @Embeddedid
    private AuthUserRolePK authUserRolePK;
}

@Embeddable
public class AuthUserRolePK {
    public AuthUser user;
    public int role;
}

In this arrangement, you will end up with only two tables, and the "user" field will indeed be equal to the uid of AuthUser in the database, it just shows up as an object on the Java side.