user3263327 user3263327 - 1 year ago 106
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));

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 = 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

public class SuperClass {}

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

But this should be no problem?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download