Example usage for javax.persistence.criteria Subquery select

List of usage examples for javax.persistence.criteria Subquery select

  1. HOME
  2. Java
  3. javax
  4. javax.persistence.criteria.*
  5. Subquery
  6. select

Introduction

In this page you can find the example usage for javax.persistence.criteria Subquery select.

Prototype

Subquery<T> select(Expression<T> expression);

Source Link

Document

Specify the item that is to be returned as the subquery result.

Usage

From source file:hr.diskobolos.persistence.impl.EvaluationAnswerPersistenceImpl.java

@Override
public Long fetchNumberOfMemberRegistersWithoutTerms() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    Root<MemberRegister> memberRegister = cq.from(MemberRegister.class);
    Subquery<Long> sq = cq.subquery(Long.class);
    Root<EvaluationAnswer> evaluationAnswer = sq.from(EvaluationAnswer.class);
    Join<EvaluationAnswer, QuestionChoicesDef> choiceDef = evaluationAnswer.join(EvaluationAnswer_.answer);
    Join<QuestionChoicesDef, EvaluationQuestionDef> questionDef = choiceDef
            .join(QuestionChoicesDef_.evaluationQuestionDef);
    ParameterExpression<QuestionnaireType> questionnaireType = cb.parameter(QuestionnaireType.class,
            "questionnaireType");
    sq.select(evaluationAnswer.get("memberRegister").get("id"))
            .where(cb.equal(questionDef.get(EvaluationQuestionDef_.questionnaireType), questionnaireType));
    cq.select(cb.count(memberRegister.get("id"))).where(cb.not(cb.in(memberRegister.get("id")).value(sq)));
    TypedQuery<Long> query = entityManager.createQuery(cq);
    query.setParameter("questionnaireType", QuestionnaireType.TERMS_OF_CONDITION);
    return query.getSingleResult();
}

From source file:net.groupbuy.dao.impl.ProductDaoImpl.java

public List<Product> findList(ProductCategory productCategory, Brand brand, Promotion promotion, List<Tag> tags,
        Map<Attribute, String> attributeValue, BigDecimal startPrice, BigDecimal endPrice, Boolean isMarketable,
        Boolean isList, Boolean isTop, Boolean isGift, Boolean isOutOfStock, Boolean isStockAlert,
        OrderType orderType, Integer count, List<Filter> filters, List<Order> orders) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
    Root<Product> root = criteriaQuery.from(Product.class);
    criteriaQuery.select(root);/*from   www . j av  a  2s. c  o  m*/
    Predicate restrictions = criteriaBuilder.conjunction();
    if (productCategory != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.or(criteriaBuilder.equal(root.get("productCategory"), productCategory),
                        criteriaBuilder.like(root.get("productCategory").<String>get("treePath"),
                                "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                        + ProductCategory.TREE_PATH_SEPARATOR + "%")));
    }
    if (brand != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand));
    }
    if (promotion != null) {
        Subquery<Product> subquery1 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot1 = subquery1.from(Product.class);
        subquery1.select(subqueryRoot1);
        subquery1.where(criteriaBuilder.equal(subqueryRoot1, root),
                criteriaBuilder.equal(subqueryRoot1.join("promotions"), promotion));

        Subquery<Product> subquery2 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot2 = subquery2.from(Product.class);
        subquery2.select(subqueryRoot2);
        subquery2.where(criteriaBuilder.equal(subqueryRoot2, root),
                criteriaBuilder.equal(subqueryRoot2.join("productCategory").join("promotions"), promotion));

        Subquery<Product> subquery3 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot3 = subquery3.from(Product.class);
        subquery3.select(subqueryRoot3);
        subquery3.where(criteriaBuilder.equal(subqueryRoot3, root),
                criteriaBuilder.equal(subqueryRoot3.join("brand").join("promotions"), promotion));

        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.exists(subquery1),
                criteriaBuilder.exists(subquery2), criteriaBuilder.exists(subquery3)));
    }
    if (tags != null && !tags.isEmpty()) {
        Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot = subquery.from(Product.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
    }
    if (attributeValue != null) {
        for (Entry<Attribute, String> entry : attributeValue.entrySet()) {
            String propertyName = Product.ATTRIBUTE_VALUE_PROPERTY_NAME_PREFIX
                    + entry.getKey().getPropertyIndex();
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.equal(root.get(propertyName), entry.getValue()));
        }
    }
    if (startPrice != null && endPrice != null && startPrice.compareTo(endPrice) > 0) {
        BigDecimal temp = startPrice;
        startPrice = endPrice;
        endPrice = temp;
    }
    if (startPrice != null && startPrice.compareTo(new BigDecimal(0)) >= 0) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.ge(root.<Number>get("price"), startPrice));
    }
    if (endPrice != null && endPrice.compareTo(new BigDecimal(0)) >= 0) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.le(root.<Number>get("price"), endPrice));
    }
    if (isMarketable != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
    }
    if (isList != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
    }
    if (isTop != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
    }
    if (isGift != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isGift"), isGift));
    }
    Path<Integer> stock = root.get("stock");
    Path<Integer> allocatedStock = root.get("allocatedStock");
    if (isOutOfStock != null) {
        if (isOutOfStock) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock),
                    criteriaBuilder.greaterThan(stock, allocatedStock)));
        }
    }
    if (isStockAlert != null) {
        Setting setting = SettingUtils.get();
        if (isStockAlert) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
        } else {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
        }
    }
    criteriaQuery.where(restrictions);
    if (orderType == OrderType.priceAsc) {
        orders.add(Order.asc("price"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.priceDesc) {
        orders.add(Order.desc("price"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.salesDesc) {
        orders.add(Order.desc("sales"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.scoreDesc) {
        orders.add(Order.desc("score"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.dateDesc) {
        orders.add(Order.desc("createDate"));
    } else {
        orders.add(Order.desc("isTop"));
        orders.add(Order.desc("modifyDate"));
    }
    return super.findList(criteriaQuery, null, count, filters, orders);
}

From source file:net.groupbuy.dao.impl.ProductDaoImpl.java

public Page<Product> findPage(ProductCategory productCategory, Brand brand, Promotion promotion, List<Tag> tags,
        Map<Attribute, String> attributeValue, BigDecimal startPrice, BigDecimal endPrice, Boolean isMarketable,
        Boolean isList, Boolean isTop, Boolean isGift, Boolean isOutOfStock, Boolean isStockAlert,
        OrderType orderType, Pageable pageable) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
    Root<Product> root = criteriaQuery.from(Product.class);
    criteriaQuery.select(root);//from  ww  w . ja v  a2 s  .  c  o  m
    Predicate restrictions = criteriaBuilder.conjunction();
    if (productCategory != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.or(criteriaBuilder.equal(root.get("productCategory"), productCategory),
                        criteriaBuilder.like(root.get("productCategory").<String>get("treePath"),
                                "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                        + ProductCategory.TREE_PATH_SEPARATOR + "%")));
    }
    if (brand != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand));
    }
    if (promotion != null) {
        Subquery<Product> subquery1 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot1 = subquery1.from(Product.class);
        subquery1.select(subqueryRoot1);
        subquery1.where(criteriaBuilder.equal(subqueryRoot1, root),
                criteriaBuilder.equal(subqueryRoot1.join("promotions"), promotion));

        Subquery<Product> subquery2 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot2 = subquery2.from(Product.class);
        subquery2.select(subqueryRoot2);
        subquery2.where(criteriaBuilder.equal(subqueryRoot2, root),
                criteriaBuilder.equal(subqueryRoot2.join("productCategory").join("promotions"), promotion));

        Subquery<Product> subquery3 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot3 = subquery3.from(Product.class);
        subquery3.select(subqueryRoot3);
        subquery3.where(criteriaBuilder.equal(subqueryRoot3, root),
                criteriaBuilder.equal(subqueryRoot3.join("brand").join("promotions"), promotion));

        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.exists(subquery1),
                criteriaBuilder.exists(subquery2), criteriaBuilder.exists(subquery3)));
    }
    if (tags != null && !tags.isEmpty()) {
        Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot = subquery.from(Product.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
    }
    if (attributeValue != null) {
        for (Entry<Attribute, String> entry : attributeValue.entrySet()) {
            String propertyName = Product.ATTRIBUTE_VALUE_PROPERTY_NAME_PREFIX
                    + entry.getKey().getPropertyIndex();
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.equal(root.get(propertyName), entry.getValue()));
        }
    }
    if (startPrice != null && endPrice != null && startPrice.compareTo(endPrice) > 0) {
        BigDecimal temp = startPrice;
        startPrice = endPrice;
        endPrice = temp;
    }
    if (startPrice != null && startPrice.compareTo(new BigDecimal(0)) >= 0) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.ge(root.<Number>get("price"), startPrice));
    }
    if (endPrice != null && endPrice.compareTo(new BigDecimal(0)) >= 0) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.le(root.<Number>get("price"), endPrice));
    }
    if (isMarketable != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
    }
    if (isList != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
    }
    if (isTop != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
    }
    if (isGift != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isGift"), isGift));
    }
    Path<Integer> stock = root.get("stock");
    Path<Integer> allocatedStock = root.get("allocatedStock");
    if (isOutOfStock != null) {
        if (isOutOfStock) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock),
                    criteriaBuilder.greaterThan(stock, allocatedStock)));
        }
    }
    if (isStockAlert != null) {
        Setting setting = SettingUtils.get();
        if (isStockAlert) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
        } else {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
        }
    }
    criteriaQuery.where(restrictions);
    List<Order> orders = pageable.getOrders();
    if (orderType == OrderType.priceAsc) {
        orders.add(Order.asc("price"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.priceDesc) {
        orders.add(Order.desc("price"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.salesDesc) {
        orders.add(Order.desc("sales"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.scoreDesc) {
        orders.add(Order.desc("score"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.dateDesc) {
        orders.add(Order.desc("createDate"));
    } else {
        orders.add(Order.desc("isTop"));
        orders.add(Order.desc("modifyDate"));
    }
    return super.findPage(criteriaQuery, pageable);
}

From source file:gov.guilin.dao.impl.ProductDaoImpl.java

public Page<Product> findPage(ProductCategory productCategory, Brand brand, Promotion promotion, List<Tag> tags,
        Map<Attribute, String> attributeValue, BigDecimal startPrice, BigDecimal endPrice, Boolean isMarketable,
        Boolean isList, Boolean isTop, Boolean isGift, Boolean isOutOfStock, Boolean isStockAlert,
        OrderType orderType, Pageable pageable, Set<Supplier> suppliers) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
    Root<Product> root = criteriaQuery.from(Product.class);
    criteriaQuery.select(root);//from  w  w w.j  a va 2  s.  com
    Predicate restrictions = criteriaBuilder.conjunction();
    if (productCategory != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.or(criteriaBuilder.equal(root.get("productCategory"), productCategory),
                        criteriaBuilder.like(root.get("productCategory").<String>get("treePath"),
                                "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                        + ProductCategory.TREE_PATH_SEPARATOR + "%")));
    }
    if (brand != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand));
    }
    if (promotion != null) {
        Subquery<Product> subquery1 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot1 = subquery1.from(Product.class);
        subquery1.select(subqueryRoot1);
        subquery1.where(criteriaBuilder.equal(subqueryRoot1, root),
                criteriaBuilder.equal(subqueryRoot1.join("promotions"), promotion));

        Subquery<Product> subquery2 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot2 = subquery2.from(Product.class);
        subquery2.select(subqueryRoot2);
        subquery2.where(criteriaBuilder.equal(subqueryRoot2, root),
                criteriaBuilder.equal(subqueryRoot2.join("productCategory").join("promotions"), promotion));

        Subquery<Product> subquery3 = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot3 = subquery3.from(Product.class);
        subquery3.select(subqueryRoot3);
        subquery3.where(criteriaBuilder.equal(subqueryRoot3, root),
                criteriaBuilder.equal(subqueryRoot3.join("brand").join("promotions"), promotion));

        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.exists(subquery1),
                criteriaBuilder.exists(subquery2), criteriaBuilder.exists(subquery3)));
    }
    if (tags != null && !tags.isEmpty()) {
        Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
        Root<Product> subqueryRoot = subquery.from(Product.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
    }
    if (attributeValue != null) {
        for (Entry<Attribute, String> entry : attributeValue.entrySet()) {
            String propertyName = Product.ATTRIBUTE_VALUE_PROPERTY_NAME_PREFIX
                    + entry.getKey().getPropertyIndex();
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.equal(root.get(propertyName), entry.getValue()));
        }
    }
    if (startPrice != null && endPrice != null && startPrice.compareTo(endPrice) > 0) {
        BigDecimal temp = startPrice;
        startPrice = endPrice;
        endPrice = temp;
    }
    if (startPrice != null && startPrice.compareTo(new BigDecimal(0)) >= 0) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.ge(root.<Number>get("price"), startPrice));
    }
    if (endPrice != null && endPrice.compareTo(new BigDecimal(0)) >= 0) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.le(root.<Number>get("price"), endPrice));
    }
    if (isMarketable != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
    }
    if (isList != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
    }
    if (isTop != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
    }
    if (isGift != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isGift"), isGift));
    }
    Path<Integer> stock = root.get("stock");
    Path<Integer> allocatedStock = root.get("allocatedStock");
    if (isOutOfStock != null) {
        if (isOutOfStock) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock),
                    criteriaBuilder.greaterThan(stock, allocatedStock)));
        }
    }
    if (isStockAlert != null) {
        Setting setting = SettingUtils.get();
        if (isStockAlert) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                    criteriaBuilder.lessThanOrEqualTo(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
        } else {
            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock,
                            criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
        }
    }

    //??ADDbyDanielChen 20140502
    if ((suppliers != null) && !(suppliers.isEmpty())) {
        Expression<Supplier> exp = root.get("supplier");
        restrictions = criteriaBuilder.and(restrictions, exp.in(suppliers));
    }

    criteriaQuery.where(restrictions);
    List<Order> orders = pageable.getOrders();
    if (orderType == OrderType.priceAsc) {
        orders.add(Order.asc("price"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.priceDesc) {
        orders.add(Order.desc("price"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.salesDesc) {
        orders.add(Order.desc("sales"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.scoreDesc) {
        orders.add(Order.desc("score"));
        orders.add(Order.desc("createDate"));
    } else if (orderType == OrderType.dateDesc) {
        orders.add(Order.desc("createDate"));
    } else {
        orders.add(Order.desc("isTop"));
        orders.add(Order.desc("modifyDate"));
    }
    return super.findPage(criteriaQuery, pageable);
}

From source file:com.yunguchang.data.ApplicationRepository.java

public List<TRsDriverinfoEntity> listAllCandidateDrivers(String[] applicationIds, String carId, String keyword,
        Integer offset, Integer limit, PrincipalExt principalExt) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<TRsDriverinfoEntity> cq = cb.createQuery(TRsDriverinfoEntity.class);
    Root<TRsDriverinfoEntity> driverRoot = cq.from(TRsDriverinfoEntity.class);
    Root<TAzCarinfoEntity> carRoot = cq.from(TAzCarinfoEntity.class);
    cq.select(driverRoot);/*from   w  w  w.ja v a  2  s  . c om*/

    Subquery<TBusScheduleCarEntity> overlapScheduleCarSubQuery = cq.subquery(TBusScheduleCarEntity.class);

    applyOverlapScheduleCarSubquery(overlapScheduleCarSubQuery, applicationIds, carRoot, driverRoot, cb,
            principalExt);

    Subquery<TRsDriverinfoEntity> subqueryLicense = cq.subquery(TRsDriverinfoEntity.class);
    Root<TAzJzRelaEntity> licenseMapping = subqueryLicense.from(TAzJzRelaEntity.class);
    subqueryLicense.select(driverRoot);
    subqueryLicense.where(

            cb.equal(carRoot.get(TAzCarinfoEntity_.xszcx), licenseMapping.get(TAzJzRelaEntity_.xszcx)),
            cb.equal(licenseMapping.get(TAzJzRelaEntity_.jzyq),
                    driverRoot.get(TRsDriverinfoEntity_.drivecartype)));

    Predicate predicate = cb.and(cb.equal(carRoot.get(TAzCarinfoEntity_.id), carId),
            cb.equal(driverRoot.get(TRsDriverinfoEntity_.department), carRoot.get(TAzCarinfoEntity_.sysOrg)),
            cb.equal(driverRoot.get(TRsDriverinfoEntity_.enabled), "1"),
            cb.or(cb.exists(subqueryLicense), cb.isNull(driverRoot.get(TRsDriverinfoEntity_.drivecartype))

            ),

            cb.not(cb.exists(overlapScheduleCarSubQuery))

    );

    if (keyword != null) {
        predicate = cb.and(predicate,
                cb.or(cb.like(driverRoot.get(TRsDriverinfoEntity_.drivername), "%" + keyword + "%")));
    }

    cq.where(predicate);
    cq.orderBy(cb
            .asc(cb.function("casttogbk", String.class, cb.trim(driverRoot.get(TRsDriverinfoEntity_.drivername))

    )));
    TypedQuery<TRsDriverinfoEntity> query = em.createQuery(cq);
    if (offset != null) {
        query.setFirstResult(offset);
    }

    if (limit != null) {
        query.setMaxResults(limit);
    }

    return query.getResultList();

}

From source file:com.yunguchang.data.ApplicationRepository.java

private Subquery<TBusScheduleCarEntity> applyOverlapScheduleCarSubquery(
        Subquery<TBusScheduleCarEntity> overlapScheduleCarSubQuery, String[] applicationIds,
        Root<TAzCarinfoEntity> carRoot, Root<TRsDriverinfoEntity> driverRoot, CriteriaBuilder cb,
        PrincipalExt principalExt) {/*from w ww  .j  av  a 2s. c om*/
    Root<TBusScheduleCarEntity> subScheduleCarRoot = overlapScheduleCarSubQuery
            .from(TBusScheduleCarEntity.class);
    overlapScheduleCarSubQuery.select(subScheduleCarRoot);

    Path<DateTime> scheduleStartTime = subScheduleCarRoot.get(TBusScheduleCarEntity_.schedule)
            .get(TBusScheduleRelaEntity_.starttime);
    Path<DateTime> scheduleEndTime = subScheduleCarRoot.get(TBusScheduleCarEntity_.schedule)
            .get(TBusScheduleRelaEntity_.endtime);

    DateTime applicationStartTime = null;
    DateTime applicationEndTime = null;
    for (String applicationId : applicationIds) {
        TBusApplyinfoEntity applicationEntity = getApplicationById(applicationId, principalExt);
        if (applicationEntity == null) {
            throw logger.entityNotFound(TBusApplyinfoEntity.class, applicationId);
        }
        if (applicationStartTime == null || applicationEndTime.isAfter(applicationEntity.getBegintime())) {
            applicationStartTime = applicationEntity.getBegintime();
        }
        if (applicationEndTime == null || applicationEndTime.isBefore(applicationEntity.getEndtime())) {
            applicationEndTime = applicationEntity.getEndtime();
        }

    }

    if (applicationStartTime == null || applicationEndTime == null) {
        throw logger.invalidApplication(Arrays.deepToString(applicationIds));
    }

    Predicate predicate = cb.and(
            cb.or(cb.and(cb.between(scheduleStartTime, applicationStartTime, applicationEndTime)),
                    cb.and(cb.between(scheduleEndTime, applicationStartTime, applicationEndTime)),
                    cb.and(cb.lessThan(scheduleStartTime, applicationStartTime),
                            cb.greaterThan(scheduleEndTime, applicationEndTime))),
            subScheduleCarRoot.get(TBusScheduleCarEntity_.status).in(ScheduleStatus.AWAITING.id()));
    if (driverRoot != null) {
        predicate = cb.and(predicate,
                cb.and(cb.equal(subScheduleCarRoot.get(TBusScheduleCarEntity_.car), carRoot),
                        cb.equal(subScheduleCarRoot.get(TBusScheduleCarEntity_.driver), driverRoot)));
    } else {
        predicate = cb.and(predicate, cb.equal(subScheduleCarRoot.get(TBusScheduleCarEntity_.car), carRoot));
    }

    overlapScheduleCarSubQuery.where(predicate);
    return overlapScheduleCarSubQuery;
}

From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java

private void addPredicateParamMissingResourceLink(String joinName, String theParamName) {
    CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = builder.createQuery(Long.class);
    Root<ResourceTable> from = cq.from(ResourceTable.class);
    cq.select(from.get("myId").as(Long.class));

    Subquery<Long> subQ = cq.subquery(Long.class);
    Root<ResourceLink> subQfrom = subQ.from(ResourceLink.class);
    subQ.select(subQfrom.get("mySourceResourcePid").as(Long.class));

    // subQ.where(builder.equal(subQfrom.get("myParamName"), theParamName));
    Predicate path = createResourceLinkPathPredicate(theParamName, subQfrom);
    subQ.where(path);/*from w  w  w .  ja v  a2 s.c om*/

    List<Predicate> predicates = new ArrayList<Predicate>();
    createPredicateResourceId(builder, cq, predicates, from.get("myId").as(Long.class));
    predicates.add(builder.not(builder.in(from.get("myId")).value(subQ)));
    predicates.add(builder.equal(from.get("myResourceType"), myResourceName));

    cq.where(builder.and(toArray(predicates)));

    TypedQuery<Long> q = myEntityManager.createQuery(cq);
    List<Long> resultList = q.getResultList();
    doSetPids(new HashSet<Long>(resultList));
}

From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java

private void createPredicateResourceId(CriteriaBuilder builder, CriteriaQuery<?> cq,
        List<Predicate> thePredicates, Expression<Long> theExpression) {
    if (myParams.isPersistResults()) {
        if (mySearchEntity.getTotalCount() > -1) {
            Subquery<Long> subQ = cq.subquery(Long.class);
            Root<SearchResult> subQfrom = subQ.from(SearchResult.class);
            subQ.select(subQfrom.get("myResourcePid").as(Long.class));
            Predicate subQname = builder.equal(subQfrom.get("mySearch"), mySearchEntity);
            subQ.where(subQname);/*from w w w.  j  a v a2  s  . com*/

            thePredicates.add(theExpression.in(subQ));
        }
    } else {
        if (myPids != null) {
            thePredicates.add(theExpression.in(myPids));
        }
    }

}

From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java

private void addPredicateParamMissing(String joinName, String theParamName,
        Class<? extends BaseResourceIndexedSearchParam> theParamTable) {
    CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = builder.createQuery(Long.class);
    Root<ResourceTable> from = cq.from(ResourceTable.class);
    cq.select(from.get("myId").as(Long.class));

    Subquery<Long> subQ = cq.subquery(Long.class);
    Root<? extends BaseResourceIndexedSearchParam> subQfrom = subQ.from(theParamTable);
    subQ.select(subQfrom.get("myResourcePid").as(Long.class));
    Predicate subQname = builder.equal(subQfrom.get("myParamName"), theParamName);
    Predicate subQtype = builder.equal(subQfrom.get("myResourceType"), myResourceName);
    subQ.where(builder.and(subQtype, subQname));

    List<Predicate> predicates = new ArrayList<Predicate>();
    predicates.add(builder.not(builder.in(from.get("myId")).value(subQ)));
    predicates.add(builder.equal(from.get("myResourceType"), myResourceName));
    predicates.add(builder.isNull(from.get("myDeleted")));
    createPredicateResourceId(builder, cq, predicates, from.get("myId").as(Long.class));

    cq.where(builder.and(toArray(predicates)));

    ourLog.info("Adding :missing qualifier for parameter '{}'", theParamName);

    TypedQuery<Long> q = myEntityManager.createQuery(cq);
    doSetPids(q.getResultList());/*w w  w  . j a  v a2 s.c o  m*/
}

From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java

private void addPredicateTag(List<List<? extends IQueryParameterType>> theList, String theParamName,
        DateRangeParam theLastUpdated) {
    TagTypeEnum tagType;//  w  ww  . jav  a 2 s .  c  o m
    if (Constants.PARAM_TAG.equals(theParamName)) {
        tagType = TagTypeEnum.TAG;
    } else if (Constants.PARAM_PROFILE.equals(theParamName)) {
        tagType = TagTypeEnum.PROFILE;
    } else if (Constants.PARAM_SECURITY.equals(theParamName)) {
        tagType = TagTypeEnum.SECURITY_LABEL;
    } else {
        throw new IllegalArgumentException("Param name: " + theParamName); // shouldn't happen
    }

    /*
     * CriteriaBuilder builder = myEntityManager.getCriteriaBuilder(); CriteriaQuery<Long> cq =
     * builder.createQuery(Long.class); Root<ResourceTable> from = cq.from(ResourceTable.class);
     * cq.select(from.get("myId").as(Long.class));
     * 
     * Subquery<Long> subQ = cq.subquery(Long.class); Root<? extends BaseResourceIndexedSearchParam> subQfrom =
     * subQ.from(theParamTable); subQ.select(subQfrom.get("myResourcePid").as(Long.class));
     * Predicate subQname = builder.equal(subQfrom.get("myParamName"), theParamName); Predicate subQtype =
     * builder.equal(subQfrom.get("myResourceType"), myResourceName);
     * subQ.where(builder.and(subQtype, subQname));
     * 
     * List<Predicate> predicates = new ArrayList<Predicate>();
     * predicates.add(builder.not(builder.in(from.get("myId")).value(subQ)));
     * predicates.add(builder.equal(from.get("myResourceType"),
     * myResourceName)); predicates.add(builder.isNull(from.get("myDeleted"))); createPredicateResourceId(builder, cq,
     * predicates, from.get("myId").as(Long.class));
     */

    List<Pair<String, String>> notTags = Lists.newArrayList();
    for (List<? extends IQueryParameterType> nextAndParams : theList) {
        for (IQueryParameterType nextOrParams : nextAndParams) {
            if (nextOrParams instanceof TokenParam) {
                TokenParam param = (TokenParam) nextOrParams;
                if (param.getModifier() == TokenParamModifier.NOT) {
                    if (isNotBlank(param.getSystem()) || isNotBlank(param.getValue())) {
                        notTags.add(Pair.of(param.getSystem(), param.getValue()));
                    }
                }
            }
        }
    }

    /*
     * We have a parameter of ResourceType?_tag:not=foo This means match resources that don't have the given tag(s)
     */
    if (notTags.isEmpty() == false) {
        // CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();
        // CriteriaQuery<Long> cq = builder.createQuery(Long.class);
        // Root<ResourceTable> from = cq.from(ResourceTable.class);
        // cq.select(from.get("myId").as(Long.class));
        //
        // Subquery<Long> subQ = cq.subquery(Long.class);
        // Root<ResourceTag> subQfrom = subQ.from(ResourceTag.class);
        // subQ.select(subQfrom.get("myResourceId").as(Long.class));
        // Predicate subQname = builder.equal(subQfrom.get("myParamName"), theParamName);
        // Predicate subQtype = builder.equal(subQfrom.get("myResourceType"), myResourceName);
        // subQ.where(builder.and(subQtype, subQname));
        //
        // List<Predicate> predicates = new ArrayList<Predicate>();
        // predicates.add(builder.not(builder.in(from.get("myId")).value(subQ)));
        // predicates.add(builder.equal(from.get("myResourceType"), myResourceName));
        // predicates.add(builder.isNull(from.get("myDeleted")));
        // createPredicateResourceId(builder, cq, predicates, from.get("myId").as(Long.class));
    }

    for (List<? extends IQueryParameterType> nextAndParams : theList) {
        boolean haveTags = false;
        for (IQueryParameterType nextParamUncasted : nextAndParams) {
            if (nextParamUncasted instanceof TokenParam) {
                TokenParam nextParam = (TokenParam) nextParamUncasted;
                if (isNotBlank(nextParam.getValue())) {
                    haveTags = true;
                } else if (isNotBlank(nextParam.getSystem())) {
                    throw new InvalidRequestException("Invalid " + theParamName
                            + " parameter (must supply a value/code and not just a system): "
                            + nextParam.getValueAsQueryToken(myContext));
                }
            } else {
                UriParam nextParam = (UriParam) nextParamUncasted;
                if (isNotBlank(nextParam.getValue())) {
                    haveTags = true;
                }
            }
        }
        if (!haveTags) {
            continue;
        }

        CriteriaBuilder builder = myEntityManager.getCriteriaBuilder();

        boolean paramInverted = false;
        List<Pair<String, String>> tokens = Lists.newArrayList();
        for (IQueryParameterType nextOrParams : nextAndParams) {
            String code;
            String system;
            if (nextOrParams instanceof TokenParam) {
                TokenParam nextParam = (TokenParam) nextOrParams;
                code = nextParam.getValue();
                system = nextParam.getSystem();
                if (nextParam.getModifier() == TokenParamModifier.NOT) {
                    paramInverted = true;
                }
            } else {
                UriParam nextParam = (UriParam) nextOrParams;
                code = nextParam.getValue();
                system = null;
            }

            if (isNotBlank(code)) {
                tokens.add(Pair.of(system, code));
            }
        }

        if (tokens.isEmpty()) {
            continue;
        }

        if (paramInverted) {
            ourLog.debug("Searching for _tag:not");

            CriteriaQuery<Long> cq = builder.createQuery(Long.class);
            Root<ResourceTable> newFrom = cq.from(ResourceTable.class);

            Subquery<Long> subQ = cq.subquery(Long.class);
            Root<ResourceTag> subQfrom = subQ.from(ResourceTag.class);
            subQ.select(subQfrom.get("myResourceId").as(Long.class));

            cq.select(newFrom.get("myId").as(Long.class));

            List<Predicate> andPredicates = new ArrayList<Predicate>();
            andPredicates = new ArrayList<Predicate>();
            andPredicates.add(builder.equal(newFrom.get("myResourceType"), myResourceName));
            andPredicates.add(builder.not(builder.in(newFrom.get("myId")).value(subQ)));

            Subquery<Long> defJoin = subQ.subquery(Long.class);
            Root<TagDefinition> defJoinFrom = defJoin.from(TagDefinition.class);
            defJoin.select(defJoinFrom.get("myId").as(Long.class));

            subQ.where(subQfrom.get("myTagId").as(Long.class).in(defJoin));

            List<Predicate> orPredicates = createPredicateTagList(defJoinFrom, builder, tagType, tokens);
            defJoin.where(toArray(orPredicates));

            cq.where(toArray(andPredicates));

            TypedQuery<Long> q = myEntityManager.createQuery(cq);
            Set<Long> pids = new HashSet<Long>(q.getResultList());
            doSetPids(pids);
            continue;
        }

        CriteriaQuery<Long> cq = builder.createQuery(Long.class);
        Root<ResourceTag> from = cq.from(ResourceTag.class);
        List<Predicate> andPredicates = new ArrayList<Predicate>();
        andPredicates.add(builder.equal(from.get("myResourceType"), myResourceName));
        From<ResourceTag, TagDefinition> defJoin = from.join("myTag");

        Join<?, ResourceTable> defJoin2 = from.join("myResource");

        Predicate notDeletedPredicatePrediate = builder.isNull(defJoin2.get("myDeleted"));
        andPredicates.add(notDeletedPredicatePrediate);

        List<Predicate> orPredicates = createPredicateTagList(defJoin, builder, tagType, tokens);
        andPredicates.add(builder.or(toArray(orPredicates)));

        if (theLastUpdated != null) {
            andPredicates.addAll(createLastUpdatedPredicates(theLastUpdated, builder, defJoin2));
        }

        createPredicateResourceId(builder, cq, andPredicates, from.get("myResourceId").as(Long.class));
        Predicate masterCodePredicate = builder.and(toArray(andPredicates));

        cq.select(from.get("myResourceId").as(Long.class));
        cq.where(masterCodePredicate);

        TypedQuery<Long> q = myEntityManager.createQuery(cq);
        Set<Long> pids = new HashSet<Long>(q.getResultList());
        doSetPids(pids);
    }

}