Java Subquery类代码示例

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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)))));
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
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);
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
/**
 * 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));
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
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))
        );
    };
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
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);
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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);
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
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);
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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());
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
/**
 * {@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();
    }
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
/**
 * {@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");
	}
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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();
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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();
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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;
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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;
}
 

import javax.persistence.criteria.Subquery; //导入依赖的package包/类
@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;
}