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