Examples with Subquery javax.persistence.criteria.Subquery used on opensource projects

Example 6 with Subquery

use of javax.persistence.criteria.Subquery in project hibernate-orm by hibernate.

the class InPredicate method render.

@Override
public String render(boolean isNegated, RenderingContext renderingContext) {
    final StringBuilder buffer = new StringBuilder();
    final Expression exp = getExpression();
    if (ParameterExpressionImpl.class.isInstance(exp)) {
        // technically we only need to CAST (afaik) if expression and all values are parameters.
        // but checking for that condition could take long time on a lon value list
        final ParameterExpressionImpl parameterExpression = (ParameterExpressionImpl) exp;
        final SessionFactoryImplementor sfi = criteriaBuilder().getEntityManagerFactory().unwrap(SessionFactoryImplementor.class);
        final Type mappingType = sfi.getTypeResolver().heuristicType(parameterExpression.getParameterType().getName());
        buffer.append("cast(").append(parameterExpression.render(renderingContext)).append(" as ").append(mappingType.getName()).append(")");
    } else {
        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();
}

Example 7 with Subquery

use of javax.persistence.criteria.Subquery in project microservices by pwillhan.

the class Subselects method executeQueries.

@Test
public void executeQueries() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    CriteriaBuilder cb = JPA.getEntityManagerFactory().getCriteriaBuilder();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        {
            // Correlated
            CriteriaQuery criteria = cb.createQuery();
            Root<User> u = criteria.from(User.class);
            Subquery<Long> sq = criteria.subquery(Long.class);
            Root<Item> i = sq.from(Item.class);
            sq.select(cb.count(i)).where(cb.equal(i.get("seller"), u));
            criteria.select(u);
            criteria.where(cb.greaterThan(sq, 1L));
            Query q = em.createQuery(criteria);
            List<User> result = q.getResultList();
            assertEquals(result.size(), 1);
            User user = result.iterator().next();
            assertEquals(user.getId(), testData.users.getFirstId());
        }
        em.clear();
        {
            // Uncorrelated
            CriteriaQuery criteria = cb.createQuery();
            Root<Bid> b = criteria.from(Bid.class);
            Subquery<BigDecimal> sq = criteria.subquery(BigDecimal.class);
            Root<Bid> b2 = sq.from(Bid.class);
            sq.select(cb.max(b2.<BigDecimal>get("amount")));
            criteria.select(b);
            criteria.where(cb.greaterThanOrEqualTo(cb.sum(b.<BigDecimal>get("amount"), new BigDecimal(1)), sq));
            Query q = em.createQuery(criteria);
            List<Bid> result = q.getResultList();
            assertEquals(result.size(), 2);
        }
        em.clear();
        {
            // Exists
            CriteriaQuery criteria = cb.createQuery();
            Root<Item> i = criteria.from(Item.class);
            Subquery<Bid> sq = criteria.subquery(Bid.class);
            Root<Bid> b = sq.from(Bid.class);
            sq.select(b).where(cb.equal(b.get("item"), i));
            criteria.select(i);
            criteria.where(cb.exists(sq));
            Query q = em.createQuery(criteria);
            List<Item> result = q.getResultList();
            assertEquals(result.size(), 2);
        }
        em.clear();
        {
            // Quantify ALL
            CriteriaQuery criteria = cb.createQuery();
            Root<Item> i = criteria.from(Item.class);
            Subquery<BigDecimal> sq = criteria.subquery(BigDecimal.class);
            Root<Bid> b = sq.from(Bid.class);
            sq.select(b.<BigDecimal>get("amount"));
            sq.where(cb.equal(b.get("item"), i));
            criteria.select(i);
            criteria.where(cb.greaterThanOrEqualTo(cb.literal(new BigDecimal(10)), cb.all(sq)));
            Query q = em.createQuery(criteria);
            List<Item> result = q.getResultList();
            assertEquals(result.size(), 2);
        }
        em.clear();
        {
            // Quantify ANY
            CriteriaQuery criteria = cb.createQuery();
            Root<Item> i = criteria.from(Item.class);
            Subquery<BigDecimal> sq = criteria.subquery(BigDecimal.class);
            Root<Bid> b = sq.from(Bid.class);
            sq.select(b.<BigDecimal>get("amount"));
            sq.where(cb.equal(b.get("item"), i));
            criteria.select(i);
            criteria.where(cb.equal(cb.literal(new BigDecimal("101.00")), cb.any(sq)));
            Query q = em.createQuery(criteria);
            List<Item> result = q.getResultList();
            assertEquals(result.size(), 1);
        }
        em.clear();
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 8 with Subquery

use of javax.persistence.criteria.Subquery in project CzechIdMng by bcvsolutions.

the class DefaultIdmAutomaticRoleAttributeService method getPredicateForRuleByContract.

/**
 * Return predicate for given rule by contract
 *
 * @param rule
 * @param root
 * @param query
 * @param cb
 * @return
 */
private Predicate getPredicateForRuleByContract(IdmAutomaticRoleAttributeRuleDto rule, Root<IdmIdentityContract> root, CriteriaQuery<?> query, CriteriaBuilder cb, boolean pass) {
    // 
    Metamodel metamodel = entityManager.getMetamodel();
    if (rule.getType() == AutomaticRoleAttributeRuleType.CONTRACT) {
        SingularAttribute<? super IdmIdentityContract, ?> singularAttribute = metamodel.entity(IdmIdentityContract.class).getSingularAttribute(rule.getAttributeName());
        Path<Object> path = root.get(singularAttribute.getName());
        // role will be added and wich roles will be removed.
        return getPredicateWithComparsion(path, castToType(singularAttribute, rule.getValue(), rule.getComparison()), cb, rule.getComparison(), !pass);
    } else if (rule.getType() == AutomaticRoleAttributeRuleType.CONTRACT_EAV) {
        IdmFormAttributeDto formAttributeDto = formAttributeService.get(rule.getFormAttribute());
        AutomaticRoleAttributeRuleComparison comparison = rule.getComparison();
        // Cast given value to specific persistent type
        // For is empty and is not empty comparison is returned null even if value exists
        Object value = getFormValue(rule.getValue(), formAttributeDto, comparison);
        // 
        // For contract form attribute was composed only one subquery
        Subquery<IdmIdentityContractFormValue> subquery = query.subquery(IdmIdentityContractFormValue.class);
        Root<IdmIdentityContractFormValue> subRoot = subquery.from(IdmIdentityContractFormValue.class);
        subquery.select(subRoot);
        // 
        Path<?> path = subRoot.get(getSingularAttributeForEav(formAttributeDto.getPersistentType()));
        // Is empty comparison has specific behavior because form value isn't empty, but value doesn't exist
        if (comparison == AutomaticRoleAttributeRuleComparison.IS_EMPTY) {
            subquery.where(cb.or(// Predicate for check if value exists
            getPredicateForNullFormAttributeIdentityContract(root, query, cb, formAttributeDto), cb.and(cb.equal(subRoot.get(IdmIdentityContractFormValue_.owner), root), cb.equal(subRoot.get(IdmIdentityContractFormValue_.formAttribute).get(AbstractFormValue_.id), formAttributeDto.getId()), getPredicateWithComparsion(path, null, cb, rule.getComparison(), null))));
            if (pass) {
                return cb.not(cb.exists(subquery));
            }
            return cb.exists(subquery);
        }
        // 
        subquery.where(cb.and(cb.equal(subRoot.get(IdmIdentityContractFormValue_.owner), root), cb.equal(subRoot.get(IdmIdentityContractFormValue_.formAttribute).get(AbstractFormValue_.id), formAttributeDto.getId()), getPredicateWithComparsion(path, value, cb, rule.getComparison(), null)));
        // 
        Predicate existsInEav = getPredicateForConnection(subquery, cb, pass, formAttributeDto.isMultiple());
        // For comparison with not is required also check null values
        if (comparison == AutomaticRoleAttributeRuleComparison.NOT_CONTAINS || comparison == AutomaticRoleAttributeRuleComparison.NOT_END_WITH || comparison == AutomaticRoleAttributeRuleComparison.NOT_EQUALS || comparison == AutomaticRoleAttributeRuleComparison.NOT_START_WITH) {
            if (pass) {
                existsInEav = cb.or(existsInEav, cb.not(getPredicateForNullFormAttributeIdentityContract(root, query, cb, formAttributeDto)));
            } else {
                existsInEav = cb.and(existsInEav, getPredicateForNullFormAttributeIdentityContract(root, query, cb, formAttributeDto));
            }
        }
        // 
        return existsInEav;
    } else if (rule.getType() == AutomaticRoleAttributeRuleType.IDENTITY_EAV) {
        IdmFormAttributeDto formAttributeDto = formAttributeService.get(rule.getFormAttribute());
        AutomaticRoleAttributeRuleComparison comparison = rule.getComparison();
        // Cast given value to specific persistent type
        // For is empty and is not empty comparison is returned null even if value exists
        Object value = getFormValue(rule.getValue(), formAttributeDto, comparison);
        // 
        // Rules for identity form values must contains two subquery identity -> identity eav
        Subquery<IdmIdentity> subquery = query.subquery(IdmIdentity.class);
        Root<IdmIdentity> subRoot = subquery.from(IdmIdentity.class);
        subquery.select(subRoot);
        // 
        Subquery<IdmIdentityFormValue> subQueryIdentityEav = query.subquery(IdmIdentityFormValue.class);
        Root<IdmIdentityFormValue> subRootIdentityEav = subQueryIdentityEav.from(IdmIdentityFormValue.class);
        subQueryIdentityEav.select(subRootIdentityEav);
        // 
        Path<?> path = subRootIdentityEav.get(getSingularAttributeForEav(formAttributeDto.getPersistentType()));
        // Is empty comparison has specific behavior because form value isn't empty, but value doesn't exist
        if (comparison == AutomaticRoleAttributeRuleComparison.IS_EMPTY) {
            subquery.where(cb.and(cb.equal(root.get(IdmIdentityContract_.identity), subRoot), cb.or(cb.exists(subQueryIdentityEav.where(cb.and(cb.equal(subRootIdentityEav.get(IdmIdentityFormValue_.owner), subRoot), cb.equal(subRootIdentityEav.get(IdmIdentityFormValue_.formAttribute).get(AbstractFormValue_.id), formAttributeDto.getId()), getPredicateWithComparsion(path, null, cb, rule.getComparison(), null)))), // Predicate for check if value exists
            getPredicateForNullFormAttributeIdentity(subRoot, subquery, cb, formAttributeDto))));
            // 
            if (pass) {
                return cb.not(cb.exists(subquery));
            }
            return cb.exists(subquery);
        }
        // 
        subQueryIdentityEav.where(cb.and(cb.equal(subRootIdentityEav.get(IdmIdentityFormValue_.owner), subRoot), cb.equal(root.get(IdmIdentityContract_.identity), subRoot), cb.equal(subRootIdentityEav.get(IdmIdentityFormValue_.formAttribute).get(AbstractFormValue_.id), formAttributeDto.getId()), getPredicateWithComparsion(path, value, cb, rule.getComparison(), null)));
        Predicate existsInEav = getPredicateForConnection(subQueryIdentityEav, cb, pass, formAttributeDto.isMultiple());
        // For comparison with not is required also check null values
        if (comparison == AutomaticRoleAttributeRuleComparison.NOT_CONTAINS || comparison == AutomaticRoleAttributeRuleComparison.NOT_END_WITH || comparison == AutomaticRoleAttributeRuleComparison.NOT_EQUALS || comparison == AutomaticRoleAttributeRuleComparison.NOT_START_WITH) {
            if (pass) {
                existsInEav = cb.or(existsInEav, cb.not(getPredicateForNullFormAttributeIdentity(subRoot, subquery, cb, formAttributeDto)));
            } else {
                existsInEav = cb.and(existsInEav, getPredicateForNullFormAttributeIdentity(subRoot, subquery, cb, formAttributeDto));
            }
        }
        // 
        subquery.where(cb.and(cb.equal(subRoot.get(IdmIdentity_.id), root.get(IdmIdentityContract_.identity).get(AbstractEntity_.id)), existsInEav));
        // 
        return cb.exists(subquery);
    } else if (rule.getType() == AutomaticRoleAttributeRuleType.IDENTITY) {
        Subquery<IdmIdentity> subquery = query.subquery(IdmIdentity.class);
        Root<IdmIdentity> subRoot = subquery.from(IdmIdentity.class);
        subquery.select(subRoot);
        // 
        SingularAttribute<? super IdmIdentity, ?> singularAttribute = metamodel.entity(IdmIdentity.class).getSingularAttribute(rule.getAttributeName());
        Path<Object> path = subRoot.get(singularAttribute.getName());
        // 
        subquery.where(// correlation attr
        cb.and(// correlation attr
        cb.equal(subRoot.get(IdmIdentity_.id), root.get(IdmIdentityContract_.identity).get(AbstractEntity_.id)), getPredicateWithComparsion(path, castToType(singularAttribute, rule.getValue(), rule.getComparison()), cb, rule.getComparison(), null)));
        // 
        return getPredicateForConnection(subquery, cb, pass, false);
    } else {
        throw new UnsupportedOperationException("Type: " + rule.getType().name() + ", isn't supported for contract rules!");
    }
}