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:org.finra.herd.dao.impl.TagDaoImpl.java

@Override
public long getCountOfAllTags() {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> criteria = builder.createQuery(Long.class);
    Root<TagEntity> tagEntityRoot = criteria.from(TagEntity.class);
    criteria.select(builder.count(tagEntityRoot));
    return entityManager.createQuery(criteria).getSingleResult();
}

From source file:org.medici.bia.dao.image.ImageDAOJpaImpl.java

/**
 * {@inheritDoc}/*w  ww .j av  a2 s  .  c  o  m*/
 */
@SuppressWarnings("rawtypes")
@Override
public Page findImages(Integer volNum, String volLetExt, PaginationFilter paginationFilter)
        throws PersistenceException {
    // Create criteria objects
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();

    Page page = new Page(paginationFilter);

    if (paginationFilter.getTotal() == null) {
        CriteriaQuery<Long> criteriaQueryCount = criteriaBuilder.createQuery(Long.class);
        Root<Image> rootCount = criteriaQueryCount.from(Image.class);
        criteriaQueryCount.select(criteriaBuilder.count(rootCount));

        // Define predicate's elements
        ParameterExpression<Integer> parameterVolNum = criteriaBuilder.parameter(Integer.class, "volNum");
        ParameterExpression<String> parameterVolLeText = StringUtils.isEmpty("volLetExt") ? null
                : criteriaBuilder.parameter(String.class, "volLetExt");

        criteriaQueryCount
                .where(criteriaBuilder.and(criteriaBuilder.equal(rootCount.get("volNum"), parameterVolNum),
                        StringUtils.isEmpty(volLetExt) ? criteriaBuilder.isNull(rootCount.get("volLetExt"))
                                : criteriaBuilder.equal(rootCount.get("volLetExt"), parameterVolLeText)));

        TypedQuery typedQueryCount = getEntityManager().createQuery(criteriaQueryCount);
        typedQueryCount.setParameter("volNum", volNum);
        if (!StringUtils.isEmpty(volLetExt)) {
            typedQueryCount.setParameter("volLetExt", volLetExt);
        }
        page.setTotal(new Long((Long) typedQueryCount.getSingleResult()));
    }

    CriteriaQuery<Image> criteriaQuery = criteriaBuilder.createQuery(Image.class);
    Root<Image> root = criteriaQuery.from(Image.class);

    // Define predicate's elements
    ParameterExpression<Integer> parameterVolNum = criteriaBuilder.parameter(Integer.class, "volNum");
    ParameterExpression<String> parameterVolLeText = StringUtils.isEmpty("volLetExt") ? null
            : criteriaBuilder.parameter(String.class, "volLetExt");

    //We need to duplicate predicates beacause they are link to Root element
    criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.equal(root.get("volNum"), parameterVolNum),
            StringUtils.isEmpty(volLetExt) ? criteriaBuilder.isNull(root.get("volLetExt"))
                    : criteriaBuilder.equal(root.get("volLetExt"), parameterVolLeText)));

    // Set values in predicate's elements  
    TypedQuery<Image> typedQuery = getEntityManager().createQuery(criteriaQuery);
    typedQuery.setParameter("volNum", volNum);
    if (!StringUtils.isEmpty(volLetExt)) {
        typedQuery.setParameter("volLetExt", volLetExt);
    }

    //Pagination will work with index [1 ... total] and not [0 ... total1-] 
    typedQuery.setFirstResult(paginationFilter.getFirstRecord() - 1);
    typedQuery.setMaxResults(paginationFilter.getLength());
    page.setList(typedQuery.getResultList());

    return page;
}

From source file:org.medici.bia.dao.image.ImageDAOJpaImpl.java

/**
 * {@inheritDoc}//from  w  w w  .  ja v  a  2s  .  c om
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
@Override
public FoliosInformations findVolumeFoliosInformations(Integer volNum, String volLetExt)
        throws PersistenceException {
    FoliosInformations foliosInformations = new FoliosInformations();

    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();

    CriteriaQuery<Long> criteriaQueryCount = criteriaBuilder.createQuery(Long.class);
    Root<Image> rootCount = criteriaQueryCount.from(Image.class);
    criteriaQueryCount.select(criteriaBuilder.count(rootCount));

    // Define predicate's elements
    ParameterExpression<Integer> parameterVolNum = criteriaBuilder.parameter(Integer.class, "volNum");
    ParameterExpression<String> parameterVolLeText = StringUtils.isEmpty("volLetExt") ? null
            : criteriaBuilder.parameter(String.class, "volLetExt");

    criteriaQueryCount
            .where(criteriaBuilder.and(criteriaBuilder.equal(rootCount.get("volNum"), parameterVolNum),
                    StringUtils.isEmpty(volLetExt) ? criteriaBuilder.isNull(rootCount.get("volLetExt"))
                            : criteriaBuilder.equal(rootCount.get("volLetExt"), parameterVolLeText)));

    TypedQuery typedQueryCount = getEntityManager().createQuery(criteriaQueryCount);
    typedQueryCount.setParameter("volNum", volNum);
    if (!StringUtils.isEmpty(volLetExt))
        typedQueryCount.setParameter("volLetExt", volLetExt);
    foliosInformations.setTotal((Long) typedQueryCount.getSingleResult());

    StringBuilder stringBuilder = new StringBuilder(
            "SELECT imageType, imageRectoVerso, max(imageProgTypeNum) FROM Image WHERE volNum=:volNum and volLetExt ");
    if (!StringUtils.isEmpty(volLetExt))
        stringBuilder.append(" = :volLetExt");
    else
        stringBuilder.append(" is null");
    stringBuilder.append(" GROUP BY imageType, imageRectoVerso");

    Query query = getEntityManager().createQuery(stringBuilder.toString());
    query.setParameter("volNum", volNum);
    if (!StringUtils.isEmpty(volLetExt)) {
        query.setParameter("volLetExt", volLetExt);
    }

    List<Object[]> result = (List<Object[]>) query.getResultList();

    // We init every partial-total
    foliosInformations.setTotalRubricario(new Long(0));
    foliosInformations.setTotalCarta(new Long(0));
    foliosInformations.setTotalAppendix(new Long(0));
    foliosInformations.setTotalOther(new Long(0));
    foliosInformations.setTotalGuardia(new Long(0));
    foliosInformations.setTotalMissingFolios(0);
    // We set new partial-total values 
    for (int i = 0; i < result.size(); i++) {
        // This is an array defined as [ImageType, Count by ImageType]
        Object[] singleGroup = result.get(i);

        if (((ImageType) singleGroup[0]).equals(ImageType.R)) {
            if (foliosInformations.getTotalRubricario() < new Long(singleGroup[2].toString())) {
                foliosInformations.setTotalRubricario(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.C)) {
            if (foliosInformations.getTotalCarta() < new Long(singleGroup[2].toString())) {
                foliosInformations.setTotalCarta(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.A)) {
            if (foliosInformations.getTotalAppendix() < new Long(singleGroup[2].toString())) {
                foliosInformations.setTotalAppendix(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.O)) {
            if (foliosInformations.getTotalOther() < new Long(singleGroup[2].toString())) {
                foliosInformations.setTotalOther(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.G)) {
            if (foliosInformations.getTotalGuardia() < new Long(singleGroup[2].toString())) {
                foliosInformations.setTotalGuardia(new Long(singleGroup[2].toString()));
            }
        }
    }

    // Calculating missing folios start
    stringBuilder = new StringBuilder(
            "SELECT DISTINCT(imageProgTypeNum) FROM Image WHERE volNum = :volNum AND volLetExt ");
    if (!StringUtils.isEmpty(volLetExt))
        stringBuilder.append(" = :volLetExt");
    else
        stringBuilder.append(" IS NULL");
    stringBuilder.append(" AND imageType = 'C'"); // we search only for folios
    stringBuilder.append(" ORDER BY imageProgTypeNum ASC");

    query = getEntityManager().createQuery(stringBuilder.toString());
    query.setParameter("volNum", volNum);
    if (!StringUtils.isEmpty(volLetExt)) {
        query.setParameter("volLetExt", volLetExt);
    }
    List<Integer> foliosOnVolume = (List<Integer>) query.getResultList();

    for (long i = 1; i <= foliosInformations.getTotalCarta(); i++) {
        for (int j = 0; j < foliosOnVolume.size(); j++) {
            if (foliosOnVolume.get(j) == i) {
                break;
            } else if (foliosOnVolume.get(j) > i) {
                foliosInformations.setTotalMissingFolios(foliosInformations.getTotalMissingFolios() + 1);
                //LP : Missing numbering is first counter!!!!
                foliosInformations.getMissingNumberingFolios().add(((Long) i).intValue());
                break;
            }
        }

    }
    // Calculating missing folios end

    //Extracting misnumbered Folios...
    stringBuilder = new StringBuilder(
            "SELECT concat(imageProgTypeNum, missedNumbering) FROM Image WHERE volNum=:volNum and volLetExt ");
    if (!StringUtils.isEmpty(volLetExt))
        stringBuilder.append(" = :volLetExt");
    else
        stringBuilder.append(" is null");
    stringBuilder.append(" and missedNumbering is not null ORDER BY imageProgTypeNum ASC");

    query = getEntityManager().createQuery(stringBuilder.toString());
    query.setParameter("volNum", volNum);
    if (!StringUtils.isEmpty(volLetExt)) {
        query.setParameter("volLetExt", volLetExt);
    }

    foliosInformations.setMisnumberedFolios((List<String>) query.getResultList());

    return foliosInformations;
}

From source file:org.medici.bia.dao.image.ImageDAOJpaImpl.java

/**
 * This method updates every totals in input.
 * /*  w  ww.j a va 2  s  .  c o  m*/
 * @param explorer input object to be update.
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
private <T extends VolumeExplorer> void updateExplorerTotals(T explorer) {
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();

    CriteriaQuery<Long> criteriaQueryCount = criteriaBuilder.createQuery(Long.class);
    Root<Image> rootCount = criteriaQueryCount.from(Image.class);
    criteriaQueryCount.select(criteriaBuilder.count(rootCount));

    // Define predicate's elements
    ParameterExpression<Integer> parameterVolNum = criteriaBuilder.parameter(Integer.class, "volNum");
    ParameterExpression<String> parameterVolLeText = StringUtils.isEmpty("volLetExt") ? null
            : criteriaBuilder.parameter(String.class, "volLetExt");

    criteriaQueryCount
            .where(criteriaBuilder.and(criteriaBuilder.equal(rootCount.get("volNum"), parameterVolNum),
                    StringUtils.isEmpty(explorer.getVolLetExt())
                            ? criteriaBuilder.isNull(rootCount.get("volLetExt"))
                            : criteriaBuilder.equal(rootCount.get("volLetExt"), parameterVolLeText)));

    TypedQuery typedQueryCount = getEntityManager().createQuery(criteriaQueryCount);
    typedQueryCount.setParameter("volNum", explorer.getVolNum());
    if (!StringUtils.isEmpty(explorer.getVolLetExt()))
        typedQueryCount.setParameter("volLetExt", explorer.getVolLetExt());
    explorer.setTotal((Long) typedQueryCount.getSingleResult());

    StringBuilder stringBuilder = new StringBuilder(
            "SELECT imageType, imageRectoVerso, max(imageProgTypeNum) FROM Image WHERE volNum=:volNum and volLetExt ");
    if (!StringUtils.isEmpty(explorer.getVolLetExt()))
        stringBuilder.append(" = :volLetExt");
    else
        stringBuilder.append(" is null");
    stringBuilder.append(" group by imageType, imageRectoVerso");

    Query query = getEntityManager().createQuery(stringBuilder.toString());
    query.setParameter("volNum", explorer.getVolNum());
    if (!StringUtils.isEmpty(explorer.getVolLetExt())) {
        query.setParameter("volLetExt", explorer.getVolLetExt());
    }

    List<Object[]> result = (List<Object[]>) query.getResultList();

    // We init every partial-total
    explorer.setTotalRubricario(new Long(0));
    explorer.setTotalCarta(new Long(0));
    explorer.setTotalAppendix(new Long(0));
    explorer.setTotalOther(new Long(0));
    explorer.setTotalGuardia(new Long(0));

    // We set new partial-total values 
    for (int i = 0; i < result.size(); i++) {
        // This is an array defined as [ImageType, Count by ImageType]
        Object[] singleGroup = result.get(i);

        if (((ImageType) singleGroup[0]).equals(ImageType.R)) {
            if (explorer.getTotalRubricario() < new Long(singleGroup[2].toString())) {
                explorer.setTotalRubricario(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.C)) {
            if (explorer.getTotalCarta() < new Long(singleGroup[2].toString())) {
                explorer.setTotalCarta(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.A)) {
            if (explorer.getTotalAppendix() < new Long(singleGroup[2].toString())) {
                explorer.setTotalAppendix(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.O)) {
            if (explorer.getTotalOther() < new Long(singleGroup[2].toString())) {
                explorer.setTotalOther(new Long(singleGroup[2].toString()));
            }
        } else if (((ImageType) singleGroup[0]).equals(ImageType.G)) {
            if (explorer.getTotalGuardia() < new Long(singleGroup[2].toString())) {
                explorer.setTotalGuardia(new Long(singleGroup[2].toString()));
            }
        }
    }
}

From source file:org.medici.bia.dao.volume.VolumeDAOJpaImpl.java

/**
 * {@inheritDoc}/*from  w w w .ja v  a 2s.com*/
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
@Override
public Page searchVolumes(String text, PaginationFilter paginationFilter) throws PersistenceException {
    // Create criteria objects
    CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();

    Page page = new Page(paginationFilter);

    if (paginationFilter.getTotal() == null) {
        CriteriaQuery<Long> criteriaQueryCount = criteriaBuilder.createQuery(Long.class);
        Root<Volume> rootCount = criteriaQueryCount.from(Volume.class);
        criteriaQueryCount.select(criteriaBuilder.count(rootCount));

        List<Predicate> predicates = new ArrayList<Predicate>();
        predicates.add(
                criteriaBuilder.like((Expression) rootCount.get("serieList").get("title"), "%" + text + "%"));
        predicates.add(criteriaBuilder.like((Expression) rootCount.get("serieList").get("subTitle1"),
                "%" + text + "%"));
        predicates.add(criteriaBuilder.like((Expression) rootCount.get("serieList").get("subTitle2"),
                "%" + text + "%"));
        predicates.add(criteriaBuilder.like((Expression) rootCount.get("orgNotes"), "%" + text + "%"));
        predicates.add(criteriaBuilder.like((Expression) rootCount.get("recips"), "%" + text + "%"));
        predicates.add(criteriaBuilder.like((Expression) rootCount.get("researcher"), "%" + text + "%"));
        predicates.add(criteriaBuilder.like((Expression) rootCount.get("senders"), "%" + text + "%"));

        //If we omiss criteriaBuilder.or every predicate is in conjunction with others  
        criteriaQueryCount.where(criteriaBuilder.or(predicates.toArray(new Predicate[] {})));

        TypedQuery typedQueryCount = getEntityManager().createQuery(criteriaQueryCount);
        page.setTotal(new Long((Long) typedQueryCount.getSingleResult()));
    }

    CriteriaQuery<Volume> criteriaQuery = criteriaBuilder.createQuery(Volume.class);
    Root<Volume> root = criteriaQuery.from(Volume.class);

    //We need to duplicate predicates beacause they are link to Root element
    List<Predicate> predicates = new ArrayList<Predicate>();
    predicates.add(criteriaBuilder.like((Expression) root.get("serieList").get("title"), "%" + text + "%"));
    predicates.add(criteriaBuilder.like((Expression) root.get("serieList").get("subTitle1"), "%" + text + "%"));
    predicates.add(criteriaBuilder.like((Expression) root.get("serieList").get("subTitle2"), "%" + text + "%"));
    predicates.add(criteriaBuilder.like((Expression) root.get("orgNotes"), "%" + text + "%"));
    predicates.add(criteriaBuilder.like((Expression) root.get("recips"), "%" + text + "%"));
    predicates.add(criteriaBuilder.like((Expression) root.get("researcher"), "%" + text + "%"));
    predicates.add(criteriaBuilder.like((Expression) root.get("senders"), "%" + text + "%"));

    //If we omiss criteriaBuilder.or every predicate is in conjunction with others  
    criteriaQuery.where(criteriaBuilder.or(predicates.toArray(new Predicate[] {})));
    criteriaQuery.orderBy(criteriaBuilder.asc(root.get("summaryId")));

    // Set values in predicate's elements  
    TypedQuery<Volume> typedQuery = getEntityManager().createQuery(criteriaQuery);
    typedQuery.setFirstResult(paginationFilter.getFirstRecord());
    typedQuery.setMaxResults(paginationFilter.getLength());
    page.setList(typedQuery.getResultList());

    return page;
}

From source file:org.niord.core.mail.ScheduledMailService.java

/**
 * Searches the filtered set of scheduled mails
 * @param params the search parameters/*from   w ww. ja  va  2s. co m*/
 * @return the search result
 */
public PagedSearchResultVo<ScheduledMail> search(ScheduledMailSearchParams params) {

    long t0 = System.currentTimeMillis();

    PagedSearchResultVo<ScheduledMail> result = new PagedSearchResultVo<>();

    CriteriaBuilder cb = em.getCriteriaBuilder();

    // First compute the total number of matching mails
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<ScheduledMail> countMailRoot = countQuery.from(ScheduledMail.class);

    countQuery.select(cb.count(countMailRoot))
            .where(buildQueryPredicates(cb, countQuery, countMailRoot, params))
            .orderBy(cb.desc(countMailRoot.get("created")));

    result.setTotal(em.createQuery(countQuery).getSingleResult());

    // Then, extract the current page of matches
    CriteriaQuery<ScheduledMail> query = cb.createQuery(ScheduledMail.class);
    Root<ScheduledMail> mailRoot = query.from(ScheduledMail.class);
    query.select(mailRoot).where(buildQueryPredicates(cb, query, mailRoot, params))
            .orderBy(cb.desc(countMailRoot.get("created")));

    List<ScheduledMail> mails = em.createQuery(query).setMaxResults(params.getMaxSize())
            .setFirstResult(params.getPage() * params.getMaxSize()).getResultList();
    result.setData(mails);
    result.updateSize();

    log.info("Search [" + params + "] returned " + result.getSize() + " of " + result.getTotal() + " in "
            + (System.currentTimeMillis() - t0) + " ms");

    return result;
}

From source file:org.seedstack.i18n.rest.internal.KeyJpaFinder.java

@Override
protected long computeFullRequestSize(Map<String, Object> criteria) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> q = cb.createQuery(Long.class);
    Root<Key> k = q.from(Key.class);
    q.select(cb.count(k));
    if (criteria != null) {
        q.where(cb.and(getPredicates(criteria, q, cb, k)));
    }/*from   w  ww .  j a v  a2s.c  o  m*/
    return entityManager.createQuery(q).getSingleResult();
}

From source file:org.springframework.data.jpa.repository.support.SimpleJpaRepository.java

/**
 * Creates a new count query for the given {@link Specification}.
 * //from ww w  .  j a  va2  s  . c o m
 * @param spec can be {@literal null}.
 * @return
 */
protected TypedQuery<Long> getCountQuery(Specification<T> spec) {

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Long> query = builder.createQuery(Long.class);

    Root<T> root = applySpecificationToCriteria(spec, query);

    if (query.isDistinct()) {
        query.select(builder.countDistinct(root));
    } else {
        query.select(builder.count(root));
    }

    return em.createQuery(query);
}

From source file:org.xlcloud.service.dao.JpaVirtualClusterDefinitionsDao.java

/** {@inheritDoc} */
@Override//from   w  w w  . jav a 2  s  .co m
public List<VirtualClusterDefinitionModel> findByTypeAndTags(Long accountId, String type, Set<String> tags,
        boolean paged) {
    if (LOG.isDebugEnabled()) {
        LOG.debug("finding vc definitions with type id: " + type + " and following tag ids:"
                + ToStringBuilder.reflectionToString(tags));
    }

    CriteriaBuilder criteriaBuilder = getCriteriaBuilder();
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<VirtualClusterDefinitionModel> vcDefRoot = query.from(VirtualClusterDefinitionModel.class);

    Predicate typePredicate = criteriaBuilder.equal(vcDefRoot.get(VirtualClusterDefinitionModel_.type), type);
    Predicate tagsPredicate = vcDefRoot.get(VirtualClusterDefinitionModel_.tags).in(tags);

    boolean skipTagsJoin = tags.isEmpty();
    Predicate predicate = skipTagsJoin ? typePredicate : criteriaBuilder.and(typePredicate, tagsPredicate);

    Predicate scopePredicate;
    if (accountId != null) {
        Predicate accountPredicate = criteriaBuilder
                .equal(vcDefRoot.get(VirtualClusterDefinitionModel_.accountId), accountId);
        Predicate privatePredicate = criteriaBuilder
                .equal(vcDefRoot.get(VirtualClusterDefinitionModel_.catalogScope), CatalogScope.PRIVATE);
        scopePredicate = criteriaBuilder.and(accountPredicate, privatePredicate);
    } else {
        scopePredicate = criteriaBuilder.equal(vcDefRoot.get(VirtualClusterDefinitionModel_.catalogScope),
                CatalogScope.PUBLIC);
    }

    predicate = criteriaBuilder.and(predicate, scopePredicate);

    query.multiselect(vcDefRoot, criteriaBuilder.count(vcDefRoot));
    query.where(predicate);
    query.groupBy(vcDefRoot);
    if (!skipTagsJoin) {
        query.having(criteriaBuilder.equal(criteriaBuilder.count(vcDefRoot), tags.size()));
    }

    List<Tuple> list = getResultList(query, vcDefRoot, paged);
    List<VirtualClusterDefinitionModel> results = new LinkedList<VirtualClusterDefinitionModel>();
    for (Tuple t : list) {
        results.add((VirtualClusterDefinitionModel) t.get(0));
    }
    return results;
}

From source file:ru.codeinside.adm.AdminServiceImpl.java

@Override
public int countInfoSystems(boolean source) {
    final CriteriaBuilder _ = em.getCriteriaBuilder();
    final CriteriaQuery<Number> query = _.createQuery(Number.class);
    final Root<InfoSystem> infoSystems = query.from(InfoSystem.class);
    if (source) {
        query.where(_.equal(infoSystems.get(InfoSystem_.source), true));
    }//  w ww. ja  va2 s.co m
    return count(query.select(_.count(infoSystems)));
}