Bind IN values in a single array type parameter

Whenever I use the IN clause (regardless of the Literal Handling mode) I can see that Hibernate sends every value from the collection as a separate parameter:

select as col_0_0_
Document document0_
( in (
? , ? , ?

Is there a way of reconfiguring Hibernate to use a single parameter that would be a dialect-specific Array JDBC type? The reason for this is that sending the same query couple of times with different number of IN values will cause the db engine to treat each such query as a new query and force it to generate new execution plan (true at least for SQL Server and Oracle).

This feature was not implemented because not all JDBC Drivers support ARRAY types.

However, you can achieve the same goal with IN parameter padding. Check out this article for more details.

Won’t this be a performance issue? The DB will have to check for all values regardless of the fact that there are duplicates in there… seems like a very ugly workaround.

Is there a way of calling the IN clause by the CriteriaBuilder::function? Or maybe to register a custom function in a custom Dialect?

And of course: shouldn’t this be implemented for the JDBC drivers that do support ARRAY type?

It’s not ugly at all and if you benchmark it, you’ll see it is very efficient.

For more details about using ANY, check out this Pull Request. It’s a prototype, but it requires way more work and I don’t think it’s worth doing it. If you want to enhance it and make it ready for integration, send us a Pull Request when it’s done.

Hi Vlad. Thanks. I am trying to use the padding, but there is no 5.2.18 version yet and the 5.3.7 version doesn’t seem to support it either. Which version should I use?

5.2.18 will be released this week, and 5.3.7 surely has it.

I am unable to enable it correctly. Either there is a bug or I am doing something incorrectly. Opened up JIRA Ticket with test case:

Your issue was due to using literals, not bind parameters. Check out the Jira issue for more details.

If you’re willing to test you can give hibernate-array-types a try.

You can track [HHH-12647] - Hibernate JIRA if you want to know the status of this optimization within Hibernate. We might implement this for 6.2