List of usage examples for javax.persistence.criteria CriteriaBuilder isNotNull
Predicate isNotNull(Expression<?> x);
From source file:net.shopxx.dao.impl.ProductNotifyDaoImpl.java
public Page<ProductNotify> findPage(Member member, Boolean isMarketable, Boolean isOutOfStock, Boolean hasSent, Pageable pageable) {//ww w. j a va 2 s . c om CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<ProductNotify> criteriaQuery = criteriaBuilder.createQuery(ProductNotify.class); Root<ProductNotify> root = criteriaQuery.from(ProductNotify.class); criteriaQuery.select(root); Predicate restrictions = criteriaBuilder.conjunction(); if (member != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member)); } if (isMarketable != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("product").get("goods").get("isMarketable"), isMarketable)); } if (isOutOfStock != null) { Path<Integer> stock = root.get("product").get("stock"); Path<Integer> allocatedStock = root.get("product").get("allocatedStock"); 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 (hasSent != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("hasSent"), hasSent)); } criteriaQuery.where(restrictions); return super.findPage(criteriaQuery, pageable); }
From source file:aode.lx.persistence.DynamicSpecifications.java
public static <T> Specification<T> bySearchFilter(final Collection<SearchFilter> filters, final Class<T> entityClazz) { return new Specification<T>() { @Override/* ww w . ja v a 2s.c o m*/ public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) { if (Collections3.isNotEmpty(filters)) { List<Predicate> predicates = Lists.newArrayList(); List<Predicate> ORpredicates = Lists.newArrayList(); for (SearchFilter filter : filters) { // nested path translate, Task??"user.name"filedName, ?Task.user.name String[] names = StringUtils.split(filter.fieldName, "."); Path expression = root.get(names[0]); for (int i = 1; i < names.length; i++) { expression = expression.get(names[i]); } Object value = filter.value; if (expression.getJavaType().isEnum()) { value = EnumUtils.valueOf(expression.getJavaType(), filter.value.toString()); predicates.add(builder.equal(expression, value)); } else { // logic operator switch (filter.operator) { case EQ: predicates.add(builder.equal(expression, filter.value)); break; case LIKE: predicates.add(builder.like(expression, "%" + filter.value + "%")); break; case GT: predicates.add(builder.greaterThan(expression, (Comparable) filter.value)); break; case LT: predicates.add(builder.lessThan(expression, (Comparable) filter.value)); break; case GTE: predicates.add(builder.greaterThanOrEqualTo(expression, (Comparable) filter.value)); break; case LTE: predicates.add(builder.lessThanOrEqualTo(expression, (Comparable) filter.value)); break; case NEQ: predicates.add(builder.notEqual(expression, filter.value)); break; case NOTNULL: predicates.add(builder.isNotNull(expression)); break; case ISNULL: predicates.add(builder.isNull(expression)); break; case OREQ: ORpredicates.add(builder.equal(expression, filter.value)); break; case ORLIKE: ORpredicates.add(builder.like(expression, "%" + filter.value + "%")); break; case ORGT: ORpredicates.add(builder.greaterThan(expression, (Comparable) filter.value)); break; case ORLT: ORpredicates.add(builder.lessThan(expression, (Comparable) filter.value)); break; case ORGTE: ORpredicates .add(builder.greaterThanOrEqualTo(expression, (Comparable) filter.value)); break; case ORLTE: ORpredicates.add(builder.lessThanOrEqualTo(expression, (Comparable) filter.value)); break; case ORNEQ: ORpredicates.add(builder.notEqual(expression, filter.value)); break; case ORNOTNULL: ORpredicates.add(builder.isNotNull(expression)); break; case ORISNULL: ORpredicates.add(builder.isNull(expression)); break; } } } // ? and ??? if (!predicates.isEmpty() && !ORpredicates.isEmpty()) { return builder.and(builder.and(predicates.toArray(new Predicate[predicates.size()])), builder.or(ORpredicates.toArray(new Predicate[ORpredicates.size()]))); } if (!predicates.isEmpty()) { return builder.and(predicates.toArray(new Predicate[predicates.size()])); } if (!ORpredicates.isEmpty()) { return builder.or(ORpredicates.toArray(new Predicate[ORpredicates.size()])); } } return builder.conjunction(); } }; }
From source file:com.mxep.web.common.persistence.DynamicSpecifications.java
private static List<Predicate> getPredicate(Collection<SearchFilter> filters, Root<?> root, CriteriaQuery<?> query, CriteriaBuilder builder) { List<Predicate> predicates = Lists.newArrayList(); for (SearchFilter filter : filters) { // nested path translate, Task??"user.name"filedName, // ?Task.user.name String[] names = StringUtils.split(filter.fieldName, "."); Path expression = root.get(names[0]); for (int i = 1; i < names.length; i++) { expression = expression.get(names[i]); }/*from ww w . ja v a 2 s . com*/ /*????*/ if (expression.getJavaType().equals(Integer.class) && !filter.operator.equals(SearchFilter.Operator.IN)) { filter.value = Integer.parseInt(filter.value.toString()); } // logic operator switch (filter.operator) { case EQ: predicates.add(builder.equal(expression, filter.value)); break; case LIKE: predicates.add(builder.like(expression, "%" + filter.value + "%")); break; case GT: predicates.add(builder.greaterThan(expression, (Comparable) filter.value)); break; case LT: predicates.add(builder.lessThan(expression, (Comparable) filter.value)); break; case GTE: predicates.add(builder.greaterThanOrEqualTo(expression, (Comparable) filter.value)); break; case LTE: predicates.add(builder.lessThanOrEqualTo(expression, (Comparable) filter.value)); break; case NEQ: predicates.add(builder.notEqual(expression, filter.value)); break; case IN: In in = builder.in(expression); String value = filter.value.toString(); String[] values = StringUtils.split(value, ","); for (int i = 0; i < values.length; i++) { /*????*/ if (expression.getJavaType().equals(Integer.class) || expression.getJavaType().equals(int.class) || expression.getJavaType().equals(byte.class)) { in.value(Integer.valueOf(values[i])); } else { in.value(values[i]); } } predicates.add(in); break; case NULL: predicates.add(builder.isNull(expression)); break; case NOTNULL: predicates.add(builder.isNotNull(expression)); break; } } return predicates; }
From source file:net.groupbuy.dao.impl.ProductDaoImpl.java
public Long count(Member favoriteMember, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isGift, Boolean isOutOfStock, Boolean isStockAlert) { 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 v a2 s.co m Predicate restrictions = criteriaBuilder.conjunction(); 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 (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); return super.count(criteriaQuery, null); }
From source file:ch.puzzle.itc.mobiliar.business.server.boundary.ServerView.java
public List<ServerTuple> getNodeServers(String hostFilter, String appServerFilter, String runtimeFilter, String nodeFilter, String contextFilter) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<ServerTuple> q = cb.createQuery(ServerTuple.class); //get Node ResType Root<ResourceEntity> node = q.from(ResourceEntity.class); Join<ResourceEntity, ResourceTypeEntity> nodeType = node.join("resourceType", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> nodeRelease = node.join("release", JoinType.LEFT); //get Props on node Join<ResourceEntity, ResourceContextEntity> resCtx = node.join("contexts", JoinType.LEFT); Join<ResourceContextEntity, ContextEntity> nodeCtx = resCtx.join("context", JoinType.LEFT); Join<ContextEntity, ContextEntity> nodeDomain = nodeCtx.join("parent", JoinType.LEFT); Join<ResourceContextEntity, PropertyEntity> nodeProp = resCtx.join("properties", JoinType.LEFT); Join<PropertyEntity, PropertyDescriptorEntity> nodePropType = nodeProp.join("descriptor", JoinType.LEFT); //get AppServer Join<ResourceEntity, ConsumedResourceRelationEntity> nodeResRel = node.join("consumedSlaveRelations", JoinType.LEFT);/*www . j av a 2 s.co m*/ Join<ConsumedResourceRelationEntity, ResourceEntity> appServer = nodeResRel.join("masterResource", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> asRelease = appServer.join("release", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> asType = appServer.join("resourceType", JoinType.LEFT); //get Runtime of as Join<ResourceEntity, ConsumedResourceRelationEntity> asResRel = appServer.join("consumedMasterRelations", JoinType.LEFT); Join<ConsumedResourceRelationEntity, ResourceEntity> asRuntime = asResRel.join("slaveResource", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> runtimeType = asRuntime.join("resourceType", JoinType.LEFT); q.select(cb.construct(ServerTuple.class, nodeProp.get("value"), appServer.get("name"), appServer.get("id"), asRelease.get("name"), asRuntime.get("name"), node.get("name"), node.get("id"), nodeRelease.get("name"), nodeDomain.get("name"), nodeDomain.get("id"), nodeCtx.get("name"), nodeCtx.get("id"), cb.literal(1) // true )); Predicate p = cb.and(cb.equal(nodeType.get("name"), DefaultResourceTypeDefinition.NODE.name()), cb.or(cb.equal(asType.get("name"), DefaultResourceTypeDefinition.APPLICATIONSERVER.name()), cb.isNull(asType.get("name")) //nodes without appServer ), cb.or(cb.equal(runtimeType.get("name"), ResourceTypeEntity.RUNTIME), cb.isNull(runtimeType.get("name"))), cb.isNotNull(nodeProp.get("value")), cb.equal(nodePropType.get("propertyName"), "hostName") ); p = addFilters(p, cb, hostFilter, appServerFilter, runtimeFilter, nodeFilter, contextFilter, nodeProp.<String>get("value"), appServer.<String>get("name"), asRuntime.<String>get("name"), node.<String>get("name"), nodeCtx.<String>get("name")); q.where(p); TypedQuery<ServerTuple> query = entityManager.createQuery(q); List<ServerTuple> servers = query.getResultList(); return servers; }
From source file:ch.puzzle.itc.mobiliar.business.server.boundary.ServerView.java
public List<ServerTuple> getAppServers(String hostFilter, String appServerFilter, String runtimeFilter, String nodeFilter, String contextFilter) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<ServerTuple> q = cb.createQuery(ServerTuple.class); //get Node ResType Root<ResourceEntity> node = q.from(ResourceEntity.class); Join<ResourceEntity, ResourceTypeEntity> nodeType = node.join("resourceType", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> nodeRelease = node.join("release", JoinType.LEFT); //get AppServer Join<ResourceEntity, ConsumedResourceRelationEntity> nodeResRel = node.join("consumedSlaveRelations", JoinType.LEFT);/*from ww w . jav a 2 s .c o m*/ Join<ConsumedResourceRelationEntity, ResourceEntity> appServer = nodeResRel.join("masterResource", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> asRelease = appServer.join("release", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> asType = appServer.join("resourceType", JoinType.LEFT); //get Runtime of as Join<ResourceEntity, ConsumedResourceRelationEntity> asResRel = appServer.join("consumedMasterRelations", JoinType.LEFT); Join<ConsumedResourceRelationEntity, ResourceEntity> asRuntime = asResRel.join("slaveResource", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> runtimeType = asRuntime.join("resourceType", JoinType.LEFT); //get Props between as and node Join<ConsumedResourceRelationEntity, ResourceRelationContextEntity> resRelCtx = nodeResRel.join("contexts", JoinType.LEFT); Join<ResourceRelationContextEntity, ContextEntity> asCtx = resRelCtx.join("context", JoinType.LEFT); Join<ContextEntity, ContextEntity> asDomain = asCtx.join("parent", JoinType.LEFT); Join<ResourceRelationContextEntity, PropertyEntity> asProp = resRelCtx.join("properties", JoinType.LEFT); //here an on clause should be added, so we don't get hostnames that are divined directly on the node multiple times (and descriptor.propertyName = 'hostName') //on support was added in jpa 2.1 which is part of JEE 7 Join<PropertyEntity, PropertyDescriptorEntity> asPropType = asProp.join("descriptor", JoinType.LEFT); q.select(cb.construct(ServerTuple.class, asProp.get("value"), appServer.get("name"), appServer.get("id"), asRelease.get("name"), asRuntime.get("name"), node.get("name"), node.get("id"), nodeRelease.get("name"), asDomain.get("name"), asDomain.get("id"), asCtx.get("name"), asCtx.get("id"), cb.literal(0) //false )); Predicate p = cb.and(cb.equal(nodeType.get("name"), DefaultResourceTypeDefinition.NODE.name()), cb.equal(asType.get("name"), DefaultResourceTypeDefinition.APPLICATIONSERVER.name()), cb.or(cb.equal(runtimeType.get("name"), ResourceTypeEntity.RUNTIME), cb.isNull(runtimeType.get("name"))), cb.isNotNull(asProp.get("value")), cb.equal(asPropType.get("propertyName"), "hostName")); p = addFilters(p, cb, hostFilter, appServerFilter, runtimeFilter, nodeFilter, contextFilter, asProp.<String>get("value"), appServer.<String>get("name"), asRuntime.<String>get("name"), node.<String>get("name"), asCtx.<String>get("name")); q.where(p); TypedQuery<ServerTuple> query = entityManager.createQuery(q); List<ServerTuple> servers = query.getResultList(); return servers; }
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 ww.j a va 2 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); 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);//w w w .j a 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);// 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:org.pushio.webapp.support.persistence.DynamicSpecifications.java
/** * @see ??JPA// w w w . j ava 2 s. com * @param filters * @param entityClazz * @param isDistinct trueSQLdistinctfalse? * @return */ public static <T> Specification<T> bySearchFilter(final Collection<SearchFilter> filters, final Class<T> entityClazz, final boolean isDistinct, final List<OrderParam> orderParams) { return new Specification<T>() { @Override public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) { if (orderParams != null && orderParams.size() > 0) { /* CriteriaQuery<Foo> criteriaQuery = criteriaBuilder.createQuery(Foo.class); Root<Foo> from = criteriaQuery.from(Foo.class); CriteriaQuery<Foo> select = criteriaQuery.select(from); criteriaQuery.orderBy(criteriaBuilder.asc(from.get("name"))); */ List<Order> orders = new ArrayList<Order>(orderParams.size()); for (OrderParam orderParam : orderParams) { if (orderParam != null && orderParam.getField() != null) { String fields[] = StringUtil.split(orderParam.getField(), '.'); Path expression = (fields.length > 1) ? root.join(fields[0]) : root.get(fields[0]); for (int i = 1, len = fields.length; i < len; ++i) { expression = expression.get(fields[i]); } if (expression != null) { Order order = (orderParam.getType() == null || orderParam.getType().equalsIgnoreCase("asc")) ? builder.asc(expression) : builder.desc(expression); orders.add(order); // query.orderBy(order); } } } query.orderBy(orders); } query.distinct(isDistinct); if (Collections3.isNotEmpty(filters)) { List<Predicate> predicates = Lists.newArrayList(); for (SearchFilter filter : filters) { // nested path translate, Task??"user.name"filedName, ?Task.user.name String[] names = StringUtils.split(filter.fieldName, '.'); Path expression = null; ////// ? boolean hasJoin = names[0].startsWith("[join]"); String fn = hasJoin ? names[0].substring(6) : names[0]; boolean isNotDateType = !(filter.value instanceof Date); try { expression = hasJoin ? root.join(fn) : root.get(fn); if (isNotDateType && isDateType(expression.getJavaType())) { // filter.value?? filter.value = parseDate(filter.value.toString(), filter.operator); } } catch (Exception e) { // logger.error(e.getMessage(), e); continue; // ?? } boolean isPropertyNotValid = false; for (int i = 1; i < names.length; i++) { try { expression = expression.get(names[i]); if (isNotDateType && isDateType(expression.getJavaType())) { filter.value = parseDate(filter.value.toString(), filter.operator); } } catch (Exception e) { // logger.error(e.getMessage(), e); isPropertyNotValid = true; // break; // ?? } } if (expression == null || isPropertyNotValid) { continue; } /////// // logic operator switch (filter.operator) { case EQ: predicates.add(builder.equal(expression, filter.value)); break; case LIKE: predicates.add(builder.like(expression, "%" + filter.value + "%")); break; case GT: predicates.add(builder.greaterThan(expression, (Comparable) filter.value)); break; case LT: predicates.add(builder.lessThan(expression, (Comparable) filter.value)); break; case GTE: predicates.add(builder.greaterThanOrEqualTo(expression, (Comparable) filter.value)); break; case LTE: predicates.add(builder.lessThanOrEqualTo(expression, (Comparable) filter.value)); break; case ORLIKE: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.like(expression, "%" + values[i] + "%"); } predicates.add(builder.or(like)); } break; case ANDLIKE: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.like(expression, "%" + values[i] + "%"); } predicates.add(builder.and(like)); } break; case ANDNOTLIKE: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.notLike(expression, "%" + values[i] + "%"); } predicates.add(builder.and(like)); } break; case OREQ: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.equal(expression, values[i]); } predicates.add(builder.or(like)); } break; case ANDNOTEQ: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.notEqual(expression, values[i]); } predicates.add(builder.and(like)); } break; case NOTEQ: predicates.add(builder.notEqual(expression, (Comparable) filter.value)); break; case NOTLIKE: predicates.add(builder.notLike(expression, "%" + filter.value + "%")); break; case NULL: predicates.add(builder.isNull(expression)); break; case NOTNULL: predicates.add(builder.isNotNull(expression)); break; // case IN: // predicates.add(builder.in(expression).in(values)); // break; } } // ? and ??? if (!predicates.isEmpty()) { return builder.and(predicates.toArray(new Predicate[predicates.size()])); } } return builder.conjunction(); } }; }