Theta Style Left Outer Join in HQL

Question

I have the following SQL query that I am attempting to convert to HQL. Unfortunately, outer joins have never been my favorite to deal with.

SELECT DISTINCT T_EMPLOYEE.SSN,   
     T_EMPLOYEE.TRANSACTION_ID,   
     T_EMPLOYEE.NAME,   
     T_EMPLOYEE.CANCEL_DATE,   
     T_EMPLOYEE.CAN_USERID,   
     T_DEPARTMENT.LOC FROM T_EMPLOYEE,   
     T_DEPARTMENT  
WHERE ( T_EMPLOYEE.DEPARTMENT_CODE = T_DEPARTMENT.CODE (+)) and  
     ( ( T_EMPLOYEE.SSN = :s_ssn ) )

I know the (+) symbol on the right of the where is Oracle's old style outer join sequence (and since it's on the right its a left outer join). There is also no foreign key relationship between T_EMPLOYEE and T_DEPARTMENT (legacy database).

Two questions: 1. Is a theta-style (i.e. using =) outer join possible in HQL without the foreign key relationship? 2. If so, can someone help me convert this to HQL?

Jason

Firo Firo · Accepted Answer · 2012-11-06T09:03:30

assuming

class Employee
{
    private Department department;
}
<many-to-one name="department" column="DEPARTMENT_CODE" />

class Department
{
    private long id;
}
<id name="id" column="CODE" ><generator class="assigned" /></id>

then you get Employee with eager loaded Department with

"FROM Employee e join fetch e.Department d WHERE e.SSN = :s_ssn"

or only the columns you want

"SELECT DISTINCT e.ssn, ..., d.loc FROM Employee e left outer join e.Department d WHERE e.SSN = :s_ssn"

Theta Style Left Outer Join in HQL

1 Answers