Example usage for javax.persistence.criteria CriteriaBuilder and

List of usage examples for javax.persistence.criteria CriteriaBuilder and

Introduction

In this page you can find the example usage for javax.persistence.criteria CriteriaBuilder and.

Prototype

Predicate and(Expression<Boolean> x, Expression<Boolean> y);

Source Link

Document

Create a conjunction of the given boolean expressions.

Usage

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  w  w  w.j  ava2 s  .co 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:net.shopxx.dao.impl.ArticleDaoImpl.java

public List<Article> findList(ArticleCategory articleCategory, Tag tag, Boolean isPublication, Integer count,
        List<Filter> filters, List<Order> orders) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);/*from   ww  w.  j a va2s.com*/
    Predicate restrictions = criteriaBuilder.conjunction();
    if (articleCategory != null) {
        Subquery<ArticleCategory> subquery = criteriaQuery.subquery(ArticleCategory.class);
        Root<ArticleCategory> subqueryRoot = subquery.from(ArticleCategory.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.or(criteriaBuilder.equal(subqueryRoot, articleCategory),
                criteriaBuilder.like(subqueryRoot.<String>get("treePath"),
                        "%" + ArticleCategory.TREE_PATH_SEPARATOR + articleCategory.getId()
                                + ArticleCategory.TREE_PATH_SEPARATOR + "%")));
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.in(root.get("articleCategory")).value(subquery));
    }
    if (tag != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("tags"), tag));
    }
    if (isPublication != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isPublication"), isPublication));
    }
    criteriaQuery.where(restrictions);
    if (orders == null || orders.isEmpty()) {
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")),
                criteriaBuilder.desc(root.get("createDate")));
    }
    return super.findList(criteriaQuery, null, count, filters, orders);
}

From source file:net.shopxx.dao.impl.ArticleDaoImpl.java

public Page<Article> findPage(ArticleCategory articleCategory, Tag tag, Boolean isPublication,
        Pageable pageable) {/*from ww  w .j a va 2  s . com*/
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);
    Predicate restrictions = criteriaBuilder.conjunction();
    if (articleCategory != null) {
        Subquery<ArticleCategory> subquery = criteriaQuery.subquery(ArticleCategory.class);
        Root<ArticleCategory> subqueryRoot = subquery.from(ArticleCategory.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.or(criteriaBuilder.equal(subqueryRoot, articleCategory),
                criteriaBuilder.like(subqueryRoot.<String>get("treePath"),
                        "%" + ArticleCategory.TREE_PATH_SEPARATOR + articleCategory.getId()
                                + ArticleCategory.TREE_PATH_SEPARATOR + "%")));
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.in(root.get("articleCategory")).value(subquery));
    }
    if (tag != null) {
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("tags"), tag));
    }
    if (isPublication != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isPublication"), isPublication));
    }
    criteriaQuery.where(restrictions);
    if (pageable == null
            || ((StringUtils.isEmpty(pageable.getOrderProperty()) || pageable.getOrderDirection() == null)
                    && CollectionUtils.isEmpty(pageable.getOrders()))) {
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")),
                criteriaBuilder.desc(root.get("createDate")));
    }
    return super.findPage(criteriaQuery, pageable);
}

From source file:net.shopxx.dao.impl.ArticleDaoImpl.java

public List<Article> findList(ArticleCategory articleCategory, Boolean isPublication,
        Article.GenerateMethod generateMethod, Date beginDate, Date endDate, Integer first, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Article> criteriaQuery = criteriaBuilder.createQuery(Article.class);
    Root<Article> root = criteriaQuery.from(Article.class);
    criteriaQuery.select(root);//from   w w w.java 2  s  .c o m
    Predicate restrictions = criteriaBuilder.conjunction();
    if (articleCategory != null) {
        Subquery<ArticleCategory> subquery = criteriaQuery.subquery(ArticleCategory.class);
        Root<ArticleCategory> subqueryRoot = subquery.from(ArticleCategory.class);
        subquery.select(subqueryRoot);
        subquery.where(criteriaBuilder.or(criteriaBuilder.equal(subqueryRoot, articleCategory),
                criteriaBuilder.like(subqueryRoot.<String>get("treePath"),
                        "%" + ArticleCategory.TREE_PATH_SEPARATOR + articleCategory.getId()
                                + ArticleCategory.TREE_PATH_SEPARATOR + "%")));
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.in(root.get("articleCategory")).value(subquery));
    }
    if (isPublication != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("isPublication"), isPublication));
    }
    if (generateMethod != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.equal(root.get("generateMethod"), generateMethod));
    }
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
    }
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
    }
    criteriaQuery.where(restrictions);
    return super.findList(criteriaQuery, first, count, null, null);
}

From source file:org.openlmis.fulfillment.repository.custom.impl.OrderRepositoryImpl.java

private <T> CriteriaQuery<T> prepareQuery(CriteriaQuery<T> query, OrderSearchParams params,
        Set<UUID> processingPeriodIds, Pageable pageable, boolean count, Set<UUID> availableSupplyingFacilities,
        Set<UUID> availableRequestingFacilities) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    Root<Order> root = query.from(Order.class);

    if (count) {/*  ww w . j  a  va  2 s  . c  o m*/
        CriteriaQuery<Long> countQuery = (CriteriaQuery<Long>) query;
        query = (CriteriaQuery<T>) countQuery.select(builder.count(root));
    }

    Predicate predicate = builder.conjunction();
    predicate = isEqual(SUPPLYING_FACILITY_ID, params.getSupplyingFacilityId(), root, predicate, builder);
    predicate = isEqual(REQUESTING_FACILITY_ID, params.getRequestingFacilityId(), root, predicate, builder);

    if (!(isEmpty(availableSupplyingFacilities) && isEmpty(availableRequestingFacilities))) {
        Predicate orPredicate = builder.disjunction();
        orPredicate = isOneOfOr(SUPPLYING_FACILITY_ID, availableSupplyingFacilities, root, orPredicate,
                builder);
        orPredicate = isOneOfOr(REQUESTING_FACILITY_ID, availableRequestingFacilities, root, orPredicate,
                builder);
        predicate = builder.and(predicate, orPredicate);
    }

    predicate = isEqual(PROGRAM_ID, params.getProgramId(), root, predicate, builder);
    predicate = isOneOf(PROCESSING_PERIOD_ID, processingPeriodIds, root, predicate, builder);
    predicate = isOneOf(ORDER_STATUS, params.getStatusAsEnum(), root, predicate, builder);

    query.where(predicate);

    if (!count && pageable != null && pageable.getSort() != null) {
        query = addSortProperties(query, root, pageable);
    }

    return query;
}

From source file:com.sammyun.dao.impl.BaseDaoImpl.java

private void addRestrictions(CriteriaQuery<T> criteriaQuery, List<Filter> filters) {
    if (criteriaQuery == null || filters == null || filters.isEmpty()) {
        return;//from w w w.ja  v  a 2  s  .com
    }
    Root<T> root = getRoot(criteriaQuery);
    if (root == null) {
        return;
    }
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    Predicate restrictions = criteriaQuery.getRestriction() != null ? criteriaQuery.getRestriction()
            : criteriaBuilder.conjunction();
    for (Filter filter : filters) {
        if (filter == null || StringUtils.isEmpty(filter.getProperty())) {
            continue;
        }

        /**  */
        if (filter.getMold() == Mold.dl || filter.getMold() == Mold.dg) {
            if (filter.getOperator() == Operator.lt && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.lessThan(root.<Date>get(filter.getProperty()),
                                DateUtil.parseDate(filter.getValue().toString())));
            } else if (filter.getOperator() == Operator.gt && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.greaterThan(root.<Date>get(filter.getProperty()),
                                DateUtil.parseDate(filter.getValue().toString())));
            } else if (filter.getOperator() == Operator.le && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.lessThanOrEqualTo(root.<Date>get(filter.getProperty()),
                                DateUtil.parseDate(filter.getValue().toString())));
            } else if (filter.getOperator() == Operator.ge && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.greaterThanOrEqualTo(root.<Date>get(filter.getProperty()),
                                DateUtil.parseDate(filter.getValue().toString())));
            }

        } else {

            if (filter.getOperator() == Operator.eq && filter.getValue() != null) {
                if (filter.getIgnoreCase() != null && filter.getIgnoreCase()
                        && filter.getValue() instanceof String) {
                    restrictions = criteriaBuilder.and(restrictions,
                            criteriaBuilder.equal(criteriaBuilder.lower(root.<String>get(filter.getProperty())),
                                    ((String) filter.getValue()).toLowerCase()));
                } else {
                    restrictions = criteriaBuilder.and(restrictions,
                            criteriaBuilder.equal(root.get(filter.getProperty()), filter.getValue()));
                }
            } else if (filter.getOperator() == Operator.ne && filter.getValue() != null) {
                if (filter.getIgnoreCase() != null && filter.getIgnoreCase()
                        && filter.getValue() instanceof String) {
                    restrictions = criteriaBuilder.and(restrictions,
                            criteriaBuilder.notEqual(
                                    criteriaBuilder.lower(root.<String>get(filter.getProperty())),
                                    ((String) filter.getValue()).toLowerCase()));
                } else {
                    restrictions = criteriaBuilder.and(restrictions,
                            criteriaBuilder.notEqual(root.get(filter.getProperty()), filter.getValue()));
                }
            } else if (filter.getOperator() == Operator.gt && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.gt(root.<Number>get(filter.getProperty()), (Number) filter.getValue()));

            } else if (filter.getOperator() == Operator.lt && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.lt(root.<Number>get(filter.getProperty()), (Number) filter.getValue()));
            } else if (filter.getOperator() == Operator.ge && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.ge(root.<Number>get(filter.getProperty()), (Number) filter.getValue()));
            } else if (filter.getOperator() == Operator.le && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.le(root.<Number>get(filter.getProperty()), (Number) filter.getValue()));
            } else if (filter.getOperator() == Operator.like && filter.getValue() != null
                    && filter.getValue() instanceof String) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder
                        .like(root.<String>get(filter.getProperty()), (String) filter.getValue()));
            } else if (filter.getOperator() == Operator.in && filter.getValue() != null) {
                restrictions = criteriaBuilder.and(restrictions,
                        root.get(filter.getProperty()).in(filter.getValue()));
            } else if (filter.getOperator() == Operator.isNull) {
                restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).isNull());
            } else if (filter.getOperator() == Operator.isNotNull) {
                restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).isNotNull());
            }
        }
    }
    criteriaQuery.where(restrictions);
}

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.  c  om
    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:net.osxx.dao.impl.ProductDaoImpl.java

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, net.osxx.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"),
            product.get("fullName"), product.get("price"),
            criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder
                    .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
    }/* w w w.  j a va  2 s  .c  o  m*/
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions,
            criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed),
            criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"),
            product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(
            criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

From source file:com.dp2345.dao.impl.ProductDaoImpl.java

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, com.dp2345.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"),
            product.get("fullName"), product.get("price"),
            criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder
                    .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
    }//from   w ww .j  av  a2  s  . co  m
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions,
            criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed),
            criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"),
            product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(
            criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}

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

public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Product> product = criteriaQuery.from(Product.class);
    Join<Product, OrderItem> orderItems = product.join("orderItems");
    Join<Product, net.shopxx.entity.Order> order = orderItems.join("order");
    criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"),
            product.get("fullName"), product.get("price"),
            criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder
                    .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
    Predicate restrictions = criteriaBuilder.conjunction();
    if (beginDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
    }//from ww w  .ja  va 2  s . co  m
    if (endDate != null) {
        restrictions = criteriaBuilder.and(restrictions,
                criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
    }
    restrictions = criteriaBuilder.and(restrictions,
            criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed),
            criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid));
    criteriaQuery.where(restrictions);
    criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"),
            product.get("price"));
    criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(
            criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
    TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
    if (count != null && count >= 0) {
        query.setMaxResults(count);
    }
    return query.getResultList();
}