java 左连接有图_java – 将左边外部连接查询转换为JPA标准

我有以下本机SQL查询,我试图转换为JPA标准：

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)

(注意,在这种情况下,()大致相当于左外连接,是的,我知道它表示可选表,等等).

这是我尝试的代码：

EntityManager entityManager = getEntityManager();

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(EmployeeTransaction.class);

Root root = criteriaQuery.from(EmployeeTransaction.class);

// this line bombs!

Join join =

root.join(EmployeeTransaction_.assignedDepartment).join(Department_.id).join(DepartmentCompositeId_.department, JoinType.LEFT);

List predicates = new ArrayList<>();

predicates.add(criteriaBuilder.equal(root.get(EmployeeTransaction_.id).get(EmployeeTransactionCompositeId_.employeeId), employeeId));

predicates.add(criteriaBuilder.or(

criteriaBuilder.and(

criteriaBuilder.greaterThanOrEqualTo(root.get(EmployeeTransaction_.requestedStartDate), join.get(Department_.id).get(DepartmentCompositeId_.departmentCreationDate)),

criteriaBuilder.lessThanOrEqualTo(root.get(EmployeeTransaction_.requestedStartDate), join.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 query = entityManager.createQuery(criteriaQuery);

List result = query.getResultList();

这个问题似乎是我试图加入一个字符串列,assigedDepartment到复合ID的单个字段.这在SQL中是完全合法的,但在代码中并不容易.

一个选项是转换成许多子查询,这似乎完全杀死了左外连接点.

有人可以指出我在做错什么吗？

贾森