sql - Postgres Spring Data JPA 传递列表作为值

Question

我有一张 table ，上面有 name对我很重要的专栏。

insert into tab (name) values ('a');
insert into tab (name) values ('b');
insert into tab (name) values ('c');
insert into tab (name) values ('d');

从由 Hibernate 5 支持的 Spring Data Repository 中，我想传递一个名称列表，并从传入的内容中返回表中不存在的名称。所以对于列表 ('a', 'b', 'c', 'baz')它应该返回一个值为 baz 的单行.
如果手工编写，以下查询将适用于这种情况。

WITH list(name) AS (VALUES ('a'), ('b'), ('c'), ('baz'))
SELECT name
FROM list
    EXCEPT
SELECT name
FROM tab
WHERE name IN (SELECT name FROM list)

所以我将以下方法添加到我的 JpaRepository。

@Query(value = "WITH list(name) "
    + "    AS (VALUES :names) "
    + "SELECT name "
    + "FROM list "
    + "    EXCEPT "
    + "SELECT name "
    + "FROM tab "
    + "WHERE name IN (SELECT name FROM list)", nativeQuery = true)
List<String> findNamessMissingFromGivenList(Collection<String> names);

但是，Spring Data 传入 :name 的方式收藏是通过放置 ('a', 'b', 'c', 'baz')而不是 ('a'), ('b'), ('c'), ('baz') .所以集合非常适合 IN子句，但不是这种情况。基本上我需要传入列表并将它们变成一个伪表，如 VALUES做。
我不希望手动构建任何查询并避免 SQL 注入(inject)。有没有办法修改此查询以接受来自 JPA 的集合？

Answer 1

不幸的是 VALUES :names不会产生 VALUES (?),(?),(?),(?)您需要，但将拥有 VALUES (?,?,?,?) .您需要一个返回一组所需类型的函数。要获取数据库版本的列表，可以使用以下查询:

SELECT 
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types"
FROM pg_catalog.pg_proc p
WHERE pg_catalog.pg_get_function_result(p.oid) LIKE 'SETOF %';

由于您寻求的是文本类型，因此可行的功能(至少在 postgres 9.5 中)是:

unnest(anyarray) 返回任何元素的集合

regexp_split_to_table(target text,pattern text, flags text) 返回文本集

json_object_keys(json) 返回文本集

json_object_keys(jsonb) 返回文本集

第一个选项需要支持将数组作为参数传递。您可以通过添加 com.mopano:hibernate-array-contributor 来做到这一点。库添加到您的项目中，或将字符串编译到 postgresql array-as-string syntax并使用类型转换，从而使您的子查询:

WITH list(name) AS (select unnest(cast('{a,b,c,baz}' as text array)))

避免使用 ::text[] 进行类型转换语法，因为 Hibernate 会将其解析为输入参数并用问号替换它。有些人尝试使用数组构造函数，如 ARRAY[ :values ] ，但这仅在您将每个值直接输入到查询中时才有效，而不是通过 JDBC 绑定(bind)它们。
第二个要求您首先将参数编译成一个字符串，其中任何一个都不存在的分隔符，然后将该分隔符用作正则表达式，第三个参数必须是 'g'以确保它 split 所有，而不是第一场比赛。虽然这很困难且容易出错，但无需额外的库即可完成。
第三个选项是编译一个 json 对象，其中你的值是键，你可能还需要一个额外的库来支持 json 参数。我已经发布了其中的两个，但对于可以通过更简单的方式实现的事情来说，这是一个很大的解决方法。
最后，无论您选择哪一个，查询中的 where 条件也是向后的。最终结果(假设您使用 array-as-string 选项并且没有外部库)应该如下所示(在本地测试):

WITH list(name) AS (select unnest(cast('{a,b,c,baz}' as text array)))
SELECT name
FROM list WHERE name NOT IN (SELECT name FROM tab);
 name 
------
 baz
(1 row)