JPA Demystified (episode 1) - @OneToMany and @ManyToOne mappings

Let's play with some @OneToMany and @ManyToOne mappings :) As you may read in wise books or boring javadocs, they serve for mapping 1-* and *-1 relations respectively.

Let's dig a little more into it using following example: Employer (1-*) Employee (1-*) Benefit (See below for DB tables and their content). Entity model for this situation will look like this:

@Entity
@Table(name = "EMPLOYERS")
public class Employer implements Serializable {

    private String businessName;
    private List<Employee> employees;
    private Long id;

    @Column(name = "BUSINESS_NAME")
    public String getBusinessName() {
        return businessName;
    }

    @OneToMany(mappedBy = "employer")
    public List<Employee> getEmployees() {
        return employees;
    }

    @Id
    public Long getId() {
        return id;
    }

...
}

@Entity
@Table(name = "EMPLOYEES")
public class Employee implements Serializable {

    private List<Benefit> benefits;
    private Employer employer;
    private String firstName;
    private Long id;

    @OneToMany(mappedBy = "employee")
    public List<Benefit> getBenefits() {
        return benefits;
    }

    @ManyToOne
    @JoinColumn(name = "EMPLOYER_ID")
    public Employer getEmployer() {
        return employer;
    }

    @Column(name = "FIRST_NAME")
    public String getFirstName() {
        return firstName;
    }

    @Id
    public Long getId() {
        return id;
    }
...
}

@Entity
@Table(name = "BENEFITS")
public class Benefit implements Serializable {

    private Employee employee;
    private Date endDate;
    private Long id;
    private String name;
    private Date startDate;
    private BenefitType type;

    @ManyToOne
    @JoinColumn(name = "EMPLOYEE_ID")
    public Employee getEmployee() {
        return employee;
    }

    @Column(name = "END_DATE")
    @Temporal(TemporalType.DATE)
    public Date getEndDate() {
        return endDate;
    }

    @Id
    public Long getId() {
        return id;
    }

    @Basic
    public String getName() {
        return name;
    }

    @Column(name = "START_DATE")
    @Temporal(TemporalType.DATE)
    public Date getStartDate() {
        return startDate;
    }

    @Column(name = "BENEFIT_TYPE")
    @Enumerated(EnumType.STRING)
    public BenefitType getType() {
        return type;
    }
...
}

Time for real fun :) - let's query the DB using following DAO:

public class DefaultEmployeeDAO implements EmployeeDAO {

    @PersistenceContext
    private EntityManager entityManager;

    public Employee get(Long id) {
        return entityManager.find(Employee.class, id);
    }
...
}

Executed SQL queries depend on used JPA provider:

-- Hibernate 3.4.0.GA
select employee0_.id as id1_1_, employee0_.EMPLOYER_ID as EMPLOYER3_1_1_, employee0_.FIRST_NAME as FIRST2_1_1_, employer1_.id as id2_0_, employer1_.BUSINESS_NAME as BUSINESS2_2_0_ 
from EMPLOYEES employee0_ left outer join EMPLOYERS employer1_ on employee0_.EMPLOYER_ID=employer1_.id 
where employee0_.id = 1 

-- EclipseLink 2.1.1
SELECT ID, FIRST_NAME, EMPLOYER_ID FROM EMPLOYEES WHERE (ID = 1)
SELECT ID, BUSINESS_NAME FROM EMPLOYERS WHERE (ID = 1) 

-- OpenJPA 2.0.1
SELECT t1.id, t1.BUSINESS_NAME, t0.FIRST_NAME 
FROM EMPLOYEES t0 LEFT OUTER JOIN EMPLOYERS t1 ON t0.EMPLOYER_ID = t1.id 
WHERE t0.id = 1 

As you see @ManyToOne mapping causes fetching the referenced entity along with the referencing one (fetch type EAGER), while fetching entity mapped with @OneToMany is deferred until it will be accessed for the first time (fetch type LAZY).

Take a deeper look at the queries generated by EclipseLink. What the heck!? Why they don't use joins?! And what we can do about it?

This is the situation where @JoinFetch comes to the rescue - but be aware - this annotation is EclipseLink addition to JPA and hence makes your mappings not portable across the different providers! How can it be used? see example below:

    @ManyToOne
    @JoinColumn(name = "EMPLOYER_ID")
    @JoinFetch(JoinFetchType.OUTER)
    public Employer getEmployer() {
        return employer;
    }

After adding the @JoinFetch your EclipseLink SQL queries will turn into:

SELECT t1.ID, t1.FIRST_NAME, t1.EMPLOYER_ID, t0.ID, t0.BUSINESS_NAME FROM EMPLOYEES t1 LEFT OUTER JOIN EMPLOYERS t0 ON (t0.ID = t1.EMPLOYER_ID) WHERE (t1.ID = 1)

Let's back to the @OneToMany annotation for a while, and check how the providers will behave when we change the default fetch type to EAGER, in following way:

    @OneToMany(mappedBy = "employee", fetch = FetchType.EAGER)
    public List getBenefits() {
        return benefits;
    }

This time the SQL queries are:

-- Hibernate 3.4.0.GA
select employee0_.id as id1_2_, employee0_.EMPLOYER_ID as EMPLOYER3_1_2_, employee0_.FIRST_NAME as FIRST2_1_2_, benefits1_.EMPLOYEE_ID as EMPLOYEE6_4_, benefits1_.id as id4_, benefits1_.id as id0_0_, benefits1_.EMPLOYEE_ID as EMPLOYEE6_0_0_, benefits1_.END_DATE as END2_0_0_, benefits1_.name as name0_0_, benefits1_.START_DATE as START4_0_0_, benefits1_.BENEFIT_TYPE as BENEFIT5_0_0_, employer2_.id as id2_1_, employer2_.BUSINESS_NAME as BUSINESS2_2_1_ 
from EMPLOYEES employee0_ left outer join BENEFITS benefits1_ on employee0_.id=benefits1_.EMPLOYEE_ID left outer join EMPLOYERS employer2_ on employee0_.EMPLOYER_ID=employer2_.id 
where employee0_.id=1

-- EclipseLink 2.1.1 (without @JoinFetch)
SELECT ID, FIRST_NAME, EMPLOYER_ID FROM EMPLOYEES WHERE (ID = 1)
SELECT ID, BUSINESS_NAME FROM EMPLOYERS WHERE (ID = 1)
SELECT ID, START_DATE, NAME, END_DATE, BENEFIT_TYPE, EMPLOYEE_ID FROM BENEFITS WHERE (EMPLOYEE_ID = 1)

-- OpenJPA 2.0.1
SELECT t1.id, t1.BUSINESS_NAME, t0.FIRST_NAME, t2.EMPLOYEE_ID, t2.id, t2.END_DATE, t2.name, t2.START_DATE, t2.BENEFIT_TYPE 
FROM EMPLOYEES t0 LEFT OUTER JOIN EMPLOYERS t1 ON t0.EMPLOYER_ID = t1.id LEFT OUTER JOIN BENEFITS t2 ON t0.id = t2.EMPLOYEE_ID 
WHERE t0.id = 1 ORDER BY t2.EMPLOYEE_ID ASC

Both Hibernate and OpenJPA generate impressive queries :) - but in fact they are using the most efficient way to fetch all correlated entities.
Quick note for OpenJPA users - the above example leads to an exception while processing the results:

<openjpa-2.0.1-r422266:989424 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: null
FailedObject: ... .entities.domain.Employee-1 [org.apache.openjpa.util.LongId] [java.lang.String]
 at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:986)
 at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:880)
 at org.apache.openjpa.kernel.DelegatingBroker.find(DelegatingBroker.java:223)
 at org.apache.openjpa.persistence.EntityManagerImpl.find(EntityManagerImpl.java:477)
    ...
Caused by: java.lang.NullPointerException
 at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.setInverseRelation(JDBCStoreManager.java:469)
 at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:429)
 at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:322)

It looks like an error in OpenJPA.

Back to mainstream :) - we can narrow the number of queries generated by EclipseLink using @JoinFetch again:

    @OneToMany(mappedBy = "employee", fetch = FetchType.EAGER)
    @JoinFetch(JoinFetchType.OUTER)
    public List getBenefits() {
        return benefits;
    }

    @ManyToOne
    @JoinColumn(name = "EMPLOYER_ID")
    @JoinFetch(JoinFetchType.OUTER)
    public Employer getEmployer() {
        return employer;
    }

The above will lead to one query (instead of 3):

SELECT DISTINCT t1.ID, t1.FIRST_NAME, t1.EMPLOYER_ID, t0.ID, t0.START_DATE, t0.NAME, t0.END_DATE, t0.BENEFIT_TYPE, t0.EMPLOYEE_ID, t2.ID, t2.BUSINESS_NAME 
FROM EMPLOYEES t1 LEFT OUTER JOIN BENEFITS t0 ON (t0.EMPLOYEE_ID = t1.ID) LEFT OUTER JOIN EMPLOYERS t2 ON (t2.ID = t1.EMPLOYER_ID) 
WHERE (t1.ID = 1)

Finally, let's focus on optional attribute of @ManyToOne annotation. In our example both Employee and Benefit cannot exist without the Employer and Employee respectively, therefore we can change the mapping to:

    @ManyToOne(optional = false)
    @JoinColumn(name = "EMPLOYER_ID")
    public Employer getEmployer() {
        return employer;
    }

for the Employee, and:

    @ManyToOne(optional = false)
    @JoinColumn(name = "EMPLOYEE_ID")
    public Employee getEmployee() {
        return employee;
    }

for the Benefit.
This change will generate queries:

-- Hibernate 3.4.0.GA
select employee0_.id as id1_2_, employee0_.EMPLOYER_ID as EMPLOYER3_1_2_, employee0_.FIRST_NAME as FIRST2_1_2_, benefits1_.EMPLOYEE_ID as EMPLOYEE6_4_, benefits1_.id as id4_, benefits1_.id as id0_0_, benefits1_.EMPLOYEE_ID as EMPLOYEE6_0_0_, benefits1_.END_DATE as END2_0_0_, benefits1_.name as name0_0_, benefits1_.START_DATE as START4_0_0_, benefits1_.BENEFIT_TYPE as BENEFIT5_0_0_, employer2_.id as id2_1_, employer2_.BUSINESS_NAME as BUSINESS2_2_1_ 
from EMPLOYEES employee0_ left outer join BENEFITS benefits1_ on employee0_.id=benefits1_.EMPLOYEE_ID inner join EMPLOYERS employer2_ on employee0_.EMPLOYER_ID=employer2_.id where employee0_.id=1

-- EclipseLink 2.1.1 (without @JoinFetch)
SELECT ID, FIRST_NAME, EMPLOYER_ID FROM EMPLOYEES WHERE (ID = 1)
SELECT ID, BUSINESS_NAME FROM EMPLOYERS WHERE (ID = 1)
SELECT ID, START_DATE, NAME, END_DATE, BENEFIT_TYPE, EMPLOYEE_ID FROM BENEFITS WHERE (EMPLOYEE_ID = 1)

-- OpenJPA 2.0.1
SELECT t1.id, t1.BUSINESS_NAME, t0.FIRST_NAME, t2.EMPLOYEE_ID, t2.id, t2.END_DATE, t2.name, t2.START_DATE, t2.BENEFIT_TYPE FROM EMPLOYEES t0 INNER JOIN EMPLOYERS t1 ON t0.EMPLOYER_ID = t1.id LEFT OUTER JOIN BENEFITS t2 ON t0.id = t2.EMPLOYEE_ID WHERE t0.id = 1 ORDER BY t2.EMPLOYEE_ID ASC

Don't you think that default behavior of EclipseLink is a little strange? What would we do without the @JoinFetch? When we add it in the following way:

    @OneToMany(mappedBy = "employee", fetch = FetchType.EAGER)
    @JoinFetch(JoinFetchType.OUTER)
    public List getBenefits() {
        return benefits;
    }

    @ManyToOne(optional = false)
    @JoinColumn(name = "EMPLOYER_ID")
    @JoinFetch(JoinFetchType.INNER)
    public Employer getEmployer() {
        return employer;
    }

The world becomes beautiful again:

SELECT DISTINCT t1.ID, t1.FIRST_NAME, t1.EMPLOYER_ID, t0.ID, t0.BUSINESS_NAME, t2.ID, t2.START_DATE, t2.NAME, t2.END_DATE, t2.BENEFIT_TYPE, t2.EMPLOYEE_ID 
FROM EMPLOYEES t1 LEFT OUTER JOIN BENEFITS t2 ON (t2.EMPLOYEE_ID = t1.ID), EMPLOYERS t0 
WHERE ((t1.ID = 1) AND (t0.ID = t1.EMPLOYER_ID))

I know, you are already tired reading the above thoughts ;) - so let's finish for now - we will return to the @OneToMany and @ManyToOne soon with some additional deliberation on cascading and a little more complicated DB examples.

The boring part now ;) - read it at your own risk ;) - DB tables and their content (MySQL targeted)

CREATE TABLE BENEFITS (
    ID            NUMERIC     NOT NULL,
    EMPLOYEE_ID   NUMERIC     NOT NULL,
    BENEFIT_TYPE  VARCHAR(64) NOT NULL,
    START_DATE    DATE,
    END_DATE      DATE,
    NAME          VARCHAR(128), 
    CONSTRAINT BENEFITS_PK PRIMARY KEY (ID)
);

CREATE TABLE EMPLOYEES (
    ID           NUMERIC NOT NULL,
    EMPLOYER_ID  NUMERIC NOT NULL, 
    FIRST_NAME   VARCHAR(64), 
    CONSTRAINT EMPLOYEES_PK PRIMARY KEY (ID)
);

CREATE TABLE EMPLOYERS (
    ID             NUMERIC NOT NULL, 
    BUSINESS_NAME  VARCHAR(128), 
    CONSTRAINT EMPLOYERS_PK PRIMARY KEY (ID)
);

INSERT INTO EMPLOYERS (ID, BUSINESS_NAME) VALUES (1, 'Mighty Ducks');

INSERT INTO EMPLOYEES (ID, EMPLOYER_ID, FIRST_NAME) VALUES (1, 1, 'John');
INSERT INTO EMPLOYEES (ID, EMPLOYER_ID, FIRST_NAME) VALUES (2, 1, 'Mary');
INSERT INTO EMPLOYEES (ID, EMPLOYER_ID, FIRST_NAME) VALUES (3, 1, 'Eugene');

INSERT INTO BENEFITS (ID, EMPLOYEE_ID, BENEFIT_TYPE, START_DATE, END_DATE, NAME)
    VALUES(1, 1, 'HEALTH_COVERAGE', '2005-01-01', null, 'Healthy Employees');
INSERT INTO BENEFITS (ID, EMPLOYEE_ID, BENEFIT_TYPE, START_DATE, END_DATE, NAME)
    VALUES(2, 1, 'RETIREMENT_PLAN', '2005-01-01', null, 'Gold Autumn');    
INSERT INTO BENEFITS (ID, EMPLOYEE_ID, BENEFIT_TYPE, START_DATE, END_DATE, NAME)
    VALUES(3, 1, 'GROUP_TERM_LIFE', '2005-01-01', null, 'Always Secured');