Search code examples
javasqlspring-data-jpafirebirdjaybird

SQL Selecting from very long list of values

I need to select based on a big list:

SELECT ... FROM tb WHERE tb.cl IN (?, ?, ?, ..................many)

I can't do it this way because the driver implementation (Jaybird) limits the query to 1500 parameters max, also I can't select/load all and filter inside the app because there's not enough memory.

What should I do?

Extra info

I'm using Spring Boot with Hibernate and JPA Repositories, so if could be done in JPQL or using some other environment-related technique it would fit nicer.

I will actually use a ...WHERE tb.cl NOT IN..., if it makes any difference.
It's kinda like an EDI, I'm connecting two distinct databases, so using a nested select isn't an option.

Solution

  • One alternative is to use a temporary table and do as many inserts as you need into that table. After that you can use a:

    Select .. from tb where tb.cl in (select cl from myTempTable)

    and then do a truncate on the temporary table or a drop. As far as I know, all the leading relational databases set a limit in the IN clause if you are using parameters. On the other hand, using a temp table will do the trick.