java - 如何正确映射主键由两个外键组成的实体,其中一个外键本身是复合键?
问题描述
我很难弄清楚如何在某个数据库设计上正确执行 ORM。
我的架构由三个表组成:用户表、评论表和投票表。用户可以发布专辑评论,他们还可以为任何评论分配正面或负面评价。相册是从外部 API 提供的,因此架构中缺少它们的表,但会引用它们的 ID。
用户主键只包含他们的用户名。评论主键由评论者的用户名(外键)和评论专辑 ID 组成。最后,投票主键由投票者的用户名、外键和投票评论的主键组成,如前所述,由评论者的用户名和评论的专辑 ID 组成。
用户可以为每个单独的相册发布评论,也可以为每个单独的评论分配投票。
这是表示模式的 ER 模型:
为了映射实体 ID,我使用了@IdClass
注释,但我不确定我是否朝着正确的方向前进。我也尝试使用@EmbeddedId
注释,但结果是一样的。
这是我的实体类到目前为止的样子:
@Entity
public class User implements Serializable {
private static final long serialVersionUID = 1;
@Id @Column(name = "username")
private String username;
@Column(unique = true, nullable = false)
private String email;
@Column(name = "password", nullable = false)
private String password;
@Temporal(TemporalType.TIMESTAMP) @Column(name="signUpDate", nullable = false)
private Date signUpDate;
// empty constructor, getters, setters, equals and hashCode implementations
}
@Entity @IdClass(ReviewId.class)
public class Review implements Serializable {
private static final long serialVersionUID = 1;
@Id @ManyToOne @JoinColumn(name = "reviewerUsername", referencedColumnName = "username")
private User reviewer;
@Id @Column(name = "reviewedAlbumId")
private Long reviewedAlbumId;
@Column(name = "content", nullable = false, length = 2500)
private String content;
@Column(name = "rating", nullable = false)
private Integer rating;
@Temporal(TemporalType.TIMESTAMP) @Column(name = "publicationDate", nullable = false)
private Date publicationDate;
// empty constructor, getters, setters, equals and hashCode implementations
}
@Entity @IdClass(VoteId.class)
public class Vote implements Serializable {
private static final long serialVersionUID = 1;
@Id @ManyToOne @JoinColumn(name = "voterUsername", referencedColumnName = "username")
private User voter;
@Id @ManyToOne @JoinColumns({
@JoinColumn(name = "reviewerUsername", referencedColumnName = "reviewerUsername"),
@JoinColumn(name = "reviewedAlbumId", referencedColumnName = "reviewedAlbumId")
})
private Review review;
@Column(name = "vote") // @todo add attribute nullable = false
private Boolean vote;
// empty constructor, getters, setters, equals and hashCode implementations
}
这些是我的 ID 类:
public class ReviewId implements Serializable {
private static final long serialVersionUID = 1L;
private User reviewer;
private Long reviewedAlbumId;
// empty constructor, getters, setters, equals and hashCode implementations
}
public static class VoteId implements Serializable {
private static final long serialVersionUID = 1L;
private User voter;
private Review review;
// empty constructor, getters, setters, equals and hashCode implementations
}
这是用于生成模式的 MySQL 脚本的内容:
DROP SCHEMA IF EXISTS albumReviewsDatabase;
CREATE SCHEMA albumReviewsDatabase;
USE albumReviewsDatabase;
CREATE TABLE user (
username VARCHAR(20) PRIMARY KEY,
email VARCHAR(254) NOT NULL UNIQUE,
password CHAR(60) NOT NULL,
signUpDate TIMESTAMP NOT NULL DEFAULT now()
) ENGINE = INNODB;
CREATE TABLE review (
reviewerUsername VARCHAR(20) NOT NULL,
reviewedAlbumId BIGINT(20) NOT NULL,
content TEXT NOT NULL,
rating SMALLINT UNSIGNED NOT NULL,
publicationDate TIMESTAMP NOT NULL DEFAULT now(),
CHECK (rating >= 0 AND rating <= 10),
PRIMARY KEY (reviewerUsername, reviewedAlbumId),
FOREIGN KEY (reviewerUsername) REFERENCES user(username)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE = INNODB;
CREATE TABLE vote (
voterUsername VARCHAR(20) NOT NULL,
reviewerUsername VARCHAR(20) NOT NULL,
reviewedAlbumId BIGINT(20) NOT NULL,
vote BOOLEAN NOT NULL,
PRIMARY KEY (voterUsername, reviewerUsername, reviewedAlbumId),
FOREIGN KEY (voterUsername) REFERENCES user(username)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (reviewerUsername, reviewedAlbumId) REFERENCES review(reviewerUsername, reviewedAlbumId)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE = INNODB;
我目前正在使用 OpenJPA 作为 TomEE webprofile 实例上的持久性提供程序,并且使用的 JPA 版本是 2.0。
显然我误解了 JPA 的 ORM,因为当我部署包含这些实体的应用程序时,我得到以下异常:
<openjpa-2.4.2-r422266:1777108 fatal user error> org.apache.openjpa.util.MetaDataException: The id class specified by type "class application.model.Review" does not match the primary key fields of the class. Make sure your identity class has the same primary keys as your persistent type, including pk field types. Mismatched property: "reviewer"
抛出异常是因为Review
类映射,而不是Vote
类;但是,我相信通过解决Review
课堂上的问题,同样的问题会再次出现Vote
。
我更愿意使用@IdClass
注释而不是@EmbeddedId
,但是我最终将使用的两者中的哪一个都不是问题。
解决方案
这些关系是“派生的身份”;所以你的 ID 类应该是这样的(注意外键字段的类型与其对应的实体字段的类型不同):
public class ReviewId implements Serializable {
private static final long serialVersionUID = 1L;
private String reviewer; // matches name of @Id attribute and type of User PK
private Long reviewedAlbumId;
// ...
}
public static class VoteId implements Serializable {
private static final long serialVersionUID = 1L;
private String voter; // matches name of @Id attribute and type of User PK
private ReviewId review; // matches name of @Id attribute and type of Review PK
// ...
}
JPA 2.2 规范的第 2.4.1 节讨论了派生的身份(带有示例) 。
此外,作为旁注,@IdClass
有点老派,但@EmbeddedId
更干净,消除了在实体及其键之间重复的代码。
推荐阅读
- groovy - 无需通过根元素即可获取特定 json 元素的方法
- node.js - 节点[43580]:pthread_create:资源暂时不可用
- linux - 从文件中提取特定范围的 fasta 序列
- amazon-s3 - AWS S3 存储桶 URL 到自定义域链接
- actions-on-google - Google 操作状态已部署但无法从其他设备访问
- sql - 在 SQL 列中搜索字符串列表?
- powershell - 找不到接受参数“=”的位置参数
- prometheus - Prometheus查询,如何求每行的百分比
- r - R:从另一个数据帧创建间隔为 2 列的新数据帧
- google-bigquery - 将 JSON 文件从 GCS 加载到 Bigquery 表时出现数据类型问题