3

I want to make a JPQL query with a collection of non entities. This is my Table entity :

@Entity
@Table(name = "ct_table")
public class Table {
...

@CollectionOfElements(fetch = FetchType.EAGER)
@JoinTable(name = "ct_table_result", joinColumns = @JoinColumn(name = "tableId"))
@MapKey(columns = { @Column(name = "label") })
@Column(name = "value")
private Map<String, String> tableResults;
...

then I try to make a query like this

select count(*) from table where table.tableResults['somekey'].value='somevalue'

but I get the following exception:

Cannot create element join for a collection of non-entities!

Any suggestion??

thanks for your time

EDIT:

I use JPA 1 and hibernate 3.3. Default libraries in JBoss 5

Alex M
  • 471
  • 4
  • 22

1 Answers1

8

The JPA 2 spec (page 139) defines the KEY() and VALUE() functions to access the key and the value of a map-valued element collection:

select count(t.id) from Table t 
where KEY(t.tableResults) = 'somekey' and VALUE(t.tableResults) = 'somevalue'
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • and in JPA 1?? I'm using Jboss 5.5 and I have to use JPA 1 I cannot use JPA 2 – Alex M Feb 16 '12 at 12:32
  • What's the version of Hibernate you're using? – JB Nizet Feb 16 '12 at 12:34
  • JPA 1 and hibernate 3.3. Default libraries in Jboss 5 – Alex M Feb 16 '12 at 12:41
  • 4
    Unfortunately, although this version of Hibernate supports JPA2, AFAIK, support for KEY and VALUE is still not implemented (see https://hibernate.onjira.com/browse/HHH-5396), even in current versions. Hibernate as the index() function, but I'm not sure you can use it on a collection of elements. See http://docs.jboss.org/hibernate/core/3.3/reference/en-US/html_single/. Try the following: select count(t.id) from Table t join t.tableResults r where index(r) = 'somekey' and r = 'somevalue'. You could also use an entity map, rather than a map of elements. – JB Nizet Feb 16 '12 at 12:53