How to fix org.postgresql.util.PSQLException: ERROR: op ANY/ALL (array) requires array on right side

Question

I have an SQL query

select t.id as id, t.color as color from test_data t where t.id = ANY(?1) and t.color=?2

how can I pass an array of values to ANY(?1)

i.e

em.createNamedQuery("Test.getTestData", Tuple.class)
   .setParameter(1, arrayOfIds<----___can_I_pass_an_array___?____)
   .setParameter(2, yellow)
   .unwrap(NativeQuery.class)
   .addScalar("id", LongType())
   .addScalar("color", new StringType())

I get an error

Caused by: org.postgresql.util.PSQLException: ERROR: op ANY/ALL (array) requires array on right side
      Position: 507
            at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.18.jar!/:42.2.18]

Answer 1

I see two possible ways:

1. You can transform = ANY(?1) statement to the in (?1). As it explained here, they have the same meaning. And then pass List<Long> instead of Long[]:

List<Long> ids = new ArrayList<>();
List<Object[]> result = em.createNativeQuery(
   "select id, color from test_data where id in (:ids)")
   .setParameter("ids", ids)
   .getResultList();

2. You can add the following dependency:

<dependency>
   <groupId>com.vladmihalcea</groupId>
   <artifactId>hibernate-types-52</artifactId>
   <version>2.10.1</version>
</dependency>

if you use hibernate 5.4, 5.3 or 5.2 and then rewrite your query in the following way:

import com.vladmihalcea.hibernate.type.array.LongArrayType;

Long[] ids = {1L, 3L};
List<Object[]> result = em.createNativeQuery(
   "select id, color from test_data where id = ANY(:ids)")
   .unwrap(org.hibernate.query.NativeQuery.class)
   .setParameter("ids", ids, LongArrayType.INSTANCE)
   .getResultList();

Instead of adding additional dependency you can also write your own hibernate custom basic type, but it can be quite problematically without appropriate experience.