SQL arrays for INLIST bind parameters

Hibernate ORM
1

I am looking into using SQL arrays for INLIST bind parameters. Mostly for Oracle but also for other databases.

The current approach of creating a bind parameter for every inlist element has many downsides, especially on Oracle:

  • It causes a lot of hard parses, in clause parameter padding helps a little but you still several sql_ids and cursors. Besides the additional resource usage it can make monitoring and analysis by DBAs harder.
  • INLIST are limited to 1000 parameters.

SQL arrays would solve this (on databases that support them).

I started experimenting with a custom UserType in Hibernate 5. While it works it has several disadvantages:

  • It only works with native queries and dialect specific SQL.
  • The code does not port to Hibernate 6.

Then I started looking at Hibernate 6. I am happy that Hibernate 6 has some built in support for arrays. However it seems limited to model attributes, bind parameters seem unsupported for now, which seems unfortunate. I managed to get something working, however it has several downsides:

  • I’m not sure this is the correct way to use the API. It is basically trial and error until the tests are green.
  • It is still limited to native queries. It would be really great if JPQL, Criteria API an HQL could be supported, however for this dialect specific SQL would have to be generated.
  • The current Hibernate code relies a bit much on array copies. All reference array types can be cast to Object, there is no need for a copy. In addition it does not seem to be able to handle arrays of primitive types. PGJDBC and OJDBC have API extensions that support binding arrays of primitive types, it would be great of this could be supported out of the box. Right now I had to implement custom ArrayTypes and ValueBinders for this.
2

Hi there. Implementing this efficiently requires some small changes in Hibernate Core. I didn’t look too deeply into it yet, but my guess is that we either need a custom BaseSqmToSqlAstConverter for dialects that support array types, or a generic implementation in BaseSqmToSqlAstConverter itself. We’d have to change how the parameter list is translated in BaseSqmToSqlAstConverter#processInSingleParameter by creating a single JdbcParameter with the array type instead of individual JdbcParameter objects per parameter list element.
The respective SqlAstTranslator implementations of the dialects would then have to translate this to the appropriate SQL.

If you are interested about implementing this in Hibernate, join us on Zulip where we can discuss this in more detail.