CODEWITHSUNDEEP
javajpajpa-2.0eclipselinkcriteria-api
Christian
Christian

Reputation: 4084

Unneccessary join in jpa subquery

I have following jpa criteria query:

    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 produces following 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)))

As you can see there is an unneccessary join on table EMPLOYEES. How do I get rid of this join?

Upvotes: 2

Views: 2202

Answers (1)

James
James

Reputation: 18379

You don't seem to need a subquery for the query, just a join should be enough,

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

Otherwise, what version are you using? Can you try EclipseLink 2.4.

If it still has the duplicate, please log a bug and vote for it.

You might be able to use the inverse ManyToOne, instead of the OneToMany (i.e. root == subroot.get("company") ).

Also try JPQL in 2.4, is the join optimized?

Upvotes: 2

Related Questions