Spring Predicate JpaSpecificationExecutor IN (Select) Expression

Question

I have the following Query which returns me one row:

SELECT * FROM activity_entity 
WHERE activity_id IN (SELECT activity_id FROM category_entity WHERE activity_type = 9999) 
AND activity_id = 'AA924EDC-7D55-2555-6569-7D54BDF4F71F' 
AND category_id = '45CF9A18-1718-482A-A1C6-CA77E23D29B1' 
AND internal_module_id = '311';

And the following Spring Predicate:

public List<ActivityEntity> getRepositoryDataPrincipalActivity(AccessRepVisitLogInDto inDto,AccessRepEntity accessRep) {
    List<ActivityEntity> activityL = activityRepository.findAll(new Specification<ActivityEntity>() {

        @Override
        public Predicate toPredicate(Root<ActivityEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            List<Predicate> predicates = new ArrayList<>();

            predicates.add(cb.equal(root.get("activityId"), accessRep.getActivityId()));
            predicates.add(cb.equal(root.get("categoryId"), inDto.getCategory_id()));
            predicates.add(cb.equal(root.get("internalModuleId"), inDto.getInternalModuleId()));

            return cb.and(predicates.toArray(new Predicate[0]));
        }
    });
    return activityL;
}

On my predicate I want to include the IN (SELECT) statement:

activity_id IN (SELECT activity_id FROM category_entity WHERE activity_type = 9999)

It is a validation that I need to add to my predicate so that it returns correctly the row that I need.

Answer 1

Subquery<Long> subquery = query.subquery(Long.class);
Root<EntityCategory> category = subquery.from(EntityCategory.class);

Predicate subPredicate = cb.equal(category.get("activityType"), 9999); 
subquery.select(category.get("activityId")).where(subPredicate);

predicates.add(root.get("id").in(subquery));