Java Examples for javax.persistence.criteria.Subquery

@Override
public List<OptionListDO> getOptionListsForScope(Long scopeId) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<OptionListDO> cq = cb.createQuery(OptionListDO.class);
    Root<OptionListDO> fromOptionList = cq.from(OptionListDO.class);
    Path<ScopeDO> scopePath = fromOptionList.get(OptionListDO_.scope);
    Predicate shared = cb.isTrue(fromOptionList.get(OptionListDO_.shared));
    Subquery<ScopeDO> subQuery = cq.subquery(ScopeDO.class);
    Root<ScopeTreeDO> fromScopeTree = subQuery.from(ScopeTreeDO.class);
    subQuery.select(fromScopeTree.get(ScopeTreeDO_.ancestorScope));
    Predicate scope = cb.equal(fromScopeTree.get(ScopeTreeDO_.scope).get(ScopeDO_.scopeId), scopeId);
    Predicate ancestorNotNull = cb.isNotNull(fromScopeTree.get(ScopeTreeDO_.ancestorScope));
    subQuery.where(scope, ancestorNotNull);
    cq.where(cb.and(cb.in(scopePath).value(subQuery), shared));
    return getResultList(em.createQuery(cq).setHint("org.hibernate.cacheable", Boolean.TRUE));
}

public String render(CriteriaQueryCompiler.RenderingContext renderingContext) {
    StringBuilder buffer = new StringBuilder();
    buffer.append(((Renderable) getExpression()).render(renderingContext));
    if (isNegated()) {
        buffer.append(" not");
    }
    buffer.append(" in ");
    // subquery expressions are already wrapped in parenthesis, so we only need to
    // render the parenthesis here if the values represent an explicit value list
    boolean isInSubqueryPredicate = getValues().size() == 1 && Subquery.class.isInstance(getValues().get(0));
    if (isInSubqueryPredicate) {
        buffer.append(((Renderable) getValues().get(0)).render(renderingContext));
    } else {
        buffer.append('(');
        String sep = "";
        for (Expression value : getValues()) {
            buffer.append(sep).append(((Renderable) value).render(renderingContext));
            sep = ", ";
        }
        buffer.append(')');
    }
    return buffer.toString();
}

/**
	 * Returns a session for a key
	 * 
	 * @param key
	 *            The key of the requested session
	 * @throws NoResultException
	 *             if no session with this key is stored
	 * @return The persisted Session
	 */
public List<SessionEntity> findBySessionKeyAndUsername(String key, String username) throws NoResultException {
    CriteriaBuilder criteriaBuilder = this.getCriteriaBuilder();
    CriteriaQuery<SessionEntity> criteriaQuery = criteriaBuilder.createQuery(SessionEntity.class);
    Root<SessionEntity> entityRoot = criteriaQuery.from(SessionEntity.class);
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(criteriaBuilder.equal(entityRoot.get(SessionEntity_.key), key));
    Subquery<String> subQuery = criteriaQuery.subquery(String.class);
    Root<BundesbruderEntity> entityRoot2 = subQuery.from(BundesbruderEntity.class);
    predicates.add(criteriaBuilder.equal(entityRoot2.get(BundesbruderEntity_.userName), username));
    criteriaQuery.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));
    TypedQuery<SessionEntity> typedQuery = this.createQuery(criteriaQuery);
    List<SessionEntity> searchResult = typedQuery.getResultList();
    return searchResult;
}

public String render(CriteriaQueryCompiler.RenderingContext renderingContext) {
    StringBuilder buffer = new StringBuilder();
    buffer.append(((Renderable) getExpression()).render(renderingContext));
    if (isNegated()) {
        buffer.append(" not");
    }
    buffer.append(" in ");
    // subquery expressions are already wrapped in parenthesis, so we only need to
    // render the parenthesis here if the values represent an explicit value list
    boolean isInSubqueryPredicate = getValues().size() == 1 && Subquery.class.isInstance(getValues().get(0));
    if (isInSubqueryPredicate) {
        buffer.append(((Renderable) getValues().get(0)).render(renderingContext));
    } else {
        buffer.append('(');
        String sep = "";
        for (Expression value : getValues()) {
            buffer.append(sep).append(((Renderable) value).render(renderingContext));
            sep = ", ";
        }
        buffer.append(')');
    }
    return buffer.toString();
}

public static <T> BaseCriteria createSubQueryBaseCriteria(final BasicCriteriaElements basicCriteriaElements, final String selectedAttribute, final Class<T> subQueryClass) {
    final BaseCriteria baseForSubQuery = basicCriteriaElements.getBaseCriteriaForSubQuery();
    final Class selectedAttributeClass = extractClassFromAttribute(selectedAttribute, subQueryClass);
    final Subquery subquery = baseForSubQuery.getCriteriaQuery().subquery(selectedAttributeClass);
    final Root subQueryRoot = subquery.from(subQueryClass);
    final BaseCriteria subCriteria = new BaseCriteria(subQueryRoot, subquery, baseForSubQuery.getCriteriaBuilder(), subQueryClass);
    final Path subSelectSelectedAttribute = PathHelper.extractPath(subCriteria, selectedAttribute);
    subquery.select(subSelectSelectedAttribute);
    return subCriteria;
}

@Test
// MySQL does not allow "delete/update from" and subqueries to use the same table
@SkipForDialect(MySQLDialect.class)
public void testDeleteWithUnCorrelatedSubquery() {
    CriteriaBuilder builder = entityManagerFactory().getCriteriaBuilder();
    EntityManager em = getOrCreateEntityManager();
    em.getTransaction().begin();
    // attempt to delete Customers who's age is less than the AVG age
    CriteriaDelete<Customer> criteria = builder.createCriteriaDelete(Customer.class);
    Root<Customer> customerRoot = criteria.from(Customer.class);
    Subquery<Double> subCriteria = criteria.subquery(Double.class);
    Root<Customer> subQueryCustomerRoot = subCriteria.from(Customer.class);
    subCriteria.select(builder.avg(subQueryCustomerRoot.get(Customer_.age)));
    // also illustrates the new capability to use the subquery selection as an expression!
    criteria.where(builder.lessThan(customerRoot.get(Customer_.age), subCriteria.getSelection().as(Integer.class)));
    // make sure Subquery#getParent fails...
    try {
        subCriteria.getParent();
        fail("Expecting Subquery.getParent call to fail on DELETE containing criteria");
    } catch (IllegalStateException expected) {
    }
    Query query = em.createQuery(criteria);
    try {
        // first, make sure an attempt to list fails
        query.getResultList();
        fail("Attempt to getResultList() on delete criteria should have failed");
    } catch (IllegalStateException expected) {
    }
    query.executeUpdate();
    em.getTransaction().commit();
    em.close();
}

public List<Object[]> findMaxNumCertificatesByBranch_criteria() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
    Root<Branch> branch = query.from(Branch.class);
    Join<Branch, Employee> employee = branch.join("employees");
    Join<Employee, Certificate> cert = employee.join("certificates");
    Subquery<Long> sq = query.subquery(Long.class);
    Root<Employee> emp2 = sq.from(Employee.class);
    sq.select(cb.count(emp2.get("certificates"))).where(cb.isMember(emp2, branch.<List<Employee>>get("employees"))).groupBy(emp2);
    query.multiselect(branch, cb.count(cert)).distinct(true).groupBy(branch, employee).having(cb.greaterThanOrEqualTo(cb.count(cert), cb.all(sq))).orderBy(cb.asc(branch.get("id")));
    return em.createQuery(query).getResultList();
}

/* Parameters */
public Set<ParameterExpression<?>> getParameters() {
    // NOTE: we have to always visit them because it's not possible to cache that easily
    ParameterVisitor visitor = new ParameterVisitor();
    visitor.visit(selection);
    visitor.visit(restriction);
    if (subqueries != null) {
        for (Subquery<?> subquery : subqueries) {
            visitor.visit(subquery);
        }
    }
    for (RootImpl<?> r : roots) {
        r.visit(visitor);
    }
    for (AbstractFrom<?, ?> r : correlationRoots) {
        r.visit(visitor);
    }
    visitor.visit(having);
    if (groupList != null) {
        for (Expression<?> grouping : groupList) {
            visitor.visit(grouping);
        }
    }
    if (orderList != null) {
        for (Order ordering : orderList) {
            visitor.visit(ordering.getExpression());
        }
    }
    return visitor.getParameters();
}

@Override
public BusinessObjectDataEntity getBusinessObjectDataByAltKeyAndStatus(BusinessObjectDataKey businessObjectDataKey, String businessObjectDataStatus) {
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<BusinessObjectDataEntity> criteria = builder.createQuery(BusinessObjectDataEntity.class);
    // The criteria root is the business object data.
    Root<BusinessObjectDataEntity> businessObjectDataEntity = criteria.from(BusinessObjectDataEntity.class);
    // Join to other tables that we need to filter on.
    Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> businessObjectFormatEntity = businessObjectDataEntity.join(BusinessObjectDataEntity_.businessObjectFormat);
    Join<BusinessObjectFormatEntity, FileTypeEntity> fileTypeEntity = businessObjectFormatEntity.join(BusinessObjectFormatEntity_.fileType);
    Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity = businessObjectFormatEntity.join(BusinessObjectFormatEntity_.businessObjectDefinition);
    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate mainQueryRestriction = getQueryRestriction(builder, businessObjectDataEntity, businessObjectFormatEntity, fileTypeEntity, businessObjectDefinitionEntity, businessObjectDataKey);
    // If a format version was specified, use the latest available for this partition value.
    if (businessObjectDataKey.getBusinessObjectFormatVersion() == null) {
        // Business object format version is not specified, so just use the latest available for this set of partition values.
        Subquery<Integer> subQuery = criteria.subquery(Integer.class);
        // The criteria root is the business object data.
        Root<BusinessObjectDataEntity> subBusinessObjectDataEntity = subQuery.from(BusinessObjectDataEntity.class);
        // Join to the other tables we can filter on.
        Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> subBusinessObjectFormatEntity = subBusinessObjectDataEntity.join(BusinessObjectDataEntity_.businessObjectFormat);
        Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> subBusinessObjectDefinitionEntity = subBusinessObjectFormatEntity.join(BusinessObjectFormatEntity_.businessObjectDefinition);
        Join<BusinessObjectFormatEntity, FileTypeEntity> subBusinessObjectFormatFileTypeEntity = subBusinessObjectFormatEntity.join(BusinessObjectFormatEntity_.fileType);
        Join<BusinessObjectDataEntity, BusinessObjectDataStatusEntity> subBusinessObjectDataStatusEntity = subBusinessObjectDataEntity.join(BusinessObjectDataEntity_.status);
        // Create the standard restrictions (i.e. the standard where clauses).
        Predicate subQueryRestriction = builder.equal(subBusinessObjectDefinitionEntity, businessObjectDefinitionEntity);
        subQueryRestriction = builder.and(subQueryRestriction, builder.equal(subBusinessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage), businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage)));
        subQueryRestriction = builder.and(subQueryRestriction, builder.equal(subBusinessObjectFormatFileTypeEntity, fileTypeEntity));
        // Create and add standard restrictions on primary and sub-partition values.
        subQueryRestriction = builder.and(subQueryRestriction, getQueryRestrictionOnPartitionValues(builder, subBusinessObjectDataEntity, businessObjectDataEntity));
        // Add restrictions on business object data version and business object data status.
        Predicate subQueryRestrictionOnBusinessObjectDataVersionAndStatus = getQueryRestrictionOnBusinessObjectDataVersionAndStatus(builder, subBusinessObjectDataEntity, subBusinessObjectDataStatusEntity, businessObjectDataKey.getBusinessObjectDataVersion(), businessObjectDataStatus);
        if (subQueryRestrictionOnBusinessObjectDataVersionAndStatus != null) {
            subQueryRestriction = builder.and(subQueryRestriction, subQueryRestrictionOnBusinessObjectDataVersionAndStatus);
        }
        subQuery.select(builder.max(subBusinessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion))).where(subQueryRestriction);
        mainQueryRestriction = builder.and(mainQueryRestriction, builder.in(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion)).value(subQuery));
    }
    // If a data version was not specified, use the latest one as per specified business object data status.
    if (businessObjectDataKey.getBusinessObjectDataVersion() == null) {
        // Since business object data version is not specified, just use the latest one as per specified business object data status.
        if (businessObjectDataStatus != null) {
            // Business object data version is not specified, so get the latest one as per specified business object data status.
            Subquery<Integer> subQuery = criteria.subquery(Integer.class);
            // The criteria root is the business object data.
            Root<BusinessObjectDataEntity> subBusinessObjectDataEntity = subQuery.from(BusinessObjectDataEntity.class);
            // Join to the other tables we can filter on.
            Join<BusinessObjectDataEntity, BusinessObjectDataStatusEntity> subBusinessObjectDataStatusEntity = subBusinessObjectDataEntity.join(BusinessObjectDataEntity_.status);
            Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> subBusinessObjectFormatEntity = subBusinessObjectDataEntity.join(BusinessObjectDataEntity_.businessObjectFormat);
            // Create the standard restrictions (i.e. the standard where clauses).
            Predicate subQueryRestriction = builder.equal(subBusinessObjectFormatEntity, businessObjectFormatEntity);
            // Create and add standard restrictions on primary and sub-partition values.
            subQueryRestriction = builder.and(subQueryRestriction, getQueryRestrictionOnPartitionValues(builder, subBusinessObjectDataEntity, businessObjectDataEntity));
            // Create and add standard restrictions on business object data status.
            subQueryRestriction = builder.and(subQueryRestriction, builder.equal(builder.upper(subBusinessObjectDataStatusEntity.get(BusinessObjectDataStatusEntity_.code)), businessObjectDataStatus.toUpperCase()));
            subQuery.select(builder.max(subBusinessObjectDataEntity.get(BusinessObjectDataEntity_.version))).where(subQueryRestriction);
            mainQueryRestriction = builder.and(mainQueryRestriction, builder.in(businessObjectDataEntity.get(BusinessObjectDataEntity_.version)).value(subQuery));
        } else {
            // Both business object data version and business object data status are not specified, so just use the latest business object data version.
            mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal(businessObjectDataEntity.get(BusinessObjectDataEntity_.latestVersion), true));
        }
    }
    criteria.select(businessObjectDataEntity).where(mainQueryRestriction);
    return executeSingleResultQuery(criteria, String.format("Found more than one business object data instance with parameters {namespace=\"%s\", businessObjectDefinitionName=\"%s\"," + " businessObjectFormatUsage=\"%s\", businessObjectFormatFileType=\"%s\", businessObjectFormatVersion=\"%d\"," + " businessObjectDataPartitionValue=\"%s\", businessObjectDataSubPartitionValues=\"%s\", businessObjectDataVersion=\"%d\"," + " businessObjectDataStatus=\"%s\"}.", businessObjectDataKey.getNamespace(), businessObjectDataKey.getBusinessObjectDefinitionName(), businessObjectDataKey.getBusinessObjectFormatUsage(), businessObjectDataKey.getBusinessObjectFormatFileType(), businessObjectDataKey.getBusinessObjectFormatVersion(), businessObjectDataKey.getPartitionValue(), CollectionUtils.isEmpty(businessObjectDataKey.getSubPartitionValues()) ? "" : StringUtils.join(businessObjectDataKey.getSubPartitionValues(), ","), businessObjectDataKey.getBusinessObjectDataVersion(), businessObjectDataStatus));
}

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

public List<CommunicatorMessage> listThreadsInTrash(UserEntity user, Integer firstResult, Integer maxResults) {
    EntityManager entityManager = getEntityManager();
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<CommunicatorMessage> criteria = criteriaBuilder.createQuery(CommunicatorMessage.class);
    Root<CommunicatorMessageRecipient> root = criteria.from(CommunicatorMessageRecipient.class);
    Join<CommunicatorMessageRecipient, CommunicatorMessage> messageJoin = root.join(CommunicatorMessageRecipient_.communicatorMessage);
    Join<CommunicatorMessage, CommunicatorMessageId> threadJoin = messageJoin.join(CommunicatorMessage_.communicatorMessageId);
    // SubQuery finds the latest date in a thread when linked to the main query 
    // and thus allows finding the latest message in the thread.
    Subquery<Date> subQuery = criteria.subquery(Date.class);
    Root<CommunicatorMessageRecipient> subQueryRoot = subQuery.from(CommunicatorMessageRecipient.class);
    Join<CommunicatorMessageRecipient, CommunicatorMessage> subMessageJoin = subQueryRoot.join(CommunicatorMessageRecipient_.communicatorMessage);
    subQuery.select(criteriaBuilder.greatest(subMessageJoin.get(CommunicatorMessage_.created)));
    subQuery.where(criteriaBuilder.and(criteriaBuilder.equal(subMessageJoin.get(CommunicatorMessage_.communicatorMessageId), messageJoin.get(CommunicatorMessage_.communicatorMessageId)), criteriaBuilder.or(criteriaBuilder.and(criteriaBuilder.equal(subQueryRoot.get(CommunicatorMessageRecipient_.recipient), user.getId()), criteriaBuilder.equal(subQueryRoot.get(CommunicatorMessageRecipient_.trashedByReceiver), Boolean.TRUE), criteriaBuilder.equal(subQueryRoot.get(CommunicatorMessageRecipient_.archivedByReceiver), Boolean.FALSE)), criteriaBuilder.and(criteriaBuilder.equal(subMessageJoin.get(CommunicatorMessage_.sender), user.getId()), criteriaBuilder.equal(subMessageJoin.get(CommunicatorMessage_.trashedBySender), Boolean.TRUE), criteriaBuilder.equal(subMessageJoin.get(CommunicatorMessage_.archivedBySender), Boolean.FALSE)))));
    criteria.select(messageJoin);
    criteria.where(criteriaBuilder.and(criteriaBuilder.equal(messageJoin.get(CommunicatorMessage_.created), subQuery), criteriaBuilder.or(criteriaBuilder.and(criteriaBuilder.equal(root.get(CommunicatorMessageRecipient_.recipient), user.getId()), criteriaBuilder.equal(root.get(CommunicatorMessageRecipient_.trashedByReceiver), Boolean.TRUE), criteriaBuilder.equal(root.get(CommunicatorMessageRecipient_.archivedByReceiver), Boolean.FALSE)), criteriaBuilder.and(criteriaBuilder.equal(messageJoin.get(CommunicatorMessage_.sender), user.getId()), criteriaBuilder.equal(messageJoin.get(CommunicatorMessage_.trashedBySender), Boolean.TRUE), criteriaBuilder.equal(messageJoin.get(CommunicatorMessage_.archivedBySender), Boolean.FALSE)))));
    criteria.groupBy(threadJoin);
    criteria.orderBy(criteriaBuilder.desc(messageJoin.get(CommunicatorMessage_.created)));
    TypedQuery<CommunicatorMessage> query = entityManager.createQuery(criteria);
    query.setFirstResult(firstResult);
    query.setMaxResults(maxResults);
    return query.getResultList();
}

@Override
public long countLastContacts(SearchParameters<Contact> searchParameters) {
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
    Root<Contact> root = query.from(Contact.class);
    Subquery<Contact> subquery = query.subquery(Contact.class);
    Root<Contact> subroot = subquery.from(Contact.class);
    subquery.select(subroot).where(criteriaBuilder.equal(root.get("producer"), subroot.get("producer")), criteriaBuilder.greaterThan(subroot.<LocalDate>get("current"), root.<LocalDate>get("current")));
    Predicate predicate = getPredicate(query, root, criteriaBuilder, searchParameters);
    if (predicate == null) {
        predicate = criteriaBuilder.not(criteriaBuilder.exists(subquery));
    } else {
        predicate = criteriaBuilder.and(predicate, criteriaBuilder.not(criteriaBuilder.exists(subquery)));
    }
    return getEntityManager().createQuery(query.select(criteriaBuilder.count(root)).where(predicate)).getSingleResult();
}

@SuppressWarnings({ "unchecked", "rawtypes" })
@Override
public JPAHead getJPAHead(long timestamp) throws EDBException {
    synchronized (entityManager) {
        LOGGER.debug("Loading head for timestamp {}", timestamp);
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<JPAObject> query = criteriaBuilder.createQuery(JPAObject.class);
        Root<JPAObject> from = query.from(JPAObject.class);
        query.select(from);
        Subquery<Number> subquery = query.subquery(Number.class);
        Root maxTime = subquery.from(JPAObject.class);
        subquery.select(criteriaBuilder.max(maxTime.get("timestamp")));
        Predicate subPredicate1 = criteriaBuilder.le(maxTime.get("timestamp"), timestamp);
        Predicate subPredicate2 = criteriaBuilder.equal(maxTime.get("oid"), from.get("oid"));
        subquery.where(criteriaBuilder.and(subPredicate1, subPredicate2));
        Predicate predicate1 = criteriaBuilder.equal(from.get("timestamp"), subquery);
        Predicate predicate2 = criteriaBuilder.notEqual(from.get("isDeleted"), Boolean.TRUE);
        query.where(criteriaBuilder.and(predicate1, predicate2));
        TypedQuery<JPAObject> typedQuery = entityManager.createQuery(query);
        List<JPAObject> resultList = typedQuery.getResultList();
        JPAHead head = new JPAHead();
        head.setJPAObjects(resultList);
        head.setTimestamp(timestamp);
        return head;
    }
}

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

protected <T> TypedQuery<T> buildCustomerQuery(Date dateCreatedMinThreshold, Boolean registered, Boolean deactivated, Boolean isPreview, Class<T> returnType) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<T> criteria = builder.createQuery(returnType);
    Root<CustomerImpl> root = criteria.from(CustomerImpl.class);
    if (Long.class.equals(returnType)) {
        criteria.select((Selection<? extends T>) builder.count(root));
    } else {
        criteria.select((Selection<? extends T>) root);
    }
    //find only customers that do not have any orders, otherwise a purge would fail because of referential integrity
    Subquery<Long> subquery = criteria.subquery(Long.class);
    Root orderRoot = subquery.from(OrderImpl.class);
    subquery.select(builder.count(orderRoot));
    subquery.where(builder.equal(orderRoot.get("customer"), root));
    List<Predicate> restrictions = new ArrayList<Predicate>();
    restrictions.add(builder.equal(subquery, 0L));
    if (registered != null) {
        if (registered) {
            restrictions.add(builder.isTrue(root.get("registered").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("registered")), builder.isFalse(root.get("registered").as(Boolean.class))));
        }
    }
    if (deactivated != null) {
        if (deactivated) {
            restrictions.add(builder.isTrue(root.get("deactivated").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("deactivated")), builder.isFalse(root.get("deactivated").as(Boolean.class))));
        }
    }
    if (dateCreatedMinThreshold != null) {
        restrictions.add(builder.lessThan(root.get("auditable").get("dateCreated").as(Date.class), dateCreatedMinThreshold));
    }
    if (isPreview != null) {
        if (isPreview) {
            restrictions.add(builder.isTrue(root.get("previewable").get("isPreview").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("previewable").get("isPreview")), builder.isFalse(root.get("previewable").get("isPreview").as(Boolean.class))));
        }
    }
    criteria.where(restrictions.toArray(new Predicate[restrictions.size()]));
    return em.createQuery(criteria);
}

protected <T> TypedQuery<T> buildCustomerQuery(Date dateCreatedMinThreshold, Boolean registered, Boolean deactivated, Boolean isPreview, Class<T> returnType) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<T> criteria = builder.createQuery(returnType);
    Root<CustomerImpl> root = criteria.from(CustomerImpl.class);
    if (Long.class.equals(returnType)) {
        criteria.select((Selection<? extends T>) builder.count(root));
    } else {
        criteria.select((Selection<? extends T>) root);
    }
    //find only customers that do not have any orders, otherwise a purge would fail because of referential integrity
    Subquery<Long> subquery = criteria.subquery(Long.class);
    Root orderRoot = subquery.from(OrderImpl.class);
    subquery.select(builder.count(orderRoot));
    subquery.where(builder.equal(orderRoot.get("customer"), root));
    List<Predicate> restrictions = new ArrayList<Predicate>();
    restrictions.add(builder.equal(subquery, 0L));
    if (registered != null) {
        if (registered) {
            restrictions.add(builder.isTrue(root.get("registered").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("registered")), builder.isFalse(root.get("registered").as(Boolean.class))));
        }
    }
    if (deactivated != null) {
        if (deactivated) {
            restrictions.add(builder.isTrue(root.get("deactivated").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("deactivated")), builder.isFalse(root.get("deactivated").as(Boolean.class))));
        }
    }
    if (dateCreatedMinThreshold != null) {
        restrictions.add(builder.lessThan(root.get("auditable").get("dateCreated").as(Date.class), dateCreatedMinThreshold));
    }
    if (isPreview != null) {
        if (isPreview) {
            restrictions.add(builder.isTrue(root.get("previewable").get("isPreview").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("previewable").get("isPreview")), builder.isFalse(root.get("previewable").get("isPreview").as(Boolean.class))));
        }
    }
    criteria.where(restrictions.toArray(new Predicate[restrictions.size()]));
    return em.createQuery(criteria);
}

protected <T> TypedQuery<T> buildCustomerQuery(Date dateCreatedMinThreshold, Boolean registered, Boolean deactivated, Boolean isPreview, Class<T> returnType) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<T> criteria = builder.createQuery(returnType);
    Root<CustomerImpl> root = criteria.from(CustomerImpl.class);
    if (Long.class.equals(returnType)) {
        criteria.select((Selection<? extends T>) builder.count(root));
    } else {
        criteria.select((Selection<? extends T>) root);
    }
    //find only customers that do not have any orders, otherwise a purge would fail because of referential integrity
    Subquery<Long> subquery = criteria.subquery(Long.class);
    Root orderRoot = subquery.from(OrderImpl.class);
    subquery.select(builder.count(orderRoot));
    subquery.where(builder.equal(orderRoot.get("customer"), root));
    List<Predicate> restrictions = new ArrayList<Predicate>();
    restrictions.add(builder.equal(subquery, 0L));
    if (registered != null) {
        if (registered) {
            restrictions.add(builder.isTrue(root.get("registered").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("registered")), builder.isFalse(root.get("registered").as(Boolean.class))));
        }
    }
    if (deactivated != null) {
        if (deactivated) {
            restrictions.add(builder.isTrue(root.get("deactivated").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("deactivated")), builder.isFalse(root.get("deactivated").as(Boolean.class))));
        }
    }
    if (dateCreatedMinThreshold != null) {
        restrictions.add(builder.lessThan(root.get("auditable").get("dateCreated").as(Date.class), dateCreatedMinThreshold));
    }
    if (isPreview != null) {
        if (isPreview) {
            restrictions.add(builder.isTrue(root.get("previewable").get("isPreview").as(Boolean.class)));
        } else {
            restrictions.add(builder.or(builder.isNull(root.get("previewable").get("isPreview")), builder.isFalse(root.get("previewable").get("isPreview").as(Boolean.class))));
        }
    }
    criteria.where(restrictions.toArray(new Predicate[restrictions.size()]));
    return em.createQuery(criteria);
}

@SuppressWarnings("unchecked")
public static <Q, T> Predicate variableInstanceLogSpecificCreatePredicateFromSingleCriteria(CriteriaQuery<Q> query, CriteriaBuilder builder, QueryCriteria criteria, Root<T> table) {
    Predicate predicate;
    if (LAST_VARIABLE_LIST.equals(criteria.getListId())) {
        Subquery<VariableInstanceLog> maxIdSubQuery = query.subquery(VariableInstanceLog.class);
        Root from = maxIdSubQuery.from(VariableInstanceLog.class);
        maxIdSubQuery.select(builder.max(from.get(VariableInstanceLog_.id)));
        maxIdSubQuery.groupBy(from.get(VariableInstanceLog_.variableId), from.get(VariableInstanceLog_.processInstanceId));
        Attribute varIdField = VariableInstanceLog_.id;
        // TODO: add the current group's criteria list to the subquery, 
        // in order to make the subquery more efficient
        // -- but that requires making the criteria list available here as an argument.. :/
        Expression expr;
        if (varIdField instanceof SingularAttribute) {
            expr = table.get((SingularAttribute<T, ?>) varIdField);
        } else {
            throw new IllegalStateException("Unexpected " + varIdField.getClass().getName() + " when processing last variable query criteria!");
        }
        predicate = builder.in(expr).value(maxIdSubQuery);
    } else if (VAR_VALUE_ID_LIST.equals(criteria.getListId())) {
        assert criteria.getValues().size() == 1 : "Only 1 var id/value parameter expected!";
        // extract var/val information from criteria
        Object varVal = criteria.getValues().get(0);
        String[] parts = ((String) varVal).split(VAR_VAL_SEPARATOR, 2);
        String varId = parts[1].substring(0, Integer.parseInt(parts[0]));
        String val = parts[1].substring(Integer.parseInt(parts[0]) + 1);
        // create predicates
        SingularAttribute varVarIdField = VariableInstanceLog_.variableId;
        Path varVarIdPath = table.get(varVarIdField);
        SingularAttribute varValField = VariableInstanceLog_.value;
        Path varValIdPath = table.get(varValField);
        Predicate varIdPredicate = builder.equal(varVarIdPath, varId);
        Predicate valPredicate;
        if (QueryCriteriaType.REGEXP.equals(criteria.getType())) {
            val = convertRegexToJPALikeExpression(val);
            valPredicate = builder.like(varValIdPath, val);
        } else {
            valPredicate = builder.equal(varValIdPath, val);
        }
        // intersect predicates
        predicate = builder.and(varIdPredicate, valPredicate);
    } else {
        throw new IllegalStateException("List id [" + getQueryParameterIdNameMap().get(Integer.parseInt(criteria.getListId())) + "] is not supported for queries on " + table.getJavaType().getSimpleName() + ".");
    }
    return predicate;
}

public Predicate toPredicate(Root<Info> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    Predicate pred = fs.toPredicate(root, query, cb);
    if (mainNodeId != null) {
        pred = cb.and(pred, cb.equal(root.get("node").get("id"), mainNodeId));
    } else if (nodeId != null) {
        pred = cb.and(pred, cb.equal(root.join("infoNodes").join("node").get("id"), nodeId));
    } else if (StringUtils.isNotBlank(treeNumber)) {
        pred = cb.and(pred, cb.like(root.get("node").<String>get("treeNumber"), treeNumber + "%"));
    } else if (siteId != null) {
        pred = cb.and(pred, cb.equal(root.get("site").get("id"), siteId));
    }
    if (!allInfo) {
        Path<Integer> userPath = root.join("node").join("infoRoleSites").join("role").join("users").<Integer>get("id");
        pred = cb.and(pred, cb.equal(userPath, userId));
        query.distinct(true);
    }
    if (infoRightType == RoleSite.INFO_RIGHT_SELF) {
        pred = cb.and(pred, cb.equal(root.get("creator").<Integer>get("id"), userId));
    }
    if (StringUtils.isNotBlank(status)) {
        if (status.length() == 1) {
            pred = cb.and(pred, cb.equal(root.get("status"), status));
        } else if (status.equals("auditing")) {
            pred = cb.and(pred, cb.between(root.<String>get("status"), "1", "9"));
        } else if (status.equals("pending") || status.equals("notpassed")) {
            boolean rejection = false;
            if (status.equals("notpassed")) {
                rejection = true;
            }
            Subquery<Integer> sq = query.subquery(Integer.class);
            Root<WorkflowProcess> root2 = sq.from(WorkflowProcess.class);
            sq.select(root2.<Integer>get("dataId"));
            sq.where(cb.equal(root2.get("rejection"), rejection), cb.equal(root2.get("type"), 1), cb.equal(root2.get("end"), false), cb.equal(root2.join("processUsers").join("user").get("id"), userId));
            pred = cb.and(pred, cb.in(root.<Integer>get("id")).value(sq));
        }
    }
    return pred;
}

public void testSubqueries1() {
    String jpql = "SELECT goodCustomer FROM Customer goodCustomer WHERE " + "goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) " + " FROM " + "Customer c)";
    CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
    Root<Customer> goodCustomer = q.from(Customer.class);
    Subquery<Double> sq = q.subquery(Double.class);
    Root<Customer> c = sq.from(Customer.class);
    q.where(cb.lt(goodCustomer.get(Customer_.balanceOwed), sq.select(cb.avg(c.get(Customer_.balanceOwed)))));
    q.select(goodCustomer);
    assertEquivalence(q, jpql);
}

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

@Override
public CriteriaQuery<DateDimensionImpl> apply(CriteriaBuilder cb) {
    final CriteriaQuery<DateDimensionImpl> criteriaQuery = cb.createQuery(DateDimensionImpl.class);
    final Root<DateDimensionImpl> dimensionRoot = criteriaQuery.from(DateDimensionImpl.class);
    //Build subquery for max date
    final Subquery<LocalDate> maxDateSub = criteriaQuery.subquery(LocalDate.class);
    final Root<DateDimensionImpl> maxDateDimensionSub = maxDateSub.from(DateDimensionImpl.class);
    maxDateSub.select(cb.greatest(maxDateDimensionSub.get(DateDimensionImpl_.date)));
    //Get the date dimension
    criteriaQuery.select(dimensionRoot).where(cb.equal(dimensionRoot.get(DateDimensionImpl_.date), maxDateSub));
    return criteriaQuery;
}

// subqueries:
/**
     * Create a predicate testing the existence of a subquery result.
     *
     * @param subquery
     *            subquery whose result is to be tested
     * @return exists predicate
     */
@Override
public Predicate exists(Subquery<?> subquery) {
    // Setting SubQuery's SubSelectExpression as a base for the expression created by operator allows setting a new ExpressionBuilder later in the SubSelectExpression (see integrateRoot method in SubQueryImpl).
    return new CompoundExpressionImpl(metamodel, ExpressionOperator.getOperator(Integer.valueOf(ExpressionOperator.Exists)).expressionFor(((SubQueryImpl) subquery).getCurrentNode()), buildList(subquery), "exists");
}

/**
	 * {@inheritDoc}
	 */
@Override
public List<JmxSensorValueData> getJmxDataOverview(JmxSensorValueData template, Date fromDate, Date toDate) {
    if (template == null) {
        return null;
    }
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<JmxSensorValueData> c = cb.createQuery(JmxSensorValueData.class);
    Root<JmxSensorValueData> root = c.from(JmxSensorValueData.class);
    Subquery<Long> sq = c.subquery(Long.class);
    Root<JmxSensorValueData> sqRoot = sq.from(JmxSensorValueData.class);
    Predicate platformIdentPredicate = cb.equal(sqRoot.get("platformIdent"), template.getPlatformIdent());
    Predicate sensorTypeIdentPredicate = cb.equal(sqRoot.get("sensorTypeIdent"), template.getSensorTypeIdent());
    Predicate predicate = cb.and(platformIdentPredicate, sensorTypeIdentPredicate);
    if (template.getJmxSensorDefinitionDataIdentId() > 0) {
        predicate = cb.and(predicate, cb.equal(sqRoot.get("jmxSensorDefinitionDataIdentId"), template.getJmxSensorDefinitionDataIdentId()));
    }
    if ((fromDate != null) && (toDate != null)) {
        predicate = cb.and(predicate, cb.between(sqRoot.get("timeStamp").as(Date.class), fromDate, toDate));
    }
    sq.select(cb.max(sqRoot.get("id").as(Long.class))).where(predicate).groupBy(sqRoot.get("jmxSensorDefinitionDataIdentId"));
    c.select(root).where(cb.in(root.get("id")).value(sq));
    return entityManager.createQuery(c).getResultList();
}

@SuppressWarnings("unchecked")
@Override
public List<ElaboracaoNormativa> buscaPorParametros(Map<String, Object> mapaCampos) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<ElaboracaoNormativa> cq = cb.createQuery(ElaboracaoNormativa.class);
    Root<ElaboracaoNormativa> en = cq.from(ElaboracaoNormativa.class);
    Join<ElaboracaoNormativa, Orgao> oen = en.join("origem", JoinType.LEFT);
    Join<ElaboracaoNormativa, Orgao> ca = en.join("coAutor", JoinType.LEFT);
    Join<ElaboracaoNormativa, StatusSidof> ss = en.join("statusSidof", JoinType.LEFT);
    Join<ElaboracaoNormativa, Equipe> eq = en.join("equipe", JoinType.LEFT);
    Join<ElaboracaoNormativa, Usuario> us = en.join("parecerista", JoinType.LEFT);
    cq.select(cb.construct(ElaboracaoNormativa.class, en.get("id"), en.get("tipo"), en.get("subTipo"), en.get("elaboracaoNormativaSituacao"), en.get("elaboracaoNormativaNorma"), en.get("ano"), en.get("numero"), oen.get("nome"), ca.get("nome"), en.get("ementa"), ss.get("descricao"), en.get("identificacao"), eq.get("nome"), us.get("nome"), en.get("nup"), en.get("dataInclusaoSIDOF"), en.get("dataAssinaturaSIDOF"), en.get("ementaManifestacao"), en.get("dataManifestacao"), en.get("normaGeradaAno"), en.get("normaGeradaNumero")));
    List<Predicate> predicates = new ArrayList<Predicate>();
    if (!Objects.isNull(mapaCampos.get("ano")) && !mapaCampos.get("ano").equals("")) {
        Predicate ano = cb.equal(en.get("ano"), mapaCampos.get("ano"));
        predicates.add(ano);
    }
    if (!Objects.isNull(mapaCampos.get("numero")) && !mapaCampos.get("numero").equals("")) {
        Predicate numero = cb.equal(en.get("numero"), mapaCampos.get("numero"));
        predicates.add(numero);
    }
    if (!Objects.isNull(mapaCampos.get("identificacao")) && !mapaCampos.get("identificacao").equals("")) {
        Predicate identificacao = cb.equal(en.get("identificacao"), ElaboracaoNormativaObjeto.get((String) mapaCampos.get("identificacao")));
        predicates.add(identificacao);
    }
    if (!Objects.isNull(mapaCampos.get("distribuicao")) && ((Long) mapaCampos.get("distribuicao")).compareTo(0L) != 0L) {
        Predicate distribuicao = cb.equal(en.get("distribuicao"), mapaCampos.get("distribuicao"));
        predicates.add(distribuicao);
    }
    if (!Objects.isNull(mapaCampos.get("parecerista")) && ((Long) mapaCampos.get("parecerista")).compareTo(0L) != 0L) {
        Predicate parecerista = cb.equal(en.get("parecerista"), mapaCampos.get("parecerista"));
        predicates.add(parecerista);
    }
    if (!Objects.isNull(mapaCampos.get("statusSidof")) && ((Long) mapaCampos.get("statusSidof")).compareTo(0L) != 0L) {
        Predicate statusSidof = cb.equal(ss.get("id"), mapaCampos.get("statusSidof"));
        predicates.add(statusSidof);
    }
    if (!Objects.isNull(mapaCampos.get("elaboracaoNormativaNorma")) && !mapaCampos.get("elaboracaoNormativaNorma").equals("")) {
        Predicate elaboracaoNormativaNorma = cb.equal(en.get("elaboracaoNormativaNorma"), ElaboracaoNormativaNorma.get((String) mapaCampos.get("elaboracaoNormativaNorma")));
        predicates.add(elaboracaoNormativaNorma);
    }
    if (!Objects.isNull(mapaCampos.get("elaboracaoNormativaSituacao")) && !mapaCampos.get("elaboracaoNormativaSituacao").equals("")) {
        Predicate elaboracaoNormativaSituacao = cb.equal(en.get("elaboracaoNormativaSituacao"), ElaboracaoNormativaSituacao.get((String) mapaCampos.get("elaboracaoNormativaSituacao")));
        predicates.add(elaboracaoNormativaSituacao);
    }
    if (!Objects.isNull(mapaCampos.get("tipo")) && !mapaCampos.get("tipo").equals("")) {
        Predicate tipo = cb.equal(en.get("tipo"), ElaboracaoNormativaTipo.get((String) mapaCampos.get("tipo")));
        predicates.add(tipo);
    }
    if (!Objects.isNull(mapaCampos.get("subTipo")) && !mapaCampos.get("subTipo").equals("")) {
        Predicate subTipo = cb.equal(en.get("subTipo"), ElaboracaoNormativaSubTipo.get((String) mapaCampos.get("subTipo")));
        predicates.add(subTipo);
    }
    if (!Objects.isNull(mapaCampos.get("ementa")) && !mapaCampos.get("ementa").equals("")) {
        Predicate ementa = cb.like(en.<String>get("ementa"), mapaCampos.get("ementa").toString());
        predicates.add(ementa);
    }
    if (!Objects.isNull(mapaCampos.get("listaOrigensSelecionadosDropdown")) && !mapaCampos.get("listaOrigensSelecionadosDropdown").equals("")) {
        List<String> lista = SislegisUtil.jsonArrayToList(mapaCampos.get("listaOrigensSelecionadosDropdown").toString());
        if (!lista.isEmpty()) {
            Predicate listaOrigensSelecionadosDropdown = oen.get("id").in(lista);
            predicates.add(listaOrigensSelecionadosDropdown);
        }
    }
    if (!Objects.isNull(mapaCampos.get("listaTagsSelecionadosDropdown")) && !mapaCampos.get("listaTagsSelecionadosDropdown").equals("")) {
        Subquery<TagElaboracaoNormativa> subqueryTags = cq.subquery(TagElaboracaoNormativa.class);
        @SuppressWarnings("rawtypes") Root fromTagElaboracaoNormativa = subqueryTags.from(TagElaboracaoNormativa.class);
        subqueryTags.select(fromTagElaboracaoNormativa.get("elaboracaoNormativa"));
        Join<TagElaboracaoNormativa, Tag> tag = fromTagElaboracaoNormativa.join("tag", JoinType.INNER);
        List<String> lista = SislegisUtil.jsonArrayToList(mapaCampos.get("listaTagsSelecionadosDropdown").toString());
        if (!lista.isEmpty()) {
            subqueryTags.where(tag.get("tag").in(lista));
            predicates.add(en.get("id").in(subqueryTags));
        }
    }
    if (!Objects.isNull(mapaCampos.get("listaCoAutoresSelecionadosDropdown")) && !mapaCampos.get("listaCoAutoresSelecionadosDropdown").equals("")) {
        List<String> lista = SislegisUtil.jsonArrayToList(mapaCampos.get("listaCoAutoresSelecionadosDropdown").toString());
        if (!lista.isEmpty()) {
            Predicate listaCoAutoresSelecionadosDropdown = ca.get("id").in(lista);
            predicates.add(listaCoAutoresSelecionadosDropdown);
        }
    }
    if (!Objects.isNull(mapaCampos.get("nup")) && !mapaCampos.get("nup").equals("")) {
        Predicate nup = cb.like(en.<String>get("nup"), mapaCampos.get("nup").toString());
        predicates.add(nup);
    }
    cq.where(predicates.toArray(new Predicate[] {}));
    Query query = getEntityManager().createQuery(cq);
    List<ElaboracaoNormativa> result = query.getResultList();
    return result;
}

@Override
public void filtersWillBeAdded(CriteriaBuilder criteriaBuilder, CriteriaQuery<?> query, List<Predicate> predicates) {
    // полный доÑ?туп длÑ? общего управлÑ?ющего
    if (roles.contains(Role.SuperSupervisor)) {
        return;
    }
    String login = Flash.login();
    Set<String> groupNames = getGroupNames(login);
    Root<?> smevTask = query.getRoots().iterator().next();
    Path<String> path = smevTask.get("employee").get("login");
    Predicate loginPredicate = criteriaBuilder.equal(path, login);
    if (!groupNames.isEmpty()) {
        Subquery groups = query.subquery(Long.class);
        Root sameTask = groups.from(SmevTask.class);
        groups.select(sameTask.get("id"));
        groups.where(criteriaBuilder.and(criteriaBuilder.equal(sameTask.get("id"), smevTask.get("id")), sameTask.get("groups").in(groupNames)));
        predicates.add(criteriaBuilder.or(loginPredicate, criteriaBuilder.exists(groups)));
    } else {
        predicates.add(loginPredicate);
    }
}

/**
         * Adds a visibility criteria to the query.
         * 
         * @param whichProjects
         *            criteria to add
         * @return this plan query
         */
public PlanQuery addVisibility(WhichProjects whichProjects) {
    // Select usernames of the users group
    if (whichProjects == WhichProjects.MYPROJECTS) {
        Subquery<User> subquery = cq.subquery(User.class);
        Root<User> fromUser = subquery.from(User.class);
        subquery.select(fromUser.<User>get("username"));
        subquery.where(builder.equal(fromUser.get("userGroup"), user.getUserGroup()));
        visibilityPredicates.add(fromPP.get("owner").in(subquery));
    } else if (whichProjects == WhichProjects.PUBLICPROJECTS) {
        visibilityPredicates.add(builder.or(builder.isFalse(fromPP.<Boolean>get("privateProject")), builder.isTrue(fromPP.<Boolean>get("reportPublic"))));
    } else if (whichProjects == WhichProjects.ALLPROJECTS) {
        if (user.isAdmin()) {
            // Always true
            visibilityPredicates.add(builder.conjunction());
        }
    }
    return this;
}

public List<Student> listBy(String email, List<StudentGroup> groups, Boolean archived, Integer firstResult, Integer maxResults) {
    EntityManager entityManager = getEntityManager();
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Student> criteria = criteriaBuilder.createQuery(Student.class);
    Root<Student> root = criteria.from(Student.class);
    Join<Student, ContactInfo> contactInfoJoin = root.join(Student_.contactInfo);
    List<Predicate> predicates = new ArrayList<>();
    if (StringUtils.isNotBlank(email)) {
        ListJoin<ContactInfo, Email> emailJoin = contactInfoJoin.join(ContactInfo_.emails);
        predicates.add(criteriaBuilder.equal(emailJoin.get(Email_.address), email));
    }
    if (archived != null) {
        predicates.add(criteriaBuilder.equal(root.get(Student_.archived), archived));
    }
    if (groups != null) {
        Subquery<Student> subquery = criteria.subquery(Student.class);
        Root<StudentGroupStudent> studentGroup = subquery.from(StudentGroupStudent.class);
        subquery.select(studentGroup.get(StudentGroupStudent_.student));
        subquery.where(studentGroup.get(StudentGroupStudent_.studentGroup).in(groups));
        predicates.add(root.in(subquery));
    }
    criteria.select(root);
    if (!predicates.isEmpty()) {
        criteria.where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
    }
    TypedQuery<Student> query = entityManager.createQuery(criteria);
    if (firstResult != null) {
        query.setFirstResult(firstResult);
    }
    if (maxResults != null) {
        query.setMaxResults(maxResults);
    }
    return query.getResultList();
}

/**
     * @see org.artificer.common.query.xpath.visitors.XPathVisitor#visit(org.artificer.common.query.xpath.ast.SubartifactSet)
     */
@Override
public void visit(SubartifactSet node) {
    if (node.getFunctionCall() != null) {
        node.getFunctionCall().accept(this);
    } else if (node.getRelationshipPath() != null) {
        From oldRootContext = from;
        if (node.getRelationshipPath().getRelationshipType().equalsIgnoreCase("relatedDocument")) {
            // derivedFrom
            // TODO: Should this really be LEFT?
            from = from.join("derivedFrom", JoinType.LEFT);
            // Now add any additional predicates included.
            if (node.getPredicate() != null) {
                node.getPredicate().accept(this);
            }
        } else if (node.getRelationshipPath().getRelationshipType().equalsIgnoreCase("expandedFromDocument") || node.getRelationshipPath().getRelationshipType().equalsIgnoreCase("expandedFromArchive")) {
            // expandedFrom
            from = from.join("expandedFrom");
            // Now add any additional predicates included.
            if (node.getPredicate() != null) {
                node.getPredicate().accept(this);
            }
        } else {
            // Relationship within a predicate.
            // Create a subquery and 'exists' conditional.  The subquery is much easier to handle, later on, if this
            // predicate is negated, as opposed to removing the inner join or messing with left joins.
            List<Predicate> oldPredicates = predicates;
            predicates = new ArrayList<>();
            Subquery relationshipSubquery = query.subquery(ArtificerRelationship.class);
            relationshipFrom = relationshipSubquery.from(ArtificerRelationship.class);
            targetFrom = relationshipFrom.join("targets");
            relationshipSubquery.select(relationshipFrom.get("id"));
            Join relationshipOwnerJoin = relationshipFrom.join("owner");
            predicates.add(criteriaBuilder.equal(relationshipOwnerJoin.get("id"), oldRootContext.get("id")));
            from = relationshipFrom;
            // process constraints on the relationship itself
            node.getRelationshipPath().accept(this);
            // context now needs to be the relationship targets
            from = targetFrom.join("target");
            // Now add any additional predicates included.
            if (node.getPredicate() != null) {
                node.getPredicate().accept(this);
            }
            // Add predicates to subquery
            relationshipSubquery.where(compileAnd(predicates));
            predicates = oldPredicates;
            // Add 'exists' predicate (using subquery) to original list
            predicates.add(criteriaBuilder.exists(relationshipSubquery));
        }
        // restore the original selector (since the relationship was in a predicate, not a path)
        from = oldRootContext;
        if (node.getSubartifactSet() != null) {
            throw new RuntimeException(Messages.i18n.format("XP_MULTILEVEL_SUBARTYSETS_NOT_SUPPORTED"));
        }
    }
}

private AbstractFrom<?, ?> getAliased(Object q, String alias) {
    final Map<String, AbstractFrom<?, ?>> aliasMap = this.aliasMap.get(q);
    if (aliasMap != null) {
        final AbstractFrom<?, ?> from = aliasMap.get(alias);
        if (from != null) {
            return from;
        }
    }
    if (q instanceof Subquery) {
        final SubqueryImpl<?> s = (SubqueryImpl<?>) q;
        final AbstractFrom<?, ?> aliased = this.getAliased(s.getParent(), alias);
        if (aliased instanceof RootImpl) {
            s.correlate((RootImpl<?>) aliased);
        }
        return aliased;
    }
    throw new PersistenceException("Alias is not bound: " + alias);
}

/**
	 * Add reference predicate to the current search
	 */
private void addPredicateReference(String theResourceName, String theParamName, List<? extends IQueryParameterType> theList) {
    assert theParamName.contains(".") == false;
    if (theList.get(0).getMissing() != null) {
        addPredicateParamMissing(theResourceName, theParamName, theList.get(0).getMissing());
        return;
    }
    Join<ResourceTable, ResourceLink> join = myResourceTableRoot.join("myResourceLinks", JoinType.LEFT);
    List<Predicate> codePredicates = new ArrayList<Predicate>();
    for (IQueryParameterType nextOr : theList) {
        IQueryParameterType params = nextOr;
        if (params instanceof ReferenceParam) {
            ReferenceParam ref = (ReferenceParam) params;
            if (isBlank(ref.getChain())) {
                IIdType dt = new IdDt(ref.getBaseUrl(), ref.getResourceType(), ref.getIdPart(), null);
                if (dt.hasBaseUrl()) {
                    if (myCallingDao.getConfig().getTreatBaseUrlsAsLocal().contains(dt.getBaseUrl())) {
                        dt = dt.toUnqualified();
                    } else {
                        ourLog.debug("Searching for resource link with target URL: {}", dt.getValue());
                        Predicate eq = myBuilder.equal(join.get("myTargetResourceUrl"), dt.getValue());
                        codePredicates.add(eq);
                        continue;
                    }
                }
                List<Long> targetPid;
                try {
                    targetPid = myCallingDao.translateForcedIdToPids(dt);
                } catch (ResourceNotFoundException e) {
                    targetPid = Collections.singletonList(-1L);
                }
                for (Long next : targetPid) {
                    ourLog.debug("Searching for resource link with target PID: {}", next);
                    Predicate pathPredicate = createResourceLinkPathPredicate(theResourceName, theParamName, join);
                    Predicate pidPredicate = myBuilder.equal(join.get("myTargetResourcePid"), next);
                    codePredicates.add(myBuilder.and(pathPredicate, pidPredicate));
                }
            } else {
                List<Class<? extends IBaseResource>> resourceTypes;
                String resourceId;
                if (!ref.getValue().matches("[a-zA-Z]+\\/.*")) {
                    RuntimeResourceDefinition resourceDef = myContext.getResourceDefinition(myResourceType);
                    String paramPath = myCallingDao.getSearchParamByName(resourceDef, theParamName).getPath();
                    if (paramPath.endsWith(".as(Reference)")) {
                        paramPath = paramPath.substring(0, paramPath.length() - ".as(Reference)".length()) + "Reference";
                    }
                    BaseRuntimeChildDefinition def = myContext.newTerser().getDefinition(myResourceType, paramPath);
                    if (def instanceof RuntimeChildChoiceDefinition) {
                        RuntimeChildChoiceDefinition choiceDef = (RuntimeChildChoiceDefinition) def;
                        resourceTypes = choiceDef.getResourceTypes();
                    } else if (def instanceof RuntimeChildResourceDefinition) {
                        RuntimeChildResourceDefinition resDef = (RuntimeChildResourceDefinition) def;
                        resourceTypes = resDef.getResourceTypes();
                    } else {
                        throw new ConfigurationException("Property " + paramPath + " of type " + myResourceName + " is not a resource: " + def.getClass());
                    }
                    resourceId = ref.getValue();
                } else {
                    RuntimeResourceDefinition resDef = myContext.getResourceDefinition(ref.getResourceType());
                    resourceTypes = new ArrayList<Class<? extends IBaseResource>>(1);
                    resourceTypes.add(resDef.getImplementingClass());
                    resourceId = ref.getIdPart();
                }
                boolean foundChainMatch = false;
                String chain = ref.getChain();
                String remainingChain = null;
                int chainDotIndex = chain.indexOf('.');
                if (chainDotIndex != -1) {
                    remainingChain = chain.substring(chainDotIndex + 1);
                    chain = chain.substring(0, chainDotIndex);
                }
                for (Class<? extends IBaseResource> nextType : resourceTypes) {
                    RuntimeResourceDefinition typeDef = myContext.getResourceDefinition(nextType);
                    String subResourceName = typeDef.getName();
                    IFhirResourceDao<?> dao = myCallingDao.getDao(nextType);
                    if (dao == null) {
                        ourLog.debug("Don't have a DAO for type {}", nextType.getSimpleName());
                        continue;
                    }
                    int qualifierIndex = chain.indexOf(':');
                    String qualifier = null;
                    if (qualifierIndex != -1) {
                        qualifier = chain.substring(qualifierIndex);
                        chain = chain.substring(0, qualifierIndex);
                    }
                    boolean isMeta = BaseHapiFhirDao.RESOURCE_META_PARAMS.containsKey(chain);
                    RuntimeSearchParam param = null;
                    if (!isMeta) {
                        param = myCallingDao.getSearchParamByName(typeDef, chain);
                        if (param == null) {
                            ourLog.debug("Type {} doesn't have search param {}", nextType.getSimpleName(), param);
                            continue;
                        }
                    }
                    IQueryParameterType chainValue;
                    if (remainingChain != null) {
                        if (param == null || param.getParamType() != RestSearchParameterTypeEnum.REFERENCE) {
                            ourLog.debug("Type {} parameter {} is not a reference, can not chain {}", new Object[] { nextType.getSimpleName(), chain, remainingChain });
                            continue;
                        }
                        chainValue = new ReferenceParam();
                        chainValue.setValueAsQueryToken(myContext, theParamName, qualifier, resourceId);
                        ((ReferenceParam) chainValue).setChain(remainingChain);
                    } else if (isMeta) {
                        IQueryParameterType type = BaseHapiFhirDao.newInstanceType(chain);
                        type.setValueAsQueryToken(myContext, theParamName, qualifier, resourceId);
                        chainValue = type;
                    } else {
                        chainValue = toParameterType(param, qualifier, resourceId);
                    }
                    foundChainMatch = true;
                    Subquery<Long> subQ = myResourceTableQuery.subquery(Long.class);
                    Root<ResourceTable> subQfrom = subQ.from(ResourceTable.class);
                    subQ.select(subQfrom.get("myId").as(Long.class));
                    List<List<? extends IQueryParameterType>> andOrParams = new ArrayList<List<? extends IQueryParameterType>>();
                    andOrParams.add(Collections.singletonList(chainValue));
                    /*
						 * We're doing a chain call, so push the current query root
						 * and predicate list down and put new ones at the top of the
						 * stack and run a subuery
						 */
                    Root<ResourceTable> stackRoot = myResourceTableRoot;
                    ArrayList<Predicate> stackPredicates = myPredicates;
                    myResourceTableRoot = subQfrom;
                    myPredicates = new ArrayList<Predicate>();
                    // Create the subquery predicates
                    myPredicates.add(myBuilder.equal(myResourceTableRoot.get("myResourceType"), subResourceName));
                    myPredicates.add(myBuilder.isNull(myResourceTableRoot.get("myDeleted")));
                    searchForIdsWithAndOr(subResourceName, chain, andOrParams);
                    subQ.where(toArray(myPredicates));
                    /*
						 * Pop the old query root and predicate list back
						 */
                    myResourceTableRoot = stackRoot;
                    myPredicates = stackPredicates;
                    Predicate pathPredicate = createResourceLinkPathPredicate(theResourceName, theParamName, join);
                    Predicate pidPredicate = join.get("myTargetResourcePid").in(subQ);
                    codePredicates.add(myBuilder.and(pathPredicate, pidPredicate));
                }
                if (!foundChainMatch) {
                    throw new InvalidRequestException(myContext.getLocalizer().getMessage(BaseHapiFhirResourceDao.class, "invalidParameterChain", theParamName + '.' + ref.getChain()));
                }
            }
        } else {
            throw new IllegalArgumentException("Invalid token type (expecting ReferenceParam): " + params.getClass());
        }
    }
    myPredicates.add(myBuilder.or(toArray(codePredicates)));
}

@Override
public <V extends Relationship> List<V> fetchQueryResults(IdentityContext context, RelationshipQuery<V> query) {
    EntityManager entityManager = getEntityManager(context);
    List entities = new ArrayList();
    Object[] identityParameterValues = query.getParameter(Relationship.IDENTITY);
    if (identityParameterValues != null) {
        for (Object parameterValue : identityParameterValues) {
            if (IdentityType.class.isInstance(parameterValue)) {
                entities = findIdentityTypeRelationships(context, query.getRelationshipClass(), (IdentityType) parameterValue);
            } else {
                throw MESSAGES.queryUnsupportedParameterValue("Relationship.IDENTITY", parameterValue);
            }
        }
    } else {
        Class<V> relationshipType = query.getRelationshipClass();
        EntityMapper entityMapper = getRootMapper(relationshipType);
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<?> cq = cb.createQuery(entityMapper.getEntityType());
        Root root = cq.from(entityMapper.getEntityType());
        List<Predicate> predicates = new ArrayList<Predicate>();
        Property typeProperty = entityMapper.getProperty(RelationshipClass.class).getValue();
        if (!Relationship.class.equals(relationshipType)) {
            predicates.add(cb.equal(root.get(typeProperty.getName()), relationshipType.getName()));
        }
        Object[] idParameterValues = query.getParameter(Relationship.ID);
        Property idProperty = entityMapper.getProperty(Identifier.class).getValue();
        if (idParameterValues != null && idParameterValues.length > 0) {
            predicates.add(cb.equal(root.get(idProperty.getName()), idParameterValues[0]));
        } else {
            for (Entry<QueryParameter, Object[]> entry : query.getParameters().entrySet()) {
                QueryParameter queryParameter = entry.getKey();
                Object[] values = entry.getValue();
                if (queryParameter instanceof RelationshipQueryParameter) {
                    RelationshipQueryParameter identityTypeParameter = (RelationshipQueryParameter) entry.getKey();
                    List<String> identityTypeIdentifiers = new ArrayList<String>();
                    EntityMapper relationshipMemberMapper = getEntityMapperForProperty(relationshipType, RelationshipMember.class);
                    for (Object object : values) {
                        IdentityType identityType = (IdentityType) object;
                        if (identityType == null) {
                            return Collections.emptyList();
                        }
                        Property<Object> identityTypeProperty = relationshipMemberMapper.getProperty(RelationshipMember.class).getValue();
                        if (identityTypeProperty.getJavaClass().equals(String.class)) {
                            identityTypeIdentifiers.add(IdentityTypeUtil.formatId(identityType));
                        } else {
                            identityTypeIdentifiers.add(identityType.getId());
                        }
                    }
                    Property<Object> relationshipProperty = relationshipMemberMapper.getProperty(OwnerReference.class).getValue();
                    Subquery<?> subQuery = cq.subquery(relationshipMemberMapper.getEntityType());
                    Root fromRelationshipIdentityType = subQuery.from(relationshipMemberMapper.getEntityType());
                    subQuery.select(fromRelationshipIdentityType.get(relationshipProperty.getName()).get(idProperty.getName()));
                    List<Predicate> subQueryPredicates = new ArrayList<Predicate>();
                    Property<String> descriptorProperty = relationshipMemberMapper.getProperty(RelationshipDescriptor.class).getValue();
                    subQueryPredicates.add(cb.equal(fromRelationshipIdentityType.get(descriptorProperty.getName()), identityTypeParameter.getName()));
                    Property<Object> identityProperty = relationshipMemberMapper.getProperty(RelationshipMember.class).getValue();
                    if (identityProperty.getJavaClass().equals(String.class)) {
                        subQueryPredicates.add(fromRelationshipIdentityType.get(identityProperty.getName()).in(identityTypeIdentifiers));
                    } else {
                        Join join = fromRelationshipIdentityType.join(identityProperty.getName());
                        EntityMapper identityTypeMapper = getMapperForEntity(identityProperty.getJavaClass());
                        Property identifierProperty = identityTypeMapper.getProperty(Identifier.class).getValue();
                        subQueryPredicates.add(join.get(identifierProperty.getName()).in(identityTypeIdentifiers));
                    }
                    subQuery.where(subQueryPredicates.toArray(new Predicate[subQueryPredicates.size()]));
                    predicates.add(cb.in(root.get(idProperty.getName())).value(subQuery));
                } else if (AttributeParameter.class.equals(entry.getKey().getClass())) {
                    AttributeParameter attributeParameter = (AttributeParameter) entry.getKey();
                    Object[] parameterValues = entry.getValue();
                    EntityMapper parameterEntityMapper = getEntityMapperForProperty(relationshipType, attributeParameter.getName());
                    if (parameterEntityMapper != null) {
                        Root<?> propertyEntityJoin = root;
                        Property ownerProperty = parameterEntityMapper.getProperty(relationshipType, OwnerReference.class).getValue();
                        if (ownerProperty.getJavaClass().equals(entityMapper.getEntityType())) {
                            propertyEntityJoin = cq.from(parameterEntityMapper.getEntityType());
                            predicates.add(cb.and(cb.equal(propertyEntityJoin.get(ownerProperty.getName()), root)));
                        }
                        Object parameterValue = parameterValues[0];
                        Property mappedProperty = parameterEntityMapper.getProperty(relationshipType, attributeParameter.getName()).getValue();
                        if (isMappedType(mappedProperty.getJavaClass())) {
                            AttributedType ownerType = (AttributedType) parameterValue;
                            if (ownerType != null) {
                                parameterValue = entityManager.find(mappedProperty.getJavaClass(), ownerType.getId());
                            }
                        }
                        predicates.add(cb.equal(propertyEntityJoin.get(mappedProperty.getName()), parameterValue));
                    } else {
                        addAttributeQueryPredicates(relationshipType, entityManager, cb, cq, root, predicates, attributeParameter, null, parameterValues);
                    }
                }
            }
        }
        cq.select(root);
        cq.where(predicates.toArray(new Predicate[predicates.size()]));
        entities = entityManager.createQuery(cq).getResultList();
    }
    List<V> result = new ArrayList<V>();
    for (Object relationshipObject : entities) {
        result.add(this.<V>convertToRelationshipType(context, relationshipObject));
    }
    return result;
}

@Override
public <U> Subquery<U> subquery(Class<U> type) {
    return null;
}

@Override
public <U> Subquery<U> subquery(Class<U> arg0) {
    // TODO Auto-generated method stub
    return null;
}

public <U> Subquery<U> subquery(Class<U> type) {
    // TODO Auto-generated method stub
    return null;
}

public Subquery<E> getSubQuery() {
    if (predicate != null) {
        subQuery.where(predicate);
    }
    return subQuery;
}

@Override
public <U> Subquery<U> subquery(Class<U> type) {
    // TODO Auto-generated method stub
    return null;
}

/**
     * Creates a specification where {@link LineStop#id} must be in a list provided by a sub-query.
     *
     * @param subQuery returning a list of Id's
     * @return a predicate
     */
public static Specification<LineStop> idsIn(Subquery<Long> subQuery) {
    return ( root,  query,  builder) -> builder.in(root.get(LineStop_.id)).value(subQuery);
}

@Nonnull
@Override
public Subquery<V> apply(final CriteriaBuilder builder) {
    return criteria.subquery(attribute.getType().getJavaType());
}