More than 1000 values in IN clause with Blaze Persistence

Hibernate ORM
1

Hi,

If there are lots of elements, let’s say more than 1000, in a query with IN clause with Blaze Persistence, and the DB is Oracle, for instance (or MySQL or SQL Server) :

  1. Will Blaze Persistence automatically create something like this :
 AND codes IN (...1000 codes...)
       OR  codes IN (...200 codes...)

to avoid the runtime exception that may occur with Oracle, if putting 1200 elements in one single IN clause ?

  1. If the answer is yes : what about the impact on performance (of having more than 1000 entries in IN clause(s)) ?
  2. If the answer is no or in case of bad performance : what could be done ? Create a temporary table and use a JOIN statement ? Or something else (simpler) ?

Thanks

2

First of all, Blaze-Persistence just delegates to Hibernate ORM for the SQL rendering of such constructs, so it would have to be Hibernate ORM to take care of this. In fact, Hibernate ORM knows about this limit and will split the predicate into two or more IN predicates.
I don’t know how the performance is going to be, but you could also use fromValues() to put the parameters into a values clause against which you can join.
Ultimately, it’s you who has to know if you could compute this list somehow as subquery maybe instead. Usually, that will perform better as there are fewer round trips between application and database server.