Java Code Examples for org.hibernate.Query#setTimestamp()

private void setParameters(Query query, Object[] parameters)
{
	if (parameters != null)
	{
		for (int i = 0; i < parameters.length; i++)
		{
			if (parameters[i] instanceof Date)
			{
				query.setTimestamp(i, (Date) parameters[i]);
			}
			else
			{
				query.setParameter(i, parameters[i]);
			}
		}
	}
}
 

@Override
@Transactional
@SelectDataSource(type = DataSourceType.READ_ONLY, storage = Storage.SHARDED)
public List findStatesByStatus(ShardId shardId, String stateMachineName, Timestamp fromTime, Timestamp toTime, String stateName, List<Status> statuses) {
    Query query;
    String queryString = "select state.stateMachineId, state.id, state.status from State state join StateMachine sm " +
            "on sm.id = state.stateMachineId and sm.createdAt between :fromTime and :toTime and sm.name = :stateMachineName";

    if (statuses != null && !statuses.isEmpty()) {
        StringBuilder sb = new StringBuilder(" and state.status in (");
        for (Status status : statuses) {
            sb.append("'" + status.toString() + "',");
        }
        sb.deleteCharAt(sb.length() - 1).append(")");
        String statusClause = sb.toString();
        queryString = queryString.concat(statusClause);
    }

    if (stateName == null) {
        query = currentSession().createQuery(queryString);
    } else {
        query = currentSession().createQuery(queryString + " and state.name = :stateName");
        query.setString("stateName", stateName);
    }

    query.setString("stateMachineName", stateMachineName);
    query.setTimestamp("fromTime", fromTime);
    query.setTimestamp("toTime", toTime);
    return query.list();
}
 

/**
 * Reads in all MonitoringEvents from the database that were between the
 * beginTime and endTime.
 * 
 * @param agencyId
 *            Which project getting data for
 * @param beginTime
 *            Specifies time range for query
 * @param endTime
 *            Specifies time range for query
 * @param sqlClause
 *            Optional. Can specify an SQL clause to winnow down the data,
 *            such as "AND routeId='71'".
 * @return
 */
public static List<MonitoringEvent> getMonitoringEvents(String agencyId,
		Date beginTime, Date endTime, String sqlClause) {
	IntervalTimer timer = new IntervalTimer();
	
	// Get the database session. This is supposed to be pretty light weight
	Session session = HibernateUtils.getSession(agencyId);

	// 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 MonitorEvent " +
			"    WHERE time >= :beginDate " +
			"      AND time < :endDate"; 
	if (sqlClause != null)
		hql += " " + sqlClause;
	Query query = session.createQuery(hql);
	
	// Set the parameters
	query.setTimestamp("beginDate", beginTime);
	query.setTimestamp("endDate", endTime);
	
	try {
		@SuppressWarnings("unchecked")
		List<MonitoringEvent> monitorEvents = query.list();
		logger.debug("Getting MonitoringEvent from database "
				+ "took {} msec", timer.elapsedMsec());
		return monitorEvents;
	} catch (HibernateException e) {
		logger.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();
	}

}
 

/**
 * 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.
 * 
 * @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();
	}
}
 

/**
 * Reads in all VehicleEvents from the database that were between the
 * beginTime and endTime.
 * 
 * @param agencyId
 *            Which project getting data for
 * @param beginTime
 *            Specifies time range for query
 * @param endTime
 *            Specifies time range for query
 * @param sqlClause
 *            Optional. Can specify an SQL clause to winnow down the data,
 *            such as "AND routeId='71'".
 * @return
 */
public static List<VehicleEvent> getVehicleEvents(String agencyId,
		Date beginTime, Date endTime, String sqlClause) {
	IntervalTimer timer = new IntervalTimer();
	
	// Get the database session. This is supposed to be pretty light weight
	Session session = HibernateUtils.getSession(agencyId);

	// 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 VehicleEvent " +
			"    WHERE time >= :beginDate " +
			"      AND time < :endDate"; 
	if (sqlClause != null)
		hql += " " + sqlClause;
	Query query = session.createQuery(hql);
	
	// Set the parameters
	query.setTimestamp("beginDate", beginTime);
	query.setTimestamp("endDate", endTime);
	
	try {
		@SuppressWarnings("unchecked")
		List<VehicleEvent> vehicleEvents = query.list();
		logger.debug("Getting VehicleEvents from database took {} msec",
				timer.elapsedMsec());
		return vehicleEvents;
	} catch (HibernateException e) {
		logger.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();
	}
}
 

@Retryable
@Transactional(propagation = Propagation.REQUIRES_NEW)
public int updateLastNotifiedDateByIds(Set<Long> ids, Date date) {
    Query query = genericDao.getNamedQuery(Subscription.UPDATE_LASTNOTIFIEDDATE_BY_IDS);
    query.setParameterList("ids", ids);
    query.setTimestamp("date", date);
    return query.executeUpdate();
}
 

@Retryable
@Transactional(propagation = Propagation.REQUIRES_NEW)
public int updateLastNotifiedDateByIds(Set<Long> ids, Date date) {
    Query query = genericDao.getNamedQuery(Subscription.UPDATE_LASTNOTIFIEDDATE_BY_IDS);
    query.setParameterList("ids", ids);
    query.setTimestamp("date", date);
    return query.executeUpdate();
}
 

public void testTS() throws Exception {
	if (getDialect() instanceof Oracle9Dialect) return;
	Session session = openSession();
	Transaction txn = session.beginTransaction();
	Simple sim = new Simple();
	sim.setDate( new Date() );
	session.save( sim, new Long(1) );
	Query q = session.createSQLQuery("select {sim.*} from Simple {sim} where {sim}.date_ = ?", "sim", Simple.class);
	q.setTimestamp( 0, sim.getDate() );
	assertTrue ( q.list().size()==1 );
	session.delete(sim);
	txn.commit();
	session.close();
}
 

/**
 * Function the update the marathon json in the environment table
 * 
 * @param conn
 * @param environmentVO
 * 
 */
public static void updateWestMarathonJson(final EnvironmentVO environmentVO) {
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(HQLConstants.DB_QUERY_UPDATE_MARATHON_JSON);
	query.setBinary("marathonJson", environmentVO.getMarathonJson().getBytes());
	query.setTimestamp("lastUpdateTime", new java.sql.Timestamp(new Date().getTime()));
	query.setLong("environmentId", environmentVO.getEnvironmentId());
	query.executeUpdate();
	txn.commit();
}
 

/**
 * Gets list of AvlReports from database for the time span specified.
 * 
 * @param beginTime
 * @param endTime
 * @param vehicleId
 *            Optional. If not null then will only return results for that
 *            vehicle
 * @param clause
 * 				Optional. If not null then the clause, such as "ORDER BY time"
 * will be added to the hql statement.
 * @return List of AvlReports or null if an exception is thrown
 */
public static List<AvlReport> getAvlReportsFromDb(
		Date beginTime, 
		Date endTime, 
		String vehicleId,
		String clause) {
	// Sessions are not threadsafe so need to create a new one each time.
	// They are supposed to be lightweight so this should be OK.
	Session session = HibernateUtils.getSession();
	
	// Create the query. Table name is case sensitive!
	String hql = "FROM AvlReport " +
			"    WHERE time >= :beginDate " +
			"      AND time < :endDate"; 
	if (vehicleId != null)
		hql += " AND vehicleId=:vehicleId";
	if (clause != null)
		hql += " " + clause;
	Query query = session.createQuery(hql);
	
	// Set the parameters
	if (vehicleId != null)
		query.setString("vehicleId", vehicleId);
	query.setTimestamp("beginDate", beginTime);
	query.setTimestamp("endDate", endTime);
	
	try {
		@SuppressWarnings("unchecked")
		List<AvlReport> avlReports = query.list();
		return avlReports;
	} 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();
	}
}
 

/**
 * Update the tps_service table with latest tps values. 
 * 
 */
public static void updateTpsService(final int envId, int componentId, float tpsVaule, float latencyValue){
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(HQLConstants.UPDATE_TPS_SERVICE_DETAILS);
	query.setFloat("tpsVaule", tpsVaule);
	query.setFloat("latencyValue", latencyValue);
	query.setTimestamp("lastUpdateDate", new java.sql.Timestamp(System.currentTimeMillis()));
	query.setLong("compId", componentId);
	query.setLong("environmentId", envId);
	query.executeUpdate();
	txn.commit();
}
 

/**
 * Update the prom_lookup table with latest http path. 
 * 
 */
public static void updatePromLookup(final int envId, int compId, String httpPath){

	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(HQLConstants.UPDATE_PROM_LOOKUP_DETAILS);
	query.setString("httpPath", httpPath);
	query.setTimestamp("lastUpdateDate", new java.sql.Timestamp(System.currentTimeMillis()));
	query.setLong("compId", compId);
	query.setLong("environmentId", envId);
	query.executeUpdate();
	txn.commit();
}
 

/**
 * @param launchDate
 * @param compId
 * @param envId
 * @throws ParseException
 */
public static void updateAppLaunchDate(String launchDate, int compId, int envId) throws ParseException {
	SimpleDateFormat sd1 = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.sss'Z'");
	Date dt = sd1.parse(launchDate);
	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query =session.createQuery(HQLConstants.DB_QUERY_UPDATE_APP_LAUNCH_DATE);
	query.setTimestamp("createdDate", new java.sql.Timestamp(format.parse(format.format(dt)).getTime()));
	query.setLong("compId", compId);
	query.setLong("enviromentId", envId);
	query.executeUpdate();
	txn.commit();
}
 

/**
 * Function the update the East marathon json in the environment table
 * 
 * @param conn
 * @param environmentVO
 * 
 */
public static void updateEastMarathonJson(final EnvironmentVO environmentVO) {
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(HQLConstants.DB_QUERY_UPDATE_EAST_MARATHON_JSON);
	query.setBinary("marathonJson", environmentVO.getEastMarathonJson().getBytes());
	query.setTimestamp("lastUpdateTime", new java.sql.Timestamp(new Date().getTime()));
	query.setLong("environmentId", environmentVO.getEnvironmentId());
	query.executeUpdate();
	txn.commit();
}
 

public static void setQueryParameterFromString(Query query, Class propertyClass, int pos, String value) throws Exception {
	if (propertyClass.equals(String.class)) {
		query.setString(pos, value);
	} else if (propertyClass.equals(Long.class)) {
		query.setBigInteger(pos, new BigInteger(value));
	} else if (propertyClass.equals(java.lang.Long.TYPE)) {
		query.setBigInteger(pos, new BigInteger(value));
	} else if (propertyClass.equals(Integer.class)) {
		query.setInteger(pos, new Integer(value));
	} else if (propertyClass.equals(java.lang.Integer.TYPE)) {
		query.setInteger(pos, new Integer(value));
	} else if (propertyClass.equals(Boolean.class)) {
		query.setBoolean(pos, new Boolean(value));
	} else if (propertyClass.equals(java.lang.Boolean.TYPE)) {
		query.setBoolean(pos, new Boolean(value));
	} else if (propertyClass.equals(Float.class)) {
		query.setFloat(pos, CommonUtil.parseFloat(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(java.lang.Float.TYPE)) {
		query.setFloat(pos, CommonUtil.parseFloat(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(Double.class)) {
		query.setDouble(pos, CommonUtil.parseDouble(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(java.lang.Double.TYPE)) {
		query.setDouble(pos, CommonUtil.parseDouble(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(Date.class)) {
		query.setDate(pos, CommonUtil.parseDate(value, CommonUtil.getInputDatePattern(CoreUtil.getUserContext().getDateFormat())));
	} else if (propertyClass.equals(Timestamp.class)) {
		query.setTimestamp(pos, CommonUtil.dateToTimestamp(CommonUtil.parseDate(value, CommonUtil.getInputDateTimePattern(CoreUtil.getUserContext().getDateFormat()))));
	} else if (propertyClass.equals(VariablePeriod.class)) {
		query.setString(pos, VariablePeriod.fromString(value).name());
	} else if (propertyClass.equals(AuthenticationType.class)) {
		query.setString(pos, AuthenticationType.fromString(value).name());
	} else if (propertyClass.equals(Sex.class)) {
		query.setString(pos, Sex.fromString(value).name());
	} else if (propertyClass.equals(RandomizationMode.class)) {
		query.setString(pos, RandomizationMode.fromString(value).name());
	} else if (propertyClass.equals(DBModule.class)) {
		query.setString(pos, DBModule.fromString(value).name());
	} else if (propertyClass.equals(HyperlinkModule.class)) {
		query.setString(pos, HyperlinkModule.fromString(value).name());
	} else if (propertyClass.equals(JournalModule.class)) {
		query.setString(pos, JournalModule.fromString(value).name());
	} else if (propertyClass.equals(FileModule.class)) {
		query.setString(pos, FileModule.fromString(value).name());
	} else if (propertyClass.equals(Color.class)) {
		query.setString(pos, Color.fromString(value).name());
	} else if (propertyClass.equals(InputFieldType.class)) {
		query.setString(pos, InputFieldType.fromString(value).name());
	} else if (propertyClass.equals(EventImportance.class)) {
		query.setString(pos, EventImportance.fromString(value).name());
	} else if (propertyClass.equals(JobStatus.class)) {
		query.setString(pos, JobStatus.fromString(value).name());
	} else if (propertyClass.isArray() && propertyClass.getComponentType().equals(java.lang.Byte.TYPE)) { // only string hashes supported, no boolean, float, etc...
		query.setBinary(pos, CryptoUtil.hashForSearch(value));
	} else {
		// illegal type...
		throw new IllegalArgumentException(MessageFormat.format(CommonUtil.INPUT_TYPE_NOT_SUPPORTED, propertyClass.toString()));
	}
}
 

public static void setQueryParameterFromCriterion(String propertyName, Query query, CriterionValueType valueType, int pos, CriterionInstantVO value) throws Exception {
	switch (valueType) {
		case BOOLEAN:
			query.setBoolean(pos, value.getBooleanValue());
			break;
		case BOOLEAN_HASH:
			query.setBinary(pos, CryptoUtil.hashForSearch(value.getBooleanValue()));
			break;
		case DATE:
			query.setDate(pos, value.getDateValue());
			break;
		case DATE_HASH:
			query.setBinary(pos, CryptoUtil.hashForSearch(value.getDateValue()));
			break;
		case TIME:
			query.setTime(pos, value.getTimeValue());
			break;
		case TIME_HASH:
			query.setBinary(pos, CryptoUtil.hashForSearch(value.getTimeValue()));
			break;
		case FLOAT:
			query.setFloat(pos, value.getFloatValue().floatValue());
			break;
		case FLOAT_HASH:
			query.setBinary(pos, CryptoUtil.hashForSearch(value.getFloatValue()));
			break;
		case LONG:
			query.setBigInteger(pos, new BigInteger(value.getLongValue().toString()));
			break;
		case LONG_HASH:
			query.setBinary(pos, CryptoUtil.hashForSearch(value.getLongValue()));
			break;
		case STRING:
			query.setString(pos, value.getStringValue());
			break;
		case STRING_HASH:
			query.setBinary(pos, CryptoUtil.hashForSearch(value.getStringValue()));
			break;
		case TIMESTAMP:
			query.setTimestamp(pos, value.getTimestampValue());
			break;
		case TIMESTAMP_HASH:
			query.setBinary(pos, CryptoUtil.hashForSearch(value.getTimestampValue()));
			break;
		case NONE:
			break;
		default:
			// datatype unimplemented
			throw new IllegalArgumentException(MessageFormat.format(CommonUtil.UNSUPPORTED_CRITERION_VALUE_TYPE, valueType.toString()));
	}
}
 

@Override
public SbiAudit getLastExecution(Integer objId) throws EMFUserError {
	logger.debug("IN");
	Session aSession = null;
	Transaction tx = null;
	SbiAudit toReturn = new SbiAudit();
	if (objId == null) {
		logger.warn("The object id in input is null or empty.");
		return toReturn;
	}
	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		StringBuffer hql = new StringBuffer();
		hql.append("select ");
		hql.append("		max(a.executionStartTime)");
		hql.append("from ");
		hql.append("		SbiAudit a ");
		hql.append("where 	");
		hql.append("		a.sbiObject is not null and ");
		hql.append("		a.sbiObject.biobjId = ? ");
		Query hqlQuery = aSession.createQuery(hql.toString());
		hqlQuery.setInteger(0, objId.intValue());
		Timestamp date = (Timestamp) hqlQuery.uniqueResult();
		toReturn.setDocumentId(objId);
		toReturn.setExecutionStartTime(date);

		StringBuffer hql2 = new StringBuffer();
		hql2.append("select ");
		hql2.append("		a.userName, ");
		hql2.append("		a.documentParameters, ");
		hql2.append("		a.requestTime, ");
		hql2.append("		a.executionEndTime, ");
		hql2.append("		a.executionState ");
		hql2.append("from ");
		hql2.append("		SbiAudit a ");
		hql2.append("where 	");
		hql2.append("		a.sbiObject is not null and ");
		hql2.append("		a.sbiObject.biobjId = ? and ");
		hql2.append("		a.executionStartTime = ? ");
		Query hqlQuery2 = aSession.createQuery(hql2.toString());
		hqlQuery2.setInteger(0, objId.intValue());
		hqlQuery2.setTimestamp(1, date);
		Object[] row = (Object[]) hqlQuery2.uniqueResult();

		toReturn.setUserName((String) row[0]);
		toReturn.setDocumentParameters((String) row[1]);
		toReturn.setRequestTime((Timestamp) row[2]);
		toReturn.setExecutionEndTime((Timestamp) row[3]);
		toReturn.setExecutionState((String) row[4]);

	} catch (Exception ex) {
		logger.error(ex);
		if (tx != null)
			tx.rollback();
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
		logger.debug("OUT");
	}
	return toReturn;
}
 

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

/**
 * Allows batch retrieval of Match data from database. This is likely the
 * best way to read in large amounts of data.
 * 
 * @param projectId
 * @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
 * @param maxResults
 * @return
 */
public static List<Match> getMatchesFromDb(
		String projectId, Date beginTime, Date endTime, 
		String sqlClause,
		final int firstResult, final int maxResults) {
	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 Match " +
			"    WHERE avlTime >= :beginDate " +
			"      AND avlTime < :endDate";
	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
	query.setFirstResult(firstResult);
	query.setMaxResults(maxResults);
	
	try {
		@SuppressWarnings("unchecked")
		List<Match> matches = query.list();
		logger.debug("Getting matches from database took {} msec",
				timer.elapsedMsec());
		return matches;
	} 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();
	}
}
 

/**
 * 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.
 * 
 * @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 List<ArrivalDeparture> or null if there is an exception
 */
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();
	}
	
}