Check existence of multiple IDs with Spring Data JPA

Question

Analogous to the built-in method existsById I'd like to hava an existsAllById method with Spring Data JPA. To achieve this, I added the following method to the repository interface.

@Query(
  "select case when (count(thing) = size(:ids)) then true else false end " +
  "from Thing thing " +
  "where thing.id in :ids")
boolean existsAllById(@Param("ids") Set<UUID> ids);

However, Hibernate seemingly doesn't like my usage of size and reports me this error:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unexpected expression ? found for collection function size [select case when (count(trhing) = size(:ids)) then true else false end from com.example.Thing resource where thing.id in :ids] at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:282) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:113) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:73) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162) at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:613) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725)

How can this query be fixed? What is the best way to check the existence of multiple IDs with Spring Data JPA?

Answer 1

You can count the amount of found items and then compare that to the size of your input.

Note, use a Set<> here to prevent duplicates that'd ruin the count.

Integer countAllByIdIn(Set<UUID> ids);

default boolean existsAllById(Set<UUID> ids) {
    return countAllByIdIn(ids).equals(ids.size());
} 

Alternatively you can do, the key word being existsAll:

Boolean existsAllByIdIn(Set<UUID> ids);