java - 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

Leave out the array[...] from your SQL:

WHERE CAST(:commoditySpecIds AS BIGINT[])

and then pass the list of IDs as a string that looks like this:

"{1,2,3,4}"

The default toString() for Lists usually returns something like: "[1,2,3]", so you could do something like this:

String literal = commoditySpecsIds.toString();
literal = "{" + literal.substring(1,literal.length() - 1) + "};

and then pass that to your obfuscation layer:

setParameter("commoditySpecIds", literal)