使用复合键休眠复合键

Question

我目前正在映射一个复杂的数据库模式wqith HIbernate，而我遇到了一个实体，该实体有一个复合键和另一个复合密钥。

我有一个带有复合密钥(site_id，id(的角色表

CREATE TABLE IF NOT EXISTS core.roles
(
id uuid NOT NULL DEFAULT gen_random_uuid(),
name character varying(100) NOT NULL,
is_system_role boolean NOT NULL DEFAULT FALSE,
site_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL DEFAULT now(),
updated_at timestamp with time zone NOT NULL DEFAULT now(),
created_by uuid NOT NULL,
updated_by uuid NOT NULL,
CONSTRAINT roles_pkey PRIMARY KEY (site_id, id),
CONSTRAINT roles_name_key UNIQUE (site_id, name),
CONSTRAINT roles_site_id_fkey FOREIGN KEY (site_id)
REFERENCES core.sites (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT roles_created_by_fkey FOREIGN KEY (created_by)
REFERENCES core.users (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE RESTRICT,
CONSTRAINT roles_updated_by_fkey FOREIGN KEY (updated_by)
REFERENCES core.users (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE RESTRICT
);

我有一个带有复合键的表，它也使用了前一个。

CREATE TABLE IF NOT EXISTS core.user_site_roles
(
user_id uuid NOT NULL,
site_id uuid NOT NULL,
role_id uuid NOT NULL,
created_at timestamp with time zone NOT NULL DEFAULT now(),
created_by uuid NOT NULL,
CONSTRAINT user_site_roles_pkey PRIMARY KEY (site_id, user_id, role_id),
CONSTRAINT user_site_roles_site_id_fkey FOREIGN KEY (site_id)
REFERENCES core.sites (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT user_site_roles_role_id_fkey FOREIGN KEY (site_id, role_id)
REFERENCES core.roles (site_id, id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT user_site_roles_user_id_fkey FOREIGN KEY (user_id)
REFERENCES core.users (id) MATCH SIMPLE
ON UPDATE NO ACTION

CONSTRAINT user_site_roles_created_by_fkey FOREIGN KEY (created_by)
REFERENCES core.users (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE RESTRICT
);

我目前正在工作的角色映射是：

@Embeddable
public class CommonId implements Serializable {
@Type(type = "pg-id-uuid")
@Column(columnDefinition = "uuid", updatable = false)
private UUID id;
@Type(type = "pg-id-uuid")
@Column(name = "site_id", columnDefinition = "uuid", updatable = false)
private UUID siteId;
}
@Entity
@Table(name = "roles", schema = "core")
@Data
@TypeDefs({
@TypeDef(name = "pg-id-uuid", typeClass = PostgresIdUUIDType.class)
})
public class Role extends AuditAtBy implements Serializable {
@EmbeddedId
private CommonId roleId;
@ManyToOne
@MapsId("siteId")
@OnDelete(action = OnDeleteAction.CASCADE)
private Site site;
@Column(nullable = false, unique = true, length = 100)
private String name;
@Column(name = "is_system_role", nullable = false)
private boolean isSystemRole;
}

我尝试了类似的方法来处理UserSiteRole的复合密钥，但Hibernate告诉我，当我在表中只有id时，它需要列来映射roleId，但正如你在脚本中看到的那样，PK是由两个值形成的，不确定如何实实在在地映射它。

@Embeddable
public class UserSiteRoleId implements Serializable {
@Type(type = "pg-id-uuid")
@Column(columnDefinition = "uuid", updatable = false)
private UUID userId;
@Type(type = "pg-id-uuid")
@Column(name = "site_id", columnDefinition = "uuid", updatable = false)
private UUID siteId;
@Type(type = "pg-id-uuid")
@Column(name = "role_id", columnDefinition = "uuid", updatable = false)
private UUID roleId;
}
@Entity
@Table(name = "user_site_roles", schema = "core")
@Data
@TypeDefs({
@TypeDef(name = "pg-id-uuid", typeClass = PostgresIdUUIDType.class)
})
public class UserSiteRole extends AuditCreated implements Serializable {
@EmbeddedId
private UserSiteRoleId userSiteRoleId;
@ManyToOne
@MapsId("userId")
@JoinColumn(name = "user_id", columnDefinition = "uuid", nullable = false)
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private User user;
@ManyToOne
@MapsId("siteId")
@JoinColumn(name = "site_id", columnDefinition = "uuid", nullable = false)
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private Site site;
@ManyToOne
@MapsId("roleId")
@JoinColumn(name = "role_id", columnDefinition = "uuid", nullable = false)
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private Role role;
}

我很感激关于如何绘制它的任何想法，我从来没有绘制过如此复杂的关系，所以不确定在这种情况下如何进行。

Answer 1

这能回答你的问题吗？jpa-hibernate复合外键映射

实际上，这很有用，因为它清楚地表明，我们可以将映射从embeddedId更改为IdClass并使其工作。

这是我们的新IdClass，非常简单：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserSiteRoleId implements Serializable {
private User user;
private Site site;
private Role role;
}

仅在infe中工作的实体本身如下。

@Entity
@Table(name = "user_site_roles", schema = "core")
@Data
@TypeDefs({
@TypeDef(name = "pg-id-uuid", typeClass = PostgresIdUUIDType.class)
})
@IdClass(UserSiteRoleId.class)
public class UserSiteRole extends AuditCreated implements Serializable {
@Id
@ManyToOne
@JoinColumn(name = "user_id", columnDefinition = "uuid", nullable = false)
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private User user;
@Id
@ManyToOne
@JoinColumn(name = "site_id", columnDefinition = "uuid", nullable = false)
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private Site site;
@Id
@ManyToOne
@JoinColumns({
@JoinColumn(name = "role_id", referencedColumnName="id", columnDefinition = "uuid", insertable = false, updatable = false),
@JoinColumn(name = "site_id", referencedColumnName="site_id", columnDefinition = "uuid", insertable = false, updatable = false)
})
@Type(type = "pg-id-uuid")
@OnDelete(action = OnDeleteAction.CASCADE)
private Role role;
}