How to query for entities by their collection value

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'

score 18 · Accepted Answer · answered May 23 '12 at 09:00

18

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.

answered May 23 '12 at 09:00

d1e

6,372
2
28
41

1

I know in this topic we are reffering to ID's and therefore they have to be EQUAL to match, but let's say we have a `Collection` instead, can we do essentially the same thing but instead of needing an item to be `equal`, we could have an item MATCH using `LIKE` ? – dominicbri7 Aug 11 '16 at 17:46
3

@dominicbri7 on a related [question](http://stackoverflow.com/questions/7066122/how-to-make-a-like-query-to-elementcollection-of-type-map?rq=1) I found the solution. Use the join method on Root class `cb.like(from.join("apples").get("color"), textParameter)` – cirovladimir Sep 01 '16 at 15:27

score 2 · Answer 2 · answered Nov 28 '13 at 22:34

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);
    }

Teerapong Suksuwan · Answer 3 · 2018-06-02T12:26:45.020

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)
        }
    }

How to query for entities by their collection value

3 Answers3

Linked