如何注入列表参数作为文本数组传入 Spring Data JPA 中的非 IN 本机查询？

关于JPA参数注入，我真的不明白@Query：我希望有任何List<String>成为 SQL 中的{...,...}::TEXT( 或::VARCHAR) ，因为这是在 SQL 中表示字符串列表的明显方式。

@Query(
    """
    SELECT ... FROM ...
    WHERE sql_function(:myParam)
                       ^^^ I expect that to be injected as '{a,b,c,etc}'
    """,
    nativeQuery = true,
)
fun findAll(myParam: List<String>): List<...>

手动查询检查

jsonb_contains_any(jsonb_data jsonb, collection_path text[], collection_key text, search_values text[])是我编写的一个自定义PostgreSQL函数。它经过严格的测试，当我手动运行它时，它可以按预期工作：

SELECT * FROM logbook_reports
WHERE log_type = 'PNO' AND jsonb_contains_any(value, '{catchOnboard}', 'species', NULL);
SELECT * FROM logbook_reports
WHERE log_type = 'PNO' AND jsonb_contains_any(value, '{catchOnboard}', 'species', '{}');
SELECT * FROM logbook_reports
WHERE log_type = 'PNO' AND jsonb_contains_any(value, '{catchOnboard}', 'species', '{HKE}');

Successfully run. Total query runtime: 258 msec.
1 rows affected.

第一次尝试

首先，我希望这能开箱即用：

@Query(
    """
    SELECT *
    FROM logbook_reports
    WHERE log_type = 'PNO'
      AND jsonb_contains_any(value, '{catchOnboard}', 'species', (:specyCodes))
    """,
    nativeQuery = true,
)
fun findAll(specyCodes: List<String>): List<LogbookReportEntity>

后请求specyCodes=['HKE']，我收到这个错误：

24-04-11 19:39:58.973 DEBUG o.s.w.f.CommonsRequestLoggingFilter      : After request [GET /bff/v1/prior_notifications?specyCodes=HKE]
24-04-11 19:39:58.974 ERROR o.a.j.l.DirectJDKLog                     : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [
        SELECT *
        FROM logbook_reports
        WHERE log_type = 'PNO' AND jsonb_contains_any(value, '{catchOnboard}', 'species', (?))
        ] [ERROR: function jsonb_contains_any(jsonb, unknown, unknown, character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 83] [n/a]; SQL [n/a]] with root cause

你会注意到它甚至不“理解”这个'species'显然是一种TEXT类型。还能是什么？我想知道它如何能够迷失 Postgre 本身理解的东西（手动查询）？

第二次尝试

然后我尝试强制转换所有参数：

@Query(
    """
    SELECT *
    FROM logbook_reports
    WHERE log_type = 'PNO'
      AND jsonb_contains_any(value, ARRAY['catchOnboard'], CAST('species' AS TEXT), CAST(:specyCodes AS TEXT[]))
    """,
    nativeQuery = true,
)
fun findAll(specyCodes: List<String>): List<LogbookReportEntity>

相同的请求（specyCodes=['HKE']）：

24-04-11 19:48:02.601 DEBUG o.s.w.f.CommonsRequestLoggingFilter      : After request [GET /bff/v1/prior_notifications?specyCodes=HKE]
24-04-11 19:48:02.605 ERROR o.a.j.l.DirectJDKLog                     : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException: JDBC exception executing SQL [
        SELECT *
        FROM logbook_reports
        WHERE jsonb_contains_any(value, ARRAY['catchOnboard'], CAST('species' AS TEXT), CAST((?) AS TEXT[]))
        ] [ERROR: malformed array literal: "HKE"
  Detail: Array value must start with "{" or dimension information.] [n/a]; SQL [n/a]] with root cause

'HKE'它就像一个简单的字符串一样投入......