List of usage examples for org.hibernate SQLQuery setParameterList
@Override
NativeQuery<T> setParameterList(String name, Object[] values);
From source file:org.generationcp.middleware.dao.UserDefinedFieldDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public List<UserDefinedField> getNameTypesByGIDList(final List<Integer> gidList) { List<UserDefinedField> returnList = new ArrayList<>(); if (gidList != null && !gidList.isEmpty()) { try {/* ww w . j ava2 s. c o m*/ final String sql = "SELECT DISTINCT {u.*}" + " FROM names n" + " INNER JOIN udflds u" + " WHERE n.ntype=u.fldno" + " AND n.gid in (:gidList)" + " ORDER BY u.fname"; final SQLQuery query = this.getSession().createSQLQuery(sql); query.addEntity("u", UserDefinedField.class); query.setParameterList("gidList", gidList); returnList = query.list(); } catch (final HibernateException e) { throw new MiddlewareQueryException( "Error with getNameTypesByGIDList(gidList=" + gidList + "): " + e.getMessage(), e); } } return returnList; }
From source file:org.jboss.seam.wiki.plugin.blog.BlogDAO.java
License:LGPL
public List<BlogEntry> findBlogEntriesInDirectory(WikiDirectory startDir, WikiDocument ignoreDoc, Pager pager, Integer year, Integer month, Integer day, String tag, boolean countComments) { final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>(); StringBuilder queryString = new StringBuilder(); queryString.append("select").append(" "); for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) { queryString.append(getWikiDocumentSQLColumnNames()[i]); if (i != getWikiDocumentSQLColumnNames().length - 1) queryString.append(", "); }//from w w w. jav a 2s. c om queryString.append(", '0' as COMMENT_COUNT").append(" "); queryString.append(getblogEntryFromClause(tag)); queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag)); queryString.append(" "); queryString.append("order by doc2.CREATED_ON desc"); SQLQuery query = getSession().createSQLQuery(queryString.toString()); bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag); query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName()); query.addEntity(WikiDocument.class); query.addScalar("COMMENT_COUNT", Hibernate.LONG); query.setFirstResult(pager.getQueryFirstResult()); query.setMaxResults(pager.getQueryMaxResults()); query.setResultTransformer(new ResultTransformer() { public Object transformTuple(Object[] result, String[] aliases) { BlogEntry be = new BlogEntry(); be.setEntryDocument((WikiDocument) result[0]); blogEntryMap.put(be.getEntryDocument().getId(), be); // Put in map so we can attach comment count later return be; } public List transformList(List list) { return list; } }); List<BlogEntry> result = (List<BlogEntry>) query.list(); if (countComments && result.size() > 0) { // The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some DBs... StringBuilder commentQueryString = new StringBuilder(); commentQueryString.append("select doc.NODE_ID as DOC_ID, count(c3.NODE_ID) as COMMENT_COUNT") .append(" "); commentQueryString.append("from WIKI_DOCUMENT doc").append(" "); commentQueryString.append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID") .append(" "); commentQueryString.append("left outer join WIKI_COMMENT c2 on c1.NODE_ID = c2.NODE_ID").append(" "); commentQueryString.append("left outer join WIKI_COMMENT c3 on c2.NS_THREAD = c3.NS_THREAD").append(" "); commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" "); commentQueryString.append("group by doc.NODE_ID"); SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString()); commentQuery.setComment("Finding comment count for blog entries"); commentQuery.addScalar("DOC_ID"); commentQuery.addScalar("COMMENT_COUNT"); commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet()); commentQuery.setResultTransformer(new ResultTransformer() { public Object transformTuple(Object[] result, String[] aliases) { BlogEntry be = blogEntryMap.get(((BigInteger) result[0]).longValue()); be.setCommentCount(((BigInteger) result[1]).longValue()); return null; } public List transformList(List list) { return list; } }); commentQuery.list(); } return result; }
From source file:org.jcvi.ometa.hibernate.dao.SecurityDAO.java
License:Open Source License
/** * Common code for both project and sample, to get data by list-of-identifiers. * @throws Exception if anything requested is left out, iff failureResponse == throw ex. *//* w ww .j a v a 2s . c o m*/ private List<Long> getListOfAuthorizedById(List<Long> ids, String username, ResponseToFailedAuthorization failureResponse, AccessLevel accessLevel, Session session, String securedIdsQuery, String openAndSecuredIdsQuery, String securedParamListName, String openParamListName, String returnVarName) throws Exception { ids = uniquifyIds(ids); String queryStr = null; if (accessLevel == AccessLevel.View) { queryStr = openAndSecuredIdsQuery.replace(PROJ_GRP_SUBST_STR, VIEW_PROJECT_GROUP_FIELD); } else { queryStr = securedIdsQuery.replace(PROJ_GRP_SUBST_STR, EDIT_PROJECT_GROUP_FIELD); } SQLQuery query = session.createSQLQuery(queryStr); query.addScalar(returnVarName, Hibernate.STRING); if (accessLevel == AccessLevel.View) { query.setParameterList(openParamListName, ids); } query.setParameterList(securedParamListName, ids); String queryUsername = username == null ? UNLOGGED_IN_USER : username; query.setParameter(USERNAME_PARAM, queryUsername); logger.debug(query.getQueryString()); List<Long> rtnVal = query.list(); if (failureResponse == ResponseToFailedAuthorization.ThrowException && rtnVal.size() < ids.size()) { String idStr = joinIdList(ids); String message = makeUserReadableMessage(username, idStr); logger.error(message); throw new ForbiddenResourceException(message); } return rtnVal; }
From source file:org.jcvi.ometa.hibernate.dao.SecurityDAO.java
License:Open Source License
/** * Common code for both project and sample, to get data by list-of-names. * @throws Exception if anything requested is left out, iff failureResponse == throw ex. *///from w w w . ja va2s. co m private List<String> getListOfAuthorizedByName(List<String> names, String username, ResponseToFailedAuthorization failureResponse, AccessLevel accessLevel, Session session, String securedQuery, String openAndSecuredQuery, String returnVarName, String securedParamListName, String openParamListName) throws Exception { // Need to avoid sending same name multiple times. names = uniquifyNames(names); String queryStr = null; if (accessLevel == AccessLevel.View) { queryStr = openAndSecuredQuery.replace(PROJ_GRP_SUBST_STR, VIEW_PROJECT_GROUP_FIELD); } else { queryStr = securedQuery.replace(PROJ_GRP_SUBST_STR, EDIT_PROJECT_GROUP_FIELD); } SQLQuery query = session.createSQLQuery(queryStr); query.addScalar(returnVarName, Hibernate.STRING); query.setParameterList(securedParamListName, names); if (accessLevel == AccessLevel.View) { query.setParameterList(openParamListName, names); } String queryUsername = username == null ? UNLOGGED_IN_USER : username; query.setParameter(USERNAME_PARAM, queryUsername); List<String> rtnVal = query.list(); if (failureResponse == ResponseToFailedAuthorization.ThrowException && rtnVal.size() < names.size()) { String nameStr = joinNameList(names); String message = makeUserReadableMessage(username, nameStr); logger.error(message); throw new ForbiddenResourceException(message); } return rtnVal; }
From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java
License:Open Source License
@Override public List<Object> executeSqlQuery(String query, Map<String, Object> substitutions) { SQLQuery q = sessionFactory.getCurrentSession().createSQLQuery(query); for (Map.Entry<String, Object> e : substitutions.entrySet()) { if (e.getValue() instanceof Collection) { q.setParameterList(e.getKey(), (Collection) e.getValue()); } else if (e.getValue() instanceof Object[]) { q.setParameterList(e.getKey(), (Object[]) e.getValue()); } else if (e.getValue() instanceof Cohort) { q.setParameterList(e.getKey(), ((Cohort) e.getValue()).getMemberIds()); } else if (e.getValue() instanceof Date) { q.setDate(e.getKey(), (Date) e.getValue()); } else {//from w w w . java2s. co m q.setParameter(e.getKey(), e.getValue()); } } q.setReadOnly(true); List<Object> r = q.list(); return r; }
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(); }//from ww w. ja v a 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 ww w . j av a 2s . com 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); }
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.LastDispositionBeforeExitCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { LastDispositionBeforeExitCohortDefinition cd = (LastDispositionBeforeExitCohortDefinition) cohortDefinition; Location exitFromWard = cd.getExitFromWard(); List<Concept> dispositions = cd.getDispositions(); List<Concept> dispositionsToConsider = cd.getDispositionsToConsider(); String sql = "select distinct v.patient_id " + "from visit v " + "inner join encounter exit_encounter " + " on exit_encounter.visit_id = v.visit_id " + " and exit_encounter.voided = false " + " and exit_encounter.encounter_type = :exitEncounterType " + " and exit_encounter.encounter_datetime between :exitOnOrAfter and :exitOnOrBefore "; if (exitFromWard != null) { sql += " and exit_encounter.location_id = :exitFromWard "; }// www. ja v a2 s . co m sql += "inner join encounter obs_encounter " + " on obs_encounter.visit_id = v.visit_id " + " and obs_encounter.encounter_id = (" + " select find_obs_encounter.encounter_id " + " from encounter find_obs_encounter " + " inner join obs has_obs " + " on has_obs.encounter_id = find_obs_encounter.encounter_id " + " and has_obs.voided = false " + " and has_obs.concept_id = :dispositionConcept "; if (dispositionsToConsider != null) { sql += " and has_obs.value_coded in (:dispositionsToConsider) "; } sql += " where find_obs_encounter.visit_id = v.visit_id " + " and find_obs_encounter.voided = false " + " order by find_obs_encounter.encounter_datetime desc, find_obs_encounter.date_created desc limit 1 " + // if we wanted to require disposition at the same location as exit // " and find_obs_encounter.location_id = :exitFromWard " + " )" + "inner join obs o " + " on o.voided = false " + " and o.concept_id = :dispositionConcept " + " and o.encounter_id = obs_encounter.encounter_id " + "where v.voided = false " + " and o.value_coded in (:dispositions) "; SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setInteger("dispositionConcept", dispositionService.getDispositionDescriptor().getDispositionConcept().getId()); query.setParameterList("dispositions", idList(dispositions)); query.setInteger("exitEncounterType", emrApiProperties.getExitFromInpatientEncounterType().getId()); query.setTimestamp("exitOnOrAfter", cd.getExitOnOrAfter()); query.setTimestamp("exitOnOrBefore", cd.getExitOnOrBefore()); if (exitFromWard != null) { query.setInteger("exitFromWard", exitFromWard.getId()); } if (dispositionsToConsider != null) { query.setParameterList("dispositionsToConsider", idList(dispositionsToConsider)); } 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.nbs.datasource.HibernateLogicProviderDAO.java
License:Open Source License
public List<Integer> getAllProviders(Integer patientId, ArrayList<Integer> encounterList) { String encounterRestrictions = ""; if (encounterList != null && encounterList.size() == 0) { return null; }//from www.j a v a 2s . c o m if (encounterList != null) { encounterRestrictions = " and encounter_id in (:encounterList)"; } String sql = "select distinct value_numeric as provider_id from obs where encounter_id in ( " + "select encounter_id from encounter where patient_id=?" + encounterRestrictions + ") " + "and concept_id=? and value_numeric is not null"; ConceptService conceptService = Context.getConceptService(); Concept providerUserIdConcept = conceptService.getConceptByName("PROVIDER_USER_ID"); if (providerUserIdConcept == null) { return null; } SQLQuery qry = this.sessionFactory.getCurrentSession().createSQLQuery(sql); qry.setInteger(0, patientId); qry.setInteger(1, providerUserIdConcept.getConceptId()); if (encounterList != null) { qry.setParameterList("encounterList", encounterList); } qry.addScalar("provider_id"); try { List<Double> tmpList = qry.list(); List<Integer> resultList = new ArrayList(); for (Double item : tmpList) { resultList.add(item.intValue()); } return resultList; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:org.openmrs.module.reporting.data.encounter.evaluator.SqlEncounterDataEvaluator.java
License:Open Source License
@Override public EvaluatedEncounterData evaluate(EncounterDataDefinition def, EvaluationContext ctx) throws EvaluationException { SqlEncounterDataDefinition definition = (SqlEncounterDataDefinition) def; EncounterEvaluationContext context = (EncounterEvaluationContext) ctx; EvaluatedEncounterData data = new EvaluatedEncounterData(definition, context); Set<Integer> encounterIds = EncounterDataUtil.getEncounterIdsForContext(context, false); if (encounterIds.size() == 0) { return data; }// w w w . ja v a 2 s .c o m SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(definition.getSql()); query.setParameterList("encounterIds", encounterIds); DataUtil.populate(data, (List<Object[]>) query.list()); return data; }