How to query for entities by their collection value

collections, criteria-api, jpa

Solution

To get a set of FavoritesFolder entities that contains the string "favorite-id" in their productsIds member set using criteria api you should do the following:

CriteriaBuilder cb = em.getCriteriaBuilder(); //em is EntityManager
CriteriaQuery<FavoritesFolder> cq = cb.createQuery(FavoritesFolder.class);
Root<FavoritesFolder> root = cq.from(FavoritesFolder.class);

Expression<Collection<String>> productIds = root.get("productsIds");
Predicate containsFavoritedProduct = cb.isMember("favorite-id", productIds);

cq.where(containsFavoritedProduct);

List<FavoritesFolder> favoritesFolders = em.createQuery(cq).getResultList();

More information on Collections in JPQL and Criteria Queries.

Problem

I'm using jpa and I have the following entity: ``` @Entity @Table(name="favorites_folders") public class FavoritesFolder { private static final long serialVersionUID = 1L; @Id private String id; @NotNull @Size(min = 1, max = 50) public String name; @ElementCollection(fetch = FetchType.LAZY) @CollectionTable( name="favorites_products", joinColumns=@JoinColumn(name="folder_id") ) @Column(name="product_id") @NotNull private Set<String> productsIds = new HashSet<String>(); } ``` What I want to do is to get a set of `FavoritesFolder` entities that contains the string "favorite-id" in their `productsIds` member set. Does anyone know how can it be done in criteria api? Update: I'm thinking the following sql should do the trick but I'm not sure how to do it in either `JPQL` or `Criteria API`: ``` select * from favorites_folders join favorites_products on favorites_folders.id = favorites_products.folder_id where favorites_products.product_id = 'favorite-id' ```

Original source