How can I make Hibernate OUTER JOIN when using @OneToOne and @OneToMany

Question

Suppose I have a table called employee with fields of employee_id, name and supervisor_id and employee_supervisor with fields of supervisor_id and name. There is a foreign key relationship the employee and employee_supervisor table with respect to the supervisor_id column.

If I do a many to one annotation mapping in the Employee class, how can I make sure that Hibernate uses a LEFT OUTER JOIN to join the associated entities?

Answer 1

In my testing Hibernate did a LEFT OUTER JOIN by default. However you can ensure that it will always use a LEFT OUTER JOIN with annotations. I spent some time mocking up your situation using the bi-directional one to many mapping from my templates as a base, and then changing class names to match your situation.

The classes I came up with are as follows:

EmployeeSupervisor class:

@Entity(name="EMPLOYEE_SUPERVISOR")
public class EmployeeSupervisor {
    @Id
    @GenericGenerator(name = "gen", strategy = "increment")
    @GeneratedValue(generator = "gen")
    @Column(name = "id")
    private int supervisorId;

    @Column
    private String name;

    @OneToMany(mappedBy = "supervisor")
    private List<Employee> employees;
    ....
}

Employee class:

@Entity
public class Employee {
    @Id
    @GenericGenerator(name = "gen", strategy = "increment")
    @GeneratedValue(generator = "gen")
    @Column(name = "id")
    private int employeeId;

    @Column
    private String name;

    @ManyToOne
    @Fetch(FetchMode.JOIN)
    @JoinColumn(name = "supervisorId")
    private EmployeeSupervisor supervisor;
    ....
}

The annotation that ensures that you will always use a LEFT OUTER JOIN is the @Fetch(FetchMode.JOIN annotation. This tells Hibernate to load the associated record in the same select statement using a LEFT OUTER JOIN (see the documentation for the other types of FetchMode's and what each one does).

I then mocked up the database in jUnit, configured Hibernate to print all the generated SQL's in log4j

log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE

and ran a pretty basic unit test.

public class EmployeeDAOTest extends SpringTest{

    @Autowired
    private EmployeeDAO dao;

    private Employee testLinkedEmployee;
    private Employee testUnlinkedEmployee;
    private EmployeeSupervisor testSupervisor;

    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        System.out.println("Starting DAO Test");
    }

    @AfterClass
    public static void tearDownAfterClass() throws Exception {
        System.out.println("Finished DAO Test");
    }

    @Before
    public void setUp() throws Exception {
        //Irrelevant horrible setup code snipped.
        /* Set up 2 employees and a EmployeeSupervisor in the database.
         * Link one employee to the EmployeeSupervisor and not the other
         */
    }

    @Test
    @Transactional
    public void test() {
        Employee actualLinkedEmployee = dao.getEmployee(testLinkedEmployee.getEmployeeId());
        Employee actualUnlinkedEmployee = dao.getEmployee(testUnlinkedEmployee.getEmployeeId());

        assertNotNull("The linked employee's supervisor didn't get selected.", actualLinkedEmployee.getSupervisor());
        assertNull("The unlinked employee's supervisor was not null.", actualUnlinkedEmployee.getSupervisor());
    }
}

My DAO is exceedingly rudimentary:

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {

    @Autowired
    private SessionFactory sessionFactory;

    @Override
    public Employee getEmployee(int id) {
        Criteria query = sessionFactory.getCurrentSession().createCriteria(Employee.class);
        query.add(Restrictions.idEq(id));
        return (Employee) query.uniqueResult();
    }
}

and the SQL output is as follows:

[junit]     /* criteria query */ select
[junit]         this_.id as id1_1_1_,
[junit]         this_.name as name2_1_1_,
[junit]         this_.supervisorId as supervis3_1_1_,
[junit]         employeesu2_.id as id1_0_0_,
[junit]         employeesu2_.name as name2_0_0_ 
[junit]     from
[junit]         Employee this_ 
[junit]     left outer join
[junit]         EMPLOYEE_SUPERVISOR employeesu2_ 
[junit]             on this_.supervisorId=employeesu2_.id 
[junit]     where
[junit]         this_.id = ? 01:23:54:0.668 
[junit] binding parameter [1] as [INTEGER] - 5 01:23:54:0.671 

[junit]     /* criteria query */ select
[junit]         this_.id as id1_1_1_,
[junit]         this_.name as name2_1_1_,
[junit]         this_.supervisorId as supervis3_1_1_,
[junit]         employeesu2_.id as id1_0_0_,
[junit]         employeesu2_.name as name2_0_0_ 
[junit]     from
[junit]         Employee this_ 
[junit]     left outer join
[junit]         EMPLOYEE_SUPERVISOR employeesu2_ 
[junit]             on this_.supervisorId=employeesu2_.id 
[junit]     where
[junit]         this_.id = ? 01:23:54:0.704 
[junit] binding parameter [1] as [INTEGER] - 6 01:23:54:0.704 

It should be noted that by default Hibernate seems to be eagerly fetching these entities and uses a LEFT OUTER JOIN to do so. However if you try and set the default fetch type to be FetchType.LAZY then the join type changes to a FetchMode.SELECT and just issues a single select for the employee without selecting the supervisor as well.

Setting FetchType.LAZY with @Fetch(FetchMode.JOIN) however, overrides your lazy fetching and uses a join to eagerly fetch your supervisor.

Answer 2

employee_supervisor{
      id, 
      name
}

employee{
      id, 
      name,
 ManyToOne 
      supervisor_id
}

 select supervisor FROM Employee e 
 left outer JOIN e.supervisor_id supervisor 
 WHERE e.id = :id