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);

Answer 2

Thanks a ton guys. I created my Employee Entity as follows:

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

    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="EMPLOYEE_ID") 
    private Integer empId = null;

    @Column(name="EMPLOYEE_NAME") 
    private String empName = null;

    @Column(name="EMPLOYEE_SECRETARY")
    private String secretary;

    @Column(name="EMPLOYEE_PERKS")
    private int perks;       

    @ManyToOne(targetEntity = Employee.class, optional=true)
@JoinColumn(name="MANAGER_ID", nullable=true)
private Employee manager = null;

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

    ...        

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

I then added other Entity classes with no body but just Discriminator columns values, such as Manager, CEO and AsstManager. I chose to let Hibernate create the table for me. Following is the main program:

SessionFactory sessionFactory = HibernateUtil.sessionFactory;
Session session = sessionFactory.openSession();
Transaction newTrans = session.beginTransaction();

CEO empCeo = new CEO();
empCeo.setEmpName("Mr CEO");
empCeo.setSecretary("Ms Lily");

Manager empMgr = new Manager();
empMgr.setEmpName("Mr Manager1");
empMgr.setPerks(1000);
empMgr.setManager(empCeo);

Manager empMgr1 = new Manager();
empMgr1.setEmpName("Mr Manager2");
empMgr1.setPerks(2000);
empMgr1.setManager(empCeo);

AsstManager asstMgr = new AsstManager();
asstMgr.setEmpName("Mr Asst Manager");
asstMgr.setManager(empMgr);

session.save(empCeo);
session.save(empMgr);
session.save(empMgr1);
session.save(asstMgr);
newTrans.commit();

System.out.println("Mr Manager1's manager is : "
        + empMgr.getManager().getEmpName());
System.out.println("CEO's manager is : " + empCeo.getManager());
System.out.println("Asst Manager's manager is : " + asstMgr.getManager());
System.out.println("Persons Reporting to CEO: " + empCeo.getReportees());

session.clear();
session.close();

The code runs fine, Hibernate was creating a column "MANAGER_EMPLOYEE_ID" on its own where it stores the FK. I specified the JoinColumn name to make it "MANAGER_ID". Hibernate also creates a table EMPLOYEE_EMPLOYED, however the data is not being persisted there.

Method getReportees() method returns a null, while getManager() works fine, as expected.

Answer 3

I am not sure about you really want, but i think you want a Table per class hierarchy

In that case, each Entity is sorted by a DISCRIMINATOR_COLUMN as follows

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

    @Id @GeneratedValue
    @Column(name="EMPLOYEE_ID") 
    private Integer id = null;

}

And its Children is mapped according to

@Entity
@DiscriminatorValue("MANAGER")
public class Manager extends Employee {

    // Manager properties goes here        
     ...
}

In order to test, let's do the following

SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.openSession();

/*
insert 
into
    Employee
    (EMPLOYEE_TYPE) 
values
    ('EMPLOYEE')
*/
session.save(new Employee());

/*
insert 
into
    Employee
    (EMPLOYEE_TYPE) 
values
    ('MANAGER')
*/
session.save(new Manager());

session.clear();
session.close();

But, instead of inheritance (which you can see a lot of NULL column due to more than one Entity share the same table - when using InheritanceType.SINGLE_TABLE strategy), your model would be better as follows

@Entity
public class Employee { 

    private Employee manager;
    private List<Employee> reporteeList = new ArrayList<Employee>();

    /**
    * optional=true
    * because of an Employee could not have a Manager
    * CEO, for instance, do not have a Manager
    */  
    @ManyToOne(optional=true)
    public Employee getManager() {
        return manager;
    }

    @OneToMany
    public List<Employee> getReporteeList() {
        return reporteeList;
    }

}

Feel free to choice the best approach that fulfill your needs.

regards,