Hibernate: Parent/Child relationship in a single-table

Question

I hardly see any pointer on the following problem related to Hibernate. This pertains to implementing inheritance using a single database table with a parent-child relationship to itself. For example:

CREATE TABLE Employee (
  empId BIGINT NOT NULL AUTO_INCREMENT,
  empName VARCHAR(100) NOT NULL,
  managerId BIGINT,
  CONSTRAINT pk_employee PRIMARY KEY (empId)
)

Here, the managerId column may be null, or may point to another row of the Employee table. Business rule requires the Employee to know about all his reportees and for him to know about his/her manager. The business rules also allow rows to have null managerId (the CEO of the organisation doesn't have a manager).

How do we map this relationship in Hibernate, standard many-to-one relationship doesn't work here? Especially, if I want to implement my Entities not only as a corresponding "Employee" Entity class, but rather multiple classes, such as "Manager", "Assistant Manager", "Engineer" etc, each inheriting from "Employee" super entity class, some entity having attributes that don't actually apply to all, for example "Manager" gets Perks, others don't (the corresponding table column would accept null of course).

Example code would be appreciated (I intend to use Hibernate 3 annotations).

Answer 1

You are expressing two concepts here:

1. inheritance and you want to map your inheritance hierarchy in a single table.
2. a parent/child relationship.

To implement 1., you'll need to use Hibernate's single table per class hierarchy strategy:

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(
    name="emptype",
    discriminatorType=DiscriminatorType.STRING
)
public abstract class Employee { ... }

@Entity
@DiscriminatorValue("MGR")
public class Manager extends Employee { ... }

To implement 2., you'll need to add two self-referencing associations on Employee:

• many employee have zero or one manager (which is also an Employee)
• one employee has zero or many reportee(s)

The resulting Employee may looks like this:

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(
    name="emptype",
    discriminatorType=DiscriminatorType.STRING
)
public abstract class Employee { 

    ... 

    private Employee manager;
    private Set<Employee> reportees = new HashSet<Employee>();

    @ManyToOne(optional = true)
    public Employee getManager() {
        return manager;
    }

    @OneToMany
    public Set<Employee> getReportees() {
        return reportees;
    }

    ...
}

And this would result in the following tables:

CREATE TABLE EMPLOYEE_EMPLOYEE (
        EMPLOYEE_ID BIGINT NOT NULL,
        REPORTEES_ID BIGINT NOT NULL
    );

CREATE TABLE EMPLOYEE (
        EMPTYPE VARCHAR(31) NOT NULL,
        ID BIGINT NOT NULL,
        NAME VARCHAR(255),
        MANAGER_ID BIGINT
    );

ALTER TABLE EMPLOYEE ADD CONSTRAINT SQL100311183749050 PRIMARY KEY (ID);

ALTER TABLE EMPLOYEE_EMPLOYEE ADD CONSTRAINT SQL100311183356150 PRIMARY KEY (EMPLOYEE_ID, REPORTEES_ID);

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK4AFD4ACE7887BF92 FOREIGN KEY (MANAGER_ID)
    REFERENCES EMPLOYEE (ID);

ALTER TABLE EMPLOYEE_EMPLOYEE ADD CONSTRAINT FKDFD1791F25AA2BE0 FOREIGN KEY (REPORTEES_ID)
    REFERENCES EMPLOYEE (ID);

ALTER TABLE EMPLOYEE_EMPLOYEE ADD CONSTRAINT FKDFD1791F1A4AFCF1 FOREIGN KEY (EMPLOYEE_ID)
    REFERENCES EMPLOYEE (ID);