java - 不必要的加入jpa子查询

Question

我有以下 jpa 条件查询:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Company> cq = cb.createQuery(Company.class);
    Root<Company> root = cq.from(Company.class);
    cq.select(root);

    Subquery<String> sq = cq.subquery(String.class);
    Root<Employee> subroot = sq.from(Employee.class);
    sq.select(subroot.get(Employee_.lastName));

    Predicate typePredicate = cb.equal(subroot.get(Employee_.lastName), "Doe");

    Predicate correlatePredicate = cb.equal(root.get(Company_.employees), subroot);
    sq.where(cb.and(typePredicate, correlatePredicate));
    cq.where(cb.exists(sq));


    TypedQuery<Company> typedQuery = em.createQuery(cq);
    List<Company> companies = typedQuery.getResultList();

Eclipselink 生成以下 SQL:

    SELECT t0.ID, ... FROM COMPANY t0 
    WHERE EXISTS (SELECT t1.LASTNAME FROM EMPLOYEES t2, EMPLOYEES t1 
                  WHERE (((t1.LASTNAME = ?) AND (t1.ID = t2.ID)) 
                  AND (t2.COMPANY_ID = t0.ID))) 

如您所见，表 EMPLOYEES 上有一个不必要的连接。我如何摆脱这个连接？

Answer 1

您似乎不需要查询子查询，只需一个连接就足够了，

http://en.wikibooks.org/wiki/Java_Persistence/Criteria#Join

否则，您使用的是什么版本？你能试试 EclipseLink 2.4 吗？

如果它仍然有重复项，请记录一个错误并投票给它。

您可以使用反向 ManyToOne，而不是 OneToMany(即 root == subroot.get("company") )。

2.4也试试JPQL，join优化了吗？