CriteriaBuilder qb = entityManager.getCriteriaBuilder(); CriteriaQuery<Long> cq = qb.createQuery(Long.class); cq.select(qb.count(cq.from(MyEntity.class))); cq.where(/*your stuff*/); return entityManager.createQuery(cq).getSingleResult();
@SuppressWarnings("rawtypes") private static Expression getCountQuery(CriteriaQuery<?> query, CriteriaBuilder builder, Root<?> root) { return query.isDistinct() ? builder.countDistinct(root) : builder.count(root); } }
@Override public Long readNumberOfCustomers() { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); criteria.select(builder.count(criteria.from(CustomerImpl.class))); TypedQuery<Long> query = em.createQuery(criteria); return query.getSingleResult(); } }
/** * Creates a new count query for the given {@link Specification}. * * @param spec can be {@literal null}. * @param domainClass must not be {@literal null}. * @return */ protected <S extends T> TypedQuery<Long> getCountQuery(@Nullable Specification<S> spec, Class<S> domainClass) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> query = builder.createQuery(Long.class); Root<S> root = applySpecificationToCriteria(spec, domainClass, query); if (query.isDistinct()) { query.select(builder.countDistinct(root)); } else { query.select(builder.count(root)); } // Remove all Orders the Specifications might have applied query.orderBy(Collections.<Order> emptyList()); return em.createQuery(query); }
@Override public Long readNumberOfAddresses() { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); criteria.select(builder.count(criteria.from(CustomerAddressImpl.class))); TypedQuery<Long> query = em.createQuery(criteria); return query.getSingleResult(); }
public long count(Class<?> clazz) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Long> cq = cb.createQuery(Long.class); cq.select(cb.count(cq.from(clazz))); return em.createQuery(cq).getSingleResult(); }
@Override public Long readNumberOfOrderItems() { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); criteria.select(builder.count(criteria.from(OrderItemImpl.class))); TypedQuery<Long> query = em.createQuery(criteria); return query.getSingleResult(); }
@Override public Long readNumberOfOrders() { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); criteria.select(builder.count(criteria.from(OrderImpl.class))); TypedQuery<Long> query = em.createQuery(criteria); return query.getSingleResult(); } }
@Override public Long readTotalStaticAssetCount() { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); criteria.select(builder.count(criteria.from(StaticAssetImpl.class))); TypedQuery<Long> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); return query.getSingleResult(); }
CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Brand> cQuery = builder.createQuery(Brand.class); Root<Brand> from = cQuery.from(Brand.class); CriteriaQuery<Brand> select = cQuery.select(from); . . //Created many predicates and added to **Predicate[] pArray** . . CriteriaQuery<Long> cq = builder.createQuery(Long.class); cq.select(builder.count(cq.from(Brand.class))); // Following line if commented causes [org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.enabled' [select count(generatedAlias0) from xxx.yyy.zzz.Brand as generatedAlias0 where ( generatedAlias1.enabled=:param0 ) and ( lower(generatedAlias1.description) like :param1 )]] em.createQuery(cq); cq.where(pArray); Long count = em.createQuery(cq).getSingleResult(); . . select.where(pArray); . . // Added orderBy clause TypedQuery typedQuery = em.createQuery(select); typedQuery.setFirstResult(startIndex); typedQuery.setMaxResults(pageSize); List resultList = typedQuery.getResultList()
@Override public Long readTotalCategoryCount() { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); criteria.select(builder.count(criteria.from(CategoryImpl.class))); TypedQuery<Long> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Catalog"); return query.getSingleResult(); }
@Override public Long countAllowedValuesForProductOptionById(Long productOptionId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<ProductOptionValueImpl> root = criteria.from(ProductOptionValueImpl.class); criteria.select(builder.count(root)); List<Predicate> restrictions = new ArrayList<>(); List<Long> mergedIds = sandBoxHelper.mergeCloneIds(ProductOptionImpl.class, productOptionId); restrictions.add(root.get("productOption").in(mergedIds)); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Long> query = em.createQuery(criteria); return query.getSingleResult(); }
protected Long readCountAllActiveProductsInternal(Date currentDate) { // Set up the criteria query that specifies we want to return a Long CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); // The root of our search is Product Root<ProductImpl> product = criteria.from(ProductImpl.class); // We need to filter on active date on the sku Join<Product, Sku> sku = product.join("defaultSku"); // We want the count of products criteria.select(builder.count(product)); // Ensure the product is currently active List<Predicate> restrictions = new ArrayList<Predicate>(); attachActiveRestriction(currentDate, product, sku, restrictions); // Add the restrictions to the criteria query criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Long> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Catalog"); return query.getSingleResult(); }
@Override public Long countUsesByCustomer(Order order, Long customerId, Long offerId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<OfferAuditImpl> root = criteria.from(OfferAuditImpl.class); criteria.select(builder.count(root)); List<Predicate> restrictions = new ArrayList<>(); restrictions.add( builder.and( builder.or( builder.notEqual(root.get("orderId"), getOrderId(order)), builder.isNull(root.get("orderId")) ), builder.equal(root.get("customerId"), customerId), builder.equal(root.get("offerId"), offerId) ) ); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); try { return em.createQuery(criteria).getSingleResult(); } catch (Exception e) { LOG.error("Error counting offer uses by customer.", e); return null; } }
@Override public Long countOfferCodeUses(Order order, Long offerCodeId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<OfferAuditImpl> root = criteria.from(OfferAuditImpl.class); criteria.select(builder.count(root)); List<Predicate> restrictions = new ArrayList<>(); restrictions.add( builder.and( builder.or( builder.notEqual(root.get("orderId"), getOrderId(order)), builder.isNull(root.get("orderId")) ), builder.equal(root.get("offerCodeId"), offerCodeId) ) ); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); try { return em.createQuery(criteria).getSingleResult(); } catch (Exception e) { LOG.error("Error counting offer code uses.", e); return null; } }
protected Long readCountAllActiveSkusInternal(Date currentDate) { // Set up the criteria query that specifies we want to return a Long CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); // The root of our search is sku Root<SkuImpl> sku = criteria.from(SkuImpl.class); // We want the count of products criteria.select(builder.count(sku)); // Ensure the sku is currently active List<Predicate> restrictions = new ArrayList<Predicate>(); // Add the active start/end date restrictions restrictions.add(builder.lessThan(sku.get("activeStartDate").as(Date.class), currentDate)); restrictions.add(builder.or( builder.isNull(sku.get("activeEndDate")), builder.greaterThan(sku.get("activeEndDate").as(Date.class), currentDate))); // Add the restrictions to the criteria query criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Long> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Catalog"); return query.getSingleResult(); }
query.select(criteriaBuilder.count(root)); query.where(whereClause); return query;
private TypedQuery<Long> getProductIdsUsingProductOptionByIdQuery(Long productOptionId, boolean count) { // Set up the criteria query that specifies we want to return Products CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); // The root of our search is ProductOptionXref Root<ProductOptionXrefImpl> productOptionXref = criteria.from(ProductOptionXrefImpl.class); Join<ProductOptionXref, Product> product = productOptionXref.join("product"); Join<ProductOptionXref, ProductOption> productOption = productOptionXref.join("productOption"); if (count) { criteria.select(builder.count(product)); } else { // Product IDs are what we want back criteria.select(product.get("id").as(Long.class)); } criteria.distinct(true); List<Predicate> restrictions = new ArrayList<Predicate>(); restrictions.add(productOption.get("id").in(sandBoxHelper.mergeCloneIds(ProductOptionImpl.class, productOptionId))); // Execute the query with the restrictions criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); return em.createQuery(criteria); }
@Override public Long countTranslationEntries(TranslatedEntity entityType, ResultType stage) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<TranslationImpl> root = criteria.from(TranslationImpl.class); criteria.select(builder.count(root)); List<Predicate> restrictions = new ArrayList<Predicate>(); restrictions.add(builder.equal(root.get("entityType"), entityType.getFriendlyType())); try { if (extensionManager != null) { extensionManager.getProxy().setup(TranslationImpl.class, stage); extensionManager.getProxy().refineParameterRetrieve(TranslationImpl.class, stage, builder, criteria, root, restrictions); } criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Long> query = em.createQuery(criteria); if (extensionManager != null) { extensionManager.getProxy().refineQuery(TranslationImpl.class, stage, query); } query.setHint(QueryHints.HINT_CACHEABLE, true); return query.getSingleResult(); } finally { if (extensionManager != null) { extensionManager.getProxy().breakdown(TranslationImpl.class, stage); } } }
@Test public void test_criteria_group_by_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::criteria-group-by-example[] CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteria = builder.createQuery( Tuple.class ); Root<Person> root = criteria.from( Person.class ); criteria.groupBy(root.get("address")); criteria.multiselect(root.get("address"), builder.count(root)); List<Tuple> tuples = entityManager.createQuery( criteria ).getResultList(); for ( Tuple tuple : tuples ) { String name = (String) tuple.get( 0 ); Long count = (Long) tuple.get( 1 ); } //end::criteria-group-by-example[] assertEquals(2, tuples.size()); }); }