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

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. 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

5 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.

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.