Example usage for org.hibernate.criterion Projections count

List of usage examples for org.hibernate.criterion Projections count

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.criterion.*
  5. Projections
  6. count

Introduction

In this page you can find the example usage for org.hibernate.criterion Projections count.

Prototype

public static CountProjection count(String propertyName)

Source Link

Document

A property value count projection

Usage

From source file:org.jasig.ssp.dao.CaseloadDao.java

License:Apache License

private PagingWrapper<CoachCaseloadRecordCountForProgramStatus> caseloadCountsByStatusWithDateRestrictions(
        Criterion dateRestrictions, List<UUID> studentTypeIds, List<UUID> serviceReasonIds,
        List<UUID> specialServiceGroupIds, String homeDepartment, SortingAndPaging sAndP) {

    final Criteria query = createCriteria();

    query.createAlias("programStatuses", "ps").createAlias("coach", "c");

    if (dateRestrictions != null) {
        query.add(dateRestrictions);//from   ww  w.ja v  a 2 s .  c  om
    }

    if (studentTypeIds != null && !studentTypeIds.isEmpty()) {
        query.add(Restrictions.in("studentType.id", studentTypeIds));
    }

    if (serviceReasonIds != null && !serviceReasonIds.isEmpty()) {
        query.createAlias("serviceReasons", "serviceReasons");
        query.createAlias("serviceReasons.serviceReason", "serviceReason");
        query.add(Restrictions.in("serviceReason.id", serviceReasonIds));
        query.add(Restrictions.eq("serviceReasons.objectStatus", ObjectStatus.ACTIVE));
    }

    if (specialServiceGroupIds != null && !specialServiceGroupIds.isEmpty()) {
        query.createAlias("specialServiceGroups", "personSpecialServiceGroups").add(
                Restrictions.in("personSpecialServiceGroups.specialServiceGroup.id", specialServiceGroupIds));
        query.add(Restrictions.eq("personSpecialServiceGroups.objectStatus", ObjectStatus.ACTIVE));
    }

    if (homeDepartment == null || homeDepartment.length() <= 0)
        query.createAlias("coach.staffDetails", "sd", JoinType.LEFT_OUTER_JOIN);
    else {
        query.createAlias("coach.staffDetails", "sd");
        query.add(Restrictions.eq("sd.departmentName", homeDepartment));
    }
    ProjectionList projectionList = Projections.projectionList()
            .add(Projections.groupProperty("c.id").as("coachId"));
    // TODO find a way to turn these into more generic and centralized
    // feature checks on the Dialect so we at least aren't scattering
    // Dialect-specific code all over the place
    Dialect dialect = ((SessionFactoryImplementor) sessionFactory).getDialect();
    if (dialect instanceof SQLServerDialect) {
        // sql server requires all these to part of the grouping
        projectionList.add(Projections.groupProperty("c.lastName").as("coachLastName"))
                .add(Projections.groupProperty("c.firstName").as("coachFirstName"))
                .add(Projections.groupProperty("c.middleName").as("coachMiddleName"))
                .add(Projections.groupProperty("c.schoolId").as("coachSchoolId"))
                .add(Projections.groupProperty("c.username").as("coachUsername"));
    } else {
        // other dbs (postgres) don't need these in the grouping
        projectionList.add(Projections.property("c.lastName").as("coachLastName"))
                .add(Projections.property("c.firstName").as("coachFirstName"))
                .add(Projections.property("c.middleName").as("coachMiddleName"))
                .add(Projections.property("c.schoolId").as("coachSchoolId"))
                .add(Projections.property("c.username").as("coachUsername"));
    }
    projectionList.add(Projections.groupProperty("sd.departmentName").as("coachDepartmentName"))
            .add(Projections.groupProperty("ps.programStatus.id").as("programStatusId"))
            .add(Projections.count("ps.programStatus.id").as("count"));
    query.setProjection(projectionList);

    if (sAndP == null || !(sAndP.isSorted())) {
        // there are assumptions in CaseloadServiceImpl about this
        // default ordering... make sure it stays synced up
        query.addOrder(Order.asc("c.lastName")).addOrder(Order.asc("c.firstName"))
                .addOrder(Order.asc("c.middleName"));

        // can't sort on program status name without another join, but
        // sorting on id is non-deterministic across dbs (sqlserver sorts
        // UUIDs one way, Postgres another, so you can't write a single
        // integration test for both), so more dialect specific magic here.
        if (dialect instanceof SQLServerDialect) {
            query.addOrder(OrderAsString.asc("ps.programStatus.id"));
        } else {
            query.addOrder(Order.asc("ps.programStatus.id"));
        }
    }

    if (sAndP != null) {
        sAndP.addAll(query);
    }

    query.setResultTransformer(
            new AliasToBeanResultTransformer(CoachCaseloadRecordCountForProgramStatus.class));

    // item count
    Long totalRows = 0L;
    if ((sAndP != null) && sAndP.isPaged()) {
        query.setProjection(new MultipleCountProjection("c.id;ps.programStatus.id").setDistinct());
        totalRows = (Long) query.uniqueResult();

        if (totalRows == 0) {
            Collection<CoachCaseloadRecordCountForProgramStatus> empty = Lists.newArrayListWithCapacity(0);
            return new PagingWrapper<CoachCaseloadRecordCountForProgramStatus>(0, empty);
        }

        // clear the row count projection
        query.setProjection(null);
    }
    return sAndP == null ? new PagingWrapper<CoachCaseloadRecordCountForProgramStatus>(query.list())
            : new PagingWrapper<CoachCaseloadRecordCountForProgramStatus>(totalRows, query.list());
}

From source file:org.jasig.ssp.dao.EarlyAlertDao.java

License:Apache License

private Map<UUID, Number> getCountOfAlertsForPeopleId(@NotNull final Collection<UUID> personIds,
        CriteriaCallback criteriaCallback) {
    // validate/* w  w  w  .jav  a 2  s. com*/
    if (personIds == null) {
        throw new IllegalArgumentException("Must include a collection of personIds (students).");
    }

    // setup return value
    final Map<UUID, Number> countForPeopleId = Maps.newHashMap();

    // only run the query to fill the return Map if values were given
    if (!personIds.isEmpty()) {

        BatchProcessor<UUID, Object[]> processor = new BatchProcessor<UUID, Object[]>(personIds);
        do {
            Criteria query = createCriteria();

            final ProjectionList projections = Projections.projectionList();
            projections.add(Projections.groupProperty("person.id").as("personId"));
            projections.add(Projections.count("id"));
            query.setProjection(projections);

            query.add(Restrictions.eq("objectStatus", ObjectStatus.ACTIVE));

            if (criteriaCallback != null) {
                query = criteriaCallback.criteria(query);
            }

            processor.process(query, "person.id");
        } while (processor.moreToProcess());

        // run query
        @SuppressWarnings("unchecked")
        final List<Object[]> results = processor.getSortedAndPagedResultsAsList();

        // put query results into return value
        for (final Object[] result : results) {
            countForPeopleId.put((UUID) result[0], (Number) result[1]);
        }

        // ensure all people IDs that were request exist in return Map
        for (final UUID id : personIds) {
            if (!countForPeopleId.containsKey(id)) {
                countForPeopleId.put(id, 0);
            }
        }
    }

    return countForPeopleId;
}

From source file:org.jasig.ssp.dao.EarlyAlertDao.java

License:Apache License

public List<Triple<String, Long, Long>> getEarlyAlertReasonTypeCountByCriteria(Campus campus, String termCode,
        Date createdDateFrom, Date createdDateTo, ObjectStatus objectStatus) {
    final Criteria criteria = createCriteria();

    if (termCode != null) {
        criteria.add(Restrictions.eq("courseTermCode", termCode));
    }// w  w  w. j a  v a 2 s .c  o  m

    if (createdDateFrom != null) {
        criteria.add(Restrictions.ge("createdDate", createdDateFrom));
    }

    if (createdDateTo != null) {
        criteria.add(Restrictions.le("createdDate", createdDateTo));
    }

    if (campus != null) {
        criteria.add(Restrictions.eq("campus", campus));
    }

    if (objectStatus != null) {
        criteria.add(Restrictions.eq("objectStatus", objectStatus));
    }

    criteria.createAlias("earlyAlertReasonIds", "eareasons");

    ProjectionList projections = Projections.projectionList().add(Projections.property("eareasons.name"))
            .add(Projections.countDistinct("person")).add(Projections.count("id"));
    projections.add(Projections.groupProperty("eareasons.name"));

    criteria.setProjection(projections);

    criteria.addOrder(Order.asc("eareasons.name"));

    final List<Triple<String, Long, Long>> reasonCounts = new ArrayList<>();

    for (final Object result : criteria.list()) {
        Object[] resultReasonCounts = (Object[]) result;
        reasonCounts.add(new Triple((String) resultReasonCounts[0], (Long) resultReasonCounts[1],
                (Long) resultReasonCounts[2]));
    }

    return reasonCounts;
}

From source file:org.jboss.example.portlet.jasper.db.PhoneCallHibernateServiceImpl.java

License:Open Source License

public List<Object[]> getChartDataForPhoneNumber(String phoneNumber) {
    Session s = getHibernateSession();//from w  ww .j av  a2 s .  c o  m
    Transaction t = s.beginTransaction();
    /*   select des_phone_number, count(*) from pcall_recs 
        where phone_number = ':phoneNumber'
       group by des_phone_number  */

    try {
        Criteria c = s.createCriteria(PhoneCallBean.class)
                .setProjection(Projections.projectionList().add(Projections.groupProperty("desPhoneNumber"))
                        .add(Projections.count("callerName"), "callsCount"))
                .add(Restrictions.eq("phoneNumber", phoneNumber));

        /* Result of Hibernate Query with aggregate function is List<Object[]> */
        @SuppressWarnings("unchecked")
        List<Object[]> l = c.list();
        t.commit();

        return l;
    } catch (Exception e) {
        log.error(e.getMessage());
        e.printStackTrace();
    }

    return null;
}

From source file:org.openmrs.api.db.hibernate.HibernateFormDAO.java

License:Mozilla Public License

/**
 * @see org.openmrs.api.db.FormDAO#getFormCount(java.lang.String, java.lang.Boolean,
 *      java.util.Collection, java.lang.Boolean, java.util.Collection, java.util.Collection,
 *      java.util.Collection)/* w  w w .jav  a  2s . c o m*/
 */
public Integer getFormCount(String partialName, Boolean published, Collection<EncounterType> encounterTypes,
        Boolean retired, Collection<FormField> containingAnyFormField,
        Collection<FormField> containingAllFormFields, Collection<Field> fields) throws DAOException {

    Criteria crit = getFormCriteria(partialName, published, encounterTypes, retired, containingAnyFormField,
            containingAllFormFields, fields);

    crit.setProjection(Projections.count("formId"));

    return OpenmrsUtil.convertToInteger((Long) crit.uniqueResult());
}

From source file:org.openmrs.api.db.hibernate.HibernateFormDAO.java

License:Mozilla Public License

/**
 * Convenience method to create the same hibernate criteria object for both getForms and
 * getFormCount/* w  w w  .j  a v a2  s .  c  o m*/
 *
 * @param partialName
 * @param published
 * @param encounterTypes
 * @param retired
 * @param containingAnyFormField
 * @param containingAllFormFields
 * @param fields
 * @return
 */
private Criteria getFormCriteria(String partialName, Boolean published,
        Collection<EncounterType> encounterTypes, Boolean retired, Collection<FormField> containingAnyFormField,
        Collection<FormField> containingAllFormFields, Collection<Field> fields) {

    Criteria crit = sessionFactory.getCurrentSession().createCriteria(Form.class, "form");

    if (StringUtils.isNotEmpty(partialName)) {
        crit.add(Restrictions.or(Restrictions.like("name", partialName, MatchMode.START),
                Restrictions.like("name", " " + partialName, MatchMode.ANYWHERE)));
    }
    if (published != null) {
        crit.add(Restrictions.eq("published", published));
    }

    if (!encounterTypes.isEmpty()) {
        crit.add(Restrictions.in("encounterType", encounterTypes));
    }

    if (retired != null) {
        crit.add(Restrictions.eq("retired", retired));
    }

    // TODO junit test
    if (!containingAnyFormField.isEmpty()) {
        // Convert form field persistents to integers
        Set<Integer> anyFormFieldIds = new HashSet<Integer>();
        for (FormField ff : containingAnyFormField) {
            anyFormFieldIds.add(ff.getFormFieldId());
        }

        DetachedCriteria subquery = DetachedCriteria.forClass(FormField.class, "ff");
        subquery.setProjection(Projections.property("ff.form.formId"));
        subquery.add(Restrictions.in("ff.formFieldId", anyFormFieldIds));
        crit.add(Subqueries.propertyIn("form.formId", subquery));
    }

    //select * from form where len(containingallformfields) = (select count(*) from form_field ff where ff.form_id = form_id and form_field_id in (containingallformfields);
    if (!containingAllFormFields.isEmpty()) {

        // Convert form field persistents to integers
        Set<Integer> allFormFieldIds = new HashSet<Integer>();
        for (FormField ff : containingAllFormFields) {
            allFormFieldIds.add(ff.getFormFieldId());
        }
        DetachedCriteria subquery = DetachedCriteria.forClass(FormField.class, "ff");
        subquery.setProjection(Projections.count("ff.formFieldId"));
        subquery.add(Restrictions.eqProperty("ff.form", "form"));
        subquery.add(Restrictions.in("ff.formFieldId", allFormFieldIds));

        crit.add(Subqueries.eq(Long.valueOf(containingAllFormFields.size()), subquery));
    }

    // get all forms (dupes included) that have this field on them
    if (!fields.isEmpty()) {
        Criteria crit2 = crit.createCriteria("formFields", "ff");
        crit2.add(Restrictions.eqProperty("ff.form.formId", "form.formId"));
        crit2.add(Restrictions.in("ff.field", fields));
    }

    return crit;
}

From source file:org.openmrs.module.idcards.db.hibernate.HibernateIdcardsDAO.java

License:Open Source License

/**
 * @see org.openmrs.module.idcards.db.IdcardsDAO#getNumberOfUnprintedGeneratedIdentifiers()
 *//*from   www. ja v  a 2 s.  c  om*/
public Long getNumberOfUnprintedGeneratedIdentifiers() throws DAOException {
    Criteria crit = sessionFactory.getCurrentSession().createCriteria(GeneratedIdentifier.class);
    crit.setProjection(Projections.count("printed"));
    crit.add(Expression.eq("printed", false));

    return (Long) crit.uniqueResult();
}

From source file:org.openmrs.module.yank.api.db.hibernate.HibernateYankDAO.java

License:Open Source License

@Override
public Integer countYanks(String query) {
    Criteria crit = sessionFactory.getCurrentSession().createCriteria(Yank.class)
            .setProjection(Projections.count("yankId"));

    if (!StringUtils.isBlank(query))
        crit.add(Restrictions.like("summary", query, MatchMode.ANYWHERE));

    return (Integer) crit.uniqueResult();
}

From source file:org.openremote.beehive.api.service.impl.GenericDAO.java

License:Open Source License

/**
 * Counts all the record according to one field
 * /*from  ww w  .j a  v  a2  s  .  co m*/
 * @param clazz
 *           the class you want to count
 * @param fieldName
 * @param fieldValue
 * @return count number
 */
@SuppressWarnings("unchecked")
public int countByField(final Class clazz, final String fieldName, final Object fieldValue) {
    if (fieldValue != null && !"".equals(fieldValue.toString().trim())) {
        return Integer.valueOf(getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Criteria criteria = session.createCriteria(clazz.getName());
                return criteria.add(Restrictions.eq(fieldName, fieldValue))
                        .setProjection(Projections.count(fieldName)).uniqueResult();
            }
        }, true).toString());
    } else {
        return 0;
    }
}

From source file:org.openremote.modeler.dao.GenericDAO.java

License:Open Source License

/**
 * Counts all the record according to one field.
 * /*from  ww  w  .ja va 2 s. co  m*/
 * @param clazz the class you want to count
 * @param fieldName the field name
 * @param fieldValue the field value
 * 
 * @return count number
 */
@SuppressWarnings("unchecked")
public int countByField(final Class clazz, final String fieldName, final Object fieldValue) {
    if (fieldValue != null && !"".equals(fieldValue.toString().trim())) {
        return Integer.valueOf(getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws SQLException {
                Criteria criteria = session.createCriteria(clazz.getName());
                return criteria.add(Restrictions.eq(fieldName, fieldValue))
                        .setProjection(Projections.count(fieldName)).uniqueResult();
            }
        }, true).toString());
    } else {
        return 0;
    }
}