How to use CriteriaQuery for ElementCollection and CollectionTable

Question

I have a very simple entity Product which has a code, name and tags. Tags are stored in another table (product_tag) with product_id and tag columns.

I need to search for products with certain tags using CriteriaQuery. To give an example I want to find products having 'fruit' and 'red' tags.

Using spring 4.1.x, spring-data-jpa 1.8 and hibernate 4.2.x.

My entity simply is;

@Entity
@Table(name = "product", uniqueConstraints ={
        @UniqueConstraint(columnNames = "code")
    }
)
@NamedQueries({
        @NamedQuery(name = "Product.findAll", query = "select p from Product p")
})
public class Product extends EntityWithId {

    @Column(name = "code", length = 128)
    private String code;

    @Column(name = "name", length = 512)
    protected String name;

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name="product_tag", joinColumns=@JoinColumn(name="product_id"))
    @Column(name="tag")
    private Set<String> productTags = new HashSet<>();

}

here is the code how I initiate the search;

private void search() {

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> criteriaQuery = builder.createQuery(Product.class);
    Root<Product> product = criteriaQuery.from(Product.class);

    Predicate where = builder.conjunction();

    if (!StringUtils.isEmpty(nameSearch.getValue())) {
        where = builder.and(where, builder.like(product.<String>get("name"), nameSearch.getValue() + "%"));
    }

    if (!StringUtils.isEmpty(codeSearch.getValue())) {
        where = builder.and(where, builder.like(product.<String>get("code"), codeSearch.getValue() + "%"));
    }

    if (!StringUtils.isEmpty(tagsSearch.getValue())) {
         //Util.parseCommaSeparated returns Set<String>
        where = builder.and(where, product.get("productTags").in(Util.parseCommaSeparated(tagsSearch.getValue())));
    }

    criteriaQuery.where(where);
    List<Product> resultList = entityManager.createQuery(criteriaQuery).getResultList();

}

However when I run the search for tags 'fruit' I get an exception

java.lang.IllegalArgumentException: Parameter value [fruit] did not match expected type [java.util.Set (n/a)]

I really wonder to use CriteriaQuery for ElementCollection and CollectionTable.

score 25 · Accepted Answer · answered Apr 24 '15 at 05:22

25

productTags is mapped to a separate table, therefore you need to join with that table in your query.

...

if (!StringUtils.isEmpty(tagsSearch.getValue())) {
     //Util.parseCommaSeparated returns Set<String>
    where = builder.and(where, product.join("productTags").in(Util.parseCommaSeparated(tagsSearch.getValue())));
}

...

Note the product.join("productTags") instead of product.get("productTags")

answered Apr 24 '15 at 05:22

Donovan Muller

3,822
3
30
54

2

Furthermore, this creates a kind of OR search, having fuit and red in the tagsSearch, all the reds and fruits return. for an AND search i did explicitly created AND statements for each tagsSearch. for those who struggle :) – hevi Apr 26 '15 at 10:58
In my case I needed to search for just one tag and I used the following syntax Specification spec = (root, query, cb) -> cb.equal(root.join("reparti"), idReparto); where "reparti" is my collection and "idReparto" the value to select for. Hope this could help – Roberto Petrilli Sep 01 '20 at 14:46

score 1 · Answer 2 · answered Apr 24 '15 at 05:25

1

Try to use isMember() rather than in()

Check the example 5 and 7

answered Apr 24 '15 at 05:25

StanislavL

56,971
9
68
98

How to use CriteriaQuery for ElementCollection and CollectionTable

2 Answers2