Example usage for org.hibernate Query setDate

Introduction

In this page you can find the example usage for org.hibernate Query setDate.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setDate(String name, Date val) 

Source Link

Document

Bind the val (time is truncated) of a given Date object to a named query parameter.

Usage

From source file:org.openmrs.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

public Cohort getPatientsHavingNumericObs(Integer conceptId, PatientSetService.TimeModifier timeModifier,
        PatientSetService.Modifier modifier, Number value, Date fromDate, Date toDate) {

    Concept concept = Context.getConceptService().getConcept(conceptId);
    if (!concept.isNumeric()) {
        // throw new IllegalArgumentException(concept + " is not numeric");
    }/*from   w  w  w .ja  v  a  2  s .  co  m*/

    StringBuffer sb = new StringBuffer();
    boolean useValue = modifier != null && value != null;
    boolean doSqlAggregation = timeModifier == TimeModifier.MIN || timeModifier == TimeModifier.MAX
            || timeModifier == TimeModifier.AVG;
    String valueSql = "o.value_numeric";
    boolean doInvert = false;

    String dateSql = "";
    if (fromDate != null)
        dateSql += " and o.obs_datetime >= :fromDate ";
    if (toDate != null)
        dateSql += " and o.obs_datetime <= :toDate ";

    if (timeModifier == TimeModifier.ANY || timeModifier == TimeModifier.NO) {
        if (timeModifier == TimeModifier.NO)
            doInvert = true;
        sb.append("select o.person_id from obs o " + "where voided = false and concept_id = :concept_id ");
        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"
                + "    where voided = false and concept_id = :concept_id " + dateSql + "    group by person_id"
                + ") subq on o.person_id = subq.person_id and o.obs_datetime = subq.obs_datetime "
                + "where o.voided = false and o.concept_id = :concept_id ");
    } else if (doSqlAggregation) {
        String sqlAggregator = timeModifier.toString();
        valueSql = sqlAggregator + "(o.value_numeric)";
        sb.append("select o.person_id " + "from obs o where o.voided = false and concept_id = :concept_id "
                + dateSql + "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);

    query.setInteger("concept_id", conceptId);
    if (useValue) {
        query.setDouble("value", value.doubleValue());
    }
    if (fromDate != null)
        query.setDate("fromDate", fromDate);
    if (toDate != null)
        query.setDate("toDate", fromDate);

    Cohort ret;
    if (doInvert) {
        ret = getAllPatients();
        ret.getMemberIds().removeAll(query.list());
    } else {
        ret = new Cohort(query.list());
    }

    return ret;
}

---

From source file:org.openmrs.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

public Cohort getPatientsByCharacteristics(String gender, Date minBirthdate, Date maxBirthdate, Integer minAge,
        Integer maxAge, Boolean aliveOnly, Boolean deadOnly, Date effectiveDate) throws DAOException {

    if (effectiveDate == null) {
        effectiveDate = new Date();
    }//w  w w  . java  2s  .co m

    StringBuffer queryString = new StringBuffer("select patientId from Patient patient");
    List<String> clauses = new ArrayList<String>();

    clauses.add("patient.voided = false");

    if (gender != null) {
        gender = gender.toUpperCase();
        clauses.add("patient.gender = :gender");
    }
    if (minBirthdate != null) {
        clauses.add("patient.birthdate >= :minBirthdate");
    }
    if (maxBirthdate != null) {
        clauses.add("patient.birthdate <= :maxBirthdate");
    }
    if (aliveOnly != null && aliveOnly) {
        clauses.add("patient.dead = false"); // TODO: Should this use effectiveDate?  What if deathDate is null?
    }
    if (deadOnly != null && deadOnly) {
        clauses.add("patient.dead = true"); // TODO: Should this use effectiveDate?  What if deathDate is null?
    }

    Date maxBirthFromAge = null;
    if (minAge != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(effectiveDate);
        cal.add(Calendar.YEAR, -minAge);
        maxBirthFromAge = cal.getTime();
        clauses.add("patient.birthdate <= :maxBirthFromAge");
    }
    Date minBirthFromAge = null;
    if (maxAge != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(effectiveDate);
        cal.add(Calendar.YEAR, -(maxAge + 1));
        minBirthFromAge = cal.getTime();
        clauses.add("patient.birthdate > :minBirthFromAge");
    }

    clauses.add("(patient.birthdate is null or patient.birthdate <= :effectiveDate)");

    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 (gender != null) {
        query.setString("gender", gender);
    }
    if (minBirthdate != null) {
        query.setDate("minBirthdate", minBirthdate);
    }
    if (maxBirthdate != null) {
        query.setDate("maxBirthdate", maxBirthdate);
    }
    if (minAge != null) {
        query.setDate("maxBirthFromAge", maxBirthFromAge);
    }
    if (maxAge != null) {
        query.setDate("minBirthFromAge", minBirthFromAge);
    }
    query.setDate("effectiveDate", effectiveDate);

    return new Cohort(query.list());
}

---

From source file:org.openmrs.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

/**
 * Returns a Map from patientId to a Collection of drugIds for drugs active for the patients on
 * that date//from  w  w  w.  ja  v a  2 s  .  c  o m
 * If patientIds is null then do this for all patients
 * Does not return anything for voided patients
 * 
 * @throws DAOException
 */
@SuppressWarnings("unchecked")
public Map<Integer, Collection<Integer>> getActiveDrugIds(Collection<Integer> patientIds, Date fromDate,
        Date toDate) throws DAOException {
    HashSet<Integer> idsLookup = patientIds == null ? null
            : (patientIds instanceof HashSet ? (HashSet<Integer>) patientIds
                    : new HashSet<Integer>(patientIds));

    Map<Integer, Collection<Integer>> ret = new HashMap<Integer, Collection<Integer>>();

    List<String> whereClauses = new ArrayList<String>();
    whereClauses.add("o.voided = false");
    if (toDate != null)
        whereClauses.add("o.start_date <= :toDate");
    if (fromDate != null) {
        whereClauses.add("(o.auto_expire_date is null or o.auto_expire_date > :fromDate)");
        whereClauses.add("(o.discontinued_date is null or o.discontinued_date > :fromDate)");
    }

    String sql = "select o.patient_id, d.drug_inventory_id " + "from orders o "
            + "    inner join patient p on o.patient_id = p.patient_id and p.voided = false "
            + "    inner join drug_order d on o.order_id = d.order_id ";
    for (ListIterator<String> i = whereClauses.listIterator(); i.hasNext();) {
        sql += (i.nextIndex() == 0 ? " where " : " and ");
        sql += i.next();
    }

    log.debug("sql= " + sql);

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.setCacheMode(CacheMode.IGNORE);

    if (toDate != null)
        query.setDate("toDate", toDate);
    if (fromDate != null)
        query.setDate("fromDate", fromDate);

    List<Object[]> results = (List<Object[]>) query.list();
    for (Object[] row : results) {
        Integer patientId = (Integer) row[0];
        if (idsLookup == null || idsLookup.contains(patientId)) {
            Integer drugId = (Integer) row[1];
            Collection<Integer> drugIds = ret.get(patientId);
            if (drugIds == null) {
                drugIds = new HashSet<Integer>();
                ret.put(patientId, drugIds);
            }
            drugIds.add(drugId);
        }
    }
    return ret;
}

---

From source file:org.openmrs.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

public Cohort getPatientsHavingDrugOrder(List<Drug> drugList, List<Concept> drugConceptList, Date startDateFrom,
        Date startDateTo, Date stopDateFrom, Date stopDateTo, Boolean discontinued,
        List<Concept> discontinuedReason) {
    if (drugList != null && drugList.size() == 0)
        drugList = null;// w w w  . jav  a2 s .  co  m
    if (drugConceptList != null && drugConceptList.size() == 0)
        drugConceptList = null;
    StringBuilder sb = new StringBuilder();
    sb.append(" select distinct patient.id from DrugOrder where voided = false and patient.voided = false ");
    if (drugList != null)
        sb.append(" and drug.id in (:drugIdList) ");
    if (drugConceptList != null)
        sb.append(" and concept.id in (:drugConceptIdList) ");
    if (startDateFrom != null && startDateTo != null) {
        sb.append(" and startDate between :startDateFrom and :startDateTo ");
    } else {
        if (startDateFrom != null)
            sb.append(" and startDate >= :startDateFrom ");
        if (startDateTo != null)
            sb.append(" and startDate <= :startDateTo ");
    }
    if (discontinuedReason != null && discontinuedReason.size() > 0)
        sb.append(" and discontinuedReason.id in (:discontinuedReasonIdList) ");
    if (discontinued != null) {
        sb.append(" and discontinued = :discontinued ");
        if (discontinued == true) {
            if (stopDateFrom != null && stopDateTo != null) {
                sb.append(" and discontinuedDate between :stopDateFrom and :stopDateTo ");
            } else {
                if (stopDateFrom != null)
                    sb.append(" and discontinuedDate >= :stopDateFrom ");
                if (stopDateTo != null)
                    sb.append(" and discontinuedDate <= :stopDateTo ");
            }
        } else { // discontinued == false
            if (stopDateFrom != null && stopDateTo != null) {
                sb.append(" and autoExpireDate between :stopDateFrom and :stopDateTo ");
            } else {
                if (stopDateFrom != null)
                    sb.append(" and autoExpireDate >= :stopDateFrom ");
                if (stopDateTo != null)
                    sb.append(" and autoExpireDate <= :stopDateTo ");
            }
        }
    } else { // discontinued == null, so we need either
        if (stopDateFrom != null && stopDateTo != null) {
            sb.append(" and coalesce(discontinuedDate, autoExpireDate) between :stopDateFrom and :stopDateTo ");
        } else {
            if (stopDateFrom != null)
                sb.append(" and coalesce(discontinuedDate, autoExpireDate) >= :stopDateFrom ");
            if (stopDateTo != null)
                sb.append(" and coalesce(discontinuedDate, autoExpireDate) <= :stopDateTo ");
        }
    }
    log.debug("sql = " + sb);
    Query query = sessionFactory.getCurrentSession().createQuery(sb.toString());

    if (drugList != null) {
        List<Integer> ids = new ArrayList<Integer>();
        for (Drug d : drugList)
            ids.add(d.getDrugId());
        query.setParameterList("drugIdList", ids);
    }
    if (drugConceptList != null) {
        List<Integer> ids = new ArrayList<Integer>();
        for (Concept c : drugConceptList)
            ids.add(c.getConceptId());
        query.setParameterList("drugConceptIdList", ids);
    }
    if (startDateFrom != null)
        query.setDate("startDateFrom", startDateFrom);
    if (startDateTo != null)
        query.setDate("startDateTo", startDateTo);
    if (stopDateFrom != null)
        query.setDate("stopDateFrom", stopDateFrom);
    if (stopDateTo != null)
        query.setDate("stopDateTo", stopDateTo);
    if (discontinued != null)
        query.setBoolean("discontinued", discontinued);
    if (discontinuedReason != null && discontinuedReason.size() > 0) {
        List<Integer> ids = new ArrayList<Integer>();
        for (Concept c : discontinuedReason)
            ids.add(c.getConceptId());
        query.setParameterList("discontinuedReasonIdList", ids);
    }

    return new Cohort(query.list());
}

---

From source file:org.openmrs.module.sync.api.db.hibernate.HibernateSyncDAO.java

License:Open Source License

/**
 * @see org.openmrs.module.sync.api.db.SyncDAO#deleteSyncRecords(org.openmrs.module.sync.SyncRecordState[],
 *      java.util.Date)/*from  ww  w.j  av a 2  s .c o m*/
 */
public Integer deleteSyncRecords(SyncRecordState[] states, Date to) throws DAOException {
    List<String> stateStrings = new ArrayList<String>();
    for (SyncRecordState s : states) {
        stateStrings.add(s.name());
    }

    // no matter what kind of server this current server is (parent or child), the sync server records
    // must be either committed or not syncing in order to delete them
    String[] syncServerStates = new String[] { SyncRecordState.NOT_SUPPOSED_TO_SYNC.name(),
            SyncRecordState.COMMITTED.name() };

    // delete all rows in sync_server_id that are of the right state and are old
    Query deleteSSRQuery = sessionFactory.getCurrentSession().createSQLQuery(
            "delete from sync_server_record where state in (:states) and (select timestamp from sync_record sr where sr.record_id = sync_server_record.record_id) < :to");
    deleteSSRQuery.setParameterList("states", syncServerStates);
    deleteSSRQuery.setDate("to", to);
    Integer quantityDeleted = deleteSSRQuery.executeUpdate(); // this quantity isn't really used

    // if a sync_record now has zero sync_record_server rows, then that means all
    // the rows were deleted in the previous query and so the sync_record can also be deleted
    Query deleteQuery = sessionFactory.getCurrentSession().createSQLQuery(
            "delete from sync_record where (select count(*) from sync_server_record ssr where ssr.record_id = sync_record.record_id) = 0 and sync_record.timestamp <= :to and sync_record.state in (:states)");
    deleteQuery.setDate("to", to);
    deleteQuery.setParameterList("states", stateStrings);
    quantityDeleted = deleteQuery.executeUpdate();

    return quantityDeleted;
}

---

From source file:org.opensingular.lib.support.persistence.SimpleDAO.java

License:Apache License

protected Query setParametersQuery(Query query, Map<String, Object> params) {
    for (Map.Entry<String, Object> parameter : params.entrySet()) {
        if (parameter.getValue() instanceof Collection<?>) {
            query.setParameterList(parameter.getKey(), (Collection<?>) parameter.getValue());
        } else if (parameter.getValue() instanceof Integer) {
            query.setInteger(parameter.getKey(), (Integer) parameter.getValue());
        } else if (parameter.getValue() instanceof Date) {
            query.setDate(parameter.getKey(), (Date) parameter.getValue());
        } else {/*from www . j  av  a 2s .co  m*/
            query.setParameter(parameter.getKey(), parameter.getValue());
        }
    }
    return query;
}

---

From source file:org.projectforge.business.teamcal.event.TeamEventDao.java

License:Open Source License

/**
 * Get all locations of the user's calendar events (not deleted ones) with modification date within last year.
 *
 * @param searchString/* w  w w. j  av a2s.c  om*/
 */
@SuppressWarnings("unchecked")
public List<String> getLocationAutocompletion(final String searchString, final TeamCalDO[] calendars) {
    if (calendars == null || calendars.length == 0) {
        return null;
    }
    if (StringUtils.isBlank(searchString) == true) {
        return null;
    }
    checkLoggedInUserSelectAccess();
    final String s = "select distinct location from " + clazz.getSimpleName()
            + " t where deleted=false and t.calendar in :cals and lastUpdate > :lastUpdate and lower(t.location) like :location) order by t.location";
    final Query query = getSession().createQuery(s);
    query.setParameterList("cals", calendars);
    final DateHolder dh = new DateHolder();
    dh.add(Calendar.YEAR, -1);
    query.setDate("lastUpdate", dh.getDate());
    query.setString("location", "%" + StringUtils.lowerCase(searchString) + "%");
    final List<String> list = query.list();
    return list;
}

---

From source file:org.projectforge.business.timesheet.TimesheetDao.java

License:Open Source License

/**
 * Get all locations of the user's time sheet (not deleted ones) with modification date within last year.
 * //from ww w  .  j  a  v a 2 s  . com
 * @param searchString
 */
@SuppressWarnings("unchecked")
public List<String> getLocationAutocompletion(final String searchString) {
    checkLoggedInUserSelectAccess();
    if (StringUtils.isBlank(searchString) == true) {
        return null;
    }
    final String s = "select distinct location from " + clazz.getSimpleName()
            + " t where deleted=false and t.user.id = ? and lastUpdate > ? and lower(t.location) like ?) order by t.location";
    final Query query = getSession().createQuery(s);
    query.setInteger(0, ThreadLocalUserContext.getUser().getId());
    final DateHolder dh = new DateHolder();
    dh.add(Calendar.YEAR, -1);
    query.setDate(1, dh.getDate());
    query.setString(2, "%" + StringUtils.lowerCase(searchString) + "%");
    final List<String> list = query.list();
    return list;
}

---

From source file:org.projectforge.business.timesheet.TimesheetDao.java

License:Open Source License

/**
 * Get all locations of the user's time sheet (not deleted ones) with modification date within last year.
 * //from w  w  w  .j a va  2  s .c  o  m
 * @param maxResults Limit the result to the recent locations.
 * @return result as Json object.
 */
@SuppressWarnings("unchecked")
public Collection<String> getRecentLocation(final int maxResults) {
    checkLoggedInUserSelectAccess();
    log.info("Get recent locations from the database.");
    final String s = "select location from " + (clazz.getSimpleName()
            + " t where deleted=false and t.user.id = ? and lastUpdate > ? and t.location != null and t.location != '' order by t.lastUpdate desc");
    final Query query = getSession().createQuery(s);
    query.setInteger(0, ThreadLocalUserContext.getUser().getId());
    final DateHolder dh = new DateHolder();
    dh.add(Calendar.YEAR, -1);
    query.setDate(1, dh.getDate());
    final List<Object> list = query.list();
    int counter = 0;
    final List<String> res = new ArrayList<String>();
    for (final Object loc : list) {
        if (res.contains(loc) == true) {
            continue;
        }
        res.add((String) loc);
        if (++counter >= maxResults) {
            break;
        }
    }
    return res;
}

---

From source file:org.projectforge.core.BaseDao.java

License:Open Source License

/**
 * Only generic check access will be done. The matching entries will not be checked!
 * @param property Property of the data base entity.
 * @param searchString String the user has typed in.
 * @return All matching entries (like search) for the given property modified or updated in the last 2 years.
 *//*from w ww .j av  a2  s .  co  m*/
@SuppressWarnings("unchecked")
public List<String> getAutocompletion(final String property, final String searchString) {
    checkLoggedInUserSelectAccess();
    if (StringUtils.isBlank(searchString) == true) {
        return null;
    }
    final String hql = "select distinct " + property + " from " + clazz.getSimpleName()
            + " t where deleted=false and lastUpdate > ? and lower(t." + property + ") like ?) order by t."
            + property;
    final Query query = getSession().createQuery(hql);
    final DateHolder dh = new DateHolder();
    dh.add(Calendar.YEAR, -2); // Search only for entries of the last 2 years.
    query.setDate(0, dh.getDate());
    query.setString(1, "%" + StringUtils.lowerCase(searchString) + "%");
    final List<String> list = query.list();
    return list;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• »