2

I'm trying to create a query with a IN clause that maps a table with other that have a composite key. For reference the first table maps a composite key (three columns) to OtherTable (contains a OtherTableId object).

Example:

select t1
from Table t1
where t1.otherTable in :listOfOtherTables;

...
List<OtherTable> listOfOtherTables= Arrays.asList(new OtherTable(...), ...);
query.setParameter("listOfOtherTables", listOfOtherTables);

I searched how to do this and is pretty straightforward. In fact I use this with strings with success like this:

select t1
from Table t1
where t1.states in :listOfStates;

...
List<String> listOfStates = Arrays.asList("A", "B");
query.setParameter("listOfStates", listOfStates);

When I run the first example (by the way the project is in Spring 4.2 + Weblogic 12.1.2), the transformation for the query to be executed in Oracle is this:

select t1
from Table t1
where (NULL, NULL NULL) IN (("value1", "value2", "value3"), ("value1", "value2", "value3), ...);

Where is supposed to be the column names, appears NULL.

Anyone can help me?

PS: I have tried this also:

select t1
from Table t1
where t1.otherTable in (:otherTableObject1, :otherTableObject2, otherTableObject3);

Also doesn't work.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
voliveira89
  • 1,134
  • 2
  • 9
  • 22
  • Not all databases support this type of statement, and I don't believe it is supported in EclipseLink. See the answers here http://stackoverflow.com/questions/36801085/jpa-query-with-composite-values-in-where-clause – Chris May 19 '16 at 15:48
  • I believe that I have to make a native query to do what I want. I think it's weird JPA not support it even that not all databases support this type of statement. It would be nice throwing error instead putting null in column names. – voliveira89 May 19 '16 at 16:24
  • the answer shows someone attempted to add support for EclipseLink - you might give it a shot and request it be added officially. – Chris May 19 '16 at 16:37

0 Answers0