JPA原生查询如何传入ID列表，筛选数据库中不存在的ID

你当前的问题是VALUES(:ids)在原生SQL查询中不会自动将List拆分为多行记录，而是将整个列表当作单一值处理，因此只会识别第一个ID。以下是几种可行的解决方案：

方案1：PostgreSQL 专用写法（使用 unnest 函数） #

PostgreSQL 支持通过unnest函数将数组拆分为多行，Spring Data JPA 会自动将传入的List<String>转换为PostgreSQL数组：

@Query(value = "select id from unnest(array[:ids]) as v(id) except select id from employee",
       nativeQuery = true)
public List<String> filterNonExistingEmployeeIds(@Param("ids") List<String> ids);

方案2：JPQL 查询（无需原生SQL） #

改用JPQL可以让Spring Data自动处理列表参数，避免手动拼接SQL：

@Query("select id from String id where id in :ids and not exists (select e from Employee e where e.id = id)")
public List<String> filterNonExistingEmployeeIds(@Param("ids") List<String> ids);

如果employee表的id字段不存在null值，也可以用not in语法：

@Query("select id from String id where id in :ids and id not in (select e.id from Employee e)")
public List<String> filterNonExistingEmployeeIds(@Param("ids") List<String> ids);

方案3：原生SQL 手动拼接 VALUES 子句（通用型） #

通过SpEL表达式动态拼接VALUES子句，将列表中的每个ID转换为单独的行记录：

@Query(value = "select id from (VALUES #{#ids.stream().map(id -> '(''' + id + ''')').collect(java.util.stream.Collectors.joining(','))}) v(id) except select id from employee",
       nativeQuery = true)
public List<String> filterNonExistingEmployeeIds(@Param("ids") List<String> ids);

注意：此方式需确保传入的ID是可信数据，避免SQL注入风险；若ID包含特殊字符（如单引号），需要额外处理转义逻辑。

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