如何在<UUID>春季 Jpa 中转换为 UUID 数组

Question

我需要一个方法来检查数据库中是否显示了集合中的所有id，所以我编写了这个查询，但是我在将集合转换为数组时遇到了问题。我用Postgres

我的方法:

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

ERROR: cannot cast type record to uuid[]
Hibernate converts it as: SELECT COUNT(1) = 0 FROM unnest(cast((?, ?, ?) AS UUID[])) ....

Answer 1

如何将id作为字符串格式的数组常量发送，即

'{00000000-0000-0014-0000-000000000001, 00000000-0000-0014-0000-000000000002}'

可以用

@Query(value =
"SELECT COUNT(1) = 0 " +
"FROM unnest(cast(:ids AS UUID[])) AS checked_id " +
"WHERE checked_id NOT IN (" +
"   SELECT DISTINCT(id) " +
"   FROM my_table" +
"   WHERE id IN (:idset)" +
")",
nativeQuery = true)
boolean allIdsPresented(String ids, Set<UUID> idset);
default boolean allIdsPresented(Set<UUID> ids) {
return allIdsPresented( "{"+ids.stream().map(UUID::toString).collect(Collectors.joining(",")) + "}", ids);
}