user3263327 user3263327 - 14 days ago 6
SQL Question

Criteria API ignores JOIN

I have a simple criteria api query with a (inner) join

public void find(Category category) {
CriteriaBuilder b = getQueryBuilder();
CriteriaQuery<Product> q = createQuery();
Root<Product> root = q.from(Product.class);
Join<Product, Category> myCategory= root.join("category");
q.where(b.equal(myCategory, category));
entityManager.createQuery(q).getResultList();
}


The query works, but if I enable in persistence.xml the sql logging I can see that the query is a

SELECT * FROM product, category WHERE ...


and no

SELECT * FROM product join category on category.id = product.category ...


Any idea what the reason for this is? The where statement is very slow, so a real join would be really better.

I'm using eclipselink 2.5.1, Java EE7 and postgres

I also have a superclass from Product

@Entity
@Audit
public class SuperClass {}

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Product extends SuperClass {}


But this should be no problem?

Answer

A join is actual being perform, however the SQL used to perform the join is written in implicit join notation. The join will have the same performance as explicit join notation (notation using JOIN and ON). A "real" join is being performed, however it is just not in the explicit join notation (aka ANSI SQL 92) you expect.