Run the "MEMBER" query for the "ElementCollection" fields in JP-QL (JPA 2.0)

Question

Is it possible to run the query "OF MEMBERS" against associative arrays? If so, what does the syntax look like? The obvious workaround is to query yourself, but it gets pretty messy with all joins, etc. I would like to check if an object is in the map key set, value set, or record set. Maybe something like the following:

SELECT p FROM Person p WHERE 'home' MEMBER OF p.phoneNumbers.keySet SELECT p FROM Person p WHERE '867-5309' MEMBER OF p.phoneNumbers.values SELECT p FROM Person p WHERE {'home' -> '867-5309'} MEMBER OF p.phoneNumbers 

The agnostic provider code may be too large to request; Does Eclipselink support this?

Answer 1

JPQL has a function called index() , which is useful for getting the index in the @OrderColumn list. And, as you said yourself, maps are also called associative arrays, and map keys correspond to array indices. Therefore, nothing prevents index() returning a map record key.

This request works fine in sleep mode:

 SELECT p FROM Person p, in (p.phoneNumbers) number WHERE number = '867-5309' AND index(number) = 'home' 

Answer 2

It should work:

 SELECT p FROM Person p WHERE (select count(*) from p.phoneNumbers where name='home' and value='867-5309') > 0 

The following query works for me:

 select model from AnsOutboxMsg model left join fetch model.updateEntity upde left join fetch upde.update upd left join fetch model.ansMessage msg left join fetch msg.template msgt left join fetch msg.ansAction act left join fetch act.ansRule rl left join fetch rl.app app where (select count(*) from model.contextMap where name = 'fltClient' and value = 'XXX') > 0 and model.status = 'sent' and app.id = 'SPN_TICKETS' and msgt.name = 'Client' order by model.modifDate DESC, model.id ASC 

Answer 3

The JPA (2) specification does not define its syntax for using a map in MEMBER OF (you originally referenced arrays, but I don’t see relevance if you have a map), therefore, you cannot rely on any syntax valid for all JPA implementations. Since JPQL does not support Java methods such as keySet values, I don’t see that they are likely. Most likely, this will support validation of the value, for example

 '867-5309' MEMBER OF p.phoneNumbers 

For reference, in JDOQL, you would do

 p.phoneNumbers.containsKey('home'); p.phoneNumbers.containsValue('867-5309');