List of usage examples for javax.persistence.criteria CriteriaBuilder exists
Predicate exists(Subquery<?> subquery);
From source file:net.shopxx.dao.impl.OrderDaoImpl.java
public List<Order> findList(Order.Type type, Order.Status status, Member member, Goods goods, Boolean isPendingReceive, Boolean isPendingRefunds, Boolean isUseCouponCode, Boolean isExchangePoint, Boolean isAllocatedStock, Boolean hasExpired, Integer count, List<Filter> filters, List<net.shopxx.Order> orders) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class); Root<Order> root = criteriaQuery.from(Order.class); criteriaQuery.select(root);/*from w w w . j a v a 2 s. c om*/ Predicate restrictions = criteriaBuilder.conjunction(); if (type != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type)); } if (status != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("status"), status)); } if (member != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member)); } if (goods != null) { Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class); Root<Product> productSubqueryRoot = productSubquery.from(Product.class); productSubquery.select(productSubqueryRoot); productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("goods"), goods)); Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class); Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class); orderItemSubquery.select(orderItemSubqueryRoot); orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root), criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery)); restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery)); } if (isPendingReceive != null) { Predicate predicate = criteriaBuilder.and( criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery), criteriaBuilder.notEqual(root.get("status"), Order.Status.completed), criteriaBuilder.notEqual(root.get("status"), Order.Status.failed), criteriaBuilder.notEqual(root.get("status"), Order.Status.canceled), criteriaBuilder.notEqual(root.get("status"), Order.Status.denied), criteriaBuilder.lessThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount"))); if (isPendingReceive) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isPendingRefunds != null) { Predicate predicate = criteriaBuilder.or( criteriaBuilder.and( criteriaBuilder.or( criteriaBuilder.and(root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("status"), Order.Status.failed), criteriaBuilder.equal(root.get("status"), Order.Status.canceled), criteriaBuilder.equal(root.get("status"), Order.Status.denied)), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), BigDecimal.ZERO)), criteriaBuilder.and(criteriaBuilder.equal(root.get("status"), Order.Status.completed), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount")))); if (isPendingRefunds) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isUseCouponCode != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUseCouponCode"), isUseCouponCode)); } if (isExchangePoint != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isExchangePoint"), isExchangePoint)); } if (isAllocatedStock != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock)); } if (hasExpired != null) { if (hasExpired) { restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date()))); } } criteriaQuery.where(restrictions); return super.findList(criteriaQuery, null, count, filters, orders); }
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);/* w w w . j av a 2s. co m*/ 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: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 w w w .ja v a 2 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); 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 a 2 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: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);/*w w w . j a va2 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())))); } } //??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:org.finra.herd.dao.impl.HerdDaoImpl.java
/** * {@inheritDoc}/* w w w .j a v a 2 s . c o m*/ */ @Override public Map<BusinessObjectDataEntity, StoragePolicyEntity> getBusinessObjectDataEntitiesMatchingStoragePolicies( StoragePolicyPriorityLevel storagePolicyPriorityLevel, List<String> supportedBusinessObjectDataStatuses, int startPosition, int maxResult) { // Create the criteria builder and a tuple style criteria query. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteria = builder.createTupleQuery(); // The criteria root is the business object data. Root<BusinessObjectDataEntity> businessObjectDataEntity = criteria.from(BusinessObjectDataEntity.class); Root<StoragePolicyEntity> storagePolicyEntity = criteria.from(StoragePolicyEntity.class); // Join to the other tables we can filter on. Join<BusinessObjectDataEntity, StorageUnitEntity> storageUnitEntity = businessObjectDataEntity .join(BusinessObjectDataEntity_.storageUnits); Join<BusinessObjectDataEntity, BusinessObjectDataStatusEntity> businessObjectDataStatusEntity = businessObjectDataEntity .join(BusinessObjectDataEntity_.status); Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> businessObjectFormatEntity = businessObjectDataEntity .join(BusinessObjectDataEntity_.businessObjectFormat); Join<BusinessObjectFormatEntity, FileTypeEntity> fileTypeEntity = businessObjectFormatEntity .join(BusinessObjectFormatEntity_.fileType); Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity = businessObjectFormatEntity .join(BusinessObjectFormatEntity_.businessObjectDefinition); // Create main query restrictions based on the specified parameters. List<Predicate> mainQueryPredicates = new ArrayList<>(); // Add a restriction on business object definition. mainQueryPredicates.add(storagePolicyPriorityLevel.isBusinessObjectDefinitionIsNull() ? builder.isNull(storagePolicyEntity.get(StoragePolicyEntity_.businessObjectDefinition)) : builder.equal(businessObjectDefinitionEntity, storagePolicyEntity.get(StoragePolicyEntity_.businessObjectDefinition))); // Add a restriction on business object format usage. mainQueryPredicates.add(storagePolicyPriorityLevel.isUsageIsNull() ? builder.isNull(storagePolicyEntity.get(StoragePolicyEntity_.usage)) : builder.equal(builder.upper(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage)), builder.upper(storagePolicyEntity.get(StoragePolicyEntity_.usage)))); // Add a restriction on business object format file type. mainQueryPredicates.add(storagePolicyPriorityLevel.isFileTypeIsNull() ? builder.isNull(storagePolicyEntity.get(StoragePolicyEntity_.fileType)) : builder.equal(fileTypeEntity, storagePolicyEntity.get(StoragePolicyEntity_.fileType))); // Add a restriction on storage policy filter storage. mainQueryPredicates.add(builder.equal(storageUnitEntity.get(StorageUnitEntity_.storage), storagePolicyEntity.get(StoragePolicyEntity_.storage))); // Add a restriction on supported business object data statuses. mainQueryPredicates.add(businessObjectDataStatusEntity.get(BusinessObjectDataStatusEntity_.code) .in(supportedBusinessObjectDataStatuses)); // Build a subquery to eliminate business object data instances that already have storage unit in the storage policy destination storage. Subquery<BusinessObjectDataEntity> subquery = criteria.subquery(BusinessObjectDataEntity.class); Root<BusinessObjectDataEntity> subBusinessObjectDataEntity = subquery.from(BusinessObjectDataEntity.class); subquery.select(subBusinessObjectDataEntity); // Join to the other tables we can filter on for the subquery. Join<BusinessObjectDataEntity, StorageUnitEntity> subStorageUnitEntity = subBusinessObjectDataEntity .join(BusinessObjectDataEntity_.storageUnits); // Create the subquery restrictions based on the main query and storage policy destination storage. List<Predicate> subQueryPredicates = new ArrayList<>(); subQueryPredicates.add(builder.equal(subBusinessObjectDataEntity, businessObjectDataEntity)); subQueryPredicates.add(builder.equal(subStorageUnitEntity.get(StorageUnitEntity_.storage), storagePolicyEntity.get(StoragePolicyEntity_.destinationStorage))); // Add all clauses to the subquery. subquery.select(subBusinessObjectDataEntity).where(subQueryPredicates.toArray(new Predicate[] {})); // Add a main query restriction based on the subquery to eliminate business object data // instances that already have storage unit in the storage policy destination storage. mainQueryPredicates.add(builder.not(builder.exists(subquery))); // Order the results by business object data "created on" value. Order orderByCreatedOn = builder.asc(businessObjectDataEntity.get(BusinessObjectDataEntity_.createdOn)); // Add the select clause to the main query. criteria.multiselect(businessObjectDataEntity, storagePolicyEntity); // Add the where clause to the main query. criteria.where(mainQueryPredicates.toArray(new Predicate[] {})); // Add the order by clause to the main query. criteria.orderBy(orderByCreatedOn); // Run the query to get a list of tuples back. List<Tuple> tuples = entityManager.createQuery(criteria).setFirstResult(startPosition) .setMaxResults(maxResult).getResultList(); // Populate the result map from the returned tuples (i.e. 1 tuple for each row). Map<BusinessObjectDataEntity, StoragePolicyEntity> result = new LinkedHashMap<>(); for (Tuple tuple : tuples) { // Since multiple storage policies can contain identical filters, we add the below check to select each business object data instance only once. if (!result.containsKey(tuple.get(businessObjectDataEntity))) { result.put(tuple.get(businessObjectDataEntity), tuple.get(storagePolicyEntity)); } } return result; }
From source file:org.jgrades.admin.accounts.UserSpecificationsImpl.java
private static Predicate getSearchPredicate(Root<User> root, CriteriaQuery<?> cq, CriteriaBuilder cb, Class clazz) {/*from w w w. j a v a2 s . c o m*/ Subquery subquery = cq.subquery(clazz); Root subRoot = subquery.from(clazz); subquery.select(subRoot); Predicate predicate = cb.equal(subRoot.get("id"), root.get("id")); subquery.where(predicate); return cb.exists(subquery); }