1

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]
SternK
  • 11,649
  • 22
  • 32
  • 46
miroana
  • 494
  • 3
  • 22

1 Answers1

0

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();
  1. 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.

SternK
  • 11,649
  • 22
  • 32
  • 46
  • Thank you for your response, would it work if I used NamedNativeQuery and not a plain native query? – miroana Dec 13 '20 at 10:54
  • tried your second suggestion. Getting the same error Caused by: org.postgresql.util.PSQLException: ERROR: op ANY/ALL (array) requires array on right side – miroana Dec 13 '20 at 12:12
  • Sorry, after reviewing the changes it seams to be working as expected. Thank you. – miroana Dec 13 '20 at 12:21
  • And it also works with NamedNativeQuery (just tested) – miroana Dec 13 '20 at 12:26