How cast Set to UUID array in Spring Jpa

Question

I need a method that checks whether all ids from the set are presented in the DB, so I wrote this query, but I have a problem with casting a set to an array. I use Postgres.

My method:

@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[])) ....

Does this answer your question? [How to convert Set to String\[\]?](https://stackoverflow.com/questions/5982447/how-to-convert-setstring-to-string) — Hulk, Mar 30 '23 at 11:31
[This link](https://stackoverflow.com/questions/70944044/check-existence-of-multiple-ids-with-spring-data-jpa) might also help you achieve the desired result. — Calvin P., Mar 30 '23 at 12:56

Turo · Answer 1 · 2023-03-30T11:45:10.243

What works ist to send the ids as array constant in string format, i.e.

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

so what could work is

@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);
}

How cast Set to UUID array in Spring Jpa

1 Answers1