Exemplo n.º 1
0
 public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
   Root<Member> member = criteriaQuery.from(Member.class);
   Join<Product, Order> orders = member.join("orders");
   criteriaQuery.multiselect(
       member.get("id"),
       member.get("username"),
       member.get("email"),
       member.get("point"),
       member.get("amount"),
       member.get("balance"),
       criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid")));
   Predicate restrictions = criteriaBuilder.conjunction();
   if (beginDate != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions,
             criteriaBuilder.greaterThanOrEqualTo(orders.<Date>get("createDate"), beginDate));
   }
   if (endDate != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions,
             criteriaBuilder.lessThanOrEqualTo(orders.<Date>get("createDate"), endDate));
   }
   restrictions =
       criteriaBuilder.and(
           restrictions,
           criteriaBuilder.equal(orders.get("orderStatus"), Order.OrderStatus.completed),
           criteriaBuilder.equal(orders.get("paymentStatus"), Order.PaymentStatus.paid));
   criteriaQuery.where(restrictions);
   criteriaQuery.groupBy(
       member.get("id"),
       member.get("username"),
       member.get("email"),
       member.get("point"),
       member.get("amount"),
       member.get("balance"));
   criteriaQuery.orderBy(
       criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid"))));
   TypedQuery<Object[]> query =
       entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
   if (count != null && count >= 0) {
     query.setMaxResults(count);
   }
   return query.getResultList();
 }
Exemplo n.º 2
0
  /**
   * Get balance of the account for previous operations.
   *
   * @param day Get the balance for operation before this day to the opening of the account
   * @return Balance of the account
   */
  public BigDecimal getBalanceHistory(LocalDate day) {
    CriteriaBuilder b = getBuilder();

    // creating criteria
    CriteriaQuery<BigDecimal> q = b.createQuery(BigDecimal.class);
    Root<Operation> op = q.from(Operation.class);
    q.select(b.sum(op.get(Operation_.amount)));

    // adding restriction
    q.where(b.lessThan(op.get(Operation_.operationDate), day.toDate()));

    return getEm().createQuery(q).getSingleResult();
  }
Exemplo n.º 3
0
 @Test
 public void testSumWithQuotient() {
   EntityManager em = getOrCreateEntityManager();
   em.getTransaction().begin();
   CriteriaQuery<Number> criteria = builder.createQuery(Number.class);
   criteria.from(Product.class);
   criteria.select(
       builder.quot(
           builder.sum(
               builder.literal(BigDecimal.valueOf(0.0)), builder.literal(BigDecimal.valueOf(1.0))),
           BigDecimal.valueOf(2.0)));
   Number result = em.createQuery(criteria).getSingleResult();
   assertEquals(0.5d, result.doubleValue(), 0.1d);
   em.getTransaction().commit();
   em.close();
 }
Exemplo n.º 4
0
  /** This test provides a demonstration of the SUM and AVE aggregate functions */
  @Test
  public void testSumAve() {
    log.info("*** testSumAve() ***");

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
    Root<Sale> s = qdef.from(Sale.class);

    // select sum(s.amount) from Sale s
    qdef.select(cb.sum(s.<BigDecimal>get("amount")));
    List<Number> results = executeQuery(qdef);
    assertEquals("unexpected number of rows", 1, results.size());
    assertEquals("unexpected result", 250, results.get(0).intValue());

    // select avg(s.amount) from Sale s
    qdef.select(cb.avg(s.<BigDecimal>get("amount")));
    results = executeQuery(qdef);
    assertEquals("unexpected number of rows", 1, results.size());
    assertEquals("unexpected result", 125, results.get(0).intValue());
  }
Exemplo n.º 5
0
 public List<Goods> findList(
     Goods.Type type,
     ProductCategory productCategory,
     Brand brand,
     Promotion promotion,
     Tag tag,
     Map<Attribute, String> attributeValueMap,
     BigDecimal startPrice,
     BigDecimal endPrice,
     Boolean isMarketable,
     Boolean isList,
     Boolean isTop,
     Boolean isOutOfStock,
     Boolean isStockAlert,
     Boolean hasPromotion,
     Goods.OrderType orderType,
     Integer count,
     List<Filter> filters,
     List<Order> orders) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Goods> criteriaQuery = criteriaBuilder.createQuery(Goods.class);
   Root<Goods> root = criteriaQuery.from(Goods.class);
   criteriaQuery.select(root);
   Predicate restrictions = criteriaBuilder.conjunction();
   if (type != null) {
     restrictions =
         criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type));
   }
   if (productCategory != null) {
     Subquery<ProductCategory> subquery = criteriaQuery.subquery(ProductCategory.class);
     Root<ProductCategory> subqueryRoot = subquery.from(ProductCategory.class);
     subquery.select(subqueryRoot);
     subquery.where(
         criteriaBuilder.or(
             criteriaBuilder.equal(subqueryRoot, productCategory),
             criteriaBuilder.like(
                 subqueryRoot.<String>get("treePath"),
                 "%"
                     + ProductCategory.TREE_PATH_SEPARATOR
                     + productCategory.getId()
                     + ProductCategory.TREE_PATH_SEPARATOR
                     + "%")));
     restrictions =
         criteriaBuilder.and(
             restrictions, criteriaBuilder.in(root.get("productCategory")).value(subquery));
   }
   if (brand != null) {
     restrictions =
         criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand));
   }
   if (promotion != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions, criteriaBuilder.equal(root.join("promotions"), promotion));
   }
   if (tag != null) {
     restrictions =
         criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("tags"), tag));
   }
   if (attributeValueMap != null) {
     for (Map.Entry<Attribute, String> entry : attributeValueMap.entrySet()) {
       String propertyName =
           Goods.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(BigDecimal.ZERO) >= 0) {
     restrictions =
         criteriaBuilder.and(
             restrictions, criteriaBuilder.ge(root.<Number>get("price"), startPrice));
   }
   if (endPrice != null && endPrice.compareTo(BigDecimal.ZERO) >= 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 (isOutOfStock != null) {
     Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
     Root<Product> subqueryRoot = subquery.from(Product.class);
     subquery.select(subqueryRoot);
     Path<Integer> stock = subqueryRoot.get("stock");
     Path<Integer> allocatedStock = subqueryRoot.get("allocatedStock");
     if (isOutOfStock) {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
     } else {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.greaterThan(stock, allocatedStock));
     }
     restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
   }
   if (isStockAlert != null) {
     Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
     Root<Product> subqueryRoot = subquery.from(Product.class);
     subquery.select(subqueryRoot);
     Path<Integer> stock = subqueryRoot.get("stock");
     Path<Integer> allocatedStock = subqueryRoot.get("allocatedStock");
     Setting setting = SystemUtils.getSetting();
     if (isStockAlert) {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.lessThanOrEqualTo(
               stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
     } else {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.greaterThan(
               stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
     }
     restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
   }
   if (hasPromotion != null) {
     restrictions =
         criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(root.join("promotions")));
   }
   criteriaQuery.where(restrictions);
   if (orderType != null) {
     switch (orderType) {
       case topDesc:
         criteriaQuery.orderBy(
             criteriaBuilder.desc(root.get("isTop")),
             criteriaBuilder.desc(root.get("createDate")));
         break;
       case priceAsc:
         criteriaQuery.orderBy(
             criteriaBuilder.asc(root.get("price")), criteriaBuilder.desc(root.get("createDate")));
         break;
       case priceDesc:
         criteriaQuery.orderBy(
             criteriaBuilder.desc(root.get("price")),
             criteriaBuilder.desc(root.get("createDate")));
         break;
       case salesDesc:
         criteriaQuery.orderBy(
             criteriaBuilder.desc(root.get("sales")),
             criteriaBuilder.desc(root.get("createDate")));
         break;
       case scoreDesc:
         criteriaQuery.orderBy(
             criteriaBuilder.desc(root.get("score")),
             criteriaBuilder.desc(root.get("createDate")));
         break;
       case dateDesc:
         criteriaQuery.orderBy(criteriaBuilder.desc(root.get("createDate")));
         break;
     }
   } else if (CollectionUtils.isEmpty(orders)) {
     criteriaQuery.orderBy(
         criteriaBuilder.desc(root.get("isTop")), criteriaBuilder.desc(root.get("createDate")));
   }
   return super.findList(criteriaQuery, null, count, filters, orders);
 }
Exemplo n.º 6
0
 public Long count(
     Goods.Type type,
     Member favoriteMember,
     Boolean isMarketable,
     Boolean isList,
     Boolean isTop,
     Boolean isOutOfStock,
     Boolean isStockAlert) {
   CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery<Goods> criteriaQuery = criteriaBuilder.createQuery(Goods.class);
   Root<Goods> root = criteriaQuery.from(Goods.class);
   criteriaQuery.select(root);
   Predicate restrictions = criteriaBuilder.conjunction();
   if (type != null) {
     restrictions =
         criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type));
   }
   if (favoriteMember != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions, criteriaBuilder.equal(root.join("favoriteMembers"), favoriteMember));
   }
   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 (isOutOfStock != null) {
     Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
     Root<Product> subqueryRoot = subquery.from(Product.class);
     subquery.select(subqueryRoot);
     Path<Integer> stock = subqueryRoot.get("stock");
     Path<Integer> allocatedStock = subqueryRoot.get("allocatedStock");
     if (isOutOfStock) {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
     } else {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.greaterThan(stock, allocatedStock));
     }
     restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
   }
   if (isStockAlert != null) {
     Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
     Root<Product> subqueryRoot = subquery.from(Product.class);
     subquery.select(subqueryRoot);
     Path<Integer> stock = subqueryRoot.get("stock");
     Path<Integer> allocatedStock = subqueryRoot.get("allocatedStock");
     Setting setting = SystemUtils.getSetting();
     if (isStockAlert) {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.lessThanOrEqualTo(
               stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
     } else {
       subquery.where(
           criteriaBuilder.equal(subqueryRoot.get("goods"), root),
           criteriaBuilder.greaterThan(
               stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
     }
     restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
   }
   criteriaQuery.where(restrictions);
   return super.count(criteriaQuery, null);
 }