Java Code Examples for javax.persistence.criteria.Subquery#where()

/**
 * Build specification by post query.
 *
 * @param postQuery post query must not be null
 * @return a post specification
 */
@NonNull
private Specification<Post> buildSpecByQuery(@NonNull PostQuery postQuery) {
    Assert.notNull(postQuery, "Post query must not be null");

    return (Specification<Post>) (root, query, criteriaBuilder) -> {
        List<Predicate> predicates = new LinkedList<>();

        if (postQuery.getStatus() != null) {
            predicates.add(criteriaBuilder.equal(root.get("status"), postQuery.getStatus()));
        }

        if (postQuery.getCategoryId() != null) {
            Subquery<Post> postSubquery = query.subquery(Post.class);
            Root<PostCategory> postCategoryRoot = postSubquery.from(PostCategory.class);
            postSubquery.select(postCategoryRoot.get("postId"));
            postSubquery.where(
                criteriaBuilder.equal(root.get("id"), postCategoryRoot.get("postId")),
                criteriaBuilder.equal(postCategoryRoot.get("categoryId"),
                    postQuery.getCategoryId()));
            predicates.add(criteriaBuilder.exists(postSubquery));
        }

        if (postQuery.getKeyword() != null) {
            // Format like condition
            String likeCondition = String
                .format("%%%s%%", StringUtils.strip(postQuery.getKeyword()));

            // Build like predicate
            Predicate titleLike = criteriaBuilder.like(root.get("title"), likeCondition);
            Predicate originalContentLike = criteriaBuilder
                .like(root.get("originalContent"), likeCondition);

            predicates.add(criteriaBuilder.or(titleLike, originalContentLike));
        }

        return query.where(predicates.toArray(new Predicate[0])).getRestriction();
    };
}
 

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

/**
 * (related to NCL-5192, NCL-5351)
 *
 * When (re)building a temporary build: - if there are existing *successful* temporary builds having the same idRev,
 * ignore persistent builds - if there are no existing temporary builds having the same idRev, include also
 * persistent builds having the same idRev
 *
 * When (re)building a persistent build: - include only existing persistent builds having the same idRev
 *
 * @param idRev the revision of the build to (re)build
 * @param temporary if requested (re)build is temporary
 * @return Predicate that filters out builds according to description
 */
public static Predicate<BuildRecord> includeTemporary(IdRev idRev, boolean temporary) {
    return (root, query, cb) -> {
        if (temporary) {
            // Create subquery that counts number of temporary builds for the same BuildConfigurationAudited.idRev
            Subquery<Long> temporaryCount = query.subquery(Long.class);
            Root<BuildRecord> subRoot = temporaryCount.from(BuildRecord.class);
            temporaryCount.select(cb.count(subRoot.get(BuildRecord_.id)));
            temporaryCount.where(
                    cb.and(
                            cb.isTrue(subRoot.get(BuildRecord_.temporaryBuild)),
                            cb.equal(subRoot.get(BuildRecord_.status), BuildStatus.SUCCESS),
                            cb.equal(subRoot.get(BuildRecord_.buildConfigurationId), idRev.getId()),
                            cb.equal(subRoot.get(BuildRecord_.buildConfigurationRev), idRev.getRev())));

            // only false if build is persistent and there are temporaryBuilds present
            return cb
                    .or(cb.isTrue(root.get(BuildRecord_.temporaryBuild)), cb.lessThanOrEqualTo(temporaryCount, 0L));
        }
        return cb.isFalse(root.get(BuildRecord_.temporaryBuild));
    };
}
 

/**
 * {@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");
	}
}
 

public static Predicate<BuildRecord> withoutAttribute(String key) {
    return (root, query, cb) -> {
        Subquery<String> subquery = query.subquery(String.class);
        Root<BuildRecordAttribute> subRoot = subquery.from(BuildRecordAttribute.class);
        subquery.select(subRoot.get(BuildRecordAttribute_.key));
        subquery.where(
                cb.and(
                        cb.equal(subRoot.get(BuildRecordAttribute_.key), key),
                        cb.equal(root.get(BuildRecord_.id), subRoot.get(BuildRecordAttribute_.buildRecord))));
        return query.where(cb.not(cb.exists(subquery))).getRestriction();
    };
}
 

/**
 * Get the {@link Predicate} for the query which will find the clusters which match the given criterion.
 *
 * @param root      The {@link Root} of the query
 * @param cq        The {@link CriteriaQuery}
 * @param cb        The {@link CriteriaBuilder}
 * @param criterion The {@link Criterion} to match clusters against
 * @return A {@link Predicate} for this query
 */
public static Predicate findClustersMatchingCriterion(
    final Root<ClusterEntity> root,
    final CriteriaQuery<?> cq,
    final CriteriaBuilder cb,
    final Criterion criterion
) {
    final Subquery<Long> criterionSubquery = cq.subquery(Long.class);
    final Root<ClusterEntity> criterionSubqueryRoot = criterionSubquery.from(ClusterEntity.class);
    criterionSubquery.select(criterionSubqueryRoot.get(ClusterEntity_.id));
    criterionSubquery.where(
        PredicateUtils.createCriterionPredicate(
            criterionSubqueryRoot,
            criterionSubquery,
            cb,
            ClusterEntity_.uniqueId,
            ClusterEntity_.name,
            ClusterEntity_.version,
            ClusterEntity_.status,
            () -> criterionSubqueryRoot.join(ClusterEntity_.tags, JoinType.INNER),
            ClusterEntity_.id,
            criterion
        )
    );

    return root.get(ClusterEntity_.id).in(criterionSubquery);
}
 

/**
 * Get the specification for the query which will find the commands which match the given criterion.
 *
 * @param root      The {@link Root} (from) for the query
 * @param cq        The {@link CriteriaQuery} instance
 * @param cb        The {@link CriteriaBuilder} instance
 * @param criterion The {@link Criterion} to match commands against
 * @return A {@link Predicate} for this query
 */
public static Predicate findCommandsMatchingCriterion(
    final Root<CommandEntity> root,
    final CriteriaQuery<?> cq,
    final CriteriaBuilder cb,
    final Criterion criterion
) {
    final Subquery<Long> criterionSubquery = cq.subquery(Long.class);
    final Root<CommandEntity> criterionSubqueryRoot = criterionSubquery.from(CommandEntity.class);
    criterionSubquery.select(criterionSubqueryRoot.get(CommandEntity_.id));
    criterionSubquery.where(
        cb.and(
            PredicateUtils.createCriterionPredicate(
                criterionSubqueryRoot,
                criterionSubquery,
                cb,
                CommandEntity_.uniqueId,
                CommandEntity_.name,
                CommandEntity_.version,
                CommandEntity_.status,
                () -> criterionSubqueryRoot.join(CommandEntity_.tags, JoinType.INNER),
                CommandEntity_.id,
                criterion
            ),
            cb.isNotEmpty(criterionSubqueryRoot.get(CommandEntity_.clusterCriteria))
        )
    );

    return root.get(CommandEntity_.id).in(criterionSubquery);
}
 

private List<javax.persistence.criteria.Predicate> getSearchCriteria(Root<RuleBaseValues> root,CriteriaQuery<RuleBaseValues> query,
        String docTypeName, String ruleTemplateId,
        String ruleDescription, Boolean activeInd, Map<String,String> extensionValues) {
    List<javax.persistence.criteria.Predicate> predicates = new ArrayList<javax.persistence.criteria.Predicate>();
    CriteriaBuilder criteribaBuilder = getEntityManager().getCriteriaBuilder();

    predicates.add(criteribaBuilder.equal(root.get("currentInd"),Boolean.TRUE));
    predicates.add(criteribaBuilder.equal(root.get("templateRuleInd"), Boolean.FALSE));
    if (activeInd != null) {
        predicates.add(criteribaBuilder.equal(root.get("active"),activeInd));
    }
    if (docTypeName != null) {
        predicates.add(criteribaBuilder.like(criteribaBuilder.upper(root.<String>get("docTypeName")), docTypeName.toUpperCase()));
    }
    if (ruleDescription != null && !ruleDescription.trim().equals("")) {
        predicates.add(criteribaBuilder.like(criteribaBuilder.upper(root.<String>get("description")),ruleDescription.toUpperCase()));
    }
    if (ruleTemplateId != null) {
        predicates.add(criteribaBuilder.equal(root.get("ruleTemplateId"),ruleTemplateId));
    }

    if (extensionValues != null && !extensionValues.isEmpty()) {
        for (Map.Entry<String,String> entry : extensionValues.entrySet()) {
            if (!StringUtils.isEmpty(entry.getValue())) {
                Subquery ruleExtSubQuery = query.subquery(RuleExtensionBo.class);
                Root<RuleExtensionBo> ruleExtRoot = ruleExtSubQuery.from(RuleExtensionBo.class);
                javax.persistence.criteria.Predicate predAnd = criteribaBuilder.and(
                        criteribaBuilder.equal(ruleExtRoot.get("extensionValues").get("key"),entry.getKey()),
                        criteribaBuilder.like(ruleExtRoot.get("extensionValues").<String>get("value"),
                                ("%" + (String) entry.getValue() + "%").toUpperCase()));
                ruleExtSubQuery.where(predAnd);
                ruleExtSubQuery.select(ruleExtRoot.get("ruleBaseValuesId"));

                predicates.add(criteribaBuilder.in(root.get("id")).value(ruleExtSubQuery));
            }
        }
    }
    return predicates;
}
 

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

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

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

static CustomPredicateFilter<ContestModel> hasSupervisor(String userJid) {
    return (cb, cq, root) -> {
        Subquery<ContestSupervisorModel> sq = cq.subquery(ContestSupervisorModel.class);
        Root<ContestSupervisorModel> subRoot = sq.from(ContestSupervisorModel.class);

        sq.where(
                cb.equal(subRoot.get(ContestSupervisorModel_.contestJid), root.get(ContestModel_.jid)),
                cb.equal(subRoot.get(ContestSupervisorModel_.userJid), userJid));
        sq.select(subRoot);

        return cb.exists(sq);
    };
}
 

@Test
@Transactional
public void testCopy() {
	EntityManager em = bookDao.getEntityManager();
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Book> criteria = cb.createQuery(Book.class);
	
	// Fetch join
	Root<Book> root = criteria.from(Book.class);
	Path<String> path = root.join("author").<String>get("name");
	root.fetch("author");
	criteria.select(root);
	
	// SubQuery
	Subquery<String> sq = criteria.subquery(String.class);
	Root<Author> author = sq.from(Author.class);
	sq.select(author.<String>get("name"));
	sq.where(cb.equal(author.<String>get("name"), "Rod"));
	
	criteria.where(cb.in(path).value(sq));
	
	CriteriaQuery<Book> copy = cb.createQuery(Book.class);
	JpaUtils.copyCriteria(criteria, copy);
	
	List<Book> copyBooks = em.createQuery(copy).getResultList();
	List<Book> books = em.createQuery(criteria).getResultList();
	assertEquals(books, copyBooks);
}
 

static CustomPredicateFilter<ContestModel> hasContestant(String userJid) {
    return (cb, cq, root) -> {
        Subquery<ContestContestantModel> sq = cq.subquery(ContestContestantModel.class);
        Root<ContestContestantModel> subRoot = sq.from(ContestContestantModel.class);

        sq.where(
                cb.equal(subRoot.get(ContestContestantModel_.contestJid), root.get(ContestModel_.jid)),
                cb.equal(subRoot.get(ContestContestantModel_.userJid), userJid),
                cb.equal(subRoot.get(ContestContestantModel_.status), APPROVED.name()));
        sq.select(subRoot);

        return cb.exists(sq);
    };
}
 

static CustomPredicateFilter<ContestModel> hasManager(String userJid) {
    return (cb, cq, root) -> {
        Subquery<ContestManagerModel> sq = cq.subquery(ContestManagerModel.class);
        Root<ContestManagerModel> subRoot = sq.from(ContestManagerModel.class);

        sq.where(
                cb.equal(subRoot.get(ContestManagerModel_.contestJid), root.get(ContestModel_.jid)),
                cb.equal(subRoot.get(ContestManagerModel_.userJid), userJid));
        sq.select(subRoot);

        return cb.exists(sq);
    };
}
 

static CustomPredicateFilter<ContestModel> hasModule(ContestModuleType type) {
    return (cb, cq, root) -> {
        Subquery<ContestModuleModel> sq = cq.subquery(ContestModuleModel.class);
        Root<ContestModuleModel> subRoot = sq.from(ContestModuleModel.class);

        sq.where(
                cb.equal(subRoot.get(ContestModuleModel_.contestJid), root.get(ContestModel_.jid)),
                cb.equal(subRoot.get(ContestModuleModel_.name), type.name()),
                cb.isTrue(subRoot.get(ContestModuleModel_.enabled)));
        sq.select(subRoot);

        return cb.exists(sq);
    };
}
 

public <T extends ResourceEntity> SearchResult<T> search(Class<T> clazz, ParseTree filterTree, int count,
        int startIndex,
        String sortBy, String sortOrder, FilterParser<T> filterParser) {

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<T> resourceQuery = cb.createQuery(clazz);
    Root<T> resourceRoot = resourceQuery.from(clazz);

    Subquery<Long> internalIdQuery = resourceQuery.subquery(Long.class);
    Root<T> internalIdRoot = internalIdQuery.from(clazz);
    internalIdQuery.select(internalIdRoot.get(ResourceEntity_.internalId));

    if (filterTree != null && filterTree.getChildCount() > 0) {
        Predicate predicate = filterParser.createPredicateAndJoin(filterTree, internalIdRoot);
        internalIdQuery.where(predicate);
    }

    resourceQuery.select(resourceRoot).where(
            cb.in(resourceRoot.get(ResourceEntity_.internalId)).value(internalIdQuery));

    // TODO: evaluate if a User-/GroupDao supplied default sortBy field is possible
    Expression<?> sortByField = resourceRoot.get(ResourceEntity_.id);

    if (sortBy != null && !sortBy.isEmpty()) {
        sortByField = filterParser.createSortByField(sortBy, resourceRoot);
    }

    // default order is ascending
    Order order = cb.asc(sortByField);

    if (sortOrder.equalsIgnoreCase("descending")) {
        order = cb.desc(sortByField);
    }

    resourceQuery.orderBy(order);

    TypedQuery<T> query = em.createQuery(resourceQuery);
    query.setFirstResult(startIndex);
    query.setMaxResults(count);

    List<T> results = query.getResultList();

    long totalResult = getTotalResults(clazz, internalIdQuery);

    return new SearchResult<>(results, totalResult);
}
 

private List<javax.persistence.criteria.Predicate> getSearchCriteria(Root<RuleBaseValues> root,CriteriaQuery<RuleBaseValues> query,
        String docTypeName, String ruleTemplateId,
        String ruleDescription, Boolean delegateRule, Boolean activeInd, Map extensionValues) {
    List<javax.persistence.criteria.Predicate> predicates = new ArrayList<javax.persistence.criteria.Predicate>();
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();

    predicates.add(cb.equal(root.get("currentInd"),Boolean.TRUE));
    predicates.add(cb.equal(root.get("templateRuleInd"), Boolean.FALSE));
    if (activeInd != null) {
        predicates.add(cb.equal(root.get("active"),activeInd));
    }
    if (docTypeName != null) {
        predicates.add(cb.like(cb.upper(root.<String>get("docTypeName")), docTypeName.toUpperCase()));
    }
    if (ruleDescription != null && !ruleDescription.trim().equals("")) {
        predicates.add(cb.like(cb.upper(root.<String>get("description")),ruleDescription.toUpperCase()));
    }
    if (ruleTemplateId != null) {
        predicates.add(cb.equal(root.get("ruleTemplateId"),ruleTemplateId));
    }
    if (delegateRule != null) {
        predicates.add(cb.equal(root.get("delegateRule"),delegateRule));
    }
    if (extensionValues != null && !extensionValues.isEmpty()) {
        for (Iterator iter2 = extensionValues.entrySet().iterator(); iter2.hasNext();) {
            Map.Entry entry = (Map.Entry) iter2.next();
            if (!StringUtils.isEmpty((String) entry.getValue())) {
                Subquery ruleExtSubQuery = query.subquery(RuleExtensionBo.class);
                Root<RuleExtensionBo> ruleExtRoot = ruleExtSubQuery.from(RuleExtensionBo.class);
                javax.persistence.criteria.Predicate predAnd = cb.and(
                        cb.equal(ruleExtRoot.get("extensionValues").get("key"),entry.getKey()),
                        cb.like(ruleExtRoot.get("extensionValues").<String>get("value"),
                                ("%" + (String) entry.getValue() + "%").toUpperCase()));
                ruleExtSubQuery.where(predAnd);
                ruleExtSubQuery.select(ruleExtRoot.get("ruleBaseValuesId"));

                predicates.add(cb.in(root.get("id")).value(ruleExtSubQuery));
            }
        }
    }
    return predicates;
}
 

/**
 * Get sharing predicates based on given UserInfo and Access String
 *
 * @param builder CriteriaBuilder
 * @param user    UserInfo
 * @param access  Access String
 * @return List of Function<Root<T>, Predicate>
 */
@Override
public List<Function<Root<T>, Predicate>> getSharingPredicates( CriteriaBuilder builder, UserInfo user, String access )
{
    List<Function<Root<T>, Predicate>> predicates = new ArrayList<>();

    CriteriaQuery<T> criteria = builder.createQuery( getClazz() );

    preProcessPredicates( builder, predicates );

    if ( !sharingEnabled( user ) || user == null )
    {
        return predicates;
    }

    Function<Root<T>, Subquery<Integer>> userGroupPredicate = (( Root<T> root ) -> {
        Subquery<Integer> userGroupSubQuery = criteria.subquery( Integer.class );
        Root<T> ugdc = userGroupSubQuery.from( getClazz() );
        Join<T, UserGroupAccess> uga = ugdc.join( "userGroupAccesses" );
        userGroupSubQuery.select( uga.get( "id" ) );

        return userGroupSubQuery.where(
            builder.and(
                builder.equal( root.get( "id" ), ugdc.get( "id" ) ),
                builder.equal( uga.join( "userGroup" ).join( "members" ).get( "id" ), user.getId() ),
                builder.like( uga.get( "access" ), access ) ) );
    });

    Function<Root<T>, Subquery<Integer>> userPredicate = (root -> {
        Subquery<Integer> userSubQuery = criteria.subquery( Integer.class );
        Root<T> udc = userSubQuery.from( getClazz() );
        Join<T, UserAccess> ua = udc.join( "userAccesses" );
        userSubQuery.select( ua.get( "id" ) );

        return userSubQuery.where(
            builder.and(
                builder.equal( root.get( "id" ), udc.get( "id" ) ),
                builder.equal( ua.get( "user" ).get( "id" ), user.getId() ),
                builder.like( ua.get( "access" ), access ) ) );
    });

    predicates.add( root -> builder.or(
        builder.like( root.get( "publicAccess" ), access ),
        builder.isNull( root.get( "publicAccess" ) ),
        builder.isNull( root.get( "user" ) ),
        builder.equal( root.get( "user" ).get( "id" ), user.getId() ),
        builder.exists( userGroupPredicate.apply( root ) ),
        builder.exists( userPredicate.apply( root ) ) ) );

    return predicates;
}
 

static CustomPredicateFilter<ContestModel> hasParticipatingContestant(String userJid) {
    return (cb, cq, root) -> {
        Subquery<ContestContestantModel> sq = cq.subquery(ContestContestantModel.class);
        Root<ContestContestantModel> subRoot = sq.from(ContestContestantModel.class);

        sq.where(
                cb.equal(subRoot.get(ContestContestantModel_.contestJid), root.get(ContestModel_.jid)),
                cb.equal(subRoot.get(ContestContestantModel_.userJid), userJid),
                cb.equal(subRoot.get(ContestContestantModel_.status), APPROVED.name()),
                cb.isNotNull(subRoot.get(ContestContestantModel_.finalRank)));
        sq.select(subRoot);

        return cb.exists(sq);
    };
}