JPQL Query a list of Strings inside an Entity

Question

I'm using Java EE7 with a GlassFish 4.1 Server to build a system where basically you can post Ideas, and each Idea may have tags. I've declared the entity Idea as:

@Entity
@Table(name = "IDEAS")
public class Idea implements Serializable {

// Id, description, etc.

@ElementCollection
private List<String> tags;

// Getters, Setter, etc.
}

After reading JPA: Query an embeddable List inside an entity I tried to find by Tag the following way:

public List<Idea> getIdeasWithTag(String tag) {
    String queryStr = "select ideatags from Idea i "
            + "inner join i.tags ideatags "
            + "where ideatags.tags = :pTag";
    Object res = em.createQuery(queryStr)
            .setParameter("pTag", tag)
            .getResultList();
    return (List<Idea>) res;
}

But I'm getting a TransactionRolledbackLocalException caused by:

Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [select ideatags from Idea i inner join i.tags ideatags where ideatags.tags = :pTag]. [61, 74] The state field path 'ideatags.tags' cannot be resolved to a valid type.

I appreciate any help, thanks in advance!!

Answer 1

You have some problems with your query:

select ideatags 
from Idea i inner join i.tags ideatags
where ideatags.tags = :pTag

You want the result as Idea but you select a List.

You get your List tags in ideatags, therefore you cant get the attribute tag of ideatags.

if you want to search in the list you have to use IN.

You can try this:

select i
from Idea i
where :pTag IN (i.tags)

Answer 2

elements() - this work!

where :pTag IN (i.tags) is not work (

Answer 3

Try this:

select e from entity e WHERE :element in elements(e.listOfStrings)