The best way to map a Composite Primary Key with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

One of my readers asked me to help him map a Composite Primary Key using JPA and Hibernate. Because this is a recurrent question, I decided to write a blog post in which I describe this mapping is more detail.

Domain Model

A relational database composite key contains two or more columns which together for the primary key of a given table.

employee_phone_composite_key

In the diagram above, the employee table has a Composite Primary Key, which consists of two columns:

  • company_id
  • employee_number

Every Employee can also have a Phone, which uses the same composite key to reference its owning Employee.

To map this database table mapping, we need to isolate the compound key into an @Embeddable first:

@Embeddable
public class EmployeeId implements Serializable {

    @Column(name = "company_id")
    private Long companyId;

    @Column(name = "employee_number")
    private Long employeeNumber;

    public EmployeeId() {
    }

    public EmployeeId(Long companyId, Long employeeId) {
        this.companyId = companyId;
        this.employeeNumber = employeeId;
    }

    public Long getCompanyId() {
        return companyId;
    }

    public Long getEmployeeNumber() {
        return employeeNumber;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof EmployeeId)) return false;
        EmployeeId that = (EmployeeId) o;
        return Objects.equals(getCompanyId(), that.getCompanyId()) &&
                Objects.equals(getEmployeeNumber(), that.getEmployeeNumber());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getCompanyId(), getEmployeeNumber());
    }
}

The Embeddable must be `Serializable` and we need to provide an implementation for `equals` and `hashCode`.

The Employee mapping looks as follows:

@Entity(name = "Employee")
@Table(name = "employee")
public class Employee {

    @EmbeddedId
    private EmployeeId id;

    private String name;

    public EmployeeId getId() {
        return id;
    }

    public void setId(EmployeeId id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

The @EmbeddedId is used to instruct Hibernate that the Employee entity uses a compound key.

The Phone mapping is rather straightforward as well:

@Entity(name = "Phone")
@Table(name = "phone")
public class Phone {

    @Id
    @Column(name = "`number`")
    private String number;

    @ManyToOne
    @JoinColumns({
        @JoinColumn(
            name = "company_id",
            referencedColumnName = "company_id"),
        @JoinColumn(
            name = "employee_number",
            referencedColumnName = "employee_number")
    })
    private Employee employee;

    public Employee getEmployee() {
        return employee;
    }

    public void setEmployee(Employee employee) {
        this.employee = employee;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }
}

The Phone uses the number as an entity identifier since every phone number and the @ManyToOne mapping uses the two columns that are part of the compound key.

Testing time

To see how it works, consider the following persistence logic:

doInJPA(entityManager -> {
    Employee employee = new Employee();
    employee.setId(new EmployeeId(1L, 100L));
    employee.setName("Vlad Mihalcea");
    entityManager.persist(employee);
});

doInJPA(entityManager -> {
    Employee employee = entityManager.find(
        Employee.class, new EmployeeId(1L, 100L));
    Phone phone = new Phone();
    phone.setEmployee(employee);
    phone.setNumber("012-345-6789");
    entityManager.persist(phone);
});

doInJPA(entityManager -> {
    Phone phone = entityManager.find(Phone.class, "012-345-6789");
    assertNotNull(phone);
    assertEquals(new EmployeeId(1L, 100L), phone.getEmployee().getId());
});

Which generates the following SQL statements:

INSERT INTO employee (name, company_id, employee_number)
VALUES ('Vlad Mihalcea', 1, 100)

SELECT e.company_id AS company_1_0_0_ ,
       e.employee_number AS employee2_0_0_ ,
       e.name AS name3_0_0_
FROM   employee e
WHERE  e.company_id = 1
       AND e.employee_number = 100

INSERT INTO phone (company_id, employee_number, `number`) 
VALUES (1, 100, '012-345-6789')

SELECT p.number AS number1_1_0_ ,
       p.company_id AS company_2_1_0_ ,
       p.employee_number AS employee3_1_0_ ,
       e.company_id AS company_1_0_1_ ,
       e.employee_number AS employee2_0_1_ ,
       e.name AS name3_0_1_
FROM   phone p
LEFT OUTER JOIN employee e 
ON     p.company_id = e.company_id AND p.employee_number = e.employee_number
WHERE  p.number = '012-345-6789'

Mapping relationships using the Composite Key

We can even map relationships using the information provided within the Composite Key itself. In this particular example, the company_id references a Company entity which looks as follows:

@Entity(name = "Company")
@Table(name = "company")
public class Company {

    @Id
    private Long id;

    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof Company)) return false;
        Company company = (Company) o;
        return Objects.equals(getName(), company.getName());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getName());
    }
}

We can have the Composite Key mapping referencing the Company entity withing the Employee entity:

@Entity(name = "Employee")
@Table(name = "employee")
public class Employee {

    @EmbeddedId
    private EmployeeId id;

    private String name;

    @ManyToOne
    @JoinColumn(name = "company_id",insertable = false, updatable = false)
    private Company company;

    public EmployeeId getId() {
        return id;
    }

    public void setId(EmployeeId id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Notice that the @ManyToOne association instructs Hibernate to ignore inserts and updates issued on this mapping since the company_id is controlled by the @EmbeddedId.

Mapping a relationships inside @Embeddable

But that’s not all. We can even move the @ManyToOne inside the @Embeddable itself:

@Embeddable
public class EmployeeId implements Serializable {

    @ManyToOne
    @JoinColumn(name = "company_id")
    private Company company;

    @Column(name = "employee_number")
    private Long employeeNumber;

    public EmployeeId() {
    }

    public EmployeeId(Company company, Long employeeId) {
        this.company = company;
        this.employeeNumber = employeeId;
    }

    public Company getCompany() {
        return company;
    }

    public Long getEmployeeNumber() {
        return employeeNumber;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof EmployeeId)) return false;
        EmployeeId that = (EmployeeId) o;
        return Objects.equals(getCompany(), that.getCompany()) &&
                Objects.equals(getEmployeeNumber(), that.getEmployeeNumber());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getCompany(), getEmployeeNumber());
    }
}

Now, the Employee mapping will no longer require the extra @ManyToOne association since it’s offered by the entity identifier:

@Entity(name = "Employee")
@Table(name = "employee")
public class Employee {

    @EmbeddedId
    private EmployeeId id;

    private String name;

    public EmployeeId getId() {
        return id;
    }

    public void setId(EmployeeId id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

The persistence logic changes as follows:

Company company = doInJPA(entityManager -> {
    Company _company = new Company();
    _company.setId(1L);
    _company.setName("vladmihalcea.com");
    entityManager.persist(_company);
    return _company;
});

doInJPA(entityManager -> {
    Employee employee = new Employee();
    employee.setId(new EmployeeId(company, 100L));
    employee.setName("Vlad Mihalcea");
    entityManager.persist(employee);
});

doInJPA(entityManager -> {
    Employee employee = entityManager.find(
        Employee.class, 
        new EmployeeId(company, 100L)
    );
    Phone phone = new Phone();
    phone.setEmployee(employee);
    phone.setNumber("012-345-6789");
    entityManager.persist(phone);
});

doInJPA(entityManager -> {
    Phone phone = entityManager.find(Phone.class, "012-345-6789");
    assertNotNull(phone);
    assertEquals(new EmployeeId(company, 100L), phone.getEmployee().getId());
});

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Knowing how to map a composite key is very important because many database schemas use this type of primary key. As demonstrated by this blog post, such a mapping is not complicated at all.

Code available on GitHub.

Transactions and Concurrency Control eBook

11 Comments on “The best way to map a Composite Primary Key with JPA and Hibernate

  1. Is there some downside of not using separate class to embed the composite key? I mean, I know that it might not be the most elegant solution, but would I get some kind of error or long term problems if I just use @Id on a few columns in an entity class that implements Serializable and implements equals and hashcode only on those fields annotated as @Id? Speaking shortly – have it all in one class?

    • The main downside is that you cannot pass the id in the EntityManager.find call. You’d have to pass the entity object as an id holder, which is far from ideal.

      • Yes, that is right, however I could use some sort of findByFieldXandFieldY(T fieldX, T fieldY) method implementation to find unique entity if that is the main disadvantage. Of course I know all that is not ideal, however I ask mostly, because I have joined a legacy project where majority of entities is composed that way and I wonder if I have to refactor all those entities to avoid problems when doing persist and merge operations or am I on the safe (though I am well aware not perfect) side.

  2. Hi Vlad,

    when designing a new database model, would you ever use a composite key? Or can it always substitute it with one semantic autogenerated key without any harm? What cons does the semantic key in comparison to the composite key. I think that I can guarantee a uniqueness of column combination without having to use a composite key(which is the main purpose to use it, as I see it) – using:
    uniqueConstraints = {
    @UniqueConstraint(columnNames = {“COL_1”, “COL_2”})
    }

    Am I right?

    Awesome work with your blog and all the topics

    Best regards,
    Przemek

    • If the underlying table has a composite key, then you should use it. Surrogate keys are required only when you don’t have an existing business key.

      • So if there is yet no underlying table and I am designing the database model from the scratch, should I still use a composite key, or I am better of them and can go for the surrogate key?

        As a Champion on Database/Hibernate/Java related topics – can you recommend any books or sources related to designing a good database model that is flexible enough to meet most of the constantly changing business requirements?

      • For many to many associations, you need to use a composite key. For parent tables, you’ll either use a business key or a surrogate key.

        I’m not aware of any good book related to database modeling. I simply follow the normalization rules and keeping columns as compact as possible.

  3. Hi
    I have one Parent entity A which is having onetomany Child entity B using joincloumns of embeddedid which is same for both classes . The child class B have 2 onetoone sub childeren C and D which again uses embededId as the primary key (composite) the entity is getting saved properly in old spring boot 1.2.x version but after incrementing the version to 2.2.x i am getting foreign key constraint in table B and C. On debugging i found that the session actionqueue is saving child table first in new Spring boot version but in old version it is saving parent entity first. Can you please help me with why it is behaving like this.

    • This question can only be answered via consulting as it requires me to debug the code. Talk to your manager if they want to hire me for this.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.