How to handle long IN conditions safely in Hibernate queries

I have an Hibernate query of the form:

select count(DISTINCT mo.name)
from com.myproject.MyObject as mo
where lower(mo.id) in ('aaaaaaaa-bbbb-cccc-dddd-eeeeeeeee', 'baaaaaaa-bbbb-cccc-dddd-eeeeeeeee', 'caaaaaaa-bbbb-cccc-dddd-eeeeeeeee')
order by mo.name asc

The list ('aaaaaaaa-bbbb-cccc-dddd-eeeeeeeee', 'baaaaaaa-bbbb-cccc-dddd-eeeeeeeee', 'caaaaaaa-bbbb-cccc-dddd-eeeeeeeee') is dynamic. I don't know in advance how long it will be. The source of the IDs in the list is the user input. Thus, I can't replace the list by an inner query.

Assuming the list is significantly long (e.g. ~10,000 items of ~30 characters each) - will it result in a runtime error, some sort of exception, or a performance issue?

If so, is there a way to avoid such a long query?

Thanks!

Solution

Generally, the database systems set a limit on the IN clause length. If your list length is greater than that limit, an error is raised.

You have multiple to options bypass this limitation. The two options that come to my mind are:

Break the IN unique clause in multiple IN clauses and then programmatically join the parts with OR. Then, for each IN subclause you put the corresponding part of the list as a parameter:
```
AND (value IN (:sublist1) OR value IN (:sublist2)...)
```
Insert the contents of the list in a table and build the IN clause with a subquery. That sort of temporary table could have two columns: a identifier that must be generated for each query you run and the column that stores the list values. Obviously, after the query is executed, you must clean the values of that table that you have inserted before the query.
```
AND value IN (SELECT value FROM tablewhereyouinsertthelistelements WHERE queryidentifier = :queryIdentifier)
```