JPA Mapping - Composite Key in Relationship

Question

I have a problem with JPA mapping for a particular relation. In my MySQL database I have 3 entity in relationship:

USER
USER_SERIAL_NUMBER VARCHAR(20) not null,
..... VARCHAR(4) not null,
..... VARCHAR(3) not null,
..... VARCHAR(10) not null,
primary key (USER_SERIAL_NUMBER)
);
OFFICE (
OFFICE_SERIAL_NUMBER VARCHAR(20) not null,
TOWN VARCHAR(15) not null,
YEAR VARCHAR(4) not null,
DESCRIPTION VARCHAR(200) not null,
NOTE VARCHAR(100) not null,
CREATION_DATE DATE not null,
LAST_UPDATE_DATE DATE not null,
primary key (OFFICE_SERIAL_NUMBER)
);

ROLE_TYPE (
ROLE_ID BIGINT not null,
DESCRIPTION VARCHAR(20) not null,
CREATION_DATE DATE not null,
LAST_UPDATE_DATE DATE not null,
primary key (ROLE_ID)
);

and the relation table

ROLE (
ROLE_ID BIGINT not null,
USER_SERIAL_NUMBER VARCHAR(20) not null,
OFFICE_SERIAL_NUMBER VARCHAR(20) not null,
YEAR VARCHAR(4) not null,
CREATION_DATE DATE not null,
LAST_UPDATE_DATE DATE not null,
primary key (ROLE_ID, USER_SERIAL_NUMBER, OFFICE_SERIAL_NUMBER, YEAR)
);

In the relation table I have a composite key with the 3 foreign key and a local key (the year), because a user can have more (different) role on particular office in a specified year but probably will change the next year (I have to mantain the historic role).

I have made many attempts to map these tables in entities but I didn't yet found the right mix so that everything works.

I would like to navigate the relationship in both direction, so I need to know who and how many are users who have a particular role in a particular office, but I also need to know in how many offices work a particular user, and what role he has.

Here is the Java code:

@Entity
@Scope(ScopeType.CONVERSATION)
@Name("role")
@Table(name = "ROLE")
@IdClass(RolePK.class)
public class Role implements Serializable {

private static final long serialVersionUID = 111111111L;

@Id
@OneToOne
@Column(name="USER_SERIAL_NUMBER")
private User User;

@Id
@OneToOne
@Column(name="OFFICE_SERIAL_NUMBER")
private Office office;

@Id
@OneToOne
@Column(name="ROLE_ID")
private RoleType roleType;

@Id
@Column(name = "YEAR", length = 4, nullable = false)
private String year;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "CREATION_DATE", nullable = false)
private Date creationDate;

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "LAST_UPDATE_DATE", nullable = false)
private Date lastUpdateDate;

… getter and setter … 
… equals and hash code …
… toString …
}

@Name("rolePK")
public class RolePK implements Serializable {

private static final long serialVersionUID = 2222222222222L;

private String office;
private Long roleType;
private String User;
private String year;

public RolePK() {

… getter and setter … 
… equals and hash code …
… toString …
}
@Entity
@Scope(ScopeType.CONVERSATION)
@Name("user")
@Table(name = "USER")
public class User implements Serializable {

private static final long serialVersionUID = 333333333333L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "USER_SERIAL_NUMBER", length = 20, nullable = false)
private String serialNumber;

… other properties… 

@OneToMany(mappedBy="user")
private Collection<Role> roleCollection;

… getter and setter … 
… equals and hash code …
… toString …


@Entity
@Scope(ScopeType.CONVERSATION)
@Name("office")
@Table(name = "OFFICE")
public class Office implements Serializable {

private static final long serialVersionUID = 55555555555555555L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="OFFICE_SERIAL_NUMBER", length=20, nullable=false)
private String officeSerialNumber;
… other properties… 

@OneToMany(mappedBy="office")
private Collection<Role> roleCollection;

… getter and setter … 
… equals and hash code …
… toString …

@Entity
@Scope(ScopeType.CONVERSATION)
@Name("roleType")
@Table(name = "ROLE_TYPE")
public class RoleType implements Serializable {

private static final long serialVersionUID = 44444444444444444L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ROLE_TYPE_ID", nullable = false)
private Long id; 
… other properties… 

@OneToMany(mappedBy="roleType")
private Collection<Role> roleCollection;

… getter and setter … 
… equals and hash code …
… toString …

I tried to implement this solution (which I had already tried before), but I still have configuration problems. In this particular configuration, the error I have is that when Hibernate tries to create a query, does not seem to be able to generate a proper query. In particular, I have the names of the properties that are not equal to the names of the columns of the relationship table. I tried to use annotations to specify the name of the column on which to bind the property, but the query is generated using the property name and not the name of the column. Thus, the system throws an exception, specifically: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'roleco0_.user'in 'field list'.

Someone can help me? Thanks in advance

Answer 1

Why don't you introduce an autogenerated ID in the Role table, and a unique constraint on the tuple (roleId, use, office, year). This would make sthings much much simpler.

If you really want to continue with this composite primary key, then you should read http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#mapping-declaration-id, which ends with an example of an embedded ID containing an association. But be careful with your associations : they should be ManyToOne, and not OneToOne, since a user (for example) can be referenced by several roles.

You should thus have

@Embeddable
public class RoleId {
    @ManyToOne
    private User user;

    @ManyToOne
    private Office office;

    @ManyToOne
    private RoleType roleType;

    private int year;

    // constructor, getters, equals, hashCode
}

@Entity
public class Role {
    @EmbeddedId
    private RoleId id;

    private Date creationDate;
    // ...
}