@Override public Set<String> getTagsSuggest(String url) { String hashUrl = MD5Util.md5Hex(url); CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> query = cb.createTupleQuery(); Root<Bookmark> root = query.from(Bookmark.class); // joins SetJoin<Bookmark, String> tagsJoin = root.joinSet("tags"); query.select(cb.tuple(tagsJoin.alias("tag"), cb.count(tagsJoin).alias("count_tags"))); query.where(cb.equal(root.get("hashUrl"), hashUrl)); query.groupBy(tagsJoin); query.orderBy(cb.desc(cb.count(tagsJoin))); // top five tags List<Tuple> result = entityManager.createQuery(query).setMaxResults(5).getResultList(); Set<String> topTags = new HashSet<String>(); for (Tuple t : result) { topTags.add((String) t.get("tag")); } return topTags; }
/** * Search by criteria. * * @param criteria the criteria * @return the list< t> */ protected List<T> searchByCriteria(E criteria) { CriteriaQuery<T> query = getCriteriaBuilder().createQuery(getEntityClass()); Root<T> from = query.from(getEntityClass()); query.select(from); List<Predicate> predicateList = buildConditions(from, criteria); if (predicateList != null) { Predicate[] predicates = new Predicate[predicateList.size()]; predicateList.toArray(predicates); query.where(predicates); } if (criteria.getGroupBy() != null) { query.groupBy(from.get(criteria.getGroupBy())); } if (criteria.getOrderField() != null) { if (criteria.getOrderType() != null && !"".equals(criteria.getOrderType())) { StringTokenizer outerTokenizer = new StringTokenizer(criteria.getOrderField(), ","); StringTokenizer typeTokenizer = new StringTokenizer(criteria.getOrderType(), ","); List<Order> orders = new ArrayList<Order>(); while (outerTokenizer.hasMoreElements()) { String field = (String) outerTokenizer.nextElement(); String type = (String) typeTokenizer.nextElement(); Path ex = null; if (field.indexOf(".") > 0) { StringTokenizer tokenizer = new StringTokenizer(field, "."); while (tokenizer.hasMoreElements()) { String key = (String) tokenizer.nextElement(); if (ex == null) { ex = from.get(key); } else { ex = ex.get(key); } } } else { ex = from.get(field); } if ("desc".equals(type)) { orders.add(getCriteriaBuilder().desc(ex)); } else if ("asc".equals(type)) { orders.add(getCriteriaBuilder().asc(ex)); } } if (orders.size() > 0) { query.orderBy(orders); } } else { query.orderBy(getCriteriaBuilder().desc(from.get(criteria.getOrderField()))); } } TypedQuery<T> typedQuery = getEntityManager().createQuery(query); if (criteria.getStartIndex() != null) { typedQuery.setFirstResult(criteria.getStartIndex()); typedQuery.setMaxResults(criteria.getPageSize()); } else { typedQuery.setFirstResult(0); typedQuery.setMaxResults(rowLimit); } System.out.println("dfjdfjdkfdkfjd" + typedQuery.toString()); return typedQuery.getResultList(); }
@Override public Map<String, Long> countTagsByUser(String userName) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> query = cb.createTupleQuery(); Root<Bookmark> root = query.from(Bookmark.class); // joins SetJoin<Bookmark, String> tagsJoin = root.joinSet("tags"); Join<Bookmark, User> userJoin = root.join("user"); query.select(cb.tuple(tagsJoin.alias("tag"), cb.count(tagsJoin).alias("count_tags"))); query.where(cb.equal(userJoin.get("userName"), userName)); query.groupBy(tagsJoin); query.orderBy(cb.desc(cb.count(tagsJoin))); List<Tuple> result = entityManager.createQuery(query).setMaxResults(30).getResultList(); Map<String, Long> tagRanking = new TreeMap<String, Long>(); for (Tuple t : result) { tagRanking.put((String) t.get("tag"), (Long) t.get("count_tags")); } return tagRanking; }
@Override public final List<Product> findByFeatures(final Map<Integer, List<String>> featureValues) { CriteriaBuilder builder = manager.getCriteriaBuilder(); CriteriaQuery<Product> criteriaQuery = builder.createQuery(Product.class); Root<Product> product = criteriaQuery.from(Product.class); Path<ProductFeature> feature = product.join("features"); CriteriaQuery<Product> select = criteriaQuery.select(product); Predicate featurePredicate = builder.disjunction(); for (final Map.Entry<Integer, List<String>> fValue : featureValues.entrySet()) { Predicate equalFeatureId = builder.equal(feature.get("featureId"), fValue.getKey()); List<String> values = fValue.getValue(); Predicate equalsValues = builder.disjunction(); for (String value : values) { Predicate equalFeatureVal = builder.equal(feature.get("value"), value); equalsValues = builder.or(equalsValues, equalFeatureVal); } featurePredicate = builder.or(featurePredicate, builder.and(equalFeatureId, equalsValues)); } select.where(featurePredicate); select.groupBy(product.get("id")); select.having(builder.equal(builder.count(product), featureValues.size())); TypedQuery<Product> query = manager.createQuery(criteriaQuery); return query.getResultList(); }
public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class); Root<Member> member = criteriaQuery.from(Member.class); Join<Product, Order> orders = member.join("orders"); criteriaQuery.multiselect( member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance"), criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid"))); Predicate restrictions = criteriaBuilder.conjunction(); if (beginDate != null) { restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.greaterThanOrEqualTo(orders.<Date>get("createDate"), beginDate)); } if (endDate != null) { restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.lessThanOrEqualTo(orders.<Date>get("createDate"), endDate)); } restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.equal(orders.get("orderStatus"), Order.OrderStatus.completed), criteriaBuilder.equal(orders.get("paymentStatus"), Order.PaymentStatus.paid)); criteriaQuery.where(restrictions); criteriaQuery.groupBy( member.get("id"), member.get("username"), member.get("email"), member.get("point"), member.get("amount"), member.get("balance")); criteriaQuery.orderBy( criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid")))); TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT); if (count != null && count >= 0) { query.setMaxResults(count); } return query.getResultList(); }