JPA @ElementCollection how can I query?

Question

I am using Spring JPA and in order to ad a List of String to my Entity I am using @ElementCollection as below.

@ElementCollection
private Map<Integer, String> categories;

When I use this it generates a table called subscription_categories this contains the following columns subscription(varchar), catergories(varchar) and caterogies_key (int)

If I use my SQL tool on my desktop I can query this table fine with the following

select `subscription_categories`.`subscription` from `subscription_categories` where `subscription_categories`.`categories`='TESTING';

However, when I attempt to use this in Spring Data it fails with a "... not mapped" error

Here are a few attempts below:

@Query("select s.subscription from subscription_categories s where s.categories = ?1")
    List<Subscription> findUsernameByCategory(String category);



@Query("select s.subscription from categories s where s.categories = ?1")
    List<Subscription> findUsernameByCategory(String category);

Both return the same error.

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: categories is not mapped

My question is this:

How can I query the table created by the @ElementCollection?

@volatilevar If I add @CollectionTable(name="SUBSCRIPTION_LIST") and then attempt to query that I get the same error SUBSCRIPTION_LIST not mapped. — Yonkee, Jan 08 '17 at 00:47
I removed my last comment because it was wrong. `categories` is a member of the outer class. Assume the class is Foo, then you need to do something like `select .... from Foo f where .... f.categories ....`. You can refer to this question http://stackoverflow.com/questions/18657422/hql-join-collectiontable — volatilevar, Jan 08 '17 at 04:45
Since `subscription_categories` is not an entity, so you can't run `hql` or `jpql` on it. You'll have to use native query. Alternatively, you can create a `Categories` entity and then have `@OneToMany` mapping from `Subscription` — ares, Jan 08 '17 at 06:46
Why not start by posting complete info? starting with your JPA entity. — Neil Stockton, Jan 08 '17 at 07:45
_"add a List of String to my Entity"_ ... the field seems to be a Map, so no it's not a List. — Neil Stockton, Jan 08 '17 at 17:54

score 6 · Accepted Answer · edited Aug 06 '20 at 18:37

You can't directly query from @ElementCollection. You should query base entity (I assume its name is Subscription).

@Query("select s from Subscription s where s.categories = ?1")
List<Subscription> findUsernameByCategory(String category);

If you want query by key, then use

@Query("select s from Subscription s where index(s.categories) = ?1")
List<Subscription> findUsernameByCategoryKey(Integer key);

score 6 · Answer 2 · answered Dec 18 '18 at 15:21

I'd also side with @talex on this and argue that you need to base your query on the parent/container/base object of the @ElementCollection.

In my experience following query should suffice:

@Query("select category from Subscription subscription inner join subscription.categories category")

Side-note: Querying subscription_categories seems to be the wrong path, since this table is part of a different layer (the database layer in Sql/Jpql), while the query should be formed on the Hibernate layer (hql), which uses your entity/class-names as references. I have used Upper-case class names, instead of lower-case table names.

JPA @ElementCollection how can I query?

2 Answers2

Linked