Java Code Examples for javax.persistence.criteria.CriteriaQuery#subquery()

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

@Override
public List<DeptEmployee> searchByDepartmentQuery(String searchKey) {
    CriteriaQuery<DeptEmployee> criteriaQuery = createCriteriaQuery(DeptEmployee.class);
    Root<DeptEmployee> emp = criteriaQuery.from(DeptEmployee.class);

    Subquery<Department> subquery = criteriaQuery.subquery(Department.class);
    Root<Department> dept = subquery.from(Department.class);
    subquery.select(dept)
        .distinct(true)
        .where(criteriaBuilder.like(dept.get("name"), new StringBuffer("%").append(searchKey)
            .append("%")
            .toString()));

    criteriaQuery.select(emp)
        .where(criteriaBuilder.in(emp.get("department"))
            .value(subquery));

    TypedQuery<DeptEmployee> query = entityManager.createQuery(criteriaQuery);
    return query.getResultList();
}
 

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

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

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

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

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

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

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

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

@Override
public List<Function<Root<T>, Predicate>> getDataSharingPredicates( CriteriaBuilder builder, UserInfo user, String access )
{
    List<Function<Root<T>, Predicate>> predicates = new ArrayList<>();

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

    preProcessPredicates( builder, predicates );

    if ( !dataSharingEnabled( 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.exists( userGroupPredicate.apply( root ) ),
        builder.exists( userPredicate.apply( root ) ) ) );

    return predicates;
}
 

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