List of usage examples for javax.persistence.criteria CriteriaBuilder like
Predicate like(Expression<String> x, String pattern);
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 www . j av a 2s.com*/ 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);/* ww w . j a v a 2s .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:net.groupbuy.dao.impl.ProductDaoImpl.java
public List<Product> findList(ProductCategory productCategory, Date beginDate, Date endDate, Integer first, Integer count) {/*from w ww. jav a 2s.c om*/ CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class); Root<Product> root = criteriaQuery.from(Product.class); criteriaQuery.select(root); Predicate restrictions = criteriaBuilder.conjunction(); restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), true)); 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 (beginDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate)); } if (endDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate)); } criteriaQuery.where(restrictions); criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop"))); return super.findList(criteriaQuery, first, count, null, null); }
From source file:com.bxf.hradmin.common.utils.QueryParameterTransformer.java
@SuppressWarnings({ "unchecked", "rawtypes" })
public static Predicate generatePredicate(Root root, CriteriaBuilder builder,
QueryParameter... queryParameters) {
Predicate condition = builder.conjunction();
for (QueryParameter queryParameter : queryParameters) {
Object value = queryParameter.getValue();
if (value == null || StringUtils.isBlank(value.toString())) {
continue;
}//from w w w. j a va 2s . c o m
Path path = root.get(queryParameter.getKey());
switch (queryParameter.getMode()) {
case BETWEEN:
Object[] values = asArray(value);
if (values != null) {
condition = builder.and(builder.between((Path<Comparable>) path, asComparable(values[0]),
asComparable(values[1])));
}
break;
case GREATER_THAN:
condition = builder.and(condition,
builder.greaterThan((Path<Comparable>) path, asComparable(value)));
break;
case GREATER_EQUALS:
condition = builder.and(condition,
builder.greaterThanOrEqualTo((Path<Comparable>) path, asComparable(value)));
break;
case LESS_THAN:
condition = builder.and(condition, builder.lessThan((Path<Comparable>) path, asComparable(value)));
break;
case LESS_EQUALS:
condition = builder.and(condition,
builder.lessThanOrEqualTo((Path<Comparable>) path, asComparable(value)));
break;
case IS_NULL:
condition = builder.and(condition, builder.isNull(path));
break;
case IS_NOT_NULL:
condition = builder.and(condition, builder.isNotNull(path));
break;
case IN:
condition = builder.and(condition, path.in(asArray(value)));
break;
case NOT_IN:
condition = builder.and(condition, builder.not(path.in(asArray(value))));
break;
case LIKE:
condition = builder.and(condition, builder.like(path, "%" + String.valueOf(value) + "%"));
break;
case NOT_LIKE:
condition = builder.and(condition, builder.notLike(path, "%" + String.valueOf(value) + "%"));
break;
case EQUALS:
condition = builder.and(condition, builder.equal(path, value));
break;
case NOT_EQUALS:
condition = builder.and(condition, builder.notEqual(path, value));
break;
default:
break;
}
}
return condition;
}
From source file:com.hiperium.dao.common.generic.GenericDAO.java
/** * //from w w w .j av a 2s. c o m * @param criteriaBuilder * @param criteriaQuery * @param root * @param entity */ private <E> void constructQuery(CriteriaBuilder criteriaBuilder, final CriteriaQuery<E> criteriaQuery, Root<E> root, E entity) { Map<String, Object> properties = this.getEntityProperties(entity); CriteriaFieldConditions criteria = new CriteriaFieldConditions(properties); List<Predicate> predicateList = new ArrayList<Predicate>(); // Add equality conditions conditions Set<String> equalityKeys = criteria.equality.keySet(); for (String key : equalityKeys) { predicateList.add(criteriaBuilder.equal(root.get(key), criteria.equality.get(key))); } // Add like conditions Set<String> likeKeys = criteria.like.keySet(); for (String key : likeKeys) { predicateList.add(criteriaBuilder.like(root.<String>get(key), criteria.like.get(key).toString())); } // Add composite conditions, only with equality conditions Set<String> compositeKeys = criteria.composite.keySet(); for (String key : compositeKeys) { Object value = criteria.composite.get(key); try { if (value.toString().startsWith("class java.util")) { continue; } else if (StringUtils.containsIgnoreCase(key, PK_OBJECT_NAME)) { Map<String, Object> propsComposites = this.getEntityProperties(value, key); CriteriaFieldConditions compositeCriteria = new CriteriaFieldConditions(propsComposites); if (!compositeCriteria.equality.isEmpty()) { Set<String> equalityKeysPk = compositeCriteria.equality.keySet(); for (String keyPk : equalityKeysPk) { String pkValueName = keyPk.replace(PK_OBJECT_NAME.concat("."), ""); predicateList.add(criteriaBuilder.equal(root.get(PK_OBJECT_NAME).get(pkValueName), compositeCriteria.equality.get(keyPk))); } } if (!compositeCriteria.like.isEmpty()) { Set<String> likeKeysPK = compositeCriteria.like.keySet(); for (String keyPk : likeKeysPK) { String pkValueName = keyPk.replace(PK_OBJECT_NAME.concat("."), ""); Expression<String> expression = root.<String>get(PK_OBJECT_NAME).get(pkValueName); predicateList.add( criteriaBuilder.like(expression, compositeCriteria.like.get(keyPk).toString())); } } } } catch (RuntimeException e) { continue; } } // Adding where stuff is necessary if (predicateList.size() == 1) { criteriaQuery.where(predicateList.get(0)); } else if (predicateList.size() > 1) { Predicate[] predicateCriteria = new Predicate[predicateList.size()]; predicateCriteria = predicateList.toArray(predicateCriteria); criteriaQuery.where(criteriaBuilder.and(predicateCriteria)); } }
From source file:gov.gtas.repository.CaseDispositionRepositoryImpl.java
@Override public Pair<Long, List<Case>> findByCriteria(CaseRequestDto dto) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Case> q = cb.createQuery(Case.class); Root<Case> root = q.from(Case.class); List<Predicate> predicates = new ArrayList<>(); TypedQuery<Case> typedQuery = em.createQuery(q); // sorting//from w w w.ja va 2 s .c o m if (dto.getSort() != null) { List<Order> orders = new ArrayList<>(); for (SortOptionsDto sort : dto.getSort()) { Expression<?> e = root.get(sort.getColumn()); Order order; if ("desc".equalsIgnoreCase(sort.getDir())) { order = cb.desc(e); } else { order = cb.asc(e); } orders.add(order); } q.orderBy(orders); } if (dto.getFlightId() != null) { predicates.add(cb.equal(root.<Long>get("flightId"), dto.getFlightId())); } if (dto.getPaxId() != null) { predicates.add(cb.equal(root.<Long>get("paxId"), dto.getPaxId())); } if (dto.getPaxName() != null) { String likeString = String.format("%%%s%%", dto.getPaxName().toUpperCase()); predicates.add(cb.like(root.<String>get("paxName"), likeString)); } if (dto.getLastName() != null) { // map this to full pax name String likeString = String.format("%%%s%%", dto.getLastName().toUpperCase()); predicates.add(cb.like(root.<String>get("paxName"), likeString)); } if (dto.getStatus() != null) { String likeString = String.format("%%%s%%", dto.getStatus().toUpperCase()); predicates.add(cb.like(root.<String>get("status"), likeString)); } if (dto.getFlightNumber() != null) { predicates.add(cb.equal(root.<Long>get("flightNumber"), dto.getFlightNumber())); } if (dto.getRuleCatId() != null) { predicates.add(cb.equal(root.<Long>get("highPriorityRuleCatId"), dto.getRuleCatId())); } Predicate etaCondition; if (dto.getEtaStart() != null && dto.getEtaEnd() != null) { Path<Date> eta = root.<Date>get("flightETADate"); Predicate startPredicate = cb.or(cb.isNull(eta), cb.greaterThanOrEqualTo(eta, dto.getEtaStart())); Predicate endPredicate = cb.or(cb.isNull(eta), cb.lessThanOrEqualTo(eta, dto.getEtaEnd())); etaCondition = cb.and(startPredicate, endPredicate); predicates.add(etaCondition); } q.select(root).where(predicates.toArray(new Predicate[] {})); typedQuery = em.createQuery(q); // total count CriteriaQuery<Long> countQuery = cb.createQuery(Long.class); countQuery.select(cb.count(countQuery.from(Case.class))).where(predicates.toArray(new Predicate[] {})); Long count = em.createQuery(countQuery).getSingleResult(); // pagination int pageNumber = dto.getPageNumber(); int pageSize = dto.getPageSize(); int firstResultIndex = (pageNumber - 1) * pageSize; typedQuery.setFirstResult(firstResultIndex); typedQuery.setMaxResults(dto.getPageSize()); logger.debug(typedQuery.unwrap(org.hibernate.Query.class).getQueryString()); List<Case> results = typedQuery.getResultList(); return new ImmutablePair<>(count, results); }
From source file:eu.domibus.common.dao.MessageLogDao.java
public List<MessageLogEntry> findPaged(int from, int max, String column, boolean asc, HashMap<String, Object> filters) { CriteriaBuilder cb = this.em.getCriteriaBuilder(); CriteriaQuery<MessageLogEntry> cq = cb.createQuery(MessageLogEntry.class); Root<MessageLogEntry> mle = cq.from(MessageLogEntry.class); cq.select(mle);//from ww w . ja va 2 s. c om List<Predicate> predicates = new ArrayList<Predicate>(); for (Map.Entry<String, Object> filter : filters.entrySet()) { if (filter.getValue() != null) { if (filter.getValue() instanceof String) { if (!filter.getValue().toString().isEmpty()) { switch (filter.getKey().toString()) { case "receivedFrom": predicates.add(cb.greaterThanOrEqualTo(mle.<Date>get("received"), Timestamp.valueOf(filter.getValue().toString()))); break; case "receivedTo": predicates.add(cb.lessThanOrEqualTo(mle.<Date>get("received"), Timestamp.valueOf(filter.getValue().toString()))); break; default: predicates.add(cb.like(mle.<String>get(filter.getKey()), (String) filter.getValue())); break; } } } else { predicates.add(cb.equal(mle.<String>get(filter.getKey()), filter.getValue())); } } } cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()]))); if (column != null) { if (asc) { cq.orderBy(cb.asc(mle.get(column))); } else { cq.orderBy(cb.desc(mle.get(column))); } } final TypedQuery<MessageLogEntry> query = this.em.createQuery(cq); query.setFirstResult(from); query.setMaxResults(max); return query.getResultList(); }
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 . jav a 2s .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())))); } } //??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:com.movies.dao.impl.BaseDaoImpl.java
@Override public <T> List<T> getObjectsByCriteria(Map<String, Object> map, Class returnClass, List<SingularAttribute> singleAttributes, List<ListAttribute> listAttributes, List<SetAttribute> setAttributes) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<T> cq = cb.createQuery(returnClass).distinct(true); Root<T> root = cq.from(returnClass); if (CollectionUtils.isNotEmpty(singleAttributes)) { for (SingularAttribute attribute : singleAttributes) { root.join(attribute);//from w w w .j a v a 2 s . c o m root.fetch(attribute); } } if (CollectionUtils.isNotEmpty(listAttributes)) { for (ListAttribute attribute : listAttributes) { root.join(attribute, JoinType.LEFT); root.fetch(attribute, JoinType.LEFT); } } if (CollectionUtils.isNotEmpty(setAttributes)) { for (SetAttribute attribute : setAttributes) { root.join(attribute, JoinType.LEFT); root.fetch(attribute, JoinType.LEFT); } } Set<Entry<String, Object>> set = map.entrySet(); int numberOfClauses = set.size(); Predicate[] predicates = new Predicate[numberOfClauses]; int i = 0; for (Entry<String, Object> entry : set) { String key = entry.getKey(); if (MovieConstants.NAME_FIELD.equals(key) || MovieConstants.SURNAME_FIELD.equals(key)) { predicates[i++] = cb.like(cb.upper(root.<String>get(key)), LIKE + entry.getValue() + LIKE); } else if (MovieConstants.MOVIE_DIRECTOR_FIELD.equals(key)) { //predicates[i++] = cb.equal( ,entry.getValue()); } else { predicates[i++] = cb.equal(root.get(key), entry.getValue()); } } return em.createQuery(cq.select(root).where(predicates)).getResultList(); }
From source file:com.alliander.osgp.adapter.ws.infra.specifications.JpaDeviceSpecifications.java
@Override public Specification<Device> forFirmwareModuleVersion(final FirmwareModuleFilterType firmwareModuleFilterType, final String firmwareModuleVersion) throws ArgumentNullOrEmptyException { if (StringUtils.isEmpty(firmwareModuleVersion)) { throw new ArgumentNullOrEmptyException("firmwareModuleVersion"); }// w ww. j a v a2 s .c o m if (firmwareModuleFilterType == null) { throw new ArgumentNullOrEmptyException("firmwareModuleType"); } return new Specification<Device>() { @Override public Predicate toPredicate(final Root<Device> deviceRoot, final CriteriaQuery<?> query, final CriteriaBuilder cb) { String moduleFieldName = ""; switch (firmwareModuleFilterType) { case COMMUNICATION: moduleFieldName = "moduleVersionComm"; break; case FUNCTIONAL: moduleFieldName = "moduleVersionFunc"; break; case SECURITY: moduleFieldName = "moduleVersionSec"; break; case M_BUS: moduleFieldName = "moduleVersionMbus"; break; case MODULE_ACTIVE: moduleFieldName = "moduleVersionMa"; break; case ACTIVE_FIRMWARE: break; default: break; } final Subquery<Long> subquery = query.subquery(Long.class); final Root<DeviceFirmware> deviceFirmwareRoot = subquery.from(DeviceFirmware.class); subquery.select(deviceFirmwareRoot.get("device").get("id").as(Long.class)); subquery.where(cb.and( cb.like(cb.upper(deviceFirmwareRoot.get("firmware").<String>get(moduleFieldName)), firmwareModuleVersion.toUpperCase()), cb.equal(deviceFirmwareRoot.<Boolean>get("active"), true))); return cb.in(deviceRoot.get("id").as(Long.class)).value(subquery); } }; }