java - Spring Predicate JpaSpecificationExecutor IN (Select) 表达式

Question

我有以下查询返回一行：

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

以及以下Spring 谓词：

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

在我的谓词中，我想包含IN (SELECT)语句：

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

这是我需要添加到谓词中的验证，以便它正确返回我需要的行。

标签： javaspringjpaspring-data-jpacriteria

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