Conversion of SQL Left Outer Join Query to JPA Criteria

Question

I have the following native SQL query that I am trying to convert to JPA criteria:

select et.* from t_empl_tx et, t_dept d 
where et.assigned_dept = d.dept (+)
  and et.employee_id = :employee_id
  and (et.start_date >= d.dept_est_date and
       et.start_date <= d.dept_close_date or
       et.start_date is null or
       d.dept is null)

(Note that (+) is roughly equivalent to a left outer join in this case. Yes, I know it denotes the OPTIONAL table, etc, etc).

Here is my attempt at the code:

EntityManager entityManager = getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<EmployeeTransaction> criteriaQuery = criteriaBuilder.createQuery(EmployeeTransaction.class);
Root<EmployeeTransaction> root = criteriaQuery.from(EmployeeTransaction.class);

    // this line bombs!
Join<EmployeeTransaction, Department> join = 
    root.join(EmployeeTransaction_.assignedDepartment).join(Department_.id).join(DepartmentCompositeId_.department, JoinType.LEFT);

List<Predicate> predicates = new ArrayList<>();

predicates.add(criteriaBuilder.equal(root.get(EmployeeTransaction_.id).get(EmployeeTransactionCompositeId_.employeeId), employeeId));
predicates.add(criteriaBuilder.or(
    criteriaBuilder.and(
    criteriaBuilder.greaterThanOrEqualTo(root.<Date>get(EmployeeTransaction_.requestedStartDate), join.get(Department_.id).<Date>get(DepartmentCompositeId_.departmentCreationDate)),
    criteriaBuilder.lessThanOrEqualTo(root.<Date>get(EmployeeTransaction_.requestedStartDate), join.<Date>get(Department_.departmentCloseDate))
    ),
    criteriaBuilder.isNull(root.get(EmployeeTransaction_.requestedStartDate)),
    criteriaBuilder.isNull(join.get(Department_.id).get(DepartmentCompositeId_.departmentCreationDate))
));

criteriaQuery.select(root).where(predicates.toArray(new Predicate[]{}));

TypedQuery<EmployeeTransaction> query = entityManager.createQuery(criteriaQuery);
List<EmployeeTransaction> result = query.getResultList();

This issue seems to be that I'm trying to join a string column, assigedDepartment, to a single field of a composite ID. This is perfectly legal in SQL, but not so easy in the code.

One option is to convert to a number of subqueries, which seems to kill the point of the left outer join entirely.

Can anyone point out what I'm doing wrong?

Jason

Answer 1

You should post your entities so that the answers can be more specific.

However, I'll give a try.

If I am right, you can rewrite the query:

select et.* 
from t_empl_tx et
    left join t_dept d on et.assigned_dept = d.dept
where 
    et.employee_id = :employee_id
    and (
        et.start_date >= d.dept_est_date 
        and et.start_date <= d.dept_close_date
        or et.start_date is null 
        or d.dept is null)

So, shortly, you have to move the JoinType.LEFT to assignedDepartment join:

EntityManager entityManager = getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<EmployeeTransaction> criteriaQuery = criteriaBuilder.createQuery(EmployeeTransaction.class);

Root<EmployeeTransaction> root = criteriaQuery.from(EmployeeTransaction.class);
Join<EmployeeTransaction, Department> department =  root.join(EmployeeTransaction_.assignedDepartment, JoinType.LEFT);
Path<Date> employeeTransactionRequestedStartDate = root.get(EmployeeTransaction_.requestedStartDate);
Path<DepartmentCompositeId> departmentId = department.get(Department_.id);
Path<Date> departmentCreationDate = departmentId.get(DepartmentCompositeId_.departmentCreationDate)
Path<Date> departmentCloseDate = departmentId.get(DepartmentCompositeId_.departmentCloseDate)

criteriaQuery.select(root).where(
    criteriaBuilder.equal(root.get(EmployeeTransaction_.id).get(EmployeeTransactionCompositeId_.employeeId), employeeId),
    criteriaBuilder.or(
        criteriaBuilder.and(
            criteriaBuilder.greaterThanOrEqualTo(employeeTransactionRequestedStartDate, departmentCreationDate)),
            criteriaBuilder.lessThanOrEqualTo(employeeTransactionRequestedStartDate, departmentCloseDate)
        ),
        criteriaBuilder.isNull(employeeTransactionRequestedStartDate),
        criteriaBuilder.isNull(departmentCreationDate)
    )
);

TypedQuery<EmployeeTransaction> query = entityManager.createQuery(criteriaQuery);
List<EmployeeTransaction> result = query.getResultList();