Join Two table in Criteria Query

Question

I have three tables one is ItemCategory,ItemMaster and Price. I am referring itemaCategoryId in ItemMaster table and like that referring itemmasterid in price. Now i have to display contents of price order by itemcategory id. This is my criteria query.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Price> cq = cb.createQuery(Price.class);
    Root<Price> root = cq.from(Price.class);
    Root<ItemMaster> itemMasterRoot = cq.from(ItemMaster.class);
    Root<ItemCategory> itemCategoryRoot = cq.from(ItemCategory.class);
    Join<ItemMaster, ItemCategory> s=itemMasterRoot.join(ItemMaster_.category);     
    Join<Price,ItemMaster> y=root.join(Price_.itemMaster);

    Path<Long> itemMasterId=root.get(Price_.itemMasterId);
    cq.select(root).where(cb.equal(root.get(Price_.priceMasterId), priceMasterId))
    .orderBy(cb.asc(itemMasterId));

    TypedQuery<Price> q = entityManager.createQuery(cq);

Above my criteria Query

Solution

If you use multiple from statements, you get the cartesian product of all entities. If you want to preserve the relationships, use join instead:

Root<Price> price = cq.from(Price.class);
Join<Price,ItemMaster> itemMaster = price.join(Price_.itemMaster);
Join<ItemMaster, ItemCategory> itemCategory = itemMaster.join(ItemMaster_.category);

However it looks like at least the second join may be useless, because you are able to access the category property directly using the getter, isn't it?:

Price aPriceResult;
ItemCategory categoryResult = aPriceResult.getItemMaster().getCategory();

Comments

[Uday]

Finally i have written like this using your code but giving error

[Uday]

cq.multiselect(itemCategory).where(cb.equal(root.get(Price_.‌​priceMasterId), priceMasterId)) .orderBy(cb.asc(itemCategory.get(ItemCategory_.id)));

[perissf]

Why multiselect? Use select.

[Uday]

finally i have got something @perissf. Thanks for response man.

[Uday]

This is how i have written. cq.select(root).where(cb.equal(root.get(Price_.priceMasterId‌​), priceMasterId)) .orderBy(cb.asc(itemCategoryJoin.get(ItemCategory_.id)));

[perissf]

@kobe it's a Metamodel-generated entity class.