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

Example 36 with SQLQuery

use of org.hibernate.SQLQuery in project openmrs-module-pihcore by PIH.

the class InpatientTransferCohortDefinitionEvaluator method evaluate.

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

Example 37 with SQLQuery

use of org.hibernate.SQLQuery in project hibernate-orm by hibernate.

the class SQLLoaderTest method testEmbeddedCompositeProperties.

@Test
public void testEmbeddedCompositeProperties() throws HibernateException, SQLException {
    Session session = openSession();
    session.beginTransaction();
    Single s = new Single();
    s.setId("my id");
    s.setString("string 1");
    session.save(s);
    session.getTransaction().commit();
    session = openSession();
    session.beginTransaction();
    SQLQuery query = session.createSQLQuery("select {sing.*} from Single {sing}").addEntity("sing", Single.class);
    List list = query.list();
    assertTrue(list.size() == 1);
    session.clear();
    query = session.createSQLQuery("select {sing.*} from Single {sing} where sing.id = ?").addEntity("sing", Single.class);
    query.setString(0, "my id");
    list = query.list();
    assertTrue(list.size() == 1);
    session.clear();
    query = session.createSQLQuery("select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?").addEntity("sing", Single.class);
    query.setString(0, "my id");
    list = query.list();
    assertTrue(list.size() == 1);
    session.clear();
    query = session.createSQLQuery("select s.id as {sing.id}, s.string_ as {sing.string}, s.prop as {sing.prop} from Single s where s.id = ?").addEntity("sing", Single.class);
    query.setString(0, "my id");
    list = query.list();
    assertTrue(list.size() == 1);
    session.getTransaction().commit();
    session.close();
}

Example 38 with SQLQuery

use of org.hibernate.SQLQuery in project hibernate-orm by hibernate.

the class SQLLoaderTest method componentTest.

private void componentTest(String sql) throws SQLException {
    Componentizable c = setupComponentData();
    Session session = openSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql).addEntity("comp", Componentizable.class);
    List list = q.list();
    assertEquals(list.size(), 1);
    Componentizable co = (Componentizable) list.get(0);
    assertEquals(c.getNickName(), co.getNickName());
    assertEquals(c.getComponent().getName(), co.getComponent().getName());
    assertEquals(c.getComponent().getSubComponent().getSubName(), co.getComponent().getSubComponent().getSubName());
    session.delete(co);
    session.getTransaction().commit();
    session.close();
}

Example 39 with SQLQuery

use of org.hibernate.SQLQuery in project hibernate-orm by hibernate.

the class QueryAndSQLTest method testNativeQueryWithFormulaAttribute.

@Test
public void testNativeQueryWithFormulaAttribute() {
    SQLFunction dateFunction = getDialect().getFunctions().get("current_date");
    String dateFunctionRendered = dateFunction.render(null, java.util.Collections.EMPTY_LIST, sessionFactory());
    String sql = String.format("select t.TABLE_NAME as {t.tableName}, %s as {t.daysOld} from ALL_TABLES t  where t.TABLE_NAME = 'AUDIT_ACTIONS' ", dateFunctionRendered);
    String sql2 = String.format("select TABLE_NAME as t_name, %s as t_time from ALL_TABLES   where TABLE_NAME = 'AUDIT_ACTIONS' ", dateFunctionRendered);
    Session s = openSession();
    s.beginTransaction();
    s.createSQLQuery(sql).addEntity("t", AllTables.class).list();
    s.createSQLQuery(sql2).setResultSetMapping("all").list();
    SQLQuery q = s.createSQLQuery(sql2);
    q.addRoot("t", AllTables.class).addProperty("tableName", "t_name").addProperty("daysOld", "t_time");
    q.list();
    s.getTransaction().commit();
    s.close();
}

Example 40 with SQLQuery

use of org.hibernate.SQLQuery in project hibernate-orm by hibernate.

the class NativeSQLQueriesTest method testExplicitReturnAPI.

@Test
@SuppressWarnings({ "UnusedDeclaration" })
public void testExplicitReturnAPI() {
    Session s = openSession();
    s.beginTransaction();
    Organization jboss = new Organization("JBoss");
    Person me = new Person("Steve");
    Employment emp = new Employment(me, jboss, "US");
    Serializable jbossId = s.save(jboss);
    s.save(me);
    s.save(emp);
    s.getTransaction().commit();
    s.close();
    s = openSession();
    s.beginTransaction();
    String sql = "SELECT org.ORGID 		as orgid," + "       org.NAME 		as name," + "       emp.EMPLOYER 	as employer," + "       emp.EMPID 		as empid," + "       emp.EMPLOYEE 	as employee," + "       emp.EMPLOYER 	as employer," + "       emp.STARTDATE 	as startDate," + "       emp.ENDDATE 	as endDate," + "       emp.REGIONCODE 	as regionCode," + "       emp.AMOUNT 		as AMOUNT," + "       emp.CURRENCY 	as CURRENCY" + " FROM 	ORGANIZATION org" + "    LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER";
    // as a control, lets apply an existing rs mapping
    SQLQuery sqlQuery = s.createSQLQuery(sql);
    sqlQuery.setResultSetMapping("org-description");
    sqlQuery.list();
    // next try a partial mapping def
    sqlQuery.addRoot("org", Organization.class);
    sqlQuery.addFetch("emp", "org", "employments");
    sqlQuery.list();
    // now try full explicit mappings
    sqlQuery.addRoot("org", Organization.class).addProperty("id", "orgid").addProperty("name").addColumnAlias("name");
    sqlQuery.addFetch("emp", "org", "employments").addProperty("key", "employer").addProperty("element", "empid").addProperty("element.employee", "employee").addProperty("element.employer", "employer").addProperty("element.startDate", "startDate").addProperty("element.endDate", "endDate").addProperty("element.regionCode", "regionCode").addProperty("element.employmentId", "empId").addProperty("element.salary").addColumnAlias("AMOUNT").addColumnAlias("CURRENCY");
    sqlQuery.list();
    // lets try a totally different approach now and pull back scalars, first with explicit types
    sqlQuery.addScalar("orgid", LongType.INSTANCE).addScalar("name", StringType.INSTANCE).addScalar("empid", LongType.INSTANCE).addScalar("employee", LongType.INSTANCE).addScalar("startDate", TimestampType.INSTANCE).addScalar("endDate", TimestampType.INSTANCE).addScalar("regionCode", StringType.INSTANCE).addScalar("empId", LongType.INSTANCE).addScalar("AMOUNT", FloatType.INSTANCE).addScalar("CURRENCY", StringType.INSTANCE);
    s.getTransaction().commit();
    s.close();
    s = openSession();
    s.beginTransaction();
    s.delete(emp);
    s.delete(jboss);
    s.delete(me);
    s.getTransaction().commit();
    s.close();
}