Bind array param to native query

Question

I have table product_spec_entry with following columns:

• product_spec_id
• commodity_spec_id

for one product_spec_id may be several commodity_spec_id, for example:

|product_spec_id | commodity_spec_id|
|----------------|------------------|
|1683            |1681              |
|1692            |1693              |
|1692            |1681              |
|1692            |1687              |
|1692            |1864              |
|1860            |1681              |
|1868            |1681              |
|1868            |1864              |

I want get all product_spec_id that have all commodity_spec_id are passed as parameter.

I wrote next query:

SELECT ps.product_spec_id, commodities
FROM (
       SELECT
         product_spec_id,
         array_agg(commodity_spec_id) AS commodities
       FROM system.product_spec_entry
       GROUP BY product_spec_id) ps
WHERE Cast(ARRAY [1681, 1864] as BIGINT[]) <@ Cast(ps.commodities as BIGINT[]);

It's work fine, and return expected result:

product_spec_id = 1692, 1868

I try use this query for JPA native query:

String query = "SELECT ps.product_spec_id " +
                "FROM ( " +
                "       SELECT " +
                "         product_spec_id, " +
                "         array_agg(commodity_spec_id) AS commodities " +
                "       FROM system.product_spec_entry " +
                "       GROUP BY product_spec_id) ps " +
                "WHERE CAST(ARRAY[:commoditySpecIds] AS BIGINT[]) <@ CAST(ps.commodities AS BIGINT[])";
List<Long> commoditySpecsIds = commoditySpecs.stream().map(Spec::getId).collect(Collectors.toList());

List<BigInteger> productSpecIds = em.createNativeQuery(query).setParameter("commoditySpecIds", commoditySpecsIds)
                .getResultList();

It does not work because I get array of record (ARRAY[(1692, 1868)]) instead array of bigint (ARRAY[1692, 1868])

How I should bind array param to my query? May be I can use more simple query for it.

Answer 1

I'm exactly in the same situation. Hope @VladMihalcea can help us

Edit

I figure it out to do it with JPA. After reading the impementation of setParameter, i discovered something similar to UserType, the TypedParameterValue.

When you use

setParameter("commoditySpecIds", new TypedParameterValue(IntArrayType.INSTANCE, commoditySpecsIds))

Where IntArrayType.INSTANCE come from "hibernate-types" librairy provided by Vlad Mihalcea. Be carefull, the "commoditySpecsIds" must be an array, not a Collection.

Hope that helps