Java Subquery.from方法代码示例

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包/类
@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包/类
@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包/类
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();
}
 

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

	};

}
 

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

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

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