How to map compound primary key in JPA, where part of Primary Key is a Foreign Key

Question

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
}

Answer 1

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