List of usage examples for org.hibernate Query getQueryString
String getQueryString();
From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java
License:Open Source License
/** * // w w w . j ava2 s.c o m */ public Cohort getPatientsHavingBirthDateBetweenDates(Date bornOnOrAfter, Date bornOnOrBefore) throws DAOException { StringBuffer queryString = new StringBuffer("select patientId from Patient patient"); List<String> clauses = new ArrayList<String>(); clauses.add("patient.voided = false"); if (bornOnOrAfter != null) { clauses.add("patient.birthdate >= :bornOnOrAfter"); } if (bornOnOrBefore != null) { clauses.add("patient.birthdate <= :bornOnOrBefore"); } boolean first = true; for (String clause : clauses) { if (first) { queryString.append(" where ").append(clause); first = false; } else { queryString.append(" and ").append(clause); } } Query query = sessionFactory.getCurrentSession().createQuery(queryString.toString()); log.debug("Patients having birthdate between dates: " + query.getQueryString()); query.setCacheMode(CacheMode.IGNORE); if (bornOnOrAfter != null) { query.setDate("bornOnOrAfter", bornOnOrAfter); } if (bornOnOrBefore != null) { query.setDate("bornOnOrBefore", bornOnOrBefore); } return new Cohort(query.list()); }
From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java
License:Open Source License
/** * /* w w w . jav a 2 s . c om*/ */ public Cohort getPatientsHavingDiedBetweenDates(Date diedOnOrAfter, Date diedOnOrBefore) throws DAOException { StringBuffer queryString = new StringBuffer("select patientId from Patient patient"); List<String> clauses = new ArrayList<String>(); clauses.add("patient.voided = false"); clauses.add("patient.dead = true"); if (diedOnOrAfter != null) { clauses.add("patient.deathDate >= :diedOnOrAfter"); } if (diedOnOrBefore != null) { clauses.add("patient.deathDate <= :diedOnOrBefore"); } boolean first = true; for (String clause : clauses) { if (first) { queryString.append(" where ").append(clause); first = false; } else { queryString.append(" and ").append(clause); } } Query query = sessionFactory.getCurrentSession().createQuery(queryString.toString()); query.setCacheMode(CacheMode.IGNORE); if (diedOnOrAfter != null) { query.setDate("diedOnOrAfter", diedOnOrAfter); } if (diedOnOrBefore != null) { query.setDate("diedOnOrBefore", diedOnOrBefore); } log.debug("Patients having died between dates query: " + query.getQueryString()); return new Cohort(query.list()); }
From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java
License:Open Source License
/** * //ww w.j av a 2 s .c om */ public Cohort getPatientsHavingObs(Integer conceptId, TimeModifier timeModifier, Modifier modifier, Object value, Date fromDate, Date toDate, List<User> providers, EncounterType encounterType) { if (conceptId == null && value == null) throw new IllegalArgumentException("Can't have conceptId == null and value == null"); if (conceptId == null && (timeModifier != TimeModifier.ANY && timeModifier != TimeModifier.NO)) throw new IllegalArgumentException("If conceptId == null, timeModifier must be ANY or NO"); if (conceptId == null && modifier != Modifier.EQUAL) { throw new IllegalArgumentException("If conceptId == null, modifier must be EQUAL"); } Concept concept = null; if (conceptId != null) concept = Context.getConceptService().getConcept(conceptId); // TODO This should be refactored out Number numericValue = null; String stringValue = null; Concept codedValue = null; Date dateValue = null; Boolean booleanValue = null; String valueSql = null; if (value != null) { if (concept == null) { if (value instanceof Concept) codedValue = (Concept) value; else codedValue = Context.getConceptService().getConceptByName(value.toString()); valueSql = "o.value_coded"; } else if (concept.getDatatype().isNumeric()) { if (value instanceof Number) numericValue = (Number) value; else numericValue = new Double(value.toString()); valueSql = "o.value_numeric"; } else if (concept.getDatatype().isText()) { stringValue = value.toString(); valueSql = "o.value_text"; if (modifier == null) modifier = Modifier.EQUAL; } else if (concept.getDatatype().isCoded()) { if (value instanceof Concept) codedValue = (Concept) value; else codedValue = Context.getConceptService().getConceptByName(value.toString()); valueSql = "o.value_coded"; } else if (concept.getDatatype().isDate()) { if (value instanceof Date) { dateValue = (Date) value; } else { try { dateValue = Context.getDateFormat().parse(value.toString()); } catch (ParseException ex) { throw new IllegalArgumentException("Cannot interpret " + dateValue + " as a date in the format " + Context.getDateFormat()); } } valueSql = "o.value_datetime"; } else if (concept.getDatatype().isBoolean()) { if (value instanceof Boolean) { booleanValue = (Boolean) value; } else if (value instanceof Number) { numericValue = (Number) value; booleanValue = (numericValue.doubleValue() != 0.0) ? Boolean.TRUE : Boolean.FALSE; } else { booleanValue = Boolean.valueOf(value.toString()); } valueSql = "o.value_numeric"; } } StringBuilder sb = new StringBuilder(); boolean useValue = value != null; boolean doSqlAggregation = timeModifier == TimeModifier.MIN || timeModifier == TimeModifier.MAX || timeModifier == TimeModifier.AVG; boolean doInvert = false; String dateSql = ""; String dateSqlForSubquery = ""; if (fromDate != null) { dateSql += " and o.obs_datetime >= :fromDate "; dateSqlForSubquery += " and obs_datetime >= :fromDate "; } if (toDate != null) { dateSql += " and o.obs_datetime <= :toDate "; dateSqlForSubquery += " and obs_datetime <= :toDate "; } String encounterSql = ""; String encounterSqlTable = ""; String encounterJoin = ""; String encounterSqlForSubquery = ""; if (encounterType != null) { encounterSqlTable = ", encounter e "; encounterJoin = " and e.encounter_id = o.encounter_id "; encounterSql += " and e.encounter_type = :encounterType "; encounterSqlForSubquery = " inner join encounter e on e.encounter_id = o.encounter_id "; } if (timeModifier == TimeModifier.ANY || timeModifier == TimeModifier.NO) { if (timeModifier == TimeModifier.NO) doInvert = true; sb.append("select o.person_id from obs o " + encounterSqlTable + " where o.voided = false "); if (conceptId != null) sb.append("and o.concept_id = :concept_id "); sb.append(encounterJoin); sb.append(encounterSql); sb.append(dateSql); } else if (timeModifier == TimeModifier.FIRST || timeModifier == TimeModifier.LAST) { boolean isFirst = timeModifier == PatientSetService.TimeModifier.FIRST; sb.append("select o.person_id " + "from obs o inner join (" + " select person_id, " + (isFirst ? "min" : "max") + "(obs_datetime) as obs_datetime" + " from obs o" + " where o.voided = false and o.concept_id = :concept_id " + dateSqlForSubquery + " group by person_id" + ") subq on o.person_id = subq.person_id and o.obs_datetime = subq.obs_datetime " + encounterSqlForSubquery + "where o.voided = false and o.concept_id = :concept_id " + encounterSql); } else if (doSqlAggregation) { String sqlAggregator = timeModifier.toString(); valueSql = sqlAggregator + "(" + valueSql + ")"; sb.append("select o.person_id " + "from obs o " + encounterSqlTable + " where o.voided = false and o.concept_id = :concept_id " + dateSql + encounterJoin + encounterSql + "group by o.person_id "); } else { throw new IllegalArgumentException("TimeModifier '" + timeModifier + "' not recognized"); } if (useValue) { sb.append(doSqlAggregation ? " having " : " and "); sb.append(valueSql + " "); sb.append(modifier.getSqlRepresentation() + " :value"); } if (!doSqlAggregation) sb.append(" group by o.person_id "); log.debug("query: " + sb); Query query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString()); query.setCacheMode(CacheMode.IGNORE); if (conceptId != null) query.setInteger("concept_id", conceptId); if (useValue) { if (numericValue != null) query.setDouble("value", numericValue.doubleValue()); else if (codedValue != null) query.setInteger("value", codedValue.getConceptId()); else if (stringValue != null) query.setString("value", stringValue); else if (dateValue != null) query.setDate("value", dateValue); else if (booleanValue != null) query.setDouble("value", booleanValue ? 1.0 : 0.0); else throw new IllegalArgumentException( "useValue is true, but numeric, coded, string, boolean, and date values are all null"); } if (fromDate != null) query.setDate("fromDate", fromDate); if (toDate != null) query.setDate("toDate", toDate); if (encounterType != null) query.setInteger("encounterType", encounterType.getEncounterTypeId()); log.debug("Patients having obs query: " + query.getQueryString()); Cohort ret; if (doInvert) { ret = Cohorts.allPatients(null); ret.getMemberIds().removeAll(query.list()); } else { ret = new Cohort(query.list()); } return ret; }
From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java
License:Open Source License
/** * Prepare a Hibernate Query object using the given sql query string * and parameter mapping.//ww w. j a va 2 s .c o m * * @param sqlQuery * @param paramMap * @return a Hibernate Query object */ public Query prepareQuery(String sqlQuery, Map<String, Object> paramMap) { Query query = null; try { query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery.toString()); //query.setCacheMode(CacheMode.IGNORE); // TODO figure out what this does before using it // Bind the query parameters (query is mutable) bindQueryParameters(query, paramMap); } catch (Exception e) { log.error("Error while preparing sql query " + query.getQueryString() + ": " + e.getMessage()); throw new ReportingException( "Error while preparing sql query " + query.getQueryString() + ": " + e.getMessage(), e); } return query; }
From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java
License:Open Source License
/** * This needs to be a separate method so we can call it from both the * executeSqlQuery() and validateSqlQuery() methods *//*w w w .j av a 2 s . co m*/ private Cohort executeQuery(Query query) { // Needs to be a separate method try { // TODO Should test to make sure the returned List doesn't have more than one column return new Cohort(query.list()); } catch (HibernateException e) { throw new ParameterException("Error while executing SQL query [" + query.getQueryString() + "]: " + e.getMessage() + ". See tomcat log file for more details.", e); } }
From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java
License:Open Source License
/** * Binds the given paramMap to the query by replacing all named * parameters (e.g. :paramName) with their corresponding values * in the parameter map.// w ww .j av a 2 s .co m * * TODO Should add support for other classes. * TODO Should refactor to make more generalizable (create a new param map with correct param values) * * @param query * @param paramMap */ @SuppressWarnings("unchecked") private void bindQueryParameters(Query query, Map<String, Object> paramMap) { // Iterate over parameters and bind them to the Query object for (String paramName : paramMap.keySet()) { Object paramValue = paramMap.get(paramName); // Indicates whether we should bind this parameter in the query boolean bindParameter = (query.getQueryString().indexOf(":" + paramName) > 0); if (bindParameter) { // Make sure parameter value is not null if (paramValue == null) { // TODO Should try to convert 'columnName = null' to 'columnName IS NULL' throw new ParameterException("Cannot bind an empty value to parameter " + paramName + ". " + "Please provide a real value or use the 'IS NULL' constraint in your query (e.g. 'table.columnName IS NULL')."); } // Cohort (needs to be first, otherwise it will resolve as OpenmrsObject) if (Cohort.class.isAssignableFrom(paramValue.getClass())) { query.setParameterList(paramName, ((Cohort) paramValue).getMemberIds()); } // OpenmrsObject (e.g. Location) else if (OpenmrsObject.class.isAssignableFrom(paramValue.getClass())) { query.setInteger(paramName, ((OpenmrsObject) paramValue).getId()); } // Collection<OpenmrsObject> (e.g. List<Location>) else if (Collection.class.isAssignableFrom(paramValue.getClass())) { Collection collection = (Collection) paramValue; if (collection.iterator().hasNext()) { if (OpenmrsObject.class.isAssignableFrom(collection.iterator().next().getClass())) { query.setParameterList(paramName, SqlUtils.openmrsObjectIdListHelper( new ArrayList<OpenmrsObject>((Collection<OpenmrsObject>) paramValue))); } else { // a List of Strings, Integers? query.setParameterList(paramName, SqlUtils .objectListHelper(new ArrayList<Object>((Collection<Object>) paramValue))); } } else { query.setParameter(paramName, null); } } // java.util.Date and subclasses else if (paramValue instanceof Date) { query.setDate(paramName, (Date) paramValue); } // String, Integer, et al (this might break since this is a catch all for all other classes) else { query.setString(paramName, new String(paramValue.toString())); // need to create new string for some reason } } } }
From source file:org.osaf.cosmo.dao.hibernate.query.StandardItemFilterProcessorTest.java
License:Apache License
public void testUidQuery() throws Exception { ItemFilter filter = new ItemFilter(); filter.setUid(Restrictions.eq("abc")); Query query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.uid=:param0", query.getQueryString()); }
From source file:org.osaf.cosmo.dao.hibernate.query.StandardItemFilterProcessorTest.java
License:Apache License
public void testDisplayNameQuery() throws Exception { ItemFilter filter = new ItemFilter(); filter.setDisplayName(Restrictions.eq("test")); Query query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName=:param0", query.getQueryString()); filter.setDisplayName(Restrictions.neq("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName!=:param0", query.getQueryString()); filter.setDisplayName(Restrictions.like("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName like :param0", query.getQueryString()); filter.setDisplayName(Restrictions.nlike("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName not like :param0", query.getQueryString()); filter.setDisplayName(Restrictions.isNull()); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName is null", query.getQueryString()); filter.setDisplayName(Restrictions.ilike("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where lower(i.displayName) like :param0", query.getQueryString());/*from w ww . ja va2s. co m*/ filter.setDisplayName(Restrictions.nilike("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where lower(i.displayName) not like :param0", query.getQueryString()); }
From source file:org.osaf.cosmo.dao.hibernate.query.StandardItemFilterProcessorTest.java
License:Apache License
public void testParentQuery() throws Exception { ItemFilter filter = new ItemFilter(); CollectionItem parent = new HibCollectionItem(); filter.setParent(parent);/*from www . j a v a2 s . c om*/ Query query = queryBuilder.buildQuery(session, filter); Assert.assertEquals( "select i from HibItem i join i.parentDetails pd where pd.primaryKey.collection=:parent", query.getQueryString()); }
From source file:org.osaf.cosmo.dao.hibernate.query.StandardItemFilterProcessorTest.java
License:Apache License
public void testDisplayNameAndParentQuery() throws Exception { ItemFilter filter = new ItemFilter(); CollectionItem parent = new HibCollectionItem(); filter.setParent(parent);/*w w w . j av a 2s . c o m*/ filter.setDisplayName(Restrictions.eq("test")); Query query = queryBuilder.buildQuery(session, filter); Assert.assertEquals( "select i from HibItem i join i.parentDetails pd where pd.primaryKey.collection=:parent and i.displayName=:param1", query.getQueryString()); }