The best workaround is to use the IN with the object's id not the objects.
i.e.
"... and e.contractor.id IN (contrid1, contrid2, contrid3...)..."
If you wish to avoid the join you could either map the foreign key, or
define a TopLink QueryKey for the foreign key field.
If your object has a composite id this becomes much more difficult. You
would need to dynamically generate the JPQL to AND in an id comparison for
each object in the collection. Some databases (Oracle) allow for IN to be
used with arrays of values, i.e. (ID_A, ID_B) IN ((:id1A, :id1B), (:id2A,
:id2B), ...), however this is not supported by JPQL in the JPA spec, so you
would need to use a native query with SQL for this.
Please log a bug on Glassfish persistence for the invalid SQL that was
generated for your JPQL, it should either work, or throw a useful exception.
Also feel free to log an enhancement request for having this work, including
composite primary keys. If the bug/enhancement already exists please add
your vote to them.
Krzysztof Adamczyk wrote:
>
> Hi all,
>
> Is this a bug that I cannot use such a syntax:
> "... and e.contractor IN (contr1, contr2, contr3...)..."
> in EJBQL, or I need some workaround?
>
> The generated SQL for DB2 is missing the column name and looks like this:
> "... AND IN(...)..."
>
> contr1..3 are entities and I assign them to the query with
> Query.setParameter(String, Object).
>
> If I do the same with Strings, it works, for example
> " and e.shortName IN ('aa', 'bb', 'cc)... "
>
> Any help appreciated.
> Greetings,
> Chris
>
-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.oracle.com/technology/products/ias/toplink/ Oracle TopLink ,
http://www.eclipse.org/eclipselink/
EclipseLink , https://glassfish.dev.java.net/javaee5/persistence/ TopLink
Essentials
--
View this message in context: http://www.nabble.com/Cannot-use-entities-in-IN-%28WHERE%29-clause--tf4586886.html#a13115973
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.