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!!

you don't have the same situation as that linked post ... your List is of a String, not an Embeddable. Consequently your JPQL "ideatags.tags" makes no sense at all. So are you trying to find all Idea objects that have a specific "tag" contained? — Neil Stockton, Jun 25 '15 at 14:17
Oh thanks (i'm new to JEE and JPQL). Exactly, I want to get all the Idea objects that contain that "tag" — jmcorallo, Jun 25 '15 at 14:20

Panchitoboy · Accepted Answer · 2015-06-25T14:44:12.087

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)

edited Jun 25 '15 at 14:44

answered Jun 25 '15 at 14:24

Panchitoboy

810
9
18

Ok gracias, makes sense. But I'm getting "Syntax error parsing [select i from Idea i where :pTag IN i.tags]. [35, 35] The left parenthesis is missing from the IN expression. [42, 42] The right parenthesis is missing from the IN expression." when I try this. Do you know what it can be?? – jmcorallo Jun 25 '15 at 14:41
I tried adding parenthesis like this (:pTag IN i.tags) but didn't work either – jmcorallo Jun 25 '15 at 14:42
Put the parentheses around the "i.tags" (since that being a List was only introduced in JPA 2.1 IIRC). Some JPA implementations are weak on that – Neil Stockton Jun 25 '15 at 14:42

score 0 · Answer 2 · answered Mar 17 '21 at 20:21

0

Try this:

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

answered Mar 17 '21 at 20:21

Vikcen

153
2
9

Roman Katzendorn · Answer 3 · 2022-11-17T14:26:26.647

0

elements() - this work!

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

edited Nov 17 '22 at 14:26

answered Nov 17 '22 at 14:24

Roman Katzendorn

1
1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 20 '22 at 07:12

JPQL Query a list of Strings inside an Entity

3 Answers3