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?

Answer 1

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

Answer 2

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.