Hibernate Array Types 
User types for binding IN lists to arrays in Hibernate 5.
<dependency>
<groupId>com.github.marschall</groupId>
<artifactId>hibernate-array-types</artifactId>
<version>1.0.1</version>
</dependency>Versions 2.x are for Hibernate 6.
Advantages
- reduces the number of hard parses
- improves the effectives of cursor sharing
- works well together with client side statement caching to reduce the number of soft parses
- supports more than 1000 parameters in IN lists in Oracle
Disadvantages
- requires native queries with non-portable syntax
- only a small number of databases are supported
- works only with Hibernate, not other JPA providers
- the API for Hibernate 6 is quite different
H2 / PostgreSQL
entityManager.createNativeQuery("""
SELECT u.*
FROM user_table u
WHERE u.id = ANY(:userids)
ORDER BY u.id
""", User.class)
// you can leave out the "INTEGER" parameter and let ArrayType figure out the actual type
.setParameter("userids", ArrayType.newParameter("INTEGER", 1, 3, 5, 7, 9))
.getResultList()Additionally PostgreSQL supports arrays of primitive types through the PgIntArrayType and PgLongArrayType types.
Oracle
entityManager.createNativeQuery("""
SELECT u.*
FROM user_table u
WHERE u.id = ANY(SELECT column_value FROM TABLE(:userids))
ORDER BY u.id
""", User.class)
.setParameter("userids", OracleIntArrayType.newParameter("USER_ID_TYPE", 1, 3, 5, 7, 9))
.getResultList();You'll have to create a type of the appropriate name, for example like this
CREATE OR REPLACE TYPE USER_ID_TYPE IS TABLE OF NUMBER;HSQLDB
entityManager.createNativeQuery("""
SELECT u.*
FROM user_table u
WHERE u.id IN(UNNEST(:userids))
ORDER BY u.id
""", User.class)
// you can leave out the "INTEGER" parameter and let ArrayType figure out the actual type
.setParameter("userids", ArrayType.newParameter("INTEGER", 1, 3, 5, 7, 9))
.getResultList();Tested Databases
- H2
- HSQLDB
- Oracle
- PostgreSQL
Limitations
- Works only with native queries.
- HSQLDB and Oracle require custom SQL syntax.
- Supporting Oracle requires different classes and the creation of custom database types.
- Primitive types are only supported on Oracle and PostgresSQL and require different classes.
- Does not work with the following databases as yet do not support SQL arrays:
- Derby
- Firebird
- MariaDB
- MySQL
- SQL Server
