我有以下本机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中是完全合法的,但在代码中并不容易.
一个选项是转换成许多子查询,这似乎完全杀死了左外连接点.
有人可以指出我在做错什么吗?
贾森