Example usage for javax.persistence.criteria CriteriaBuilder count

List of usage examples for javax.persistence.criteria CriteriaBuilder count

  1. HOME
  2. Java
  3. javax
  4. javax.persistence.criteria.*
  5. CriteriaBuilder
  6. count

Introduction

In this page you can find the example usage for javax.persistence.criteria CriteriaBuilder count.

Prototype

Expression<Long> count(Expression<?> x);

Source Link

Document

Create an aggregate expression applying the count operation.

Usage

From source file:de.hopmann.msc.slave.service.PackageInstallationBean.java

public PackageInstallationEntity getInstallationEntity(PackageResolved packageModel,
        Set<PackageInstallationEntity> requiredDependencies, PackageInstallerHolder packageInstallerHolder) {
    try {//from  w ww.j a  v a 2s.  c  o  m

        // TODO repository version, flavor, etc.

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<PackageInstallationEntity> query = cb.createQuery(PackageInstallationEntity.class);
        Root<PackageInstallationEntity> p = query.from(PackageInstallationEntity.class);
        Path<PackageInstallerEntity> pInstaller = p.get(PackageInstallationEntity_.packageInstaller);

        List<Predicate> predicates = new ArrayList<Predicate>();

        predicates.add(cb.equal(p.get(PackageInstallationEntity_.packageName), packageModel.getPackageName()));
        predicates.add(cb.equal(p.get(PackageInstallationEntity_.sourceVersion).get(Version_.versionNumber),
                packageModel.getPackageAccessor().getSourceVersion().getVersionNumber()));

        predicates.add(cb.between(pInstaller.get(PackageInstallerEntity_.version).get(Version_.versionNumber),
                packageInstallerHolder.getDependencyMinVersion(),
                packageInstallerHolder.getDependencyMaxVersion()));

        // TODO flavor arch

        if (requiredDependencies.isEmpty()) {
            query.where(cb.and(predicates.toArray(new Predicate[] {})));
        } else {
            SetJoin<PackageInstallationEntity, PackageInstallationEntity> join = p
                    .join(PackageInstallationEntity_.actualDependencies);

            predicates.add(join.in(requiredDependencies));

            // Expression<Set<PackageInstallationEntity>> pDependencies = p
            // .get(PackageInstallationEntity_.actualDependencies);
            // predicates.add(pDependencies.in(requiredDependencies));

            query.where(cb.and(predicates.toArray(new Predicate[] {})));
            Path<Long> pId = p.get(PackageInstallationEntity_.id);
            query.groupBy(pId);
            query.having(cb.ge(cb.count(pId), requiredDependencies.size()));
            query.distinct(true);
        }

        return entityManager.createQuery(query).getSingleResult();
    } catch (NoResultException e) {
        return null;
    }
}

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  www .  ja v a 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:gov.osti.services.Metadata.java

/**
 * Acquire a List of records in pending ("Submitted") state, to be approved
 * for indexing and searching./*from w  ww .  j  a  v  a 2 s  . com*/
 *
 * JSON response is of the form:
 *
 * {"records":[{"code_id":n, ...} ],
 *  "start":0, "rows":20, "total":100}
 *
 * Where records is an array of DOECodeMetadata JSON, start is the beginning
 * row number, rows is the number requested (or total if less available),
 * and total is the total number of rows matching the filter.
 *
 * Return Codes:
 * 200 - OK, JSON is returned as above
 * 401 - Unauthorized, login is required
 * 403 - Forbidden, insufficient privileges (role required)
 * 500 - unexpected error
 *
 * @param start the starting row number (from 0)
 * @param rows number of rows desired (0 is unlimited)
 * @param siteCode (optional) a SITE OWNERSHIP CODE to filter by site
 * @param state the WORKFLOW STATE if desired (default Submitted and Announced). One of
 * Approved, Saved, Submitted, or Announced, if supplied.
 * @return JSON of a records response
 */
@GET
@Path("/projects/pending")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
@RequiresAuthentication
@RequiresRoles("OSTI")
public Response listProjectsPending(@QueryParam("start") int start, @QueryParam("rows") int rows,
        @QueryParam("site") String siteCode, @QueryParam("state") String state) {
    EntityManager em = DoeServletContextListener.createEntityManager();

    try {
        // get a JPA CriteriaBuilder instance
        CriteriaBuilder cb = em.getCriteriaBuilder();
        // create a CriteriaQuery for the COUNT
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<DOECodeMetadata> md = countQuery.from(DOECodeMetadata.class);
        countQuery.select(cb.count(md));

        Expression<String> workflowStatus = md.get("workflowStatus");
        Expression<String> siteOwnershipCode = md.get("siteOwnershipCode");

        // default requested STATE; take Submitted and Announced as the default values if not supplied
        List<DOECodeMetadata.Status> requestedStates = new ArrayList();
        String queryState = (StringUtils.isEmpty(state)) ? "" : state.toLowerCase();
        switch (queryState) {
        case "approved":
            requestedStates.add(DOECodeMetadata.Status.Approved);
            break;
        case "saved":
            requestedStates.add(DOECodeMetadata.Status.Saved);
            break;
        case "submitted":
            requestedStates.add(DOECodeMetadata.Status.Submitted);
            break;
        case "announced":
            requestedStates.add(DOECodeMetadata.Status.Announced);
            break;
        default:
            requestedStates.add(DOECodeMetadata.Status.Submitted);
            requestedStates.add(DOECodeMetadata.Status.Announced);
            break;
        }

        Predicate statusPredicate = workflowStatus.in(requestedStates);
        ParameterExpression<String> site = cb.parameter(String.class, "site");

        if (null == siteCode) {
            countQuery.where(statusPredicate);
        } else {
            countQuery.where(cb.and(statusPredicate, cb.equal(siteOwnershipCode, site)));
        }
        // query for the COUNT
        TypedQuery<Long> cq = em.createQuery(countQuery);
        cq.setParameter("status", requestedStates);
        if (null != siteCode)
            cq.setParameter("site", siteCode);

        long rowCount = cq.getSingleResult();
        // rows count should be less than 100 for pagination; 0 is a special case
        rows = (rows > 100) ? 100 : rows;

        // create a CriteriaQuery for the ROWS
        CriteriaQuery<DOECodeMetadata> rowQuery = cb.createQuery(DOECodeMetadata.class);
        rowQuery.select(md);

        if (null == siteCode) {
            rowQuery.where(statusPredicate);
        } else {
            rowQuery.where(cb.and(statusPredicate, cb.equal(siteOwnershipCode, site)));
        }

        TypedQuery<DOECodeMetadata> rq = em.createQuery(rowQuery);
        rq.setParameter("status", requestedStates);
        if (null != siteCode)
            rq.setParameter("site", siteCode);
        rq.setFirstResult(start);
        if (0 != rows)
            rq.setMaxResults(rows);

        RecordsList records = new RecordsList(rq.getResultList());
        records.setTotal(rowCount);
        records.setStart(start);

        return Response.ok().entity(mapper.valueToTree(records).toString()).build();
    } finally {
        em.close();
    }
}

From source file:cn.buk.hotel.dao.HotelDaoImpl.java

@Override
public List<HotelInfo> searchAvailableHotel(HotelSearchCriteria sc) {

    List<HotelInfo> hotelInfos = null;

    try {//from  w  ww .  java 2s .c  om
        //body
        CriteriaBuilder cb = getEm().getCriteriaBuilder();

        CriteriaQuery<HotelInfo> cq = cb.createQuery(HotelInfo.class);
        Root<HotelInfo> root = cq.from(HotelInfo.class);
        root.alias("h");

        List<Predicate> predicates = new ArrayList<Predicate>();

        Predicate predicate = cb.equal(root.get(HotelInfo_.cityId), sc.getCityId());
        predicates.add(predicate);

        /**
         * ratePlanStatus-1???)  yfddai 2015-1-8
         */
        predicate = cb.notEqual(root.get(HotelInfo_.ratePlanStatus), -1);
        predicates.add(predicate);

        if (sc.getDistrictId() > 0) {
            predicate = cb.equal(root.get(HotelInfo_.areaId), sc.getDistrictId());
            predicates.add(predicate);
        }

        if (sc.getHotelName() != null && sc.getHotelName().trim().length() > 0) {
            predicate = cb.like(root.get(HotelInfo_.hotelName), "%" + sc.getHotelName() + "%");
            predicates.add(predicate);
        }

        if (sc.getStar() != null && sc.getStar().length() > 0) {
            Join<HotelInfo, HotelAward> hotelAward = root.join("hotelAwards", JoinType.LEFT);
            hotelAward.alias("ha");

            predicates.add(cb.equal(hotelAward.get("provider"), "HotelStarRate"));

            String[] stars = sc.getStar().split(",");
            Predicate p0 = cb.disjunction();
            for (String star : stars) {
                if (star.length() == 0)
                    continue;
                int starLevel = Integer.parseInt(star);
                if (starLevel == 2)
                    p0 = cb.or(p0, cb.le(hotelAward.get(HotelAward_.rating), starLevel));
                else
                    p0 = cb.or(p0, cb.equal(hotelAward.get("rating"), starLevel));
            }
            predicates.add(p0);
        }

        if (sc.getZoneId() > 0) {
            Join<HotelInfo, HotelAddressZone> hotelZone = root.join(HotelInfo_.hotelAddressZones,
                    JoinType.LEFT);
            hotelZone.alias("hz");

            predicate = cb.equal(hotelZone.get(HotelAddressZone_.zoneCode), sc.getZoneId());
            predicates.add(predicate);
        }

        // count items
        CriteriaQuery<Long> cq0 = cb.createQuery(Long.class);
        Root<HotelInfo> root0 = cq0.from(HotelInfo.class);
        root0.alias("h");
        if (sc.getStar() != null && sc.getStar().length() > 0) {
            Join<HotelInfo, HotelAward> hotelAward0 = root0.join("hotelAwards", JoinType.LEFT);
            hotelAward0.alias("ha");
        }
        if (sc.getZoneId() > 0) {
            Join<HotelInfo, HotelAddressZone> hotelZone0 = root0.join(HotelInfo_.hotelAddressZones,
                    JoinType.LEFT);
            hotelZone0.alias("hz");
        }
        cq0.select(cb.count(root0)).where(predicates.toArray(new Predicate[0]));
        Long count = getEm().createQuery(cq0).getSingleResult();

        sc.getPage().setRowCount(count.intValue());

        int firstPosition = (sc.getPage().getPageNo() - 1) * sc.getPage().getPageSize();
        cq.select(root).where(predicates.toArray(new Predicate[0]));

        hotelInfos = getEm().createQuery(cq).setFirstResult(firstPosition)
                .setMaxResults(sc.getPage().getPageSize()).getResultList();
    } catch (PersistenceException e) {
        logger.error(e.getMessage());
    }

    return hotelInfos == null ? new ArrayList<HotelInfo>() : hotelInfos;
}

From source file:org.agric.oxm.utils.JpaUtils.java

/**
 * Create a row count CriteriaQuery from a CriteriaQuery
 * //from w ww  . j a v a 2s  . co  m
 * @param em
 *            entity manager
 * @param criteria
 *            source criteria
 * @return row coutnt CriteriaQuery
 */
public static <T> CriteriaQuery<Long> countCriteria(EntityManager em, CriteriaQuery<T> criteria) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Long> countCriteria = builder.createQuery(Long.class);
    copyCriteriaNoSelection(criteria, countCriteria);
    countCriteria.select(builder.count(findRoot(countCriteria, criteria.getResultType())));

    return countCriteria;
}

From source file:org.apache.ranger.service.XTrxLogService.java

public Long searchXTrxLogsCount(SearchCriteria searchCriteria) {
    EntityManager em = daoMgr.getEntityManager();
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<VXXTrxLog> selectCQ = criteriaBuilder.createQuery(VXXTrxLog.class);
    Root<VXXTrxLog> rootEntityType = selectCQ.from(VXXTrxLog.class);
    Predicate predicate = generatePredicate(searchCriteria, em, criteriaBuilder, rootEntityType);

    CriteriaQuery<Long> countCQ = criteriaBuilder.createQuery(Long.class);
    countCQ.select(criteriaBuilder.count(rootEntityType)).where(predicate);
    List<Long> countList = em.createQuery(countCQ).getResultList();
    Long count = 0L;//from w  w  w.  j a v a  2 s .c  o m
    if (!CollectionUtils.isEmpty(countList)) {
        count = countList.get(0);
        if (count == null) {
            count = 0L;
        }
    }
    return count;
}

From source file:org.apache.rave.portal.repository.impl.JpaWidgetRepository.java

@Override
public int getCountByStatusAndTypeAndFreeText(WidgetStatus widgetStatus, String type, String searchTerm) {
    final CriteriaBuilder cb = manager.getCriteriaBuilder();
    final CriteriaQuery<Long> query = cb.createQuery(Long.class);
    final Root<JpaWidget> widgetType = query.from(JpaWidget.class);
    query.select(cb.count(widgetType));
    query.where(getStatusAndTypeAndFreeTextPredicates(cb, widgetType, widgetStatus, type, searchTerm));

    final Long countResult = manager.createQuery(query).getSingleResult();
    return countResult.intValue();
}

From source file:org.broadleafcommerce.common.i18n.dao.TranslationDaoImpl.java

@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 {//from   ww  w  .j  a  v a2s. c  om
        if (extensionManager != null) {
            extensionManager.getProxy().setup(TranslationImpl.class, stage);
            extensionManager.getProxy().refineRetrieve(TranslationImpl.class, stage, builder, criteria, root,
                    restrictions);
        }
        criteria.where(restrictions.toArray(new Predicate[restrictions.size()]));

        TypedQuery<Long> query = em.createQuery(criteria);
        query.setHint(QueryHints.HINT_CACHEABLE, true);
        return query.getSingleResult();
    } finally {
        if (extensionManager != null) {
            extensionManager.getProxy().breakdown(TranslationImpl.class, stage);
        }
    }
}

From source file:org.broadleafcommerce.core.catalog.dao.ProductDaoImpl.java

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();
}

From source file:org.broadleafcommerce.openadmin.server.service.persistence.module.criteria.CriteriaTranslatorImpl.java

@SuppressWarnings("unchecked")
protected TypedQuery<Serializable> constructQuery(DynamicEntityDao dynamicEntityDao, String ceilingEntity,
        List<FilterMapping> filterMappings, boolean isCount, boolean isMax, Integer firstResult,
        Integer maxResults, String maxField) {

    CriteriaBuilder criteriaBuilder = dynamicEntityDao.getStandardEntityManager().getCriteriaBuilder();

    Class<Serializable> ceilingMarker;
    try {/*from  ww  w .  jav  a2s .c  o  m*/
        ceilingMarker = (Class<Serializable>) Class.forName(ceilingEntity);
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e);
    }

    Class<Serializable> securityRoot = rowSecurityService.getFetchRestrictionRoot(
            adminSecurityService.getPersistentAdminUser(), ceilingMarker, filterMappings);
    if (securityRoot != null) {
        ceilingMarker = securityRoot;
    }

    Class<Serializable> ceilingClass = determineRoot(dynamicEntityDao, ceilingMarker, filterMappings);
    CriteriaQuery<Serializable> criteria = criteriaBuilder.createQuery(ceilingMarker);
    Root<Serializable> original = criteria.from(ceilingClass);

    if (isCount) {
        criteria.select(criteriaBuilder.count(original));
    } else if (isMax) {
        criteria.select(criteriaBuilder.max((Path<Number>) ((Object) original.get(maxField))));
    } else {
        criteria.select(original);
    }

    List<Predicate> restrictions = new ArrayList<Predicate>();
    List<Order> sorts = new ArrayList<Order>();
    addRestrictions(ceilingEntity, filterMappings, criteriaBuilder, original, restrictions, sorts, criteria);

    criteria.where(restrictions.toArray(new Predicate[restrictions.size()]));
    if (!isCount && !isMax) {
        criteria.orderBy(sorts.toArray(new Order[sorts.size()]));
        //If someone provides a firstResult value, then there is generally pagination going on.
        //In order to produce consistent results, especially with certain databases such as PostgreSQL, 
        //there has to be an "order by" clause.  We'll add one here if we can.
        if (firstResult != null && sorts.isEmpty()) {
            Map<String, Object> idMetaData = dynamicEntityDao.getIdMetadata(ceilingClass);
            if (idMetaData != null) {
                Object idFldName = idMetaData.get("name");
                Object type = idMetaData.get("type");
                if ((idFldName instanceof String) && (type instanceof SingleColumnType)) {
                    criteria.orderBy(criteriaBuilder.asc(original.get((String) idFldName)));
                }
            }
        }
    }
    TypedQuery<Serializable> response = dynamicEntityDao.getStandardEntityManager().createQuery(criteria);

    if (!isCount && !isMax) {
        addPaging(response, firstResult, maxResults);
    }

    return response;
}