Say I have an @Entity
like
class C {
List<String> sequence;
}
and I want to query for instances of C
that exactly match a given list. (I am aware that this is order sensitive, and I'm ok with that.) I am using EclipseLink and query with JPQL.
I tried the obvious:
List<String> querySeq = ... // some list to be used in the where clause
em.createQuery("select c from C c where c.sequence = :qs", C.class)
.setParameter("qs", querySeq);
This fails when I try to execute the query with getResultList()
. The error is java.sql.SQLSyntaxErrorException: row column count mismatch
.
I also tried with join
:
select c from C c join c.sequence s where s in :qs
But this fails, too, saying The collection-valued path 'c.sequence' must resolve to an association field.
My interpretation of this is that join
does not work on a collection of primitives, but only on relations.
So my questions are:
What is the right way to do this?
Also, is there a way to do more complex operations on primitive collections, like querying for (set) intersections, unions or differences?
I couldn't find any solution or article where JPQL could be used to directly compare lists. But there is a mechanism where you can test if a value is MEMBER OF the collection.
The process is not elegant but with a bit of tweaking we could build a query similar to this (Couldn't test the code, but I think it depicts the idea and logically should work):
String query = "SELECT c FROM C c";
String checkValue = "";
for(int i = 0; i<queryString.size();i++){
checkValue = queryString.get(i);
if(i==queryString.size()-1){
query += " WHERE '" + checkValue + "'" + " MEMBER OF c.sequence";
}else{
query += " WHERE '" + checkValue + "'" + " MEMBER OF c.sequence AND";
}
}