将数组参数绑定到本机查询 [英] Bind array param to native query

问题描述

我的表 product_spec_entry 包含以下列:

• product_spec_id
• commodity_spec_id

一个 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              |

我想获取所有具有 commodity_spec_id 的所有 product_spec_id 作为参数.

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

我写了下一个查询:

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

product_spec_id = 1692, 1868

我尝试将此查询用于JPA本机查询:

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

它不起作用，因为我得到了记录数组( ARRAY [(1692，1868)] )而不是bigint数组( ARRAY [1692，1868] )

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.

推荐答案

从您的SQL中删除 array [...] :

WHERE CAST(:commoditySpecIds AS BIGINT[])

，然后将ID列表作为一个字符串传递，如下所示:

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

"{1,2,3,4}"

列表的默认toString()通常返回以下内容:"[1,2,3]" ，因此您可以执行以下操作:

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)

这篇关于将数组参数绑定到本机查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！