JOIN 3 Entities in JPA

Question

This is the first time I develop an Java application and immediately face an issue. Now I have a SQL query as below:

Select PD.* From product PD
join Stock SP on PD.id = SP.product_id
join storage SR on SR.id = SP.storage_id
join status ST on ST.storage_id = SR.id
where ST.id ='1qa7n234i'

My entities:

public class Product extends MainEntity {
   public Product(){}
   public void setAtcKey(String pAtcKey) {
    atcKey = pAtcKey;
   }
}

public class StockPosition extends MainEntity {
   public StockPosition(){}
   @XmlIDREF
    public Product getProduct() {
        return product;
    }

    public void setProduct(Product pProduct) {
        product = pProduct;
    }

    @XmlIDREF
    public Storage getStorage() {
        return storage;
    }

    public void setStorage(Storage pStorage) {
        storage = pStorage;
    }
}

public class Storage extends MainEntity {
   public Storage(){}
   @XmlTransient
   public Station getStation() {
    return station;
   }

   public void setStation(Station pStation) {
    station = pStation;
   }
}

public class Status extends MainEntity {
   public Status(){}
}

So I convert it to CriteriaQuery like:

CriteriaBuilder cb = getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> product = query.from(Product.class);
Join<StockPosition, Product> sp = product.join(StockPosition_.product);

But I got an error message likeThe method join(SingularAttribute<? super Product,Y>) in the type From<Product,Product> is not applicable for the arguments (SingularAttribute<StockPosition,Product>)? This message is very strange for me (because I am newbie). Thank you

This means that you are trying to build a criteria query. This is not the only way, you can also write queries with [jpql](http://docs.oracle.com/javaee/6/tutorial/doc/bnbtg.html) that is easier for starters. Anyway if you want a criteria query, use the corresponding tag and be aware that _there are_ valuable answers here on SO. If you still need help, post the entities and what you have already done — perissf, Apr 24 '13 at 11:43
You have to reverse the join: `Join sp = product.join(Product_.stockPositionCollection)` do you have a property like `stockPositionCollection` in `Product` ? — perissf, Apr 24 '13 at 11:59
Actually, I have three entities, Product, StockPosition and Status. StockPosition holds Product_id fk and status_id fk, other entities do not hold stockposition_id but I want to get all records from product entity. Thank you for your quick response @perissf — Phong Vo, Apr 24 '13 at 12:08
Please edit your question by adding the entities and the relevant relationship properties — perissf, Apr 24 '13 at 12:24

André · Answer 1 · 2013-04-24T13:11:40.263

CriteriaBuilder cb = getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> product = query.from(Product.class);
Join<StockPosition, Product> sp = product.join(StockPosition_.product);

I think it should look like this:

CriteriaBuilder cb = getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> product = query.from(Product.class);
Join<StockPosition, Product> sp = product.join(Product_.stockposition);

Maybe you think to much in a way of SQL joins.

If you don't need to build the query at runtime you may consider to use JPQL instead.

JPQL with NamedQueries: http://www.objectdb.com/java/jpa/query/named

Maybe this help you on other questions about JPA http://www.objectdb.com/java/jpa/query/criteria

Do you build the metamodel classes on your own? Or did you use an "Meta Model Generator" provided by your persistence framework?

Hope this helps.

score 0 · Answer 2 · answered Apr 24 '13 at 13:09

Your join is not correct. If your Root is in the Product (Root<Product>) then you must join using an attribute present in the Product class.

In this case, your Productclass should define an attribute like:

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.REFRESH)
private List<StockPosition> stackPositions;

And then perform the join as follows:

Join<Product, StackPosition> sp = product.join(Product_.stackPositions_);
Predicate p = cb.equal(sp.get(StackPosition_.id, "1qa7n234i");
...

score -1 · Answer 3 · answered Apr 24 '13 at 11:42

If you have one-to-one relationship in your DB model, then having a class and Annotation like below should give you the result.

Basically, you will fetch the Stock, and get stock.storage.status (which is joined as per the @javax.peristence.JoinColumn clause)

class Product {

@OnetoOne
@JoinColumn(name="product_id")
Stock stock;

}

class Stock {

@OnetoOne
@JoinColumn(name="storage_id")
Storage storage;

}

class Storage {

@OnetoOne
@JoinColumn(name="storage_id")
Status status;

}

Thank you, dude, but this ways does not help :( – Phong Vo Apr 24 '13 at 11:52 — Phong Vo, Apr 24 '13 at 11:52

JOIN 3 Entities in JPA

3 Answers3