I have JPA objects as described here: Syntax error QueryDSL JPA with contains in Set?
Now I'm trying to have several restrictions on Set tags in a single query:
Set<Tag> withTags = ...; Set<Tag> withoutTags = ...; q.where(license.tags.any().in(withTags)); q.where(license.tags.any().in(withoutTags).not());
When the request is executed, I get the following exception:
Exception [EclipseLink-8019] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.JPQLException Exception Description: Error compiling the query [select distinct license from License license where exists (select license_tags from Tag license_tags where license_tags member of license.tags and license_tags = ?1) and not exists (select license_tags from Tag license_tags where license_tags member of license.tags and license_tags = ?2)] multiple declaration of identification variable [license_tags], previously declared as [Tag license_tags].
I tried to insert as("withTags") into the query, but the places I can do it is after any() , which inserts the AS in JPQL in the wrong place regarding the duplication problem I'm trying to solve. And I can insert it after tags , but then I get SimpleExpression as a return, for which I cannot execute any() .
Any other thoughts on how to prevent this duplication of the identity variable?
In addition, the above statements only work if the given Set withTags / withoutTags contains only one value. If multiple values are present, the following exception is thrown:
Exception [EclipseLink-6075] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.QueryException Exception Description: Object comparisons can only use the equal() or notEqual() operators. Other comparisons must be done through query keys or direct attribute level comparisons. Expression: [Relation operator IN Base my.package.Tag Parameter 1] select distinct license from License license where exists (select license_tags from Tag license_tags where license_tags member of license.tags and license_tags in ?1) at org.eclipse.persistence.exceptions.QueryException.invalidOperatorForObjectComparison(QueryException.java:614) at org.eclipse.persistence.internal.expressions.RelationExpression.normalize(RelationExpression.java:393) at org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:226) at org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:218) at org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1306) at org.eclipse.persistence.internal.expressions.SubSelectExpression.normalizeSubSelect(SubSelectExpression.java:134) at org.eclipse.persistence.internal.expressions.ExpressionNormalizer.normalizeSubSelects(ExpressionNormalizer.java:93) at org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1379) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildNormalSelectStatement(ExpressionQueryMechanism.java:482) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareSelectAllRows(ExpressionQueryMechanism.java:1553) at org.eclipse.persistence.queries.ReadAllQuery.prepareSelectAllRows(ReadAllQuery.java:793) at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:734) at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:464) at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:732) at org.eclipse.persistence.queries.DatabaseQuery.prepareCall(DatabaseQuery.java:1577) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:240) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:173) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:125) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:109) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1326) at sun.reflect.GeneratedMethodAccessor552.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.jboss.weld.util.reflection.SecureReflections$13.work(SecureReflections.java:304) at org.jboss.weld.util.reflection.SecureReflectionAccess.run(SecureReflectionAccess.java:54) at org.jboss.weld.util.reflection.SecureReflectionAccess.runAsInvocation(SecureReflectionAccess.java:163) at org.jboss.weld.util.reflection.SecureReflections.invoke(SecureReflections.java:298) at org.jboss.weld.bean.proxy.ClientProxyMethodHandler.invoke(ClientProxyMethodHandler.java:113) at org.jboss.weld.util.CleanableMethodHandler.invoke(CleanableMethodHandler.java:43) at javax.persistence.EntityManager_$$_javassist_131.createQuery(EntityManager_$$_javassist_131.java) at com.mysema.query.jpa.impl.DefaultSessionHolder.createQuery(DefaultSessionHolder.java:35) at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:139) at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:108) at com.mysema.query.jpa.impl.AbstractJPAQuery.list(AbstractJPAQuery.java:276)
And with EclipseLink 2.4
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: The SQL datatype to be used for an instance of mypackage.Tag cannot be determined. Use 'setObject()' with an explizit type, to define it. Error Code: 0 Call: SELECT DISTINCT t0.ID, ...all the other properties... FROM LICENSE t0, WHERE ((NOT EXISTS (SELECT ? FROM LicenseTags t5, TAG t4, TAG t3 WHERE (((t3.ID = t4.ID) AND (t3.ID IN (?,?))) AND ((t5.License_ID = t0.ID) AND (t4.ID = t5.tags_ID))))))
Now I tried to get around this using the following QueryDSL syntax:
for (Tag tag : withTags) { q.where(license.tags.contains(tag)); } for (Tag tag : withoutTags) { q.where(license.tags.contains(tag).not()); }
The first part works like a charm, but the last does not return the expected results. Licenses with tags present in withoutTags are not excluded from the result set as they should be.
JPQL and SQL for the last statement are as follows:
select distinct license from License license where not ?1 member of license.tags SELECT DISTINCT t1.ID, ...all the other properties... FROM LicenseTags t2, LICENSE t1, TAG t0 WHERE (NOT (133170 = t0.ID) AND (t2.License_ID = t1.ID) AND (t0.ID = t2.tags_ID))
JPQL looks good to me, but SQL clearly fails if the license has more than one tag associated with it. So I think this is actually the case when the EclipseLink translation fails. I will see if this is a known bug for the version I'm using. This thesis is somewhat supported by the JPQL "NOT MEMBER OF" query using API criteria , although in this case the problem only occurs when using creteria api and not JPQL. This erroneous translation is still stored in EclipseLink 2.4 RC 2. Here is finally a workaround that makes it intended for the tagless part:
Collection<Integer> tagIds = new ArrayList<Integer>(); for (Tag tag : withoutTags) { tagIds.add(tag.getId()); } q.where(license.tags.any().id.in(tagIds).not());
Regards, Tilmann