首页 > 解决方案 > 如何正确映射主键由两个外键组成的实体,其中一个外键本身是复合键?

问题描述

我很难弄清楚如何在某个数据库设计上正确执行 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,但是我最终将使用的两者中的哪一个都不是问题。

标签: javajpaormforeign-keyscomposite-key

解决方案


这些关系是“派生的身份”;所以你的 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更干净,消除了在实体及其键之间重复的代码。


推荐阅读