IN clause with large list in OpenJpa causing too complex statement

I have to create a named query where I need to group my results by some fields and also using an IN clause to limit my results.

The it looks something like this

SELECT new MyDTO(e.objID) FROM Entity e WHERE e.objId IN (:listOfIDs) GROUP BY e.attr1, e.attr2

I'm using OpenJPA and IBM DB2. In some cases my List of IDs can be very large (>80.000 IDs) and then the generated SQL statement becomes too complex for DB2, because the final generated statement prints out all IDs, like this:

 SELECT new MyDTO(e.objID) FROM Entity e WHERE e.objId IN (1,2,3,4,5,6,7,...) GROUP BY e.attr1, e.attr2

Is there any good way to handle this kind of query? A possible Workaround would be to write the IDs in a temporary table and then using the IN clause on this table.

Solution

You should put all of the values in a table and rewrite the query as a join. This will not only solve your query problem, it should be more efficient as well.

declare global temporary table ids (
   objId int
) with replace on commit preserve rows;

--If this statement is too long, use a couple of insert statements.
insert into session.ids values
    (1,2,3,4,....);

select new mydto(e.objID) 
    from entity e 
    join session.ids i on 
       e.objId = i.objId 
group by e.attr1, e.attr2;