How to query for entities by their collection valu

Question

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'

Answer 1

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.

Answer 2

Just another way using IN

@Entity
public class UserCategory implements Serializable {
private static final long    serialVersionUID    = 8261676013650495854L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ElementCollection
private List<String> categoryName;


(...)
}

Then you can write a Criteria query like

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<UserCategory> q = cb.createQuery(UserCategory.class);
    Root<UserCategory> root = q.from(UserCategory.class);

    Predicate predicate = cb.conjunction();
    Predicate p1 = cb.equal(root.get(UserCategory_.targetSiteType), siteType.getName());
    Predicate p2 = root.get(UserCategory_.categoryName).in(category);
    predicate = cb.and(p1,p2);

    q.where(predicate);

    TypedQuery<UserCategory> tq = entityManager.createQuery(q);
    List<UserCategory> all = tq.getResultList();

    if (all == null || all.size() == 0){
        return null;
    }else if (all.size() > 1){
        throw new Exception("Unexpected result - "+all.size());
    }else{
        return all.get(0);
    }

Answer 3

This is my work around that works. I'm using Springboot 1.5.9. I don't have time to identify the root cause. What I know is such nested property been ignored when get through JacksonMappingAwareSortTranslator. So what I did to workaround this is not to use Sort object created by resolvers. Here's my code in Kotlin. Without doing this, the pageable.sort is null and sorting does not work. And my code will create a new PageRequest object that has non-null sort that works.

    @RequestMapping("/searchAds", method = arrayOf(RequestMethod.POST))
    fun searchAds(
        @RequestBody cmd: AdsSearchCommand,
        pageable: Pageable,
        resourceAssembler: PersistentEntityResourceAssembler,
        sort: String? = null
    ): ResponseEntity<PagedResources<Resource<Ads>>> {
        val page = adsService.searchAds(cmd, pageable.repairSortIfNeeded(sort))
        resourceAssembler as ResourceAssembler<Ads, Resource<Ads>>
        return adsPagedResourcesAssembler.toResource(page, resourceAssembler).toResponseEntity()
    }

    fun Pageable.repairSortIfNeeded(sort: String?): Pageable {
        return if (sort.isNullOrEmpty() || this.sort != null) {
            this
        } else {
            sort as String
            val sa = sort.split(",")
            val direction = if (sa.size > 1) Sort.Direction.valueOf(sa[1]) else Sort.Direction.ASC
            val property = sa[0]
            PageRequest(this.pageNumber, this.pageSize, direction, property)
        }
    }