Examples with SQLQuery org.hibernate.SQLQuery used on opensource projects

Example 46 with SQLQuery

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;
}

Example 47 with SQLQuery

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();
}

Example 48 with SQLQuery

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();
}

Example 49 with SQLQuery

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);
}

Example 50 with SQLQuery

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);
}