JPA @JoinTable使用复合(2列)主键

huangapple go评论81阅读模式
英文:

JPA @JoinTable with composite (2 column) primary keys

问题

在一个Spring Boot应用中,我有以下的实体定义:

@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;
}

我正在使用Spring Data JPA、Hibernate,并使用H2数据库。问题是,Spring Data JPA和Hibernate总是生成/创建具有单列主键的连接表(DDL),例如'username'。因此,如果在连接表('userrole')中插入记录,例如{'username','user_role'}和{'username','admin_role'},下一次插入会因为'重复'主键而失败。

我尝试过在上面的定义中同时使用两列,以及以下变化:

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

但它们导致了相同或更糟糕的问题,例如在后者中,甚至表的创建失败,因为连接表只使用单列作为主键。Role只是另一个具有'role'和'description'两列的表,基本上是一个角色目录。

我们如何告诉JPA,@JoinTable应该使用'username'和'role'列作为复合主键?

编辑:
我尝试使用独立的表/实体,如@Kamil Bęben建议,谢谢@Kamil Bęben

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

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

	@OneToMany(
		fetch = FetchType.EAGER,
		cascade = CascadeType.ALL, 
		mappedBy = "username",
		orphanRemoval = true
	)
	@ElementCollection
	private List<UserRole> roles;
}

UserRole被定义如下:

@Data
@NoArgsConstructor
@Entity
@Table(name = "userrole")
public class UserRole {

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "userrole_seq")
	Long id;	
	
	@Column(nullable = false, name = "username", length = 100)
	private String username;

	@Column(nullable = false, name = "role", length = 50)
	private String role;
}

用于用户角色连接表的仓库被定义如下:

@Repository
public interface UserRoleRepository extends CrudRepository<UserRole, Long> {

	UserRole findByUsernameAndRole(String username, String role);
	
	List<UserRole> findByUsername(String username); 
	
	List<UserRole> findByRole(String role); 
}

诚然,这样做有点不太优雅,但它是有效的。而且,它似乎以某种方式使用了正确的findByUsername()方法来检索与用户相关的角色,这可能与'mappedBy'子句有关。有点像'黑魔法'!我仍然需要在JPA、Spring和Spring Data中找到更多的东西。

编辑2:
进一步更新:
原始的@JoinTable也可以工作。
但关系需要被指定为@ManyToMany

@ManyToMany(
		fetch = FetchType.EAGER,
		cascade = CascadeType.MERGE
)
@JoinTable(name = "usersroles", 
	joinColumns = {	@JoinColumn(name = "username") },
	inverseJoinColumns = { @JoinColumn(name = "role") }
)
private List<Role> roles = new ArrayList<Role>();

这将如预期地为'users-roles'表创建两列主键。

感谢@Roman。

英文:

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

@Data
@Entity
@Table(name = &quot;users&quot;)
public class User {

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

	@JoinTable(name = &quot;userrole&quot;, 
		joinColumns = {	@JoinColumn(name = &quot;username&quot;) },
		inverseJoinColumns = { @JoinColumn(name = &quot;role&quot;) }
	)
	@OneToMany(
		cascade = CascadeType.ALL, 
		orphanRemoval = true
	)
	private List&lt;Role&gt; 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 = &quot;username&quot;),
           @JoinColumn(name = &quot;role&quot;) })
	private List&lt;Role&gt; 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?

edit:
I tried using an independent table/entity as suggested, thanks @Kamil Bęben

@Data
@Entity
@Table(name = &quot;users&quot;)
public class User {

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

	@OneToMany(
		fetch = FetchType.EAGER,
		cascade = CascadeType.ALL, 
		mappedBy = &quot;username&quot;,
		orphanRemoval = true
	)
	@ElementCollection
	private List&lt;UserRole&gt; roles;

UserRole is defined as such

@Data
@NoArgsConstructor
@Entity
@Table(name = &quot;userrole&quot;)
public class UserRole {

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = &quot;userrole_seq&quot;)
	Long id;	
	
	@Column(nullable = false, name = &quot;username&quot;, length = 100)
	private String username;

	@Column(nullable = false, name = &quot;role&quot;, length = 50)
	private String role;

the repository for that user-roles join table is defined as

@Repository
public interface UserRoleRepository extends CrudRepository&lt;UserRole, Long&gt; {

	UserRole findByUsernameAndRole(String username, String role);
	
	List&lt;UserRole&gt; findByUsername(String username); 
	 	
	List&lt;UserRole&gt; findByRole(String role); 
	
}

Admittedly, ugly, but that it works. And that somehow, it seemed to use the correct findByUsername() method to retrieve the roles as is relevant to the user, probably related to the 'mappedBy' clause. 'black magic'! There's lots more that I'd still need to find my way around JPA, Spring, Spring-data

edit2:
further update:
the original @JoinTable works as well.
But that the relations need to be specified as @ManyToMany

	@ManyToMany(
			fetch = FetchType.EAGER,
			cascade = CascadeType.MERGE
	)
	@JoinTable(name = &quot;usersroles&quot;, 
		joinColumns = {	@JoinColumn(name = &quot;username&quot;) },
		inverseJoinColumns = { @JoinColumn(name = &quot;role&quot;) }
	)
	private List&lt;Role&gt; roles = new ArrayList&lt;Role&gt;();

This creates 2 column primary keys as expected for the 'users-roles' table

Thanks to @Roman

答案1

得分: 1

如果Role只有两列,例如user_id和role,那么在JPA中进行映射的方式如下:

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

否则,JPA要求每个实体的标识符和连接列必须是不同的列,所以Role实体必须具有类似于id、user_id和role_name的列。可能会如下所示:

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

在User实体中:

@OneToMany(mappedBy = "user") // user是字段的名称,不是列名
List<Role> roles = new ArrayList<>();

更多阅读

英文:

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 = &quot;user_roles&quot;, joinColumns = @JoinColumn(name = &quot;user_id&quot;))
@Column(name = &quot;role&quot;)
List&lt;String&gt; roles = new ArrayList&lt;&gt;();

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 = &quot;user_id&quot;, referencedColumnName = &quot;id&quot;);
  User user;
  String roleName;
  // Other fields
}

And in the User entity

@OneToMany(mappedBy = &quot;user&quot;) // user is Field&#39;s name, not a column
List&lt;Role&gt; roles = new ArrayList&lt;&gt;();

Further reading

huangapple
  • 本文由 发表于 2023年1月9日 02:54:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/75050476.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定