Spring EntityManager中UNNEST关联查询的错误解决求助

问题 #

我尝试通过Hibernate将输入数据视为表并执行关联查询，目标是接收两个等长的UUID字符串列表，将其视为含两列的表（UUID对顺序与输入列表一致），再与另一张表关联。查询代码如下：

var uuidsListOne = // list of Strings
var uuidsListTwo = // list of Strings

String query =
    """
    SELECT *
    FROM the_db_table AS some_table
        JOIN ROWS FROM (UNNEST(ARRAY[?1], ARRAY[?2]) AS (a varchar, b varchar)) 
        WITH ORDINALITY AS m(first_uuid, second_uuid, idx)
            ON cast(some_table.first_uuid AS uuid) = cast(m.first_uuid AS uuid) AND cast(some_table.second_uuid AS uuid) = cast(m.second_uuid AS uuid);
    """;

return entityManager.createNativeQuery(query)
        .setParameter(1, uuidsListOne)
        .setParameter(2, uuidsListTwo)
        .getResultList();

该查询在PostgreSQL 12.13控制台中可正常运行，但通过Spring EntityManager执行时失败。针对当前查询的报错为：

org.postgresql.util.PSQLException: ERROR: a column definition list is required for functions returning "record"

若将查询第二行改为JOIN ROWS FROM (UNNEST(ARRAY[?1], ARRAY[?2]) AS (a varchar, b varchar))，则会出现报错：

org.postgresql.util.PSQLException: ERROR: function unnest(record[], record[]) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

请问如何让该查询适配JPA？

解决方案 #

问题核心是JPA参数绑定未明确数组类型，导致PostgreSQL无法识别UNNEST的输入参数，以下是几种可行的适配方案：

方案1：显式指定数组类型并调整语法 #

修改查询语句，给参数加上明确的类型转换，同时调整WITH ORDINALITY的位置，确保JPA能正确解析：

String query =
    """
    SELECT *
    FROM the_db_table AS some_table
    JOIN ROWS FROM (
        UNNEST(CAST(?1 AS varchar[]), CAST(?2 AS varchar[]))
    ) AS m(first_uuid varchar, second_uuid varchar) WITH ORDINALITY
    ON some_table.first_uuid = m.first_uuid::uuid 
    AND some_table.second_uuid = m.second_uuid::uuid;
    """;

return entityManager.createNativeQuery(query)
        .setParameter(1, uuidsListOne.toArray(new String[0]))
        .setParameter(2, uuidsListTwo.toArray(new String[0]))
        .getResultList();

关键改动：

• 对?1、?2显式转换为varchar[]，让PostgreSQL识别数组类型
• 将WITH ORDINALITY移到别名定义后，符合JPA对原生查询的语法要求
• 用PostgreSQL原生的::uuid简化UUID转换
• 绑定参数时将列表转为字符串数组，保证参数类型正确

方案2：使用命名原生查询与结果集映射（规范JPA写法） #

如果需要将结果映射到实体类，可以定义@NamedNativeQuery和@SqlResultSetMapping：

1. 定义结果集映射（假设对应实体为TheDbTable）：

@SqlResultSetMapping(
    name = "TheDbTableMapping",
    entities = @EntityResult(entityClass = TheDbTable.class)
)

2. 在实体类上添加命名原生查询：

@NamedNativeQuery(
    name = "TheDbTable.joinUuids",
    query = """
        SELECT some_table.*
        FROM the_db_table AS some_table
        JOIN ROWS FROM (
            UNNEST(CAST(?1 AS varchar[]), CAST(?2 AS varchar[]))
        ) AS m(first_uuid varchar, second_uuid varchar) WITH ORDINALITY
        ON some_table.first_uuid = m.first_uuid::uuid 
        AND some_table.second_uuid = m.second_uuid::uuid;
        """,
    resultSetMapping = "TheDbTableMapping"
)

3. 调用查询：

return entityManager.createNamedQuery("TheDbTable.joinUuids", TheDbTable.class)
        .setParameter(1, uuidsListOne.toArray(new String[0]))
        .setParameter(2, uuidsListTwo.toArray(new String[0]))
        .getResultList();

方案3：利用Hibernate的array()扩展函数 #

如果项目依赖Hibernate，可以直接用其提供的array()函数自动处理列表到数组的转换：

String query =
    """
    SELECT *
    FROM the_db_table AS some_table
    JOIN ROWS FROM (
        UNNEST(array(?1), array(?2))
    ) AS m(first_uuid varchar, second_uuid varchar) WITH ORDINALITY
    ON some_table.first_uuid = m.first_uuid::uuid 
    AND some_table.second_uuid = m.second_uuid::uuid;
    """;

return entityManager.createNativeQuery(query)
        .setParameter(1, uuidsListOne)
        .setParameter(2, uuidsListTwo)
        .getResultList();

这个方案无需手动转换数组，Hibernate会自动将列表转为对应类型的数组传递给PostgreSQL。

内容的提问来源于stack exchange，提问作者Kent Carmine