Is it possible to run a "MEMBER OF" query against associative arrays? If so, what does the syntax look like? The obvious workaround is a native query but that gets pretty messy what with all the joins and such. I'd like to test for existence of an object within the map's key set, value collection or entry 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
Provider-agnostic code might be too much to ask for; does Eclipselink support this?
JPQL has a function named index()
which is useful for getting the index in an @OrderColumn
list. And as you said yourself, maps are also called associative arrays and map keys correspond to array indices. So nothing prevents index()
from returning the key of a map entry.
This query works perfectly on hibernate:
SELECT p FROM Person p, in (p.phoneNumbers) number
WHERE number = '867-5309' AND index(number) = 'home'