Additional Columns in a ManyToMany mapping using Java Persistence API (TopLink)

The Java Persistence API is a really neat thing but it can drive you up-the-wall when you’re trying to setup your entities and annotations to match your desired database schema. In this example, I’ll cover a seemingly simple case of having a many-to-many relationship between two entities with additional columns in the association table. In other words, the following model:

CREATE TABLE USER (
  ID BIGINT AUTO_INCREMENT NOT NULL,
  USERNAME VARCHAR(255),
  PASSWORD VARCHAR(255), PRIMARY KEY (ID))

CREATE TABLE ROLE (
  ID BIGINT AUTO_INCREMENT NOT NULL,
  NAME VARCHAR(255),
  PRIMARY KEY (ID))

CREATE TABLE USER_ROLE (
  USER_ID BIGINT NOT NULL,
  ROLE_ID BIGINT NOT NULL,
  PRIMARY KEY (USER_ID, ROLE_ID),
  ENABLED TINYINT(1) default 0)

ALTER TABLE USER_ROLE ADD CONSTRAINT
  FK_USER_ROLE_USER_ID FOREIGN KEY (USER_ID)
  REFERENCES USER (ID)
ALTER TABLE USER_ROLE ADD CONSTRAINT
  FK_USER_ROLE_ROLE_ID
  FOREIGN KEY (ROLE_ID) REFERENCES ROLE (ID)

It’s a fairly simple example where a USER can have many ROLEs while a ROLE may belong to many USERs. Now if it weren’t for the extra column ENABLED on the USER_ROLE table, we could’ve easily created a User and Role class and created a property called Set<Role> roles in the User class with the @ManyToMany annotation, much like the @ManyToMany JavaDoc example. However, we can’t do that here because that approach completely ignores any extra columns that you might need besides the foreign keys of User and Role.

The solution is to create an entity mapped to the association table, USER_ROLE and store a collection of this entity in the User and Role classes. So we create a @OneToMany mapping between User and UserRole (and Role and UserRole) instead of a simple @ManyToMany mapping. Since this is a bi-directional relationship, we also need to specify a @ManyToOne mapping for both User and Role in the UserRole class. Here is the complete Java code:

@Entity
@Table(name="USER")
public class User {

  @Id
  @GeneratedValue(strategy=GenerationType.IDENTITY)
  private Long id;

  @Column(name="USERNAME")
  private String username;

  @Column(name="PASSWORD")
  private String password;

  @OneToMany(mappedBy="user")
  private Set<UserRole> userRoles;

}

@Table(name="ROLE")
@Entity
public class Role {

  @Id
  @GeneratedValue(strategy=GenerationType.IDENTITY)
  private Long id;

  @Column(name="NAME")
  private String name;

  @OneToMany(mappedBy="role")
  private Set<UserRole> userRoles;
}

@Entity
@Table(name="USER_ROLE")
@IdClass(UserRolePK.class)
public class UserRole {

  @Id
  @Column(name="USER_ID",insertable=false,
                         updatable=false)
  private Long userId;

  @Id
  @Column(name="ROLE_ID",insertable=false,
                         updatable=false)
  private Long roleId;

  @ManyToOne
  @JoinColumn(name="USER_ID")
  private User user;

  @ManyToOne
  @JoinColumn(name="ROLE_ID")
  private Role role;

  @Column(name="ENABLED")
  private boolean enabled;
}

public class UserRolePK {

  private Long userId;
  private Long roleId;
}

The Java Persistence API requires you to define an @IdClass if a composite key is being used. The properties of the @IdClass must match those of the source class (the class its an id for) and must not have any other fields.

There is a bi-directional mapping between User and UserRole (and Role and UserRole) and that is why we need to specify a @OneToMany and a @ManyToOne in the User/Role and UserRole classes respectively. We also need to specify mappedBy=”user” (and mappedBy=”role”) in the User (and Role) classes.

An oddity here is the insertable=false and updatable=false on the userId and roleId fields in the UserRole class. If you do not specify this, TopLink will complain that these fields are writable in more than once sense, and it would be right about doing so. userId and roleId are already writable in their respective entity classes, User and Role. It would not make any sense for the values in the association table to change without the entity table being updated. Making the field read-only fixes this problem. You can read more about this specific issue.

Advertisements

4 thoughts on “Additional Columns in a ManyToMany mapping using Java Persistence API (TopLink)

  1. tono

    question…
    what should i do to apply ‘on delete cascade’ constraint
    so that when i delete a role or user, referenced data in userrole will also get deleted ?
    thank’s

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s