如何在JPA中映射复合主键，其中主键的一部分是外键 [英] How to map compound primary key in JPA, where part of Primary Key is a Foreign Key

问题描述

我试图弄清楚如何构建JPA实体bean来获取适用于我的设备的数据。数据库很古老而且很有用，所以我不能改变模式。 Device Models有一个复合主键，其中一列是FK到Device Type。

我尝试了一些不同的东西。首先是Device有一个DeviceModel和一个DeviceType，但是这给了我太多错误，引用了dev_type。所以我试图让DeviceModel引用DeviceType，但我遇到了同样的错误。

如果它有帮助/重要，我使用Spring Data 4.2.x和Hibernate 4.3.8.Final来支持一切。

我在网上找到的其他答案（如如何在JPA中创建和处理复合主键）不能帮助我，因为它们只映射到基本数据类型。事实上，上面的答案是在我的代码中实现的......但我需要进一步进一步。

模式：

 创建表设备
（
 device_nbr serial（1），
 device_id nchar（20）非空唯一，
 dev_type整数不为空，
 model_nbr整数缺省值1，
 unit_addr nchar（32），
主键（device_nbr），
外键（dev_type）引用devtypes（dev_type）， 
外键（dev_type，model_nbr）引用devmodels（dev_type，model_nbr）
）; 
 
 create table devmodels 
（
 dev_type integer not null，
 model_nbr integer not null，
 model_desc nchar（20），
主键（dev_type，model_nbr），
外键（dev_type）引用devtypes（dev_type）
）; 
 
创建表devtypes 
（
 dev_type整数非空，
 dev_desc nchar（16）非空唯一，
主键（dev_type）
）; 
  

到目前为止我的Beans（不会将DeviceType绑定到Device或DeviceModel，这就是我需要的帮助）：

  @Entity 
 @Table（name =devices）
 public class Device 
 {
 @Id 
 @GeneratedValue 
 @Column（name =device_nbr）
私人长号码; 
 
 @Column（name =device_id，length = 30）
 private String id; 
 
 @Column（name =unit_addr，length = 30）
 private String unitAddress; 
 $ b @ManyToOne（fetch = FetchType.EAGER）
 @JoinColumns（{
 @JoinColumn（name =dev_type），
 @JoinColumn（name =model_nbr ）
}）
 private DeviceModel deviceModel; 
 
 ...获取者和设置者
} 
 
公共类DeviceModelPK实现可序列化
 {
 private static final long serialVersionUID = -8173857210615808268L; 
保护整数deviceTypeNumber; 
 protected Integer modelNumber; 
 
 ...获取者和设置者
} 
 
 @Entity 
 @Table（name =devmodels）
 @IdClass（DeviceModelPK .class）
 public class DeviceModel 
 {
 @Id 
 @Column（name =dev_type）
 private Integer deviceTypeNumber; 
 
 @Id 
 @Column（name =model_nbr）
 private Integer modelNumber; 
 
 @Column（name =model_desc）
私有字符串描述; 
 
 ...获取者和设置者
} 
 
 @Entity 
 @Table（name =devtypes）
 public class DeviceType 
 {
 @Id 
 @GeneratedValue 
 @Column（name =dev_type）
私人整数; 
 
 @Column（name =dev_desc，length = 30）
 private String description; 
 
 ...获得者和设定者
} 
  

解决方案

嗯，你遇到的基本问题是用列而不是实体来思考，尽管这可能是一个不公平的陈述，因为这个问题有点棘手。基本的问题是如何做一个实体作为组合键的一部分，我在这里找到了答案：。
Device：

  @Entity 
 @Table（name =devices）
 public类设备
 {
 @Id 
 @Column（name =device_nbr）
 private长号码; 
 
 @Column（name =device_id，length = 20）
 private String deviceId; 
 $ b @ManyToOne（fetch = FetchType.EAGER，cascade = CascadeType.ALL）
 @JoinColumns（{@ JoinColumn（name =dev_type，referencedColumnName =dev_type），@JoinColumn（ name =model_nbr，referencedColumnName =model_nbr）}）
 private DeviceModel deviceModel; 
 
 //这会创建一个外键约束，否则不会运行
 // deviceType必须通过deviceModel 
 // //注意到，注意，它可以用于显式选择，例如，从设备d选择d.deviceType
 @OneToOne（fetch = FetchType.EAGER，cascade = CascadeType.ALL）
 @JoinColumn（name =dev_type，referencedColumnName =dev_type， insertable = false，updatable = false）
私有设备类型deviceType; 
 
 @Column（name =unit_addr，length = 32）
 private String unitAddress; 
  

DeviceModel：

  @Entity 
 @Table（name =devmodels）
 public class DeviceModel 
 {
 @EmbeddedId 
 private DeviceModelId id; 
 $ b @ManyToOne（fetch = FetchType.EAGER，cascade = CascadeType.ALL）
 @JoinColumn（name =dev_type）
 @MapsId（deviceType）
私人设备类型deviceType; 
 
 @Column（name =model_desc，length = 20）
 private String description; 
  

DeviceModelId：

  @Embeddable 
 public class DeviceModelId implements Serializable 
 {
 private static final long serialVersionUID = -8173857210615808268L; 
私人整数deviceType; 
 @Column（name =model_nbr）
 private Integer modelNumber; 
  

请注意，我使用 @Embeddable 和 @EmbeddedId 。这只是更新，我读过JPA提供者的评论，认为它优于 @IdClass 。我认为这也使列命名更容易一些，但我不记得。

设备类型：

<$

@Id
@GeneratedValue $
$ @Entity
@Table（name =devtypes）
public class DeviceType b $ b @Column（name =dev_type）
private Integer deviceType;

@Column（name =dev_desc，length = 16）
private String description;


诀窍是 @MapsId DeviceModel 。这使得可以在CompositeKey中使用实体。该字段上的@JoinColumn使该字段的命名成为可能。使用它的唯一技巧是手动创建DeviceTypeId：

  DeviceModel model = new DeviceModel（）; 
 DeviceModelId modelId = new DeviceModelId（）; 
 modelId.setModelNumber（654321）; 
 //必须有一个DeviceType才能创建一个DeviceModel 
 model.setDeviceType（type）; 
 model.setId（modelId）; 
  

这将创建以下模式，该模式似乎与您的匹配。

  create table devices（device_nbr bigint not null，device_id varchar（20），unit_addr varchar（32），dev_type integer，model_nbr integer，主键（device_nbr））
创建表devmodels（dev_type integer not null，model_nbr integer not null，model_desc varchar（20），primary key（dev_type，model_nbr））
创建表devtypes（dev_type integer not null，dev_desc varchar（16），primary key（dev_type））
 alter table devices添加约束FK8q0a886v04gg0qv261x1b2qrf外键（dev_type，model_nbr）引用devmodels 
 alter table设备添加约束FKb72a7hq5phwjtbhaglobdkgji外键（dev_type）引用devtypes 
 alter table devmodels添加约束FK4xlwyd2gwpbs4g4hdckyb11oj外键（dev_type）引用devtypes 
  

I'm trying to figure out how to build JPA Entity beans to get the data working for my devices. The database is old and in stone, so I can't change schema. Device Models has a compound primary key, where one of the columns is a FK to Device Type.

I've tried a couple different things. First was the Device had a DeviceModel and a DeviceType, but that gave me the error that too many things were referencing dev_type. So then I tried to have DeviceModel have a reference to DeviceType but I ran into the same error.

If it helps/matters, I'm using Spring Data 4.2.x and Hibernate 4.3.8.Final to back everything.

Other answers I've found online (such as How to create and handle composite primary key in JPA) don't help me as they only map to basic data types. In fact, the answer above is implemented in my code below...but I need to go 1 level further.

The Schema:

create table devices
(
  device_nbr serial(1),
  device_id nchar(20) not null unique,
  dev_type integer not null,
  model_nbr integer default 1,
  unit_addr nchar(32),
  primary key (device_nbr),
  foreign key (dev_type) references devtypes (dev_type),
  foreign key (dev_type, model_nbr) references devmodels (dev_type, model_nbr)
);

create table devmodels
(
  dev_type      integer  not null,
  model_nbr     integer  not null,
  model_desc    nchar(20),
  primary key (dev_type, model_nbr),
  foreign key (dev_type) references devtypes (dev_type)
);

create table devtypes
(
  dev_type integer not null,
  dev_desc nchar(16) not null unique,
  primary key (dev_type)
);

My Beans so far (which don't tie DeviceType to either Device or DeviceModel, that's what I need help with):

@Entity
@Table(name = "devices")
public class Device
{
    @Id
    @GeneratedValue
    @Column(name = "device_nbr")
    private Long                number;

    @Column(name = "device_id", length = 30)
    private String          id;

    @Column(name = "unit_addr", length = 30)
    private String          unitAddress;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumns({
        @JoinColumn(name = "dev_type"),
        @JoinColumn(name = "model_nbr")
    })
    private DeviceModel deviceModel;

...Getters and setters
}

public class DeviceModelPK implements Serializable
{
    private static final long   serialVersionUID    = -8173857210615808268L;
    protected Integer                   deviceTypeNumber;
    protected Integer                   modelNumber;

...Getters and setters
}

@Entity
@Table(name = "devmodels")
@IdClass(DeviceModelPK.class)
public class DeviceModel
{
    @Id
    @Column(name = "dev_type")
    private Integer         deviceTypeNumber;

    @Id
    @Column(name = "model_nbr")
    private Integer         modelNumber;

    @Column(name = "model_desc")
    private String          description;

...Getters and setters
}

@Entity
@Table(name = "devtypes")
public class DeviceType
{
    @Id
    @GeneratedValue
    @Column(name = "dev_type")
    private Integer number;

    @Column(name = "dev_desc", length = 30)
    private String  description;

...Getters and setters
}

解决方案

Well, the basic problem you're having is thinking in terms of columns instead of Entities, though that might be an unfair statement since the issue was kind of tricky. The basic question is how to do include an Entity as part of a Composite Key and I found the answer here: How to create a composite primary key which contains a @ManyToOne attribute as an @EmbeddedId in JPA?. Device:

@Entity
@Table(name = "devices")
public class Device
{
    @Id
    @Column(name = "device_nbr")
    private Long number;

    @Column(name = "device_id", length = 20)
    private String deviceId;

    @ManyToOne(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
    @JoinColumns({@JoinColumn(name="dev_type", referencedColumnName="dev_type"), @JoinColumn(name="model_nbr", referencedColumnName="model_nbr")})
    private DeviceModel deviceModel;

    // This creates a foreign key constraint, but otherwise doesn't function
    // deviceType must be accessed through deviceModel
    // note, it can be used for explicit selects, e.g., "select d.deviceType from Device d"
    @OneToOne(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
    @JoinColumn(name="dev_type", referencedColumnName="dev_type", insertable=false, updatable=false)
    private DeviceType deviceType;

    @Column(name = "unit_addr", length = 32)
    private String unitAddress;

DeviceModel:

@Entity
@Table(name = "devmodels")
public class DeviceModel
{
    @EmbeddedId
    private DeviceModelId id;

    @ManyToOne(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
    @JoinColumn(name="dev_type")
    @MapsId("deviceType")
    private DeviceType deviceType;

    @Column(name = "model_desc", length=20)
    private String  description;

DeviceModelId:

@Embeddable
public class DeviceModelId implements Serializable
{
    private static final long   serialVersionUID    = -8173857210615808268L;
    private Integer deviceType;
    @Column(name="model_nbr")
    private Integer modelNumber;

Notice that I used @Embeddable and @EmbeddedId. It's just newer and I've read JPA provider comments that it is to be preferred over @IdClass. I think it also made the column naming a little easier, but I don't remember.

DeviceType:

@Entity
@Table(name = "devtypes")
public class DeviceType
{
    @Id
    @GeneratedValue
    @Column(name = "dev_type")
    private Integer deviceType;

    @Column(name = "dev_desc", length = 16)
    private String  description;

The trick was the @MapsId in the DeviceModel. That enabled the usage of an Entity in the CompositeKey. The @JoinColumn on that field enabled the naming of that field. The only trick to using it is to create the DeviceTypeId manually:

DeviceModel model = new DeviceModel();
DeviceModelId modelId = new DeviceModelId();
modelId.setModelNumber(654321);
// have to have a DeviceType to create a DeviceModel
model.setDeviceType(type);
model.setId(modelId);

This creates the following schema, which seems to match yours.

create table devices (device_nbr bigint not null, device_id varchar(20), unit_addr varchar(32), dev_type integer, model_nbr integer, primary key (device_nbr))
create table devmodels (dev_type integer not null, model_nbr integer not null, model_desc varchar(20), primary key (dev_type, model_nbr))
create table devtypes (dev_type integer not null, dev_desc varchar(16), primary key (dev_type))
alter table devices add constraint FK8q0a886v04gg0qv261x1b2qrf foreign key (dev_type, model_nbr) references devmodels
alter table devices add constraint FKb72a7hq5phwjtbhaglobdkgji foreign key (dev_type) references devtypes
alter table devmodels add constraint FK4xlwyd2gwpbs4g4hdckyb11oj foreign key (dev_type) references devtypes

这篇关于如何在JPA中映射复合主键，其中主键的一部分是外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！