JPA @JoinTable with composite (2 column) primary keys-CodePudding

In a spring-boot app, I've got the following entity definition:

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

    @Id
    @Column(nullable = false, name = "username", length = 100)
    private String username;

    @JoinTable(name = "userrole", 
        joinColumns = { @JoinColumn(name = "username") },
        inverseJoinColumns = { @JoinColumn(name = "role") }
    )
    @OneToMany(
        cascade = CascadeType.ALL, 
        orphanRemoval = true
    )
    private List<Role> roles;`

I'm using Spring-data-jpa,Hibernate with H2 as the database. The trouble is that spring-data-jpa, hibernate always generate/creates the join table (DDL) 'userrole' with a single column primary key. e.g. 'username'. Hence, if records such as {'username', 'user_role'} and {'username', 'admin_role'} is inserted in the join table ('userrole'), the next insert fails with an error due to the 'duplicate' primary key.

I've tried using both columns in the above definition, as well as the following variation:

    @OneToMany(
        cascade = CascadeType.ALL, 
        orphanRemoval = true
    )
    @JoinColumns({
           @JoinColumn(name = "username"),
           @JoinColumn(name = "role") })
    private List<Role> roles;`

But that they resulted in the same or worse problems, e.g. and in the latter, even table creation fails because only a single column is used as primary key for the jointable. Role is simply another table with 2 columns 'role' and 'description', basically a role catalog.

How do we specify to JPA that the @JoinTable should use both 'username' and 'role' columns as composite primary keys?

CodePudding user response：

If Role only has two columns, eg user_id and role, the way to map this in jpa would be as following

@ElementCollection
@CollectionTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id"))
@Column(name = "role")
List<String> roles = new ArrayList<>();

Otherwise, jpa really requires each entity's identifier and join columns to be separate columns, so Role entity would have to have columns like id, user_id and role_name. Could look like this .:

class Role {
  @Id
  Long id;
  @ManyToOne
  @JoinColumn(name = "user_id", referencedColumnName = "id");
  User user;
  String roleName;
  // Other fields
}

And in the User entity

@OneToMany(mappedBy = "user") // user is Field's name, not a column
List<Role> roles = new ArrayList<>();