List of usage examples for org.hibernate SQLQuery setInteger
@Deprecated @SuppressWarnings("unchecked") default Query<R> setInteger(int position, int val)
From source file:org.openhie.openempi.dao.hibernate.PersonLinkDaoHibernate.java
License:Open Source License
public Map<Long, Integer> getClusterIdByRecordIdMap(final Integer sourceId) { final String queryStr = "select distinct cluster_id, lh_person_id from person_link where link_source_id = ? " + "union select distinct cluster_id, rh_person_id from person_link where link_source_id = ? order by cluster_id"; final Map<Long, Integer> entries = new HashMap<Long, Integer>(); getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { SQLQuery query = session.createSQLQuery(queryStr); @SuppressWarnings("unchecked") List<Object[]> rows = (List<Object[]>) query.setInteger(0, sourceId).setInteger(1, sourceId).list(); for (Object[] items : rows) { log.debug("We have item " + items); Integer clusterId = (Integer) items[0]; Integer personId = (Integer) items[1]; entries.put(personId.longValue(), clusterId); }/*from w w w . j av a2 s .c o m*/ return null; } }); return entries; }
From source file:org.openmrs.api.db.hibernate.HibernateConceptDAO.java
License:Mozilla Public License
/** * @see org.openmrs.api.db.ConceptDAO#getSavedConceptDatatype(org.openmrs.Concept) *//*from w w w. j ava 2 s . co m*/ public ConceptDatatype getSavedConceptDatatype(Concept concept) { SQLQuery sql = sessionFactory.getCurrentSession() .createSQLQuery("select datatype.* from " + "concept_datatype datatype, " + "concept concept " + "where " + "datatype.concept_datatype_id = concept.datatype_id " + "and concept.concept_id=:conceptId") .addEntity(ConceptDatatype.class); sql.setInteger("conceptId", concept.getConceptId()); return (ConceptDatatype) sql.uniqueResult(); }
From source file:org.openmrs.api.db.hibernate.HibernateEncounterDAO.java
License:Mozilla Public License
/** * @see org.openmrs.api.db.EncounterDAO#getSavedEncounterDatetime(org.openmrs.Encounter) *//*w w w. j a va 2 s . c o m*/ public Date getSavedEncounterDatetime(Encounter encounter) { SQLQuery sql = sessionFactory.getCurrentSession() .createSQLQuery("select encounter_datetime from encounter where encounter_id = :encounterId"); sql.setInteger("encounterId", encounter.getEncounterId()); return (Date) sql.uniqueResult(); }
From source file:org.openmrs.api.db.hibernate.HibernateEncounterDAO.java
License:Mozilla Public License
/** * @see org.openmrs.api.db.EncounterDAO#getSavedEncounterLocation(org.openmrs.Encounter) *//*from w w w . ja v a 2 s . c o m*/ public Location getSavedEncounterLocation(Encounter encounter) { SQLQuery sql = sessionFactory.getCurrentSession() .createSQLQuery("select location_id from encounter where encounter_id = :encounterId"); sql.setInteger("encounterId", encounter.getEncounterId()); return Context.getLocationService().getLocation((Integer) sql.uniqueResult()); }
From source file:org.openmrs.api.db.hibernate.HibernatePersonDAO.java
License:Mozilla Public License
/** * @see org.openmrs.api.db.PersonDAO#getSavedPersonAttributeTypeName(org.openmrs.PersonAttributeType) *//*from w w w.j a v a2s . c om*/ public String getSavedPersonAttributeTypeName(PersonAttributeType personAttributeType) { SQLQuery sql = sessionFactory.getCurrentSession().createSQLQuery( "select name from person_attribute_type where person_attribute_type_id = :personAttributeTypeId"); sql.setInteger("personAttributeTypeId", personAttributeType.getId()); return (String) sql.uniqueResult(); }
From source file:org.openmrs.module.encounteraudit.api.db.hibernate.HibernateEncounterAuditDAO.java
License:Open Source License
@Override public List<Encounter> getAuditEncounters(Date fromDate, Date toDate, int sampleSize, Location location, EncounterType encounterType, String creatorId) { if (sampleSize < 1) { // by default return 25 records sampleSize = 25;//from w w w. j a v a 2 s.com } StringBuilder sql = new StringBuilder("select * from encounter e where "); sql.append(" encounter_datetime > :fromDate and "); sql.append(" encounter_datetime < :toDate "); if (location != null) { sql.append(" and location_id = :locationId "); } if (encounterType != null) { sql.append(" and encounter_type = :encounterType "); } if (creatorId.length() != 0) { sql.append(" and creator = :creatorId and changed_by IS NULL "); } sql.append(" order by rand() "); sql.append("limit 0,:sampleSize "); SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString()) .addEntity(Encounter.class); query.setDate("fromDate", fromDate); query.setDate("toDate", toDate); query.setInteger("sampleSize", new Integer(sampleSize)); if (location != null) { query.setInteger("locationId", new Integer(location.getLocationId())); } if (encounterType != null) { query.setInteger("encounterType", new Integer(encounterType.getEncounterTypeId())); } if (creatorId.length() != 0) { query.setString("creatorId", creatorId); } List<Encounter> encounterList = query.list(); return encounterList; }
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.AdmissionSoonAfterExitCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { AdmissionSoonAfterExitCohortDefinition cd = (AdmissionSoonAfterExitCohortDefinition) cohortDefinition; String sql = "select distinct admit.patient_id \n" + "from encounter admit \n" + "inner join encounter discharge \n" + " on discharge.patient_id = admit.patient_id \n" + " and discharge.voided = false \n" + " and discharge.encounter_type = :dischargeEncounterType \n" +/*from w ww. j av a2 s .c o m*/ // was mysql-specific " and discharge.encounter_datetime >= DATE_SUB(admit.encounter_datetime, INTERVAL :windowInDays DAY) \n" + " and discharge.encounter_datetime >= TIMESTAMPADD(DAY, -:windowInDays, admit.encounter_datetime) \n" + " and discharge.encounter_datetime < admit.encounter_datetime \n" + "where admit.voided = false \n" + " and admit.encounter_type = :admitEncounterType \n" + " and admit.encounter_datetime between :onOrAfter and :onOrBefore "; if (cd.getAdmissionLocation() != null) { sql += " and admit.location_id = :admissionLocation "; } SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setInteger("admitEncounterType", emrApiProperties.getAdmissionEncounterType().getId()); query.setInteger("dischargeEncounterType", emrApiProperties.getExitFromInpatientEncounterType().getId()); query.setTimestamp("onOrAfter", cd.getOnOrAfter()); query.setTimestamp("onOrBefore", cd.getOnOrBefore()); query.setInteger("windowInDays", cd.getWindowInDays()); if (cd.getAdmissionLocation() != null) { query.setInteger("admissionLocation", cd.getAdmissionLocation().getId()); } Cohort c = new Cohort(); for (Integer i : (List<Integer>) query.list()) { c.addMember(i); } return new EvaluatedCohort(c, cohortDefinition, context); }
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.DiedSoonAfterEncounterCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition def, EvaluationContext context) throws EvaluationException { DiedSoonAfterEncounterCohortDefinition cohortDefinition = (DiedSoonAfterEncounterCohortDefinition) def; String sql = " select distinct p.patient_id \n" + " from patient p \n" + " inner join person per \n" + " on p.patient_id = per.person_id \n" + " and per.voided = false \n" + " inner join encounter admit \n" + " on admit.patient_id = p.patient_id \n" + " and admit.encounter_type = :encounterType \n" + // was mysql-specific " and admit.encounter_datetime > DATE_SUB(per.death_date, INTERVAL :windowInHours HOUR) \n" + " and admit.encounter_datetime > TIMESTAMPADD(HOUR, -:windowInHours, per.death_date) \n" + " where per.death_date between :diedOnOrAfter and :diedOnOrBefore \n" + " and p.voided = false "; SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setTimestamp("diedOnOrAfter", cohortDefinition.getDiedOnOrAfter()); query.setTimestamp("diedOnOrBefore", cohortDefinition.getDiedOnOrBefore()); query.setInteger("encounterType", cohortDefinition.getEncounterType().getId()); query.setInteger("windowInHours", cohortDefinition.getWindowInHours()); Cohort c = new Cohort(); for (Integer i : (List<Integer>) query.list()) { c.addMember(i);// ww w .j a v a 2 s. c o m } return new EvaluatedCohort(c, cohortDefinition, context); }
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.InpatientLocationCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { InpatientLocationCohortDefinition cd = (InpatientLocationCohortDefinition) cohortDefinition; Date onDate = cd.getEffectiveDate(); if (onDate == null) { onDate = new Date(); }//w w w . j ava 2s .c om Location ward = cd.getWard(); Location visitLocation = null; if (ward != null) { visitLocation = adtService.getLocationThatSupportsVisits(ward); } EncounterType admissionEncounterType = emrApiProperties.getAdmissionEncounterType(); EncounterType dischargeEncounterType = emrApiProperties.getExitFromInpatientEncounterType(); EncounterType transferEncounterType = emrApiProperties.getTransferWithinHospitalEncounterType(); StringBuilder sb = new StringBuilder("select distinct v.patient_id " + "from visit v " + "inner join encounter admission " + " on v.visit_id = admission.visit_id " + " and admission.voided = false " + " and admission.encounter_type = :admissionEncounterType " + " and admission.encounter_datetime <= :onDate " + "inner join encounter mostRecentAdt " + " on v.visit_id = mostRecentAdt.visit_id " + " and mostRecentAdt.encounter_id = ( " + " select encounter_id " + " from encounter " + " where visit_id = v.visit_id " + " and voided = false " + " and encounter_type in (:adtEncounterTypes) " + " and encounter_datetime <= :onDate " + " order by encounter_datetime desc, date_created desc limit 1" + " ) "); sb.append("where v.voided = false"); if (visitLocation != null) { sb.append(" and v.location_id = :visitLocation "); } sb.append(" and v.date_started <= :onDate "); sb.append(" and (v.date_stopped is null or v.date_stopped > :onDate) "); if (ward != null) { sb.append(" and mostRecentAdt.location_id = :ward "); } sb.append(" and mostRecentAdt.encounter_type in (:admitOrTransferEncounterTypes)"); SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString()); query.setInteger("admissionEncounterType", admissionEncounterType.getId()); query.setTimestamp("onDate", onDate); if (visitLocation != null) { query.setInteger("visitLocation", visitLocation.getId()); } if (ward != null) { query.setInteger("ward", ward.getId()); } query.setParameterList("adtEncounterTypes", new Integer[] { admissionEncounterType.getId(), dischargeEncounterType.getId(), transferEncounterType.getId() }); query.setParameterList("admitOrTransferEncounterTypes", new Integer[] { admissionEncounterType.getId(), transferEncounterType.getId() }); // This does not actually work: org.hibernate.hql.ast.QuerySyntaxException: with-clause referenced two different from-clause elements // Query hql = sessionFactory.getCurrentSession().createQuery("select distinct(v.patient.id) " + // "from Visit v " + // "join v.encounters as mostRecentAdt " + // " with mostRecentAdt.voided = false " + // " and mostRecentAdt.encounterType in (:adtEncounterTypes) " + // " and mostRecentAdt.encounterDatetime = ( " + // " select max(encounterDatetime)" + // " from Encounter " + // " where visit = v " + // " and voided = false " + // " and encounterType in (:adtEncounterTypes) " + // " and encounterDatetime <= :onDate " + // " ) " + // "where v.voided = false " + // "and v.location = :visitLocation " + // "and v.startDatetime <= :onDate " + // "and (v.stopDatetime is null or v.stopDatetime > :onDate) " + // "and exists ( " + // " from Encounter admission " + // " where admission.visit = v " + // " and admission.voided = false " + // " and admission.encounterType = :admissionEncounterType " + // " and admission.encounterDatetime <= :onDate " + // ") " + // "and mostRecentAdt.location = :ward " + // "and mostRecentAdt.encounterType in (:admitOrTransferEncounterTypes) "); // // hql.setParameter("onDate", onDate); // hql.setParameter("visitLocation", visitLocation); // hql.setParameter("ward", ward); // hql.setParameter("admissionEncounterType", admissionEncounterType); // hql.setParameterList("adtEncounterTypes", adtEncounterTypes); // hql.setParameterList("admitOrTransferEncounterTypes", admitOrTransferEncounterTypes); Cohort c = new Cohort(); for (Integer i : (List<Integer>) query.list()) { c.addMember(i); } return new EvaluatedCohort(c, cohortDefinition, context); }
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.InpatientTransferCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { InpatientTransferCohortDefinition cd = (InpatientTransferCohortDefinition) cohortDefinition; Location outOfWard = cd.getOutOfWard(); Location inToWard = cd.getInToWard(); if (inToWard == null && outOfWard == null) { throw new IllegalArgumentException("Must specify outOfWard and/or inToWard"); }/*from w w w .jav a 2 s. co m*/ Location visitLocation = adtService.getLocationThatSupportsVisits(outOfWard != null ? outOfWard : inToWard); if (visitLocation == null) { throw new IllegalArgumentException(outOfWard + " and its ancestor locations don't support visits"); } EncounterType admissionEncounterType = emrApiProperties.getAdmissionEncounterType(); EncounterType dischargeEncounterType = emrApiProperties.getExitFromInpatientEncounterType(); EncounterType transferEncounterType = emrApiProperties.getTransferWithinHospitalEncounterType(); String sql = "select distinct v.patient_id " + "from visit v " + "inner join encounter admission " + " on v.visit_id = admission.visit_id " + " and admission.voided = false " + " and admission.encounter_type = :admissionEncounterType " + " and admission.encounter_datetime <= :onOrBefore " + "inner join encounter transfer " + " on v.visit_id = transfer.visit_id " + " and transfer.voided = false " + " and transfer.encounter_type = :transferEncounterType " + " and transfer.encounter_datetime between :onOrAfter and :onOrBefore " + " and transfer.encounter_datetime > admission.encounter_datetime "; if (inToWard != null) { sql += " and transfer.location_id = :inToWard "; } sql += "inner join encounter adtBeforeTransfer " + " on v.visit_id = adtBeforeTransfer.visit_id " + " and adtBeforeTransfer.voided = false " + " and adtBeforeTransfer.encounter_type in (:adtEncounterTypes) " + " and adtBeforeTransfer.encounter_id = ( " + " select encounter_id " + " from encounter " + " where visit_id = v.visit_id " + " and voided = false " + " and encounter_type in (:adtEncounterTypes) " + " and encounter_datetime < transfer.encounter_datetime " + " order by encounter_datetime desc, date_created desc limit 1" + " ) " + "where v.voided = false" + " and v.location_id = :visitLocation " + " and adtBeforeTransfer.encounter_type in (:admitOrTransferEncounterTypes)"; if (outOfWard != null) { sql += " and adtBeforeTransfer.location_id = :outOfWard "; } SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setInteger("admissionEncounterType", admissionEncounterType.getId()); query.setInteger("transferEncounterType", transferEncounterType.getId()); query.setTimestamp("onOrBefore", cd.getOnOrBefore()); query.setTimestamp("onOrAfter", cd.getOnOrAfter()); query.setInteger("visitLocation", visitLocation.getId()); if (outOfWard != null) { query.setInteger("outOfWard", outOfWard.getId()); } if (inToWard != null) { query.setInteger("inToWard", inToWard.getId()); } query.setParameterList("adtEncounterTypes", new Integer[] { admissionEncounterType.getId(), dischargeEncounterType.getId(), transferEncounterType.getId() }); query.setParameterList("admitOrTransferEncounterTypes", new Integer[] { admissionEncounterType.getId(), transferEncounterType.getId() }); Cohort c = new Cohort(); for (Integer i : (List<Integer>) query.list()) { c.addMember(i); } return new EvaluatedCohort(c, cohortDefinition, context); }