Check existence of multiple IDs with Spring Data JPA-CodePudding

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(trhing) = 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?

CodePudding user response：

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