Spring JPA中Set<UUID>转UUID数组报错，PostgreSQL下如何解决？

问题原因 #

Hibernate会把Set<UUID>类型的参数解析成(?, ?, ?)这种记录（record）形式，而非PostgreSQL期望的UUID数组，导致cast((:ids) AS UUID[])执行时触发cannot cast type record to uuid[]错误。

解决方案1：使用PostgreSQL数组构造函数修正查询 #

修改原生查询语句，用array[:ids]替代cast((:ids) AS UUID[])，让PostgreSQL正确识别数组参数：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Set;
import java.util.UUID;

// ...

@Query(value =
        "SELECT COUNT(1) = 0 " +
                "FROM unnest(array[:ids]::UUID[]) AS checked_id " +
                "WHERE checked_id NOT IN (" +
                "   SELECT DISTINCT(id) " +
                "   FROM my_table" +
                "   WHERE id IN (:ids)" +
                ")",
        nativeQuery = true)
boolean allIdsPresented(@Param("ids") Set<UUID> ids);

如果参数类型能被PostgreSQL自动推断，也可以简化成：

@Query(value =
        "SELECT COUNT(1) = 0 " +
                "FROM unnest(array[:ids]) AS checked_id " +
                "WHERE checked_id NOT IN (" +
                "   SELECT DISTINCT(id) " +
                "   FROM my_table" +
                "   WHERE id IN (:ids)" +
                ")",
        nativeQuery = true)
boolean allIdsPresented(@Param("ids") Set<UUID> ids);

解决方案2：换一种查询逻辑，避免数组转换 #

直接对比传入ID的总数和数据库中匹配到的ID数量，逻辑更简洁：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Set;
import java.util.UUID;

// ...

@Query(value =
        "SELECT COUNT(DISTINCT(id)) = :idCount " +
                "FROM my_table " +
                "WHERE id IN (:ids)",
        nativeQuery = true)
boolean allIdsPresented(@Param("ids") Set<UUID> ids, @Param("idCount") long idCount);

调用时传入ids.size()作为idCount参数，若数据库匹配数等于传入总数，说明所有ID都存在。

解决方案3：显式指定参数为UUID数组类型 #

通过Hibernate的类型转换器，强制参数以UUID数组形式传递：

import org.hibernate.type.UUIDArrayType;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.UUID;

// ...

@Query(value =
        "SELECT COUNT(1) = 0 " +
                "FROM unnest(:ids) AS checked_id " +
                "WHERE checked_id NOT IN (" +
                "   SELECT DISTINCT(id) " +
                "   FROM my_table" +
                "   WHERE id IN (:ids)" +
                ")",
        nativeQuery = true)
boolean allIdsPresented(@Param("ids") @Type(type = "uuid-array") UUID[] ids);

调用时把Set<UUID>转为数组：allIdsPresented(ids.toArray(new UUID[0]))。

内容的提问来源于stack exchange，提问作者Oleg Picik