Java Subquery.where方法代码示例

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

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

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

import javax.persistence.criteria.Subquery; //导入方法依赖的package包/类
public WordTemplateQuery hasProperties(Map<String, String> someProperties) {
	Subquery<Long> subQuery = query.subquery(Long.class);
	Root<PersistentTemplate> fromSubQuery = subQuery
			.from(PersistentTemplate.class);
	Path<Long> id = fromSubQuery.get("id");
	subQuery.select(id);
	MapJoin<PersistentTemplate, String, String> propertiesMap = fromSubQuery
			.joinMap("properties", JoinType.INNER);
	subQuery.where(buildPropertyRestriction(propertiesMap, someProperties));
	subQuery.groupBy(id);
	subQuery.having(criteriaBuilder.equal(criteriaBuilder.count(id),
			someProperties.size()));
	restrictions.add(persistentTemplate.get("id").in(subQuery));
	return this;
}
 

import javax.persistence.criteria.Subquery; //导入方法依赖的package包/类
private Subquery<RuleResponsibilityBo> getRuleResponsibilitySubQuery(String ruleRespName,
                    CriteriaQuery<RuleBaseValues> query){
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    Subquery<RuleResponsibilityBo> subquery = query.subquery(RuleResponsibilityBo.class);
    Root fromResp = subquery.from(RuleResponsibilityBo.class);
    subquery.where(cb.equal(fromResp.get("ruleResponsibilityName"),ruleRespName));
    subquery.select(fromResp.get("ruleBaseValuesId"));

    return subquery;
}
 

import javax.persistence.criteria.Subquery; //导入方法依赖的package包/类
/**
 * Method that returns a subquery that selects rule id from rule responsibility table based on certain criteria
 * @param ruleResponsibilityName is the responsibility name
 * @param query is he criteria query
 * @return a subquery that selects the rule id from rule responsibility table where responsibility name equals the rule responsibility name that is passed in as parameter to this method
 */
private Subquery<RuleResponsibilityBo> getResponsibilitySubQuery(String ruleResponsibilityName, CriteriaQuery<RuleBaseValues> query){
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    Subquery<RuleResponsibilityBo> ruleResponsibilitySubquery = query.subquery(RuleResponsibilityBo.class);
    Root fromResp = ruleResponsibilitySubquery.from(RuleResponsibilityBo.class);
    ruleResponsibilitySubquery.where(criteriaBuilder.equal(fromResp.get("ruleResponsibilityName"),ruleResponsibilityName));
    ruleResponsibilitySubquery.select(fromResp.get("ruleBaseValuesId"));

    return ruleResponsibilitySubquery;
}
 

import javax.persistence.criteria.Subquery; //导入方法依赖的package包/类
/**
 * Method that selects the responsibility id from rule responsibility table where rule id is equal to the value that is passed as parameter to this method
 * @param ruleBaseValuesId is the rule id
 * @param query is the criteria query
 * @return a subquery
 */
private Subquery<RuleResponsibilityBo> getRuleResponsibilitySubQuery(Long ruleBaseValuesId, CriteriaQuery<RuleDelegationBo> query){
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    Subquery<RuleResponsibilityBo> ruleResponsibilityBoSubquery = query.subquery(RuleResponsibilityBo.class);
    Root fromResp = ruleResponsibilityBoSubquery.from(RuleResponsibilityBo.class);
    ruleResponsibilityBoSubquery.where(criteriaBuilder.equal(fromResp.get("ruleBaseValuesId"),ruleBaseValuesId));
    ruleResponsibilityBoSubquery.select(fromResp.get("responsibilityId"));

    return ruleResponsibilityBoSubquery;
}