Hi.
I'm using OpenJPA 1.2.1, and have the following entity:
@Entity
public class User {
...
@PersistentCollection(elementCascade = CascadeType.ALL)
@ContainerTable(name = "users_roles", joinColumns = @XJoinColumn(name =
"user_id"))
@ElementColumn(name = "role")
private Set<Role> roles = new
HashSet<Role>();
...
}
I can use:
select u
from User u
where :role member of u.roles
However, I need to test several roles, and I'd like to do this:
select u
from User u
where exists (
select r
from u.roles r
where r in (:role1, :role2, :roleN)
)
But I have an error:
org.apache.openjpa.persistence.ArgumentException: Encountered "exists ( select
r from User u2 inner join u2 . roles r where u2 = u and r in" at character 7,
but expected: ["(", ")", "+", ",", "-", ".", ":", "<>", "=", "?", "ABS", "ALL",
"AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT",
"CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC",
"DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING",
"IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE",
"LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT", "OF",
"OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING",
"SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE", , , , , ].
This seems to be a bug, because the following query runs (but is useless, since
there's no role filter)
select u
from User u
where exists (
select u2
from User u2 inner join u2.roles r
where u2 = u
)
And the following query returns exactly the same error above (note the only
change to the previous query was the IN operator:
select u
from User u
where exists (
select u2
from User u2 inner join u2.roles r
where u2 = u
and r in (:role1, :role2, :roleN)
)
Does someone know some way I could do this without several MEMBER OF tests
(each generating a subquery)?
For this specific case, an user shouldn't have more than 3 roles, but other
cases I have with @PersistentCollection, elements may be filtered using several
values...
Any luck?
Luis Fernando Planella Gonzalez
