Java 类javax.persistence.criteria.Subquery 实例源码

@Nonnull
@Override
public final Predicate exists(
        @Nonnull final Root<P> root,
        @Nonnull final CriteriaQuery<?> query,
        @Nonnull final CriteriaBuilder cb,
        @Nonnull final JpaSubQueryPredicate<S> predicate) {

    Objects.requireNonNull(root, "root must not be null");
    Objects.requireNonNull(query, "query must not be null");
    Objects.requireNonNull(cb, "cb must not be null");
    Objects.requireNonNull(predicate, "predicate must not be null");

    final Subquery<Integer> subQuery = query.subquery(Integer.class);
    final Root<S> subRoot = subQuery.from(attribute.getDeclaringType().getJavaType());

    return cb.exists(subQuery
            .select(cb.literal(1))
            .where(cb.and(
                    predicate.predicate(subRoot, cb),
                    cb.equal(getPathToParentRoot(subRoot), subQuery.correlate(root)))));
}

public Page<Article> findPage(ArticleCategory articleCategory, List<Tag> tags, Pageable pageable) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);
    Predicate restrictions = criteriaBuilder.conjunction();
    restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isPublication"), true));
    if (articleCategory != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.equal(root.get("articleCategory"), articleCategory), criteriaBuilder.like(root.get("articleCategory").<String> get("treePath"), "%" + ArticleCategory.TREE_PATH_SEPARATOR + articleCategory.getId() + ArticleCategory.TREE_PATH_SEPARATOR + "%")));
    }
    if (tags != null && !tags.isEmpty()) {
        Subquery<Article> subquery = criteriaQuery.subquery(Article.class);
        Root<Article> subqueryRoot = subquery.from(Article.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
    }
    criteriaQuery.where(restrictions);
    criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")));
    return super.findPage(criteriaQuery, pageable);
}

public static Specification<HarvestReport> hasEndOfHuntingReportAndFieldsSpeciesIsPermitSpecies(final Long fieldsId) {
    return (root, query, cb) -> {
        final Subquery<Integer> permitQuery = query.subquery(Integer.class);
        final Root<HarvestPermit> permitRoot = permitQuery.from(HarvestPermit.class);

        final ListJoin<HarvestPermit, HarvestPermitSpeciesAmount> speciesAmounts = permitRoot.join(HarvestPermit_.speciesAmounts);
        final Path<GameSpecies> speciesAmountsSpecies = speciesAmounts.get(HarvestPermitSpeciesAmount_.gameSpecies);

        final Subquery<Integer> fieldsQuery = query.subquery(Integer.class);
        final Root<HarvestReportFields> fieldsRoot = fieldsQuery.from(HarvestReportFields.class);

        final Predicate permitSpeciesEqualToFieldsSpecies = cb.and(
                cb.equal(fieldsRoot.get(HarvestReportFields_.id), fieldsId),
                cb.equal(fieldsRoot.get(HarvestReportFields_.species), speciesAmountsSpecies));
        final Predicate fieldsExists = cb.exists(fieldsQuery.select(cb.literal(1)).where(permitSpeciesEqualToFieldsSpecies));

        return cb.exists(permitQuery
                .select(cb.literal(1))
                .where(cb.and(
                        cb.equal(permitRoot.join(HarvestPermit_.endOfHuntingReport), permitQuery.correlate(root)),
                        fieldsExists))
        );
    };
}

@Nonnull
@Override
public final Predicate exists(
        @Nonnull final Root<P> root,
        @Nonnull final CriteriaQuery<?> query,
        @Nonnull final CriteriaBuilder cb,
        @Nonnull final JpaSubQueryPredicate<S> predicate) {

    Objects.requireNonNull(root, "root must not be null");
    Objects.requireNonNull(query, "query must not be null");
    Objects.requireNonNull(cb, "cb must not be null");
    Objects.requireNonNull(predicate, "predicate must not be null");

    final Subquery<Integer> subQuery = query.subquery(Integer.class);
    final Root<P> subRoot = subQuery.correlate(root);

    return cb.exists(subQuery
            .select(cb.literal(1))
            .where(predicate.predicate(join(subRoot), cb)));
}

public List<Article> findList(ArticleCategory articleCategory, List<Tag> tags, Integer count, List<Filter> filters, List<Order> orders) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);
    Predicate restrictions = criteriaBuilder.conjunction();
    restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isPublication"), true));
    if (articleCategory != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.equal(root.get("articleCategory"), articleCategory), criteriaBuilder.like(root.get("articleCategory").<String> get("treePath"), "%" + ArticleCategory.TREE_PATH_SEPARATOR + articleCategory.getId() + ArticleCategory.TREE_PATH_SEPARATOR + "%")));
    }
    if (tags != null && !tags.isEmpty()) {
        Subquery<Article> subquery = criteriaQuery.subquery(Article.class);
        Root<Article> subqueryRoot = subquery.from(Article.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
    }
    criteriaQuery.where(restrictions);
    criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")));
    return super.findList(criteriaQuery, null, count, filters, orders);
}

@Test
public void NorMalSubQuery() {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);
    Root<User> root = criteriaQuery.from(User.class);

    Subquery<User> subQuery = criteriaQuery.subquery(User.class);
    Root<User> subRoot = subQuery.from(User.class);

    subQuery.where(criteriaBuilder.equal(subRoot.get("userName"), "admin")).select(subRoot.get("userName"));//
    //criteriaQuery.where(criteriaBuilder.exists(subquery));
    criteriaQuery.where(criteriaBuilder.equal(root.get("passWord"), subQuery));
    //CriteriaQuery<User> passWord = criteriaQuery.select(root.get("passWord")).distinct(true);

    TypedQuery<User> query = entityManager.createQuery(criteriaQuery);
    query.getResultList().forEach(System.out::println);
}

private void existsInForeignBuilder(Subquery<Long> subquery, Root<RtRegistration> subroot, AbstractJPASearchFormDataStatementBuilder<?> foreignBuilder) {
  CriteriaBuilder b = JPA.getCriteriaBuilder();

  subquery.select(subroot.get(RtRegistration_.id).get(RtRegistrationKey_.registrationNr));
  Predicate entityExists = b.exists(
      subquery.where(
          b.and(
              b.equal(
                  subroot.get(RtRegistration_.id).get(RtRegistrationKey_.registrationNr),
                  registration.get(RtRegistration_.id).get(RtRegistrationKey_.registrationNr)
                  ),
              b.equal(
                  subroot.get(RtRegistration_.id).get(RtRegistrationKey_.clientNr),
                  registration.get(RtRegistration_.id).get(RtRegistrationKey_.clientNr)
                  ),
              foreignBuilder.getPredicate())
          )
      );
  addForeignBuilder(foreignBuilder, entityExists);
}

@Override
public Object[][] getRankingClassesTableData(Long rankingNr) throws ProcessingException {
  CriteriaBuilder b = JPA.getCriteriaBuilder();
  CriteriaQuery<Object[]> selectQuery = b.createQuery(Object[].class);
  Root<RtRankingEvent> rankingEvent = selectQuery.from(RtRankingEvent.class);
  Join<RtRankingEvent, RtEvent> joinEvent = rankingEvent.join(RtRankingEvent_.rtEvent, JoinType.INNER);
  Join<RtEvent, RtEventClass> joinEventClass = joinEvent.join(RtEvent_.rtEventClasses, JoinType.INNER);

  Subquery<Long> numberOfEventsSubselect = selectQuery.subquery(Long.class);
  Root<RtRankingEvent> subroot = numberOfEventsSubselect.from(RtRankingEvent.class);
  numberOfEventsSubselect.select(b.count(subroot.get(RtRankingEvent_.id).get(RtRankingEventKey_.rankingNr))).where(b.and(b.equal(subroot.get(RtRankingEvent_.id).get(RtRankingEventKey_.rankingNr), rankingNr), b.equal(subroot.get(RtRankingEvent_.id).get(RtRankingEventKey_.clientNr), ServerSession.get().getSessionClientNr())));

  selectQuery.select(b.array(joinEventClass.get(RtEventClass_.id).get(RtEventClassKey_.classUid), b.count(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.eventNr)))).where(b.and(b.equal(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.rankingNr), rankingNr), b.equal(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.clientNr), ServerSession.get().getSessionClientNr()))).groupBy(joinEventClass.get(RtEventClass_.id).get(RtEventClassKey_.classUid)).having(b.equal(b.count(rankingEvent.get(RtRankingEvent_.id).get(RtRankingEventKey_.eventNr)), numberOfEventsSubselect));

  return JPAUtility.convertList2Array(JPA.createQuery(selectQuery).getResultList());
}

/**
 * {@inheritDoc}
 */
public boolean visit(JpqlSubselect node, CriteriaHolder query) {
    try {
        Subquery<Object> subquery = query.createSubquery();
        node.jjtGetChild(1).visit(this, query);
        node.jjtGetChild(0).visit(this, query);
        List<Selection<?>> selections = query.<List<Selection<?>>>getCurrentValue();
        subquery.select((Expression<Object>)selections.iterator().next());
        query.setValue(subquery);
        for (int i = 2; i < node.jjtGetNumChildren(); i++) {
            node.jjtGetChild(i).visit(this, query);
        }
        query.setValue(subquery);
        return false;
    } finally {
        query.removeSubquery();
    }
}

@Test
public void NorMalSubQuery() {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);
    Root<User> root = criteriaQuery.from(User.class);

    Subquery<User> subQuery = criteriaQuery.subquery(User.class);
    Root<User> subRoot = subQuery.from(User.class);

    subQuery.where(criteriaBuilder.equal(subRoot.get("userName"), "admin")).select(subRoot.get("userName"));//
    //criteriaQuery.where(criteriaBuilder.exists(subquery));
    criteriaQuery.where(criteriaBuilder.equal(root.get("passWord"), subQuery));
    //CriteriaQuery<User> passWord = criteriaQuery.select(root.get("passWord")).distinct(true);

    TypedQuery<User> query = entityManager.createQuery(criteriaQuery);
    query.getResultList().forEach(System.out::println);
}

public List<Employee> findUsingSubQuery(String projectName) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> emp = c.from(Employee.class);
    c.select(emp);

    TypedQuery<Employee> query = em.createQuery(c);

    if (projectName != null) {
        Subquery<Employee> sq = c.subquery(Employee.class);
        Root<Project> project = sq.from(Project.class);
        Join<Project, Employee> sqEmp = project.join("employees");
        sq.select(sqEmp)
                // set parameter
                .where(cb.equal(project.get("name"), cb.parameter(String.class, "project")));
        query.setParameter("project", projectName);

    }
    return null;
}

/**
 * {@inheritDoc}
 */
@Override
protected void addExistsSubquery(TranslationContext criteria, String subQueryType,
        org.kuali.rice.core.api.criteria.Predicate subQueryPredicate) {
    try {
        Class<?> subQueryBaseClass = Class.forName(subQueryType);
        Subquery<?> subquery = criteria.query.subquery(subQueryBaseClass);
        TranslationContext subQueryJpaPredicate = createCriteriaForSubQuery(subQueryBaseClass, criteria);

        // If a subQueryPredicate is passed, this is a Rice Predicate object and must be translated
        // into JPA - so we add it to the list this way.
        if (subQueryPredicate != null) {
            addPredicate(subQueryPredicate, subQueryJpaPredicate);
        }

        subquery.where(subQueryJpaPredicate.predicates.toArray(new Predicate[0]));
        criteria.addExistsSubquery(subquery);
    } catch (ClassNotFoundException e) {
        throw new IllegalArgumentException(subQueryType + " can not be resolved to a class for JPA");
    }
}

@Override
public List<RuleBaseValues> search(String docTypeName, String ruleTemplateId, String ruleDescription, Collection<String> workgroupIds, String workflowId, Boolean delegateRule, Boolean activeInd, Map extensionValues, Collection actionRequestCodes) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<RuleBaseValues> cq = cb.createQuery(RuleBaseValues.class);
    Root<RuleBaseValues> root = cq.from(RuleBaseValues.class);
    List<javax.persistence.criteria.Predicate> predicates = getSearchCriteria(root,cq,docTypeName,
                    ruleTemplateId, ruleDescription, delegateRule, activeInd, extensionValues);
    Subquery<RuleResponsibilityBo> subquery = addResponsibilityCriteria(cq,workgroupIds, workflowId, actionRequestCodes,
                    (workflowId != null), ((workgroupIds != null) && !workgroupIds.isEmpty()));
    if (subquery != null){
        predicates.add(cb.in(root.get("id")).value(subquery));
    }
    javax.persistence.criteria.Predicate[] preds = predicates.toArray(new javax.persistence.criteria.Predicate[predicates.size()]);
    cq.where(preds);
    TypedQuery<RuleBaseValues> q = getEntityManager().createQuery(cq);

    return q.getResultList();
}

@Override
public List<RegistryOperation> getChanges(Date from, Date until) {
    CriteriaQuery<RegistryOperation> query = criteriaBuilder.createQuery(RegistryOperation.class);
    Root<RegistryOperation> root = query.from(clazz);
    Subquery<Long> subquery = query.subquery(Long.class);
    Root<RegistryOperation> subqueryRoot = subquery.from(clazz);
    subquery = subquery.select(criteriaBuilder.max(subqueryRoot.get(RegistryOperation_.id)));
    Predicate subqueryPredicate = criteriaBuilder.isTrue(subqueryRoot.get(RegistryOperation_.exposed));
    subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
        criteriaBuilder.lessThanOrEqualTo(subqueryRoot.get(RegistryOperation_.date), until));
    if (from != null) {
        subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
            criteriaBuilder.greaterThanOrEqualTo(subqueryRoot.get(RegistryOperation_.date), from));
    }
    subquery = subquery.where(subqueryPredicate);
    subquery = subquery.groupBy(subqueryRoot.get(RegistryOperation_.target).get(SemanticDescriptor_.id));
    query = query.where(root.get(RegistryOperation_.id).in(subquery));
    query.orderBy(criteriaBuilder.asc(root.get(RegistryOperation_.date)));
    return entityManager.createQuery(query).getResultList();
}

@Override
public Map<Long, Long> getSizeGroupByOwner() {
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<DataFile> root = query.from(clazz);
    Root<DigitalObject> objectRoot = query.from(DigitalObject.class);

    Subquery<DataFileVersion> subquery = query.subquery(DataFileVersion.class);
    Root<DataFileVersion> subqueryRoot = subquery.from(DataFileVersion.class);
    Join<DataFileVersion, ContentVersion> joinVersion = subqueryRoot.join(DataFileVersion_.contentVersion);
    Join<ContentVersion, DigitalObject> joinObject = joinVersion.join(ContentVersion_.object);
    subquery.where(criteriaBuilder.and(criteriaBuilder.equal(joinObject, objectRoot),
        criteriaBuilder.equal(root, subqueryRoot.get(DataFileVersion_.dataFile))));
    subquery.select(subqueryRoot);

    query.where(criteriaBuilder.exists(subquery));
    query.groupBy(objectRoot.get(DigitalObject_.ownerId));
    query.multiselect(objectRoot.get(DigitalObject_.ownerId), criteriaBuilder.sum(root.get(DataFile_.size)));

    Map<Long, Long> results = new HashMap<Long, Long>();
    for (Tuple tuple : entityManager.createQuery(query).getResultList()) {
        results.put((Long) tuple.get(0), (Long) tuple.get(1));
    }

    return results;
}

@Override
public List<Operation> getChanges(Date from, Date until, NamespaceType prefix, ObjectType set, int offset,
        int pageSize) {
    CriteriaQuery<Operation> query = criteriaBuilder.createQuery(Operation.class);
    Root<Operation> root = query.from(clazz);
    Subquery<Long> subquery = query.subquery(Long.class);
    Root<Operation> subqueryRoot = subquery.from(clazz);
    subquery = subquery.select(criteriaBuilder.max(subqueryRoot.get(Operation_.id)));
    Predicate subqueryPredicate = criteriaBuilder.greaterThanOrEqualTo(subqueryRoot.get(Operation_.date), from);
    subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
        criteriaBuilder.lessThanOrEqualTo(subqueryRoot.get(Operation_.date), until));
    subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
        criteriaBuilder.equal(subqueryRoot.get(Operation_.metadataType), prefix));
    if (set != null) {
        Join<Operation, DigitalObject> operationToObject = subqueryRoot.join(Operation_.object);
        subqueryPredicate = criteriaBuilder.and(subqueryPredicate,
            criteriaBuilder.equal(operationToObject.get(DigitalObject_.type), set));
    }
    subquery = subquery.where(subqueryPredicate);
    subquery = subquery.groupBy(subqueryRoot.get(Operation_.object).get(DigitalObject_.id));
    query = query.where(root.get(Operation_.id).in(subquery));
    query.orderBy(criteriaBuilder.asc(root.get(Operation_.id)));
    return entityManager.createQuery(query).setFirstResult(offset).setMaxResults(pageSize).getResultList();
}

@Override
public Map<Long, Long> getSizeGroupByOwner() {
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<T> root = query.from(clazz);
    Root<DigitalObject> objectRoot = query.from(DigitalObject.class);

    Subquery<T> subquery = query.subquery(clazz);
    Root<T> subqueryRoot = subquery.from(clazz);
    Join<?, ContentVersion> joinVersion = joinVersions(subqueryRoot);
    Join<ContentVersion, DigitalObject> joinObject = joinVersion.join(ContentVersion_.object);
    subquery.where(criteriaBuilder.and(criteriaBuilder.equal(joinObject, objectRoot),
        criteriaBuilder.equal(root, subqueryRoot)));
    subquery.select(subqueryRoot);

    query.where(criteriaBuilder.exists(subquery));
    query.groupBy(objectRoot.get(DigitalObject_.ownerId));
    query.multiselect(objectRoot.get(DigitalObject_.ownerId), criteriaBuilder.sum(root.get(MetadataFile_.size)));

    Map<Long, Long> results = new HashMap<Long, Long>();
    for (Tuple tuple : entityManager.createQuery(query).getResultList()) {
        results.put((Long) tuple.get(0), (Long) tuple.get(1));
    }

    return results;
}

@Override
public Identifier findNextActiveIdentifier(String previousIdentifierValue) {

    CriteriaQuery<Identifier> query = criteriaBuilder.createQuery(Identifier.class);
    Root<Identifier> root = query.from(Identifier.class);
    query.select(root);
    query.where(criteriaBuilder.notEqual(root.get(Identifier_.isActive), false));
    query.orderBy(criteriaBuilder.asc(root.get(Identifier_.id)));

    if (previousIdentifierValue != null) {
        Subquery<Long> subquery = query.subquery(Long.class);
        Root<Identifier> subqueryRoot = subquery.from(Identifier.class);
        subquery.select(subqueryRoot.get(Identifier_.id));
        subquery.where(criteriaBuilder.equal(subqueryRoot.get(Identifier_.identifier), previousIdentifierValue));
        query.where(criteriaBuilder.greaterThan(root.get(Identifier_.id), criteriaBuilder.any(subquery)));
    }

    List<Identifier> identifiers = entityManager.createQuery(query).setMaxResults(1).getResultList();
    if (identifiers != null && !identifiers.isEmpty()) {
        return identifiers.get(0);
    }

    return null;
}

public static Specification<Meeting> hasPartner(String searchTerm, Collection<Long> mepIds) {
    return new Specification<Meeting>() {
        @Override
        public Predicate toPredicate(Root<Meeting> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            query.distinct(true);
            String containsLikePattern = getContainsLikePattern(searchTerm);
            log.trace(searchTerm + "," + mepIds);
            Root<Meeting> meeting = root;
            Subquery<Partner> partnerSubQuery = query.subquery(Partner.class);
            Root<Partner> partner = partnerSubQuery.from(Partner.class);
            Expression<Collection<Meeting>> partnerMeetings = partner.get("meetings");
            partnerSubQuery.select(partner);
            partnerSubQuery.where(cb.like(partner.get("name"), containsLikePattern),
                    cb.isMember(meeting, partnerMeetings));
            return cb.exists(partnerSubQuery);
        }

    };

}

public static Specification<Meeting> hasUserName(String searchTerm, Collection<Long> mepIds) {
    return new Specification<Meeting>() {
        @Override
        public Predicate toPredicate(Root<Meeting> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            query.distinct(true);
            String containsLikePattern = getContainsLikePattern(searchTerm);
            log.trace(searchTerm + "," + mepIds);
            Root<Meeting> meeting = root;
            Subquery<User> userSubQuery = query.subquery(User.class);
            Root<User> user = userSubQuery.from(User.class);
            Expression<Collection<Meeting>> userMeetings = user.get("meetings");
            userSubQuery.select(user);
            userSubQuery.where(
                    cb.or(cb.like(user.get("firstName"), containsLikePattern),
                            cb.like(user.get("lastName"), containsLikePattern)),
                    cb.isMember(meeting, userMeetings));
            return cb.exists(userSubQuery);
        }
    };
}

@Override
@Cacheable(DaoSpringModuleConfig.HERD_CACHE_NAME)
public List<String> getUnrestrictedSecurityFunctions()
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<String> criteria = builder.createQuery(String.class);

    // The criteria root is the security function.
    Root<SecurityFunctionEntity> securityFunctionEntityRoot = criteria.from(SecurityFunctionEntity.class);

    // Build a subquery to eliminate security functions that are mapped to security roles.
    Subquery<SecurityFunctionEntity> subquery = criteria.subquery(SecurityFunctionEntity.class);
    Root<SecurityRoleFunctionEntity> subSecurityRoleFunctionEntityRoot = subquery.from(SecurityRoleFunctionEntity.class);
    subquery.select(subSecurityRoleFunctionEntityRoot.get(SecurityRoleFunctionEntity_.securityFunction))
        .where(builder.equal(subSecurityRoleFunctionEntityRoot.get(SecurityRoleFunctionEntity_.securityFunction), securityFunctionEntityRoot));

    // Get the security function code column.
    Path<String> functionCodeColumn = securityFunctionEntityRoot.get(SecurityFunctionEntity_.code);

    // Add the clauses for the query.
    criteria.select(functionCodeColumn).where(builder.not(builder.exists(subquery))).orderBy(builder.asc(functionCodeColumn));

    // Run the query to get a list of unrestricted security functions.
    return entityManager.createQuery(criteria).getResultList();
}

public List<Product> findForPurchaseCustomerPremium() {
    // main query on products
    CriteriaQuery<Product> query = em.getCriteriaBuilder().createQuery(Product.class);
    Root<Product> from = query.from(Product.class);

    // subquery on product ids
    Subquery<Long> subQuery = query.subquery(Long.class);
    Root<Customer> subFrom = subQuery.from(Customer.class);
    ListJoin<Customer, Purchase> joinPurchase = subFrom.join(Customer_.purchases);
    ListJoin<Purchase, Product> joinProduct = joinPurchase.join(Purchase_.products);
    // Explicitly add to SELECT clause; otherwise, throws Error Code: 30000 SQLSyntaxErrorException
    subQuery.select(joinProduct.get(Product_.id)).distinct(true);
    subQuery.where(em.getCriteriaBuilder().equal(subFrom.get(Customer_.premium), true));

    query.select(from);
    query.where(em.getCriteriaBuilder().in(from.get(Product_.id)).value(subQuery));
    return em.createQuery(query).getResultList();
}

public List<Product> findForPurchaseCustomerPremium() {
    // main query on products
    CriteriaQuery<Product> query = em.getCriteriaBuilder().createQuery(Product.class);
    Root<Product> from = query.from(Product.class);

    // subquery on product ids
    Subquery<Long> subQuery = query.subquery(Long.class);
    Root<Customer> subFrom = subQuery.from(Customer.class);
    Join<Customer, Purchase> joinPurchase = subFrom.join("purchases");
    Join<Purchase, Product> joinProduct = joinPurchase.join("products");
    // Explicitly add to SELECT clause; otherwise, throws Error Code: 30000 SQLSyntaxErrorException
    subQuery.select(joinProduct.get("id").as(Long.class)).distinct(true);
    subQuery.where(em.getCriteriaBuilder().equal(subFrom.get("premium"), true));

    query.select(from);
    query.where(em.getCriteriaBuilder().in(from.get("id")).value(subQuery));
    return em.createQuery(query).getResultList();
}

/**
 * subquery: in
 * SELECT e FROM jpa_query_employee e WHERE e IN (SELECT emp FROM jpa_query_project p JOIN p.employees pe WHERE p.name = :projectname)
 */
@Transactional
public void doWhere1(){
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> e = c.from(Employee.class);

    // subquery
    Subquery<Employee> sq = c.subquery(Employee.class);
    Root<Project> p = sq.from(Project.class);
    Join<Project, Employee> pe = p.join("employees");
    sq.select(pe)
    .where(cb.equal(p.get("name"), cb.parameter(String.class, "projectname")));

    //
    c.select(e)
        .where(cb.in(e).value(sq));
}

/**
 * subquery(equivalent to dowhere1) : exists
 * SELECT e FROM jpa_query_employee e WHERE EXISTS (SELECT p FROM e.projects p WHERE p.name = :projectname)
 */
@Transactional
public void doWhere2(){
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> e = c.from(Employee.class);

    // subquery
    Subquery<Project> sq = c.subquery(Project.class);
    // Root<Project> p = sq.from(Project.class);
    Root<Employee> se = sq.correlate(e);
    Join<Employee, Project> p = se.join("projects");
    sq.select(p)
        .where(cb.equal(p.get("name"), cb.parameter(String.class, "projectname")));

    //
    c.select(e)
        .where(cb.exists(sq));
}

@Override
public List<EntityTypeDO> getEntitiesNotDefinedForScope(Long scopeId) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<EntityTypeDO> cq = cb.createQuery(EntityTypeDO.class);
    Root<EntityTypeDO> root = cq.from(EntityTypeDO.class);

    Subquery<EntityTypeDO> subQuery = cq.subquery(EntityTypeDO.class);
    Root<EntityDO> fromEntity = subQuery.from(EntityDO.class);
    subQuery.select(fromEntity.get(EntityDO_.entityType));
    Predicate scope = cb.equal(fromEntity.get(EntityDO_.scope).get(ScopeDO_.scopeId), scopeId);
    subQuery.where(scope);

    cq.where(cb.not(cb.in(root).value(subQuery)));

    return em.createQuery(cq).setHint("org.hibernate.cacheable", Boolean.TRUE).getResultList();
}

/**
 * {@inheritDoc}
 */
@Override
protected void addExistsSubquery(TranslationContext criteria, String subQueryType,
        org.kuali.rice.core.api.criteria.Predicate subQueryPredicate) {
    try {
        Class<?> subQueryBaseClass = Class.forName(subQueryType);
        Subquery<?> subquery = criteria.query.subquery(subQueryBaseClass);
        TranslationContext subQueryJpaPredicate = createCriteriaForSubQuery(subQueryBaseClass, criteria);

        // If a subQueryPredicate is passed, this is a Rice Predicate object and must be translated
        // into JPA - so we add it to the list this way.
        if (subQueryPredicate != null) {
            addPredicate(subQueryPredicate, subQueryJpaPredicate);
        }

        subquery.where(subQueryJpaPredicate.predicates.toArray(new Predicate[0]));
        criteria.addExistsSubquery(subquery);
    } catch (ClassNotFoundException e) {
        throw new IllegalArgumentException(subQueryType + " can not be resolved to a class for JPA");
    }
}

@Override
protected void addNotExistsSubquery(TranslationContext criteria, String subQueryType,
        org.kuali.rice.core.api.criteria.Predicate subQueryPredicate) {

    try {
        Class<?> subQueryBaseClass = Class.forName(subQueryType);
        Subquery<?> subquery = criteria.query.subquery(subQueryBaseClass);
        TranslationContext subQueryJpaPredicate = createCriteriaForSubQuery(subQueryBaseClass, criteria);

        // If a subQueryPredicate is passed, this is a Rice Predicate object and must be translated
        // into JPA - so we add it to the list this way.
        if (subQueryPredicate != null) {
            addPredicate(subQueryPredicate, subQueryJpaPredicate);
        }

        subquery.where(subQueryJpaPredicate.predicates.toArray(new Predicate[0]));
        criteria.addNotExistsSubquery(subquery);

    } catch (ClassNotFoundException e) {
            throw new IllegalArgumentException(subQueryType + " can not be resolved to a class for JPA");
    }
}

@Override
public List<RuleBaseValues> search(String docTypeName, String ruleTemplateId, String ruleDescription, Collection<String> workgroupIds, String workflowId, Boolean delegateRule, Boolean activeInd, Map extensionValues, Collection actionRequestCodes) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<RuleBaseValues> cq = cb.createQuery(RuleBaseValues.class);
    Root<RuleBaseValues> root = cq.from(RuleBaseValues.class);
    List<javax.persistence.criteria.Predicate> predicates = getSearchCriteria(root,cq,docTypeName,
                    ruleTemplateId, ruleDescription, delegateRule, activeInd, extensionValues);
    Subquery<RuleResponsibilityBo> subquery = addResponsibilityCriteria(cq,workgroupIds, workflowId, actionRequestCodes,
                    (workflowId != null), ((workgroupIds != null) && !workgroupIds.isEmpty()));
    if (subquery != null){
        predicates.add(cb.in(root.get("id")).value(subquery));
    }
    javax.persistence.criteria.Predicate[] preds = predicates.toArray(new javax.persistence.criteria.Predicate[predicates.size()]);
    cq.where(preds);
    TypedQuery<RuleBaseValues> q = getEntityManager().createQuery(cq);

    return q.getResultList();
}

@Override
public List<Node> getListOfNodesForDatasetsWithStatus(DataSetStatus[] status){
    CriteriaBuilder builder = getCriteriaBuilder();

    CriteriaQuery<Node> query = builder.createQuery(Node.class);
    Root<Node> nodeR = query.from(Node.class);
    query.select(nodeR);

    Subquery<DataSet> subQuery = query.subquery(DataSet.class);
    Root<DataSet> datSet = subQuery.from(DataSet.class);
    subQuery.select(datSet);

    Predicate statusPredicate = datSet.get(DataSet_.status).in(status);
    Predicate correlatePredicate = builder.equal(datSet.get(DataSet_.node), nodeR);

    subQuery.where(builder.and(statusPredicate, correlatePredicate));

    query.where(builder.exists(subQuery));

    TypedQuery<Node> q = getEntityManager().createQuery(query);
    return q.getResultList();
}

/**
 * Returns a session for a key
 * 
 * @param key
 *            The key of the requested session
 * @throws NoResultException
 *             if no session with this key is stored
 * @return The persisted Session
 */
public List<SessionEntity> findBySessionKeyAndUsername(String key, String username) throws NoResultException {
    CriteriaBuilder criteriaBuilder = this.getCriteriaBuilder();
    CriteriaQuery<SessionEntity> criteriaQuery = criteriaBuilder.createQuery(SessionEntity.class);

    Root<SessionEntity> entityRoot = criteriaQuery.from(SessionEntity.class);
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(criteriaBuilder.equal(entityRoot.get(SessionEntity_.key), key));

    Subquery<String> subQuery = criteriaQuery.subquery(String.class);
    Root<BundesbruderEntity> entityRoot2 = subQuery.from(BundesbruderEntity.class);

    predicates.add(criteriaBuilder.equal(entityRoot2.get(BundesbruderEntity_.userName), username));
    criteriaQuery.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));

    TypedQuery<SessionEntity> typedQuery = this.createQuery(criteriaQuery);
    List<SessionEntity> searchResult = typedQuery.getResultList();
    return searchResult;
}

static Specification<Reservation> noVirtualServices() {
  return (root, query, cb) -> {
    Subquery<VirtualService> virtualServices = query.subquery(VirtualService.class);
    Root<VirtualService> servicesRoot = virtualServices.from(VirtualService.class);
    return cb.not(
      cb.exists(
        virtualServices
          .select(servicesRoot)
          .where(
            cb.equal(
              servicesRoot.get(VirtualService_.reservation).get(Reservation_.id),
              root.get(Reservation_.id)
            )
          )
      )
    );
  };
}

static Specification<Reservation> forManager(final RichUserDetails manager) {
  return (root, query, cb) -> {
    Long prgId = manager.getSelectedRole().getPhysicalResourceGroupId().get();

    Subquery<LocalVirtualPort> lvpQuery = query.subquery(LocalVirtualPort.class);
    Root<LocalVirtualPort> lvpRoot = lvpQuery.from(LocalVirtualPort.class);
    lvpQuery.select(lvpRoot).where(cb.equal(
        lvpRoot.get(LocalVirtualPort_.physicalPort)
            .get(UniPort_.physicalResourceGroup)
            .get(PhysicalResourceGroup_.id),
        prgId));
    return cb.or(
        root.get(Reservation_.sourcePort).get(ReservationEndPoint_.virtualPort).in(lvpQuery),
        root.get(Reservation_.destinationPort).get(ReservationEndPoint_.virtualPort).in(lvpQuery));
  };
}

private Predicate createStartControlWhere(Long startcontrolUid) {
  CriteriaBuilder b = JPA.getCriteriaBuilder();
  Subquery<Long> subquery = query.subquery(Long.class);
  Root<RtRaceControl> subroot = subquery.from(RtRaceControl.class);
  subquery.select(subroot.get(RtRaceControl_.controlNr)).where(b.and(b.equal(race.get(RtRace_.id).get(RtRaceKey_.raceNr), subroot.get(RtRaceControl_.raceNr)), b.equal(subroot.get(RtRaceControl_.controlNr), startcontrolUid)));
  Predicate exists = b.exists(subquery);
  return exists;
}

private void buildForeignRunnerBuilder(AbstractRunnerSearchBoxData searchFormData) throws ProcessingException {
  Subquery<Long> subquery = query.subquery(Long.class);
  Root<RtRegistration> subroot = subquery.from(RtRegistration.class);
  Join<RtRegistration, RtEntry> joinEntry = subroot.join(RtRegistration_.rtEntries, JoinType.LEFT);
  Join<RtEntry, RtRace> joinRace = joinEntry.join(RtEntry_.rtRaces, JoinType.LEFT);
  Join<RtRace, RtRunner> joinRunner = joinRace.join(RtRace_.rtRunner, JoinType.LEFT);

  JPARunnerBoxSearchFormDataStatementBuilder foreignBuilder = new JPARunnerBoxSearchFormDataStatementBuilder(joinRunner);
  foreignBuilder.build(searchFormData);

  existsInForeignBuilder(subquery, subroot, foreignBuilder);
}

private void buildForeignRunnerDetailBuilder(AbstractRunnerDetailsSearchBoxData searchFormData) throws ProcessingException {
  Subquery<Long> subquery = query.subquery(Long.class);
  Root<RtRegistration> subroot = subquery.from(RtRegistration.class);
  Join<RtRegistration, RtEntry> joinEntry = subroot.join(RtRegistration_.rtEntries, JoinType.LEFT);
  Join<RtEntry, RtRace> joinRace = joinEntry.join(RtEntry_.rtRaces, JoinType.LEFT);
  Join<RtRace, RtRunner> joinRunner = joinRace.join(RtRace_.rtRunner, JoinType.LEFT);

  JPARunnerBoxDetailSearchFormDataStatementBuilder foreignBuilder = new JPARunnerBoxDetailSearchFormDataStatementBuilder(joinRunner);
  foreignBuilder.build(searchFormData);

  existsInForeignBuilder(subquery, subroot, foreignBuilder);
}

private void buildForeignECardBuilder(AbstractECardSearchBoxData searchFormData) throws ProcessingException {
  Subquery<Long> subquery = query.subquery(Long.class);
  Root<RtRegistration> subroot = subquery.from(RtRegistration.class);
  Join<RtRegistration, RtEntry> joinEntry = subroot.join(RtRegistration_.rtEntries, JoinType.LEFT);
  Join<RtEntry, RtRace> joinRace = joinEntry.join(RtEntry_.rtRaces, JoinType.LEFT);
  Join<RtRace, RtEcard> joinECard = joinRace.join(RtRace_.rtEcard, JoinType.LEFT);

  JPAECardBoxSearchFormDataStatementBuilder foreignBuilder = new JPAECardBoxSearchFormDataStatementBuilder(joinECard);
  foreignBuilder.build(searchFormData);

  existsInForeignBuilder(subquery, subroot, foreignBuilder);
}

private void buildForeignClubBuilder(AbstractClubSearchBoxData searchFormData) throws ProcessingException {
  Subquery<Long> subquery = query.subquery(Long.class);
  Root<RtRegistration> subroot = subquery.from(RtRegistration.class);
  Join<RtRegistration, RtEntry> joinEntry = subroot.join(RtRegistration_.rtEntries, JoinType.LEFT);
  Join<RtEntry, RtRace> joinRace = joinEntry.join(RtEntry_.rtRaces, JoinType.LEFT);
  Join<RtRace, RtClub> joinClub = joinRace.join(RtRace_.rtClub, JoinType.LEFT);

  JPAClubBoxSearchFormDataStatementBuilder foreignBuilder = new JPAClubBoxSearchFormDataStatementBuilder(joinClub);
  foreignBuilder.build(searchFormData);

  existsInForeignBuilder(subquery, subroot, foreignBuilder);
}

private void buildForeignCityBuilder(AbstractCitySearchBoxData searchFormData) throws ProcessingException {
  Subquery<Long> subquery = query.subquery(Long.class);
  Root<RtRegistration> subroot = subquery.from(RtRegistration.class);
  Join<RtRegistration, RtEntry> joinEntry = subroot.join(RtRegistration_.rtEntries, JoinType.LEFT);
  Join<RtEntry, RtRace> joinRace = joinEntry.join(RtEntry_.rtRaces, JoinType.LEFT);
  Join<RtRace, RtAddress> joinAddress = joinRace.join(RtRace_.rtAddress, JoinType.LEFT);
  Join<RtAddress, RtCity> joinCity = joinAddress.join(RtAddress_.rtCity, JoinType.LEFT);

  JPACityBoxSearchFormDataStatementBuilder foreignBuilder = new JPACityBoxSearchFormDataStatementBuilder(joinCity);
  foreignBuilder.build(searchFormData);

  existsInForeignBuilder(subquery, subroot, foreignBuilder);
}

/**
 * {@inheritDoc}
 */
public boolean visit(JpqlIn node, CriteriaHolder query) {
    node.jjtGetChild(0).visit(this, query);
    Expression<?> expression = query.<Expression<?>>getCurrentValue();
    node.jjtGetChild(1).visit(this, query);
    query.setValue(expression.in(query.<Subquery<?>>getCurrentValue()));
    return false;
}