Example usage for org.hibernate Query setTimestamp

List of usage examples for org.hibernate Query setTimestamp

Introduction

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

Prototype

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

Source Link

Document

Bind the value and the time of a given Date object to a named query parameter.

Usage

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * Encapsulates the common logic between getPatientsHavingRangedObs and getPatientsHavingDiscreteObs
 * //  w w  w.  java 2 s  . c om
 * The arguments passed in fall into two types:
 * <ol>
 *     <li>arguments that limit which obs we will look at</li>
 *     <ul>
 *         <li>timeModifier</li>
 *         <li>question</li>
 *         <li>groupingConcept</li>
 *         <li>onOrAfter</li>
 *         <li>onOrBefore</li>
 *         <li>locationList</li>
 *         <li>encounterTypeList</li>
 *         <li></li>
 *         <li></li>
 *     </ul>
 *     <li>arguments that the obs values must match after being limited by the above arguments</li>
 *     <ul>
 *         <li>operator1</li>
 *         <li>value1</li>
 *         <li>operator2</li>
 *         <li>value2</li>
 *         <li>setOperator</li>
 *         <li>valueList</li>
 *     </ul>
 * </ol> 
 * 
 * @param timeModifier
 * @param question
 * @param groupingConcept
 * @param onOrAfter
 * @param onOrBefore
 * @param locationList
 * @param encounterTypeList
 * @param operator1
 * @param value1
 * @param operator2
 * @param value2
 * @param setOperator
 * @param valueList
 * @return
 */
private Cohort getPatientsHavingObs(TimeModifier timeModifier, Concept question, Concept groupingConcept,
        Date onOrAfter, Date onOrBefore, List<Location> locationList, List<EncounterType> encounterTypeList,
        RangeComparator operator1, Object value1, RangeComparator operator2, Object value2,
        SetComparator setOperator, List<? extends Object> valueList) {

    Integer questionConceptId = question == null ? null : question.getId();
    Integer groupingConceptId = groupingConcept == null ? null : groupingConcept.getId();
    if (groupingConceptId != null)
        throw new RuntimeException("grouping concept not yet implemented");

    List<Integer> locationIds = SqlUtils.openmrsObjectIdListHelper(locationList);
    List<Integer> encounterTypeIds = SqlUtils.openmrsObjectIdListHelper(encounterTypeList);

    boolean joinOnEncounter = encounterTypeIds != null;
    String dateAndLocationSql = ""; // TODO rename to include encounterType
    String dateAndLocationSqlForSubquery = "";
    if (onOrAfter != null) {
        dateAndLocationSql += " and o.obs_datetime >= :onOrAfter ";
        dateAndLocationSqlForSubquery += " and obs.obs_datetime >= :onOrAfter ";
    }
    if (onOrBefore != null) {
        dateAndLocationSql += " and o.obs_datetime <= :onOrBefore ";
        dateAndLocationSqlForSubquery += " and obs.obs_datetime <= :onOrBefore ";
    }
    if (locationIds != null) {
        dateAndLocationSql += " and o.location_id in (:locationIds) ";
        dateAndLocationSqlForSubquery += " and obs.location_id in (:locationIds) ";
    }
    if (encounterTypeIds != null) {
        dateAndLocationSql += " and e.encounter_type in (:encounterTypeIds) ";
        dateAndLocationSqlForSubquery += " and encounter.encounter_type in (:encounterTypeIds) ";
    }

    boolean doSqlAggregation = timeModifier == TimeModifier.MIN || timeModifier == TimeModifier.MAX
            || timeModifier == TimeModifier.AVG;
    boolean doInvert = timeModifier == TimeModifier.NO;

    String valueSql = null;
    List<String> valueClauses = new ArrayList<String>();
    List<Object> valueListForQuery = null;

    if (value1 != null || value2 != null) {
        valueSql = (value1 != null && value1 instanceof Number) ? " o.value_numeric " : " o.value_datetime ";
    } else if (valueList != null && valueList.size() > 0) {
        valueListForQuery = new ArrayList<Object>();
        if (valueList.get(0) instanceof String) {
            valueSql = " o.value_text ";
            for (Object o : valueList)
                valueListForQuery.add(o);
        } else {
            valueSql = " o.value_coded ";
            for (Object o : valueList) {
                if (o instanceof Concept)
                    valueListForQuery.add(((Concept) o).getConceptId());
                else if (o instanceof Number)
                    valueListForQuery.add(((Number) o).intValue());
                else
                    throw new IllegalArgumentException(
                            "Don't know how to handle " + o.getClass() + " in valueList");
            }
        }
    }

    if (doSqlAggregation) {
        valueSql = " " + timeModifier.toString() + "(" + valueSql + ") ";
    }

    if (value1 != null || value2 != null) {
        if (value1 != null) {
            valueClauses.add(valueSql + operator1.getSqlRepresentation() + " :value1 ");
        }
        if (value2 != null) {
            valueClauses.add(valueSql + operator2.getSqlRepresentation() + " :value2 ");
        }
    } else if (valueList != null && valueList.size() > 0) {
        valueClauses.add(valueSql + setOperator.getSqlRepresentation() + " (:valueList) ");
    }

    StringBuilder sql = new StringBuilder();
    sql.append(" select o.person_id from obs o ");
    sql.append(" inner join patient p on o.person_id = p.patient_id ");
    if (joinOnEncounter) {
        sql.append(" inner join encounter e on o.encounter_id = e.encounter_id ");
    }

    if (timeModifier == TimeModifier.ANY || timeModifier == TimeModifier.NO) {
        sql.append(" where o.voided = false and p.voided = false ");
        if (questionConceptId != null) {
            sql.append(" and concept_id = :questionConceptId ");
        }
        sql.append(dateAndLocationSql);
    } else if (timeModifier == TimeModifier.FIRST || timeModifier == TimeModifier.LAST) {
        boolean isFirst = timeModifier == PatientSetService.TimeModifier.FIRST;
        sql.append(" inner join ( ");
        sql.append("    select person_id, " + (isFirst ? "MIN" : "MAX") + "(obs_datetime) as odt ");
        sql.append("    from obs ");
        if (joinOnEncounter) {
            sql.append(" inner join encounter on obs.encounter_id = encounter.encounter_id ");
        }
        sql.append("             where obs.voided = false and obs.concept_id = :questionConceptId "
                + dateAndLocationSqlForSubquery + " group by person_id ");
        sql.append(" ) subq on o.person_id = subq.person_id and o.obs_datetime = subq.odt ");
        sql.append(" where o.voided = false and p.voided = false and o.concept_id = :questionConceptId ");
        sql.append(dateAndLocationSql);
    } else if (doSqlAggregation) {
        sql.append(" where o.voided = false and p.voided = false and concept_id = :questionConceptId "
                + dateAndLocationSql);
        sql.append(" group by o.person_id ");
    } else {
        throw new IllegalArgumentException("TimeModifier '" + timeModifier + "' not recognized");
    }

    if (valueClauses.size() > 0) {
        sql.append(doSqlAggregation ? " having " : " and ");
        for (Iterator<String> i = valueClauses.iterator(); i.hasNext();) {
            sql.append(i.next());
            if (i.hasNext())
                sql.append(" and ");
        }
    }

    log.debug("sql: " + sql);
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());
    query.setCacheMode(CacheMode.IGNORE);

    if (questionConceptId != null)
        query.setInteger("questionConceptId", questionConceptId);
    if (value1 != null) {
        if (value1 instanceof Number)
            query.setDouble("value1", ((Number) value1).doubleValue());
        else
            query.setDate("value1", (Date) value1);
    }
    if (value2 != null) {
        if (value2 instanceof Number)
            query.setDouble("value2", ((Number) value2).doubleValue());
        else
            query.setDate("value2", (Date) value2);
    }
    if (valueListForQuery != null) {
        query.setParameterList("valueList", valueListForQuery);
    }
    if (onOrAfter != null)
        query.setTimestamp("onOrAfter", onOrAfter);
    if (onOrBefore != null)
        query.setTimestamp("onOrBefore", DateUtil.getEndOfDayIfTimeExcluded(onOrBefore));
    if (locationIds != null)
        query.setParameterList("locationIds", locationIds);
    if (encounterTypeIds != null)
        query.setParameterList("encounterTypeIds", encounterTypeIds);

    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

/**
 * @see org.openmrs.module.reporting.cohort.query.db.CohortQueryDAO#getPatientsHavingEncounters(java.util.Date, java.util.Date, java.util.List, java.util.List, java.util.List, java.lang.Integer, java.lang.Integer, org.openmrs.User)
 *///  w ww  . j av  a2 s .com
public Cohort getPatientsHavingEncounters(Date onOrAfter, Date onOrBefore, TimeQualifier timeQualifier,
        List<Location> locationList, List<Person> providerList, List<EncounterType> encounterTypeList,
        List<Form> formList, Integer atLeastCount, Integer atMostCount, User createdBy, Date createdOnOrAfter,
        Date createdOnOrBefore) {
    List<Integer> encTypeIds = SqlUtils.openmrsObjectIdListHelper(encounterTypeList);
    List<Integer> locationIds = SqlUtils.openmrsObjectIdListHelper(locationList);
    List<Integer> providerIds = SqlUtils.openmrsObjectIdListHelper(providerList);
    List<Integer> formIds = SqlUtils.openmrsObjectIdListHelper(formList);

    // These clauses are applicable both in the overall query and in the subquery, if applicable
    // CollectionModifiers qualify the properties EncounterType, Form, and Location
    List<String> whichClauses = new ArrayList<String>();
    whichClauses.add("voided = false");
    ObjectUtil.addIfNotNull(whichClauses, "encounter_type in (:encTypeIds)", encTypeIds);
    ObjectUtil.addIfNotNull(whichClauses, "location_id in (:locationIds)", locationIds);

    if (!ReportUtil.isOpenmrsVersionOnePointNineAndAbove()) {
        ObjectUtil.addIfNotNull(whichClauses, "provider_id in (:providerIds)", providerIds);
    }

    ObjectUtil.addIfNotNull(whichClauses, "form_id in (:formIds)", formIds);

    // These clauses are only applicable in the overall query
    List<String> whereClauses = new ArrayList<String>(whichClauses);
    ObjectUtil.addIfNotNull(whereClauses, "encounter_datetime >= :onOrAfter", onOrAfter);
    ObjectUtil.addIfNotNull(whereClauses, "encounter_datetime <= :onOrBefore", onOrBefore);
    ObjectUtil.addIfNotNull(whereClauses, "creator = :createdBy", createdBy);
    ObjectUtil.addIfNotNull(whereClauses, "date_created >= :createdOnOrAfter", createdOnOrAfter);
    ObjectUtil.addIfNotNull(whereClauses, "date_created <= :createdOnOrBefore", createdOnOrBefore);

    List<String> havingClauses = new ArrayList<String>();
    ObjectUtil.addIfNotNull(havingClauses, "count(*) >= :atLeastCount", atLeastCount);
    ObjectUtil.addIfNotNull(havingClauses, "count(*) <= :atMostCount", atMostCount);

    StringBuilder sb = new StringBuilder();
    sb.append(" select e.patient_id from encounter e inner join patient p on e.patient_id = p.patient_id");

    if (providerIds != null && ReportUtil.isOpenmrsVersionOnePointNineAndAbove()) {
        sb.append(" inner join encounter_provider ep on ep.encounter_id = e.encounter_id ");
    }

    if (timeQualifier == TimeQualifier.FIRST || timeQualifier == TimeQualifier.LAST) {
        boolean isFirst = timeQualifier == TimeQualifier.FIRST;

        sb.append(" inner join ( ");
        sb.append("    select patient_id, " + (isFirst ? "MIN" : "MAX") + "(encounter_datetime) as edt ");
        sb.append("    from encounter ");

        if (providerIds != null && ReportUtil.isOpenmrsVersionOnePointNineAndAbove()) {
            sb.append(" inner join encounter_provider ep on ep.encounter_id = encounter.encounter_id ");
        }

        for (ListIterator<String> i = whichClauses.listIterator(); i.hasNext();) {
            sb.append(i.nextIndex() == 0 ? " where " : " and ");
            sb.append("encounter." + i.next());
        }

        if (providerIds != null && ReportUtil.isOpenmrsVersionOnePointNineAndAbove()) {
            sb.append(whichClauses.size() == 0 ? " where " : " and ");
            sb.append("ep.provider_id in (:providerIds)");
        }

        sb.append(" group by encounter.patient_id ");
        sb.append(" ) subq on e.patient_id = subq.patient_id and e.encounter_datetime = subq.edt ");

    }
    for (ListIterator<String> i = whereClauses.listIterator(); i.hasNext();) {
        sb.append(i.nextIndex() == 0 ? " where " : " and ");
        sb.append("e." + i.next());
    }

    if (providerIds != null && ReportUtil.isOpenmrsVersionOnePointNineAndAbove()) {
        sb.append(whereClauses.size() == 0 ? " where " : " and ");
        sb.append("ep.provider_id in (:providerIds)");
    }

    sb.append(" and p.voided = false");
    sb.append(" group by e.patient_id ");
    for (ListIterator<String> i = havingClauses.listIterator(); i.hasNext();) {
        sb.append(i.nextIndex() == 0 ? " having " : " and ");
        sb.append(i.next());
    }

    log.debug("query: " + sb);

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString());
    if (encTypeIds != null)
        query.setParameterList("encTypeIds", encTypeIds);
    if (locationIds != null)
        query.setParameterList("locationIds", locationIds);
    if (providerIds != null)
        query.setParameterList("providerIds", providerIds);
    if (formIds != null)
        query.setParameterList("formIds", formIds);
    if (onOrAfter != null)
        query.setTimestamp("onOrAfter", onOrAfter);
    if (onOrBefore != null)
        query.setTimestamp("onOrBefore", DateUtil.getEndOfDayIfTimeExcluded(onOrBefore));
    if (atLeastCount != null)
        query.setInteger("atLeastCount", atLeastCount);
    if (atMostCount != null)
        query.setInteger("atMostCount", atMostCount);
    if (createdBy != null)
        query.setInteger("createdBy", createdBy.getId());
    if (createdOnOrAfter != null)
        query.setTimestamp("createdOnOrAfter", createdOnOrAfter);
    if (createdOnOrBefore != null)
        query.setTimestamp("createdOnOrBefore", DateUtil.getEndOfDayIfTimeExcluded(createdOnOrBefore));

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

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * @see org.openmrs.module.reporting.cohort.query.db.CohortQueryDAO#getPatientsHavingBirthAndDeath(java.util.Date, java.util.Date, java.util.Date, java.util.Date)
 *//*w w w  .  j a v a2  s.  c  o m*/
public Cohort getPatientsHavingBirthAndDeath(Date bornOnOrAfter, Date bornOnOrBefore, Date diedOnOrAfter,
        Date diedOnOrBefore) {
    StringBuilder sql = new StringBuilder();
    sql.append(" select patient_id ");
    sql.append(" from patient pat ");
    sql.append(" inner join person per on pat.patient_id = per.person_id ");
    sql.append(" where pat.voided = false and per.voided = false ");
    if (bornOnOrAfter != null)
        sql.append(" and birthdate >= :bornOnOrAfter ");
    if (bornOnOrBefore != null)
        sql.append(" and birthdate <= :bornOnOrBefore ");
    if (diedOnOrAfter != null)
        sql.append(" and death_date >= :diedOnOrAfter ");
    if (diedOnOrBefore != null)
        sql.append(" and death_date <= :diedOnOrBefore ");

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

    if (bornOnOrAfter != null)
        q.setTimestamp("bornOnOrAfter", bornOnOrAfter);
    if (bornOnOrBefore != null)
        q.setTimestamp("bornOnOrBefore", DateUtil.getEndOfDayIfTimeExcluded(bornOnOrBefore));
    if (diedOnOrAfter != null)
        q.setTimestamp("diedOnOrAfter", diedOnOrAfter);
    if (diedOnOrBefore != null)
        q.setTimestamp("diedOnOrBefore", DateUtil.getEndOfDayIfTimeExcluded(diedOnOrBefore));

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

From source file:org.opennms.netmgt.dao.hibernate.OnmsMapDaoHibernate.java

License:Open Source License

/** {@inheritDoc} */
@Override/*w ww  .  j a v  a 2  s  .  co m*/
public int updateAllAutomatedMap(final Date time) {
    return getHibernateTemplate().execute(new HibernateCallback<Integer>() {
        @Override
        public Integer doInHibernate(Session session) throws HibernateException, SQLException {

            String hql = "update OnmsMap as map set map.lastModifiedTime = :time where map.type = :type";
            Query query = session.createQuery(hql);
            query.setTimestamp("time", time);
            query.setString("type", OnmsMap.AUTOMATICALLY_GENERATED_MAP);
            return query.executeUpdate();
        }
    });

}

From source file:org.sakaiproject.attendance.dao.impl.AttendanceDaoImpl.java

License:Educational Community License

/**
 * {@inheritDoc}//ww  w  . j a v a  2  s . c o m
 */
@SuppressWarnings("unchecked")
public List<Long> getAttendanceSiteBatch(final Date syncTime, final Long lastId) {
    final HibernateCallback<List<Long>> hcb = new HibernateCallback<List<Long>>() {
        @Override
        public List<Long> doInHibernate(Session session) throws HibernateException {
            Query q = session.getNamedQuery(QUERY_GET_ATTENDANCE_SITE_BATCH);
            q.setTimestamp(SYNC_TIME, syncTime);
            q.setLong(ID, lastId);
            q.setMaxResults(5);
            return q.list();
        }
    };

    return getHibernateTemplate().execute(hcb);
}

From source file:org.sakaiproject.attendance.dao.impl.AttendanceDaoImpl.java

License:Educational Community License

/**
 * {@inheritDoc}//from w  w  w .  j av  a 2s .  com
 */
@SuppressWarnings("unchecked")
public boolean markAttendanceSiteForSync(final List<Long> ids, final Date syncTime) {
    final HibernateCallback hcb = new HibernateCallback() {
        @Override
        public Integer doInHibernate(Session session) throws HibernateException {
            Query q = session.getNamedQuery(QUERY_MARK_ATTENDANCE_SITE_IN_SYNC);
            q.setParameterList(IDS, ids);
            q.setTimestamp(SYNC_TIME, syncTime);
            return q.executeUpdate();
        }
    };

    return getHibernateTemplate().execute(hcb).equals(ids.size());
}

From source file:org.sakaiproject.tool.assessment.facade.PublishedAssessmentFacadeQueries.java

License:Educational Community License

/**
 * According to Marc inactive means either the dueDate or the retractDate
 * has passed for 1.5 release (IM on 12/17/04)
 * //from   www.  j av a2  s.co m
 * @param sortString
 * @return
 */
public ArrayList getBasicInfoOfAllInActivePublishedAssessments(String sortString, final String siteAgentId,
        boolean ascending) {

    String orderBy = getOrderBy(sortString);
    String query = "select new PublishedAssessmentData(p.publishedAssessmentId, p.title,"
            + " c.releaseTo, c.startDate, c.dueDate, c.retractDate, p.status, p.lastModifiedDate, p.lastModifiedBy) from PublishedAssessmentData p,"
            + " PublishedAccessControl c, AuthorizationData z  "
            + " where c.assessment.publishedAssessmentId=p.publishedAssessmentId "
            + " and ((p.status=:activeStatus and (c.dueDate<=:today or c.retractDate<=:today)) or p.status=:editStatus)"
            + " and p.publishedAssessmentId=z.qualifierId and z.functionId=:functionId "
            //+ " and (z.agentIdString=:siteId or z.agentIdString in (:groupIds)) "
            + " and z.agentIdString=:siteId " + " order by p." + orderBy;

    if (ascending)
        query += " asc";
    else
        query += " desc";

    final String hql = query;
    final HibernateCallback hcb = new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Query q = session.createQuery(hql);
            q.setInteger("activeStatus", 1);
            q.setTimestamp("today", new Date());
            q.setInteger("editStatus", 3);
            q.setString("functionId", "OWN_PUBLISHED_ASSESSMENT");
            q.setString("siteId", siteAgentId);
            //q.setParameterList("groupIds", groupIds);
            return q.list();
        };
    };
    List list = getHibernateTemplate().executeFind(hcb);

    // List list = getHibernateTemplate().find(query,
    // new Object[] {new Date(), new Date(),siteAgentId} ,
    // new org.hibernate.type.Type[] {Hibernate.TIMESTAMP,
    // Hibernate.TIMESTAMP,
    // Hibernate.STRING});

    ArrayList pubList = new ArrayList();
    TreeMap groupsForSite = null;
    String releaseToGroups;
    String lastModifiedBy = "";
    AgentFacade agent = null;
    for (int i = 0; i < list.size(); i++) {
        PublishedAssessmentData p = (PublishedAssessmentData) list.get(i);
        releaseToGroups = null;
        if (p.getReleaseTo().equals(AssessmentAccessControl.RELEASE_TO_SELECTED_GROUPS)) {
            if (groupsForSite == null) {
                groupsForSite = getGroupsForSite(siteAgentId);
            }
            Long assessmentId = p.getPublishedAssessmentId();
            releaseToGroups = getReleaseToGroupsAsString(groupsForSite, assessmentId);
        }

        agent = new AgentFacade(p.getLastModifiedBy());
        if (agent != null) {
            lastModifiedBy = agent.getDisplayName();
        }
        PublishedAssessmentFacade f = new PublishedAssessmentFacade(p.getPublishedAssessmentId(), p.getTitle(),
                p.getReleaseTo(), p.getStartDate(), p.getDueDate(), p.getStatus(), releaseToGroups,
                p.getLastModifiedDate(), lastModifiedBy);
        pubList.add(f);
    }
    return pubList;
}

From source file:org.transitime.db.structs.ArrivalDeparture.java

License:Open Source License

/**
 * For querying large amount of data. With a Hibernate Iterator not
 * all the data is read in at once. This means that can iterate over
 * a large dataset without running out of memory. But this can be slow
 * because when using iterate() an initial query is done to get all of
 * Id column data and then a separate query is done when iterating 
 * over each row. Doing an individual query per row is of course
 * quite time consuming. Better to use getArrivalsDeparturesFromDb()
 * with a fairly large batch size of ~50000.
 * <p>//from   ww w .j  ava2  s  . c o m
 * Note that the session needs to be closed externally once done with
 * the Iterator.
 * 
 * @param session
 * @param beginTime
 * @param endTime
 * @return
 * @throws HibernateException
 */
public static Iterator<ArrivalDeparture> getArrivalsDeparturesDbIterator(Session session, Date beginTime,
        Date endTime) throws HibernateException {
    // Create the query. Table name is case sensitive and needs to be the
    // class name instead of the name of the db table.
    String hql = "FROM ArrivalDeparture " + "    WHERE time >= :beginDate " + "      AND time < :endDate";
    Query query = session.createQuery(hql);

    // Set the parameters
    query.setTimestamp("beginDate", beginTime);
    query.setTimestamp("endDate", endTime);

    @SuppressWarnings("unchecked")
    Iterator<ArrivalDeparture> iterator = query.iterate();
    return iterator;
}

From source file:org.transitime.db.structs.ArrivalDeparture.java

License:Open Source License

/**
 * Reads the arrivals/departures for the timespan specified. All of the 
 * data is read in at once so could present memory issue if reading
 * in a very large amount of data. For that case probably best to instead
 * use getArrivalsDeparturesDb() where one specifies the firstResult and 
 * maxResult parameters./*ww  w.j  a va 2s  .c  o  m*/
 * 
 * @param projectId
 * @param beginTime
 * @param endTime
 * @return
 */
public static List<ArrivalDeparture> getArrivalsDeparturesFromDb(String projectId, Date beginTime,
        Date endTime) {
    IntervalTimer timer = new IntervalTimer();

    // Get the database session. This is supposed to be pretty light weight
    Session session = HibernateUtils.getSession(projectId);

    // Create the query. Table name is case sensitive and needs to be the
    // class name instead of the name of the db table.
    String hql = "FROM ArrivalDeparture " + "    WHERE time >= :beginDate " + "      AND time < :endDate";
    Query query = session.createQuery(hql);

    // Set the parameters
    query.setTimestamp("beginDate", beginTime);
    query.setTimestamp("endDate", endTime);

    try {
        @SuppressWarnings("unchecked")
        List<ArrivalDeparture> arrivalsDeparatures = query.list();
        logger.debug("Getting arrival/departures from database took {} msec", timer.elapsedMsec());
        return arrivalsDeparatures;
    } catch (HibernateException e) {
        // Log error to the Core logger
        Core.getLogger().error(e.getMessage(), e);
        return null;
    } finally {
        // Clean things up. Not sure if this absolutely needed nor if
        // it might actually be detrimental and slow things down.
        session.close();
    }
}

From source file:org.transitime.db.structs.ArrivalDeparture.java

License:Open Source License

/**
 * Allows batch retrieval of data. This is likely the best way to read in
 * large amounts of data. Using getArrivalsDeparturesDbIterator() reads in
 * only data as needed so good with respect to memory usage but it does a
 * separate query for each row. Reading in list of all data is quick but can
 * cause memory problems if reading in a very large amount of data. This
 * method is a good compromise because it only reads in a batch of data at a
 * time so is not as memory intensive yet it is quite fast. With a batch
 * size of 50k found it to run in under 1/4 the time as with the iterator
 * method./*from   w  w w. ja  v  a  2  s . c  om*/
 * 
 * @param dbName
 *            Name of the database to retrieve data from. If set to null
 *            then will use db name configured by Java property
 *            transitime.db.dbName
 * @param beginTime
 * @param endTime
 * @param sqlClause
 *            The clause is added to the SQL for retrieving the
 *            arrival/departures. Useful for ordering the results. Can be
 *            null.
 * @param firstResult
 *            For when reading in batch of data at a time.
 * @param maxResults
 *            For when reading in batch of data at a time. If set to 0 then
 *            will read in all data at once.
 * @param arrivalOrDeparture
 *            Enumeration specifying whether to read in just arrivals or
 *            just departures. Set to null to read in both.
 * @return
 */
public static List<ArrivalDeparture> getArrivalsDeparturesFromDb(String dbName, Date beginTime, Date endTime,
        String sqlClause, final int firstResult, final int maxResults,
        ArrivalsOrDepartures arrivalOrDeparture) {
    IntervalTimer timer = new IntervalTimer();

    // Get the database session. This is supposed to be pretty light weight
    Session session = dbName != null ? HibernateUtils.getSession(dbName) : HibernateUtils.getSession();

    // Create the query. Table name is case sensitive and needs to be the
    // class name instead of the name of the db table.
    String hql = "FROM ArrivalDeparture " + "    WHERE time >= :beginDate " + "      AND time < :endDate";
    if (arrivalOrDeparture != null) {
        if (arrivalOrDeparture == ArrivalsOrDepartures.ARRIVALS)
            hql += " AND isArrival = true";
        else
            hql += " AND isArrival = false";
    }
    if (sqlClause != null)
        hql += " " + sqlClause;
    Query query = session.createQuery(hql);

    // Set the parameters for the query
    query.setTimestamp("beginDate", beginTime);
    query.setTimestamp("endDate", endTime);

    // Only get a batch of data at a time if maxResults specified
    query.setFirstResult(firstResult);
    if (maxResults > 0)
        query.setMaxResults(maxResults);

    try {
        @SuppressWarnings("unchecked")
        List<ArrivalDeparture> arrivalsDeparatures = query.list();
        logger.debug("Getting arrival/departures from database took {} msec", timer.elapsedMsec());
        return arrivalsDeparatures;
    } catch (HibernateException e) {
        // Log error to the Core logger
        Core.getLogger().error(e.getMessage(), e);
        return null;
    } finally {
        // Clean things up. Not sure if this absolutely needed nor if
        // it might actually be detrimental and slow things down.
        session.close();
    }

}