use of org.hibernate.SQLQuery in project arquivoProject by fader-azevedo.
the class DBConnector method tooltipEstante.
public Tooltip tooltipEstante(String codigo) {
Estante est = (Estante) getEstOrPratOrPastByCodigo(codigo, Estante.class);
String datac = new SimpleDateFormat("dd/MM/yyyy HH:mm").format(est.getDataCriacao());
Usuario user = (Usuario) buscarPorId(Usuario.class, est.getUsuario());
int numDePastas = 0;
int numDeDocs = 0;
List<Pratileira> dataPraliteira = null;
List<Pasta> dataPasta = null;
List<Pauta> dataPauta = null;
try {
sessao = HibernateUtil.getSessionFactory().openSession();
sessao.beginTransaction();
SQLQuery query = sessao.createSQLQuery("SELECT * FROM Pratileira WHERE idestante =?").addEntity(Pratileira.class);
dataPraliteira = query.setString(0, est.getIdestante() + "%").list();
for (Pratileira pra : dataPraliteira) {
SQLQuery query1 = sessao.createSQLQuery("SELECT * FROM Pasta WHERE idpratileira =?").addEntity(Pasta.class);
dataPasta = query1.setString(0, pra.getIdpratileira() + "%").list();
for (Pasta pasta : dataPasta) {
numDePastas += 1;
SQLQuery query2 = sessao.createSQLQuery("SELECT * FROM Pauta WHERE idpasta =?").addEntity(Pauta.class);
dataPauta = query2.setString(0, pasta.getIdpasta() + "%").list();
for (Pauta pauta : dataPauta) {
numDeDocs += 1;
}
}
}
sessao.getTransaction().commit();
sessao.close();
} catch (HibernateException e) {
alertErro("Erro ao buscar dados " + e);
}
Tooltip tooltip = new Tooltip();
tooltip.setText("Pratileiras : " + dataPraliteira.size() + "\n" + "Pastas : " + numDePastas + "\n" + "Documentos : " + numDeDocs + "\n" + "Data Criada : " + datac + "\n" + "Criada Por : " + user.getNome() + " " + user.getApelido());
MaterialIconView icon = new MaterialIconView(MaterialIcon.INFO, "50");
icon.setFill(Paint.valueOf("#75B4C9"));
tooltip.setGraphic(icon);
return tooltip;
}
use of org.hibernate.SQLQuery in project JessMA by ldcsaa.
the class HibernateFacade method sqlQuery4.
/**
* 根据起始记录和最大记录数执行SQL查询,并根据Entities和Joins返回查询结果
*
* @param firstResult : 起始记录索引
* @param maxResults : 最大记录数
* @param sql : SQL 查询语句
* @param entities : 要绑定的查询实体集合
* @param joins : 要绑定的连接实体集合
* @param params : 查询参数
* @return : 查询结果
*/
protected <T> List<T> sqlQuery4(int firstResult, int maxResults, String sql, KV<String, Object>[] entities, KV<String, String>[] joins, Object... params) {
SQLQuery sqlQuery = getSession().createSQLQuery(sql);
for (int i = 0; i < params.length; i++) sqlQuery.setParameter(i, params[i]);
if (entities != null) {
for (int i = 0; i < entities.length; i++) {
KV<String, Object> entity = entities[i];
String key = entity.getKey();
Object value = entity.getValue();
Class<?> v1 = (value instanceof Class) ? (Class<?>) value : null;
String v2 = v1 == null ? (String) value : null;
if (key == null || key.length() == 0) {
if (v1 != null)
sqlQuery.addEntity(v1);
else
sqlQuery.addEntity(v2);
} else {
if (v1 != null)
sqlQuery.addEntity(key, v1);
else
sqlQuery.addEntity(key, v2);
}
}
}
if (joins != null) {
for (KV<String, String> join : joins) sqlQuery.addJoin(join.getKey(), (String) join.getValue());
}
if (firstResult > 0)
sqlQuery.setFirstResult(firstResult);
if (maxResults > 0)
sqlQuery.setMaxResults(maxResults);
return sqlQuery.list();
}
use of org.hibernate.SQLQuery in project xwiki-platform by xwiki.
the class XWikiHibernateStoreTest method createHibernateSequenceIfRequiredWhenNotInUpdateCommands.
@Test
public void createHibernateSequenceIfRequiredWhenNotInUpdateCommands() throws Exception {
Session session = mock(Session.class);
SessionFactoryImplementor sessionFactory = mock(SessionFactoryImplementor.class);
Dialect dialect = mock(Dialect.class);
when(session.getSessionFactory()).thenReturn(sessionFactory);
when(sessionFactory.getDialect()).thenReturn(dialect);
when(dialect.getNativeIdentifierGeneratorClass()).thenReturn(SequenceGenerator.class);
SQLQuery sqlQuery = mock(SQLQuery.class);
when(session.createSQLQuery("create sequence schema.hibernate_sequence")).thenReturn(sqlQuery);
when(sqlQuery.executeUpdate()).thenReturn(0);
this.store.createHibernateSequenceIfRequired(new String[] {}, "schema", session);
verify(session).createSQLQuery("create sequence schema.hibernate_sequence");
verify(sqlQuery).executeUpdate();
}
use of org.hibernate.SQLQuery in project openmrs-module-pihcore by PIH.
the class LastDispositionBeforeExitCohortDefinitionEvaluator method evaluate.
@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 ";
}
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 " + // " 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);
}
use of org.hibernate.SQLQuery in project openmrs-module-pihcore by PIH.
the class InpatientLocationCohortDefinitionEvaluator method evaluate.
@Override
public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException {
InpatientLocationCohortDefinition cd = (InpatientLocationCohortDefinition) cohortDefinition;
Date onDate = cd.getEffectiveDate();
if (onDate == null) {
onDate = new Date();
}
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);
}
Aggregations