I'm trying to work out the most efficient way to build a query that can compare two collections for identical members.
Something like:
SELECT o FROM Object o WHERE o.someCollection CONTAINS MEMBER OF :someCollection
Is something like this possible in the JPA spec?
A possible efficient way is to use some sort of hash function for the collections and to retrieve candidate collections by identical hash code. Candidate collections should then be further filtered by checking for equality.
The hash code of every stored collection should be stored in an indexed field, possibly in a new entity or embeddable class that would wrap the collection with its hash code.
Sounds like you are suggesting a solution to matching the entire contents of a collection? if so, that's not what I was after. I just want to check if a collection contains an element that is also present in another collection.
Right now I just do this by iteration of the smaller sized collection retrieving results for matches that contain the element for each iteration until all have been checked. I just wasn't sure if there was something in the JPA that allowed me to put this into the query ie "select FROM Object o WHERE (Iterate through :collection) IN o.collection", I hope that explanation makes sense.
Right now I am just doing:
for(String s : collection) { "select FROM Object o WHERE :s IN o.collection" }
> Sounds like you are suggesting a solution to matching the entire contents of a collection?
Yes.
> if so, that's not what I was after. I just want to check if a collection contains an element that is also present in another collection.
This is easier and supported by JPA:
SELECT o FROM MyClass o JOIN o.someCollection e WHERE e MEMBER OF :someCollection
That worked great, thanks!