JPA, Hibernate can I do composite primary key which one element is foreign kay @OneToMany?

3.8k views Asked by At

I would like to have in my entity composite primary key consisting of 2 columns (attributes), and have one of them to be foreign key simultaneously.

I write something like this, but don't know whether it works cause foreign key is marked as generated value in IntelliJ data source

@Entity
@Table(name = "service_point")
@Access(AccessType.PROPERTY)
@IdClass(ServicePointId.class)
public class ServicePoint {

    private Long providerId;
    private Integer servicePointNumber;

    private Provider provider;

    @Id
    @Basic(optional = false)
    @Column(name = "provider_id", nullable = false, insertable = false,
            updatable = false, columnDefinition = "BIGINT UNSIGNED")
    public Long getProviderId() {
        return providerId;
    }

    public void setProviderId(Long providerId) {
        this.providerId = providerId;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "service_point_no", nullable = false, columnDefinition = "BIGINT UNSIGNED")
    public Integer getServicePointNumber() {
        return servicePointNumber;
    }

    public void setServicePointNumber(Integer servicePointNumber) {
        this.servicePointNumber = servicePointNumber;
    }

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "provider_id")
    public Provider getProvider() {
        return provider;
    }

    public void setProvider(Provider provider) {
        this.provider = provider;
    }
}

UPDATED:

I have tested Brian Vosburgh and it works:

transaction.begin();

em.persist(provider);

ServicePoint servicePoint = new ServicePoint(provider, 1);

em.persist(servicePoint);

transaction.commit();

ServicePoint servicePoint2 = em.find(ServicePoint.class,
        new ServicePointId(provider.getUserId(), servicePoint.getServicePointNumber()));

assertTrue("Service point provider id and Provider provider id should be the same.",
        servicePoint2.getProvider().getUserId() == provider.getUserId());
assertNotNull("Service point number can not be null", servicePoint2.getServicePointNumber());
assertEquals(servicePoint2.getProvider(), provider);

transaction.begin();
em.remove(servicePoint);
em.remove(provider);
transaction.commit();

UPDATE 2 - new problem in next relationship composite PK (3 columns) and 2 of them are composite FK I have been trying to resemble the below solution and couldn't get through how to write ServicePointPhotoId @IdClass

extentions of previous example

2

There are 2 answers

5
Brian Vosburgh On BEST ANSWER

Get rid of the providerId field and its corresponding getter and setter. Add an @Id annotation to getProvider(). Define the IdClass like this:

public class ServicePointId {
    private Long provider;
    private Integer servicePointNumber;
    public Integer getProvider() {
        return provider;
    }
    public void setProvider(Integer provider) {
        this.provider = provider;
    }
    public Integer getServicePointNumber() {
        return servicePointNumber;
    }
    public void setServicePointNumber(Integer servicePointNumber) {
        this.servicePointNumber = servicePointNumber;
    }
}

Note that the property name in the IdClass matches the property name in the Entity (i.e. provider), but the properties' types are different. In the IdClass the property type must match the type of the Id property of Provider.

This is discussed in the JPA 2.1 spec, section 2.4.1.

Suggestion for UPDATE 2:

public class ServicePointPhotoId {
    public ServicePointId servicePoint;
    public Long photoId;
}

@Entity
@IdClass(ServicePointPhotoId.class)
@Table(name="service_point_photo")
public class ServicePointPhoto {
    @Id
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name="provider_id", referencedColumnName="provider_id"),
        @JoinColumn(name="service_point_no", referencedColumnName="service_point_no")
    })
    private ServicePoint servicePoint;

    @Id
    @Column(name="photo_id")
    private Long photoId;
}

Note the attribute name must match (i.e. servicePoint); but the IdClass attribute's type must match the referenced Entity's IdClass (i.e. ServicePointId).

I have used field annotations, but you can convert those to property annotations.

Again: the JPA 2.1 spec has an example of just this sort of relationship in section 2.4.1.3.

2
DuncanKinnear On

The best way to do composite primary keys is to use the @EmbeddedId with a corresponding @Embeddable class.

Of our 400-odd database Entities, about 135 used Embedded ID classes to implement composite (multi-field) primary keys.

There are lots of questions and answers here on SO with examples of these.