Override @CollectionTable reference to primary-key column

Question

In short

JPA documentation says that CollectionTable.joinColumns property is "The foreign key columns of the collection table which reference the primary table of the entity."

Can I make it reference not the primary key column of the entity table, but another column which has unique values?

Details

There is a legacy database with following tables (simplified):

staff:
  user_id (int)
  user_name (varchar, primary key)

staff_privileges:
  id_user (int)
  privileges_id (int) 
  privileges_status (boolean)

Now I'm introducing Hibernate into the project and trying to map this tables to the entity:

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

  @Column(name = "user_id")
  private Integer userId;

  @Id
  @Column(name = "user_name")
  private String userName;    // primary key column (legacy, cannot be changed)

  @ElementCollection
  @CollectionTable(name = "staff_privileges", joinColumns = @JoinColumn(name = "id_user"))
  @MapKeyColumn(name = "privileges_id")
  @Column(name = "privileges_status")
  private Map<Integer, Boolean> privileges; // privilegeId <-> isEnabled
}

Won't work because @CollectionTable creates reference to primary key: staff_privileges.id_user -> staff.user_name (primary key). It should be: staff_privileges.id_user -> staff.id_user. Is there way to override this reference? Are there any Hibernate or JPA annotations for this? I would like to keep things simple and not introduce any new Entity or Embeddable if possible.

Answer 1

Assuming that user_id in also unique for each User then you could simply move the @Id annotation on User to userId: your JPA provider has no knowledge of what the actual PK is in the database so as long as that value is unique it would not cause any issues.

Slightly different context but see:

https://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#No_Primary_Key

The JPA Id does not always have to match the database table primary key constraint, nor is a primary key or a unique constraint required.

The only difference would be if you were reverse engineering your tables from your mappings then the staff table would be created with PK on user_id rather than user_name.

Answer 2

You can use refrencedColumnName to map id_user instead of user_name as a join column.

Hibernate documentation has a more concise description about this parameter:

The join column is declared with the @JoinColumn annotation which looks like the @Column annotation. It has one more parameters named referencedColumnName. This parameter declares the column in the targeted entity that will be used to the join. Note that when using referencedColumnName to a non primary key column, the associated class has to be Serializable. Also note that the referencedColumnName to a non primary key column has to be mapped to a property having a single column (other cases might not work).

So in your case (tested with Hibernate 5.4.29.Final):

(I implemented Serializable to avoid a related bug to referencedColumnName)

@Entity
@Table(name = "staff")
public class User implements Serializable {
  private static final long serialVersionUID = 1L;
  ...

  @ElementCollection
  @CollectionTable(name = "staff_privileges", joinColumns = @JoinColumn(name = "id_user", referencedColumnName = "user_id"))
  @MapKeyColumn(name = "privileges_id")
  @Column(name = "privileges_status")
  private Map<Integer, Boolean> privileges;
}

For more check this SO question: What is referencedColumnName used for in JPA?