Example usage for org.hibernate.criterion Restrictions sqlRestriction

Introduction

In this page you can find the example usage for org.hibernate.criterion Restrictions sqlRestriction.

Prototype

public static Criterion sqlRestriction(String sql, Object value, Type type) 

Source Link

Document

Create a restriction expressed in SQL with one JDBC parameter.

Usage

From source file:org.jbpm.taskmgmt.exe.TaskVariablesDbTest.java

License:Open Source License

private TaskInstance getTask(String variableName, long variableValue, String actorId) {
    return (TaskInstance) jbpmContext.getSession().createCriteria(TaskInstance.class)
            .add(Restrictions.eq("actorId", actorId)).createCriteria("variableInstances")
            .add(Restrictions.eq("name", variableName))
            .add(Restrictions.sqlRestriction("{alias}.LONGVALUE_ = ?", new Long(variableValue), Hibernate.LONG))
            .uniqueResult();/*from  w  ww .j av a 2 s  . c o  m*/
}

---

From source file:org.motechproject.server.model.db.hibernate.HibernateMotechDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Obs> getActivePregnanciesDueDateObs(Facility facility, Date fromDueDate, Date toDueDate,
        Concept pregnancyDueDateConcept, Concept pregnancyConcept, Concept pregnancyStatusConcept,
        Integer maxResults) {//ww  w .jav a 2s.c o  m
    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = session.createCriteria(Obs.class, "o");

    criteria.add(Restrictions.eq("o.voided", false));
    criteria.add(Restrictions.eq("o.concept", pregnancyDueDateConcept));

    if (fromDueDate != null) {
        criteria.add(Restrictions.ge("o.valueDatetime", fromDueDate));
    }
    if (toDueDate != null) {
        criteria.add(Restrictions.le("o.valueDatetime", toDueDate));
    }

    criteria.createAlias("o.person", "p");
    criteria.add(Restrictions.eq("p.personVoided", false));

    criteria.createAlias("o.obsGroup", "g");
    criteria.add(Restrictions.eq("g.concept", pregnancyConcept));
    criteria.add(Restrictions.eq("g.voided", false));

    DetachedCriteria pregnancyActiveCriteria = DetachedCriteria.forClass(Obs.class, "s")
            .setProjection(Projections.id()).add(Restrictions.eq("s.voided", false))
            .add(Restrictions.eq("s.concept", pregnancyStatusConcept))
            .add(Restrictions.eq("s.valueNumeric", 1.0))
            .add(Restrictions.eqProperty("s.obsGroup.obsId", "g.obsId"))
            .add(Restrictions.eqProperty("s.person.personId", "p.personId"));

    criteria.add(Subqueries.exists(pregnancyActiveCriteria));

    DetachedCriteria pregnancyInactiveCriteria = DetachedCriteria.forClass(Obs.class, "e")
            .setProjection(Projections.id()).add(Restrictions.eq("e.voided", false))
            .add(Restrictions.eq("e.concept", pregnancyStatusConcept))
            .add(Restrictions.eq("e.valueNumeric", 0.0))
            .add(Restrictions.eqProperty("e.obsGroup.obsId", "g.obsId"))
            .add(Restrictions.eqProperty("e.person.personId", "p.personId"));

    criteria.add(Subqueries.notExists(pregnancyInactiveCriteria));

    if (facility != null) {
        criteria.add(Restrictions.sqlRestriction(
                "exists (select f.id from motechmodule_facility f "
                        + "inner join motechmodule_facility_patient fp " + "on f.id = fp.facility_id "
                        + "where f.facility_id = ? and fp.patient_id = {alias}.person_id)",
                facility.getFacilityId(), Hibernate.INTEGER));
    }

    criteria.addOrder(Order.asc("o.valueDatetime"));
    if (maxResults != null) {
        criteria.setMaxResults(maxResults);
    }
    return criteria.list();
}

---

From source file:org.motechproject.server.model.db.hibernate.HibernateMotechDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Encounter> getEncounters(Facility facility, EncounterType encounterType, Date fromDate, Date toDate,
        Integer maxResults) {/*  ww w .  j  av a 2s . c om*/
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Encounter.class, "e");
    criteria.add(Restrictions.eq("e.voided", false));
    criteria.add(Restrictions.eq("e.encounterType", encounterType));
    criteria.createAlias("e.patient", "p");
    criteria.add(Restrictions.eq("p.voided", false));
    if (fromDate != null) {
        criteria.add(Restrictions.ge("e.encounterDatetime", fromDate));
    }
    if (toDate != null) {
        criteria.add(Restrictions.le("e.encounterDatetime", toDate));
    }
    if (facility != null) {
        criteria.add(Restrictions.sqlRestriction(
                "exists (select f.id from motechmodule_facility f "
                        + "inner join motechmodule_facility_patient fp " + "on f.id = fp.facility_id "
                        + "where f.facility_id = ? and fp.patient_id = {alias}.patient_id)",
                facility.getFacilityId(), Hibernate.INTEGER));
    }
    criteria.addOrder(Order.asc("e.encounterDatetime"));
    if (maxResults != null) {
        criteria.setMaxResults(maxResults);
    }
    return criteria.list();
}

---

From source file:org.motechproject.server.model.db.hibernate.HibernateMotechDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<ExpectedObs> getExpectedObs(Patient patient, Facility facility, String[] groups, Date minDueDate,
        Date maxDueDate, Date maxLateDate, Date minMaxDate, Integer maxResults) {
    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = session.createCriteria(ExpectedObs.class);
    if (patient != null) {
        criteria.add(Restrictions.eq("patient", patient));
    }//from  w w  w . j  a v a2s  .c om
    if (groups != null && groups.length != 0) {
        criteria.add(Restrictions.in("group", groups));
    }
    if (minDueDate != null) {
        criteria.add(Restrictions.ge("dueObsDatetime", minDueDate));
    }
    if (maxDueDate != null) {
        criteria.add(Restrictions.le("dueObsDatetime", maxDueDate));
    }
    if (maxLateDate != null) {
        criteria.add(Restrictions.le("lateObsDatetime", maxLateDate));
    }
    if (minMaxDate != null) {
        criteria.add(Restrictions.or(Restrictions.isNull("maxObsDatetime"),
                Restrictions.gt("maxObsDatetime", minMaxDate)));
    }
    if (facility != null) {
        criteria.add(Restrictions.sqlRestriction(
                "exists (select f.id from motechmodule_facility f "
                        + "inner join motechmodule_facility_patient fp " + "on f.id = fp.facility_id "
                        + "where f.facility_id = ? and fp.patient_id = {alias}.patient_id)",
                facility.getFacilityId(), Hibernate.INTEGER));
    }
    criteria.add(Restrictions.eq("voided", false));
    criteria.addOrder(Order.asc("dueObsDatetime"));
    if (maxResults != null) {
        criteria.setMaxResults(maxResults);
    }
    return criteria.list();
}

---

From source file:org.motechproject.server.model.db.hibernate.HibernateMotechDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<ExpectedEncounter> getExpectedEncounter(Patient patient, Facility facility, String[] groups,
        Date minDueDate, Date maxDueDate, Date maxLateDate, Date minMaxDate, Integer maxResults) {
    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = session.createCriteria(ExpectedEncounter.class);
    if (patient != null) {
        criteria.add(Restrictions.eq("patient", patient));
    }/* ww w .j a va2s . c o m*/
    if (groups != null && groups.length != 0) {
        criteria.add(Restrictions.in("group", groups));
    }
    if (minDueDate != null) {
        criteria.add(Restrictions.ge("dueEncounterDatetime", minDueDate));
    }
    if (maxDueDate != null) {
        criteria.add(Restrictions.le("dueEncounterDatetime", maxDueDate));
    }
    if (maxLateDate != null) {
        criteria.add(Restrictions.le("lateEncounterDatetime", maxLateDate));
    }
    if (minMaxDate != null) {
        criteria.add(Restrictions.or(Restrictions.isNull("maxEncounterDatetime"),
                Restrictions.gt("maxEncounterDatetime", minMaxDate)));
    }
    if (facility != null) {
        criteria.add(Restrictions.sqlRestriction(
                "exists (select f.id from motechmodule_facility f "
                        + "inner join motechmodule_facility_patient fp " + "on f.id = fp.facility_id "
                        + "where f.facility_id = ? and fp.patient_id = {alias}.patient_id)",
                facility.getFacilityId(), Hibernate.INTEGER));
    }
    criteria.add(Restrictions.eq("voided", false));
    criteria.addOrder(Order.asc("dueEncounterDatetime"));
    if (maxResults != null) {
        criteria.setMaxResults(maxResults);
    }
    return criteria.list();
}

---

From source file:org.motechproject.server.model.db.hibernate.HibernateMotechDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Patient> getDuplicatePatients(String firstName, String lastName, String preferredName,
        Date birthDate, Integer facilityId, String phoneNumber, PersonAttributeType phoneNumberAttrType,
        String nhisNumber, PersonAttributeType nhisAttrType, String patientId,
        PatientIdentifierType patientIdType, Integer maxResults) {

    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Patient.class);

    criteria.createAlias("names", "name");
    criteria.createAlias("attributes", "att");
    criteria.createAlias("identifiers", "id");

    criteria.add(Restrictions.eq("voided", false));
    criteria.add(Restrictions.eq("name.voided", false));
    criteria.add(Restrictions.eq("att.voided", false));
    criteria.add(Restrictions.eq("id.voided", false));

    Criterion patientIdCriterion = Restrictions.and(Restrictions.eq("id.identifierType", patientIdType),
            Restrictions.eq("id.identifier", patientId));
    Criterion nhisCriterion = Restrictions.and(Restrictions.eq("att.attributeType", nhisAttrType),
            Restrictions.eq("att.value", nhisNumber));
    Criterion nameCriterion = Restrictions.or(
            Restrictions.and(Restrictions.eq("name.givenName", firstName),
                    Restrictions.eq("name.familyName", lastName)),
            Restrictions.and(Restrictions.eq("name.givenName", preferredName),
                    Restrictions.eq("name.familyName", lastName)));
    Criterion phoneCriterion = Restrictions.and(Restrictions.eq("att.attributeType", phoneNumberAttrType),
            Restrictions.eq("att.value", phoneNumber));

    Disjunction otherCriterion = Restrictions.disjunction();
    otherCriterion.add(Restrictions.eq("birthdate", birthDate));
    otherCriterion.add(Restrictions.sqlRestriction(
            "exists (select f.id from motechmodule_facility f " + "inner join motechmodule_facility_patient fp "
                    + "on f.id = fp.facility_id "
                    + "where f.facility_id = ? and fp.patient_id = {alias}.patient_id)",
            facilityId, Hibernate.INTEGER));
    otherCriterion.add(phoneCriterion);//ww w  . j av a 2s .  c  o  m

    // Get Patients by PatientId or NHIS or
    // (((FirstName and LastName) or (PreferredName and LastName)) and
    // (BirthDate or Community or PhoneNumber))))
    Disjunction finalCriterion = Restrictions.disjunction();
    finalCriterion.add(patientIdCriterion);
    finalCriterion.add(nhisCriterion);
    finalCriterion.add(Restrictions.and(nameCriterion, otherCriterion));

    criteria.add(finalCriterion);

    criteria.addOrder(Order.asc("name.givenName"));
    criteria.addOrder(Order.asc("name.familyName"));
    criteria.addOrder(Order.asc("birthdate"));

    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    if (maxResults != null) {
        criteria.setMaxResults(maxResults);
    }
    return criteria.list();
}

---

From source file:org.motechproject.server.model.db.hibernate.HibernateMotechDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Patient> getPatients(String firstName, String lastName, String preferredName, Date birthDate,
        Integer facilityId, String phoneNumber, PersonAttributeType phoneNumberAttrType, String nhisNumber,
        PersonAttributeType nhisAttrType, Integer communityId, String patientId,
        PatientIdentifierType patientIdType, Integer maxResults) {

    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Patient.class, "p");

    criteria.createAlias("p.names", "name");
    criteria.createAlias("p.identifiers", "id");

    criteria.add(Restrictions.eq("p.voided", false));
    criteria.add(Restrictions.eq("name.voided", false));
    criteria.add(Restrictions.eq("id.voided", false));

    if (patientId != null && patientIdType != null) {
        criteria.add(Restrictions.and(Restrictions.eq("id.identifierType", patientIdType),
                Restrictions.eq("id.identifier", patientId)));
    }/* w w w.j a v  a 2 s  .c o  m*/

    Criterion firstNameCriterion = Restrictions.like("name.givenName", firstName, MatchMode.ANYWHERE);
    Criterion preferredNameCriterion = Restrictions.like("name.givenName", preferredName, MatchMode.ANYWHERE);
    if (firstName != null && preferredName != null) {
        criteria.add(Restrictions.or(firstNameCriterion, preferredNameCriterion));
    } else if (firstName != null) {
        criteria.add(firstNameCriterion);
    } else if (preferredName != null) {
        criteria.add(preferredNameCriterion);
    }

    if (lastName != null) {
        criteria.add(Restrictions.like("name.familyName", lastName, MatchMode.ANYWHERE));
    }

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

    if (facilityId != null) {
        criteria.add(Restrictions.sqlRestriction(
                "exists (select f.id from motechmodule_facility f "
                        + "inner join motechmodule_facility_patient fp " + "on f.id = fp.facility_id "
                        + "where f.facility_id = ? and fp.patient_id = {alias}.patient_id)",
                facilityId, Hibernate.INTEGER));
    }

    if (communityId != null) {
        criteria.add(Restrictions.sqlRestriction(
                "exists (select c.id from motechmodule_community c "
                        + "inner join motechmodule_community_patient cp " + "on c.id = cp.community_id "
                        + "where c.community_id = ? and cp.patient_id = {alias}.patient_id)",
                communityId, Hibernate.INTEGER));
    }

    if (nhisNumber != null && nhisAttrType != null) {
        DetachedCriteria nhisCriteria = DetachedCriteria.forClass(PersonAttribute.class, "nattr")
                .setProjection(Projections.id()).add(Restrictions.eq("nattr.voided", false))
                .add(Restrictions.eqProperty("nattr.person.personId", "p.patientId"))
                .add(Restrictions.and(Restrictions.eq("nattr.attributeType", nhisAttrType),
                        Restrictions.eq("nattr.value", nhisNumber)));

        criteria.add(Subqueries.exists(nhisCriteria));
    }

    if (phoneNumber != null && phoneNumberAttrType != null) {
        DetachedCriteria phoneCriteria = DetachedCriteria.forClass(PersonAttribute.class, "pattr")
                .setProjection(Projections.id()).add(Restrictions.eq("pattr.voided", false))
                .add(Restrictions.eqProperty("pattr.person.personId", "p.patientId"))
                .add(Restrictions.and(Restrictions.eq("pattr.attributeType", phoneNumberAttrType),
                        Restrictions.eq("pattr.value", phoneNumber)));

        criteria.add(Subqueries.exists(phoneCriteria));
    }

    criteria.addOrder(Order.asc("name.givenName"));
    criteria.addOrder(Order.asc("name.familyName"));
    criteria.addOrder(Order.asc("p.birthdate"));

    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    if (maxResults != null) {
        criteria.setMaxResults(maxResults);
    }
    return criteria.list();
}

---

From source file:org.openeos.services.ui.form.abstractform.UIBeanSelectorProvider.java

License:Apache License

private Criterion buildSqlRestriction(String sqlRestriction, Object value) {
    try {//  ww w.  ja  va2  s. c  o  m
        String result = sqlRestriction;
        int first = result.indexOf("@");
        List<Object> values = new ArrayList<Object>();
        List<Type> types = new ArrayList<Type>();
        while (first != -1) {
            String parameter = result.substring(first);
            int second = parameter.indexOf("@", 1);
            if (second == -1) {
                LOG.warn(
                        "There are a sql restrictoion not well formed, the sql restriction is: '{}' for bean class {} ",
                        sqlRestriction, beanClass.getName());
                break;
            }
            parameter = parameter.substring(0, second + 1);
            String parameterWithoutRim = parameter.substring(1, parameter.length() - 1);
            Map<String, Object> mvelContext = new HashMap<String, Object>();
            mvelContext.put("source", value);
            Object parameterValue = MVEL.eval(parameterWithoutRim, mvelContext);
            // TODO Make more types
            if (String.class.isAssignableFrom(parameterValue.getClass())) {
                values.add(parameterValue);
                types.add(StringType.INSTANCE);
            } else if (Boolean.class.isAssignableFrom(parameterValue.getClass())) {
                values.add(parameterValue);
                types.add(BooleanType.INSTANCE);
            } else {
                throw new UnsupportedOperationException();
            }
            result = result.substring(0, first) + "? " + result.substring(second + first + 1);
            first = result.indexOf("@");
        }
        Type[] t = new Type[types.size()];
        return Restrictions.sqlRestriction(result, values.toArray(), types.toArray(t));
    } catch (CompileException ex) {
        LOG.warn(String.format("Compiling validation expression '%s' of class '%s' has thrown an error: %s",
                sqlRestriction, beanClass.toString(), ex.getMessage()), ex);
        return Restrictions.sqlRestriction("0=1");
    }
}

---

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

License:Mozilla Public License

/**
 * @see org.openmrs.api.PersonService#getRelationshipTypes(java.lang.String, java.lang.Boolean)
 * @see org.openmrs.api.db.PersonDAO#getRelationshipTypes(java.lang.String, java.lang.Boolean)
 *//*from  ww  w  . ja  v a  2 s . co m*/
@SuppressWarnings("unchecked")
public List<RelationshipType> getRelationshipTypes(String relationshipTypeName, Boolean preferred)
        throws DAOException {

    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(RelationshipType.class);
    criteria.add(Restrictions.sqlRestriction("CONCAT(a_Is_To_B, CONCAT('/', b_Is_To_A)) like (?)",
            relationshipTypeName, new StringType()));

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

    return criteria.list();
}

---

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

License:Mozilla Public License

/**
 * Utility method to add identifier expression to an existing criteria
 *
 * @param identifier// w w w .  j av  a 2 s . c om
 * @param identifierTypes
 * @param matchIdentifierExactly
 * @param includeVoided true/false whether or not to included voided patients
 */
private Criterion prepareCriterionForIdentifier(String identifier, List<PatientIdentifierType> identifierTypes,
        boolean matchIdentifierExactly, boolean includeVoided) {

    identifier = HibernateUtil.escapeSqlWildcards(identifier, sessionFactory);
    Conjunction conjunction = Restrictions.conjunction();

    if (!includeVoided) {
        conjunction.add(Restrictions.eq("ids.voided", false));
    }
    // do the identifier restriction
    if (identifier != null) {
        // if the user wants an exact search, match on that.
        if (matchIdentifierExactly) {
            SimpleExpression matchIdentifier = Restrictions.eq("ids.identifier", identifier);
            if (Context.getAdministrationService().isDatabaseStringComparisonCaseSensitive()) {
                matchIdentifier.ignoreCase();
            }
            conjunction.add(matchIdentifier);
        } else {
            AdministrationService adminService = Context.getAdministrationService();
            String regex = adminService
                    .getGlobalProperty(OpenmrsConstants.GLOBAL_PROPERTY_PATIENT_IDENTIFIER_REGEX, "");
            String patternSearch = adminService
                    .getGlobalProperty(OpenmrsConstants.GLOBAL_PROPERTY_PATIENT_IDENTIFIER_SEARCH_PATTERN, "");

            // remove padding from identifier search string
            if (Pattern.matches("^\\^.{1}\\*.*$", regex)) {
                identifier = removePadding(identifier, regex);
            }

            if (org.springframework.util.StringUtils.hasLength(patternSearch)) {
                conjunction.add(splitAndGetSearchPattern(identifier, patternSearch));
            }
            // if the regex is empty, default to a simple "like" search or if
            // we're in hsql world, also only do the simple like search (because
            // hsql doesn't know how to deal with 'regexp'
            else if ("".equals(regex) || HibernateUtil.isHSQLDialect(sessionFactory)) {
                conjunction.add(getCriterionForSimpleSearch(identifier, adminService));
            }
            // if the regex is present, search on that
            else {
                regex = replaceSearchString(regex, identifier);
                conjunction.add(Restrictions.sqlRestriction("identifier regexp ?", regex, StringType.INSTANCE));
            }
        }
    }

    // TODO add a junit test for patientIdentifierType restrictions   

    // do the type restriction
    if (!CollectionUtils.isEmpty(identifierTypes)) {
        criteria.add(Restrictions.in("ids.identifierType", identifierTypes));
    }

    return conjunction;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• »