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]
I see two possible ways:
= 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();
<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.