I am using JPA and trying to select all childentities (Product
) of a given List
of parents (Category
). The relationship is Category OneToMany Product
. I would like to keep it down to one query and not create a Predicate
like product.get("category") == category.get(0) || product.get("category") == category.get(1) || ...
.
I have tried the following bit of code, but this does not seem to work (as seen in the stack at the bottom). Does anyone have a suggestion as how to accomplish this?
Code
public List<Product> findProductsBy(List<Category> categories) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root product = query.from(Product.class);
Predicate predicateCategory = product.get("category").in(categories);
query.select(product).where(predicateCategory);
return em.createQuery(query).getResultList();
}
Stack
WARNING: Local Exception Stack:
Exception [EclipseLink-6075] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: Object comparisons can only use the equal() or notEqual() operators. Other comparisons must be done through query keys or direct attribute level comparisons.
Expression: [
Relation operator [ IN ]
Query Key category
Base (...).Product
What you can do is using the category id instead of the category object (as the error state: object comparison is not supported):
(Assuming your category id is a Long
, you can do the following.)
public List<Product> findProductsBy(List<Category> categories) {
List<Long> categoryIds = new ArrayList<Long>();
for(Category category:categories){
categoryIds.add(category.getId());
}
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root product = query.from(Product.class);
Predicate predicateCategory = product.get("categoryId").in(categoryIds);
query.select(product).where(predicateCategory);
return em.createQuery(query).getResultList();
}