Example usage for org.hibernate Query setTimestamp

List of usage examples for org.hibernate Query setTimestamp

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. 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.opencustomer.db.dao.crm.ContactDAO.java

License:Mozilla Public License

public long countListForCompany(Integer companyId, String subject, String contactType, String boundType,
        Date contactTimestampStart, Date contactTimestampEnd, String name, UserVO user) {
    String _subject = toLower(adjustWildcards(subject));
    String _name = toLower(adjustWildcards(name));

    long count = 0;

    try {/*from  w w  w . j av  a2s .  c o  m*/
        StringBuilder hql = new StringBuilder();

        hql.append(" select count(distinct e.id) ");
        hql.append(" FROM ").append(getEntityClass().getName()).append(" e ");
        hql.append(" left join e.personContacts pc ");
        hql.append(" left join pc.person p ");
        hql.append(" left join e.company c ");
        hql.append(" WHERE (c.id = :companyId ");
        hql.append(" OR pc.company.id = :companyId) ");
        if (subject != null)
            hql.append(" AND lower(e.subject) like :subject ");
        if (contactType != null)
            hql.append(" AND lower(e.contactType) like :contactType ");
        if (boundType != null)
            hql.append(" AND lower(e.boundType) like :boundType ");
        if (contactTimestampStart != null)
            hql.append(" AND e.contactTimestamp >= :contactTimestampStart ");
        if (contactTimestampEnd != null)
            hql.append(" AND e.contactTimestamp <= :contactTimestampEnd ");
        if (name != null) {
            hql.append(" AND (lower(p.firstName) like :name ");
            hql.append(" OR lower(p.lastName) like :name ");
            hql.append(" OR lower(c.companyName) like :name ");
            hql.append(" ) ");
        }
        if (user != null) {
            hql.append(" AND (e.accessGlobal != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" OR (e.accessGroup != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND exists (from ").append(UserVO.class.getName())
                    .append(" as u where u.id = :userId and u.profile.usergroups.id = e.ownerGroup)) ");
            hql.append(" OR (e.accessUser != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND e.ownerUser = :userId)) ");
        }

        Query query = HibernateContext.getSession().createQuery(hql.toString());
        if (companyId != null)
            query.setInteger("companyId", companyId);
        if (subject != null)
            query.setString("subject", _subject);
        if (contactType != null)
            query.setString("contactType", contactType);
        if (boundType != null)
            query.setString("boundType", boundType);
        if (contactTimestampStart != null)
            query.setTimestamp("contactTimestampStart", contactTimestampStart);
        if (contactTimestampEnd != null)
            query.setTimestamp("contactTimestampEnd", contactTimestampEnd);
        if (name != null)
            query.setString("name", _name);
        if (user != null)
            query.setInteger("userId", user.getId());

        count = (Long) query.uniqueResult();

        if (log.isDebugEnabled())
            log.debug("count " + count + " contacts");
    } catch (HibernateException e) {
        log.error("Could not find contacts", e);
        throw e;
    }

    return count;
}

From source file:org.opencustomer.db.dao.crm.custom.JobListDAO.java

License:Mozilla Public License

public List<JobListVO> getList(CompanyVO company, PersonVO person, JobVO job, String subject, Status[] status,
        Priority priority, Date dateStart, Date dateEnd, boolean assignedUser, UserVO activeUser, Sort sort,
        Page page, UserVO user) {//from  ww  w .  ja  v a2s.  c  o  m
    String _subject = toLower(adjustWildcards(subject));

    List<JobListVO> list = new ArrayList<JobListVO>();

    try {
        StringBuilder hql = new StringBuilder();

        hql.append(
                " SELECT e.id, e.dueDate, e.status, e.priority, e.subject, u.userName, p.firstName, p.lastName, c.companyName ");
        hql.append(" FROM ").append(getEntityClass().getName()).append(" e ");
        hql.append(" left join e.referencedCompany c ");
        hql.append(" left join e.referencedPerson p ");
        hql.append(" left join e.assignedUser u ");
        hql.append(" WHERE 1=1 ");
        if (company != null)
            hql.append(" AND e.referencedCompany = :company ");
        if (person != null)
            hql.append(" AND e.referencedPerson = :person ");
        if (job != null)
            hql.append(" AND e.parentJob = :job ");
        if (subject != null)
            hql.append(" AND lower(e.subject) like :subject ");
        if (status != null && status.length > 0)
            hql.append(" AND e.status in (:status) ");
        if (priority != null)
            hql.append(" AND e.priority = :priority ");
        if (activeUser != null) {
            if (!assignedUser)
                hql.append(" AND (e.assignedUser = :activeUser or e.ownerUser = :activeUserId) ");
            else
                hql.append(" AND e.assignedUser = :activeUser ");
        }
        if (dateStart != null)
            hql.append(" AND e.dueDate >= :dateStart ");
        if (dateEnd != null)
            hql.append(" AND e.dueDate <= :dateEnd ");
        if (user != null) {
            hql.append(" AND (e.accessGlobal != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" OR (e.accessGroup != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND exists (from ").append(UserVO.class.getName())
                    .append(" as u where u.id = :userId and u.profile.usergroups.id = e.ownerGroup)) ");
            hql.append(" OR (e.accessUser != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND e.ownerUser = :userId)) ");
        }

        if (sort != null)
            hql.append(" order by " + getSortString(sort));

        Query query = HibernateContext.getSession().createQuery(hql.toString());
        if (company != null)
            query.setEntity("company", company);
        if (person != null)
            query.setEntity("person", person);
        if (job != null)
            query.setEntity("job", job);
        if (subject != null)
            query.setString("subject", _subject);
        if (status != null)
            query.setParameterList("status", status);
        if (priority != null)
            query.setString("priority", priority.toString());
        if (activeUser != null) {
            query.setEntity("activeUser", activeUser);
            if (!assignedUser)
                query.setInteger("activeUserId", activeUser.getId());
        }
        if (dateStart != null)
            query.setTimestamp("dateStart", dateStart);
        if (dateEnd != null)
            query.setTimestamp("dateEnd", dateEnd);
        if (user != null)
            query.setInteger("userId", user.getId());

        if (page != null) {
            query.setFirstResult(getFirstResult(page));
            query.setMaxResults(getMaxResults(page));
        }

        Iterator it = query.list().iterator();
        while (it.hasNext())
            addEntity(list, (Object[]) it.next());

        if (log.isDebugEnabled())
            log.debug("found " + list.size() + " jobs");
    } catch (HibernateException e) {
        log.error("Could not find jobs", e);
        throw e;
    }

    return list;
}

From source file:org.opencustomer.db.dao.crm.custom.JobListDAO.java

License:Mozilla Public License

public long countList(CompanyVO company, PersonVO person, JobVO job, String subject, Status[] status,
        Priority priority, Date dateStart, Date dateEnd, boolean assignedUser, UserVO activeUser, UserVO user) {

    String _subject = toLower(adjustWildcards(subject));

    long count = 0;

    try {/* w w  w  .  j a va2s .c o m*/
        StringBuilder hql = new StringBuilder();
        hql.append(" select count(e.id) ");
        hql.append(" FROM ").append(getEntityClass().getName()).append(" e ");
        hql.append(" WHERE 1=1 ");
        if (company != null)
            hql.append(" AND e.referencedCompany = :company ");
        if (person != null)
            hql.append(" AND e.referencedPerson = :person ");
        if (job != null)
            hql.append(" AND e.parentJob = :job ");
        if (subject != null)
            hql.append(" AND lower(e.subject) like :subject ");
        if (status != null && status.length > 0)
            hql.append(" AND e.status in (:status) ");
        if (priority != null)
            hql.append(" AND e.priority = :priority ");
        if (activeUser != null) {
            if (!assignedUser)
                hql.append(" AND (e.assignedUser = :activeUser or e.ownerUser = :activeUserId) ");
            else
                hql.append(" AND e.assignedUser = :activeUser ");
        }
        if (dateStart != null)
            hql.append(" AND e.dueDate >= :dateStart ");
        if (dateEnd != null)
            hql.append(" AND e.dueDate <= :dateEnd ");
        if (user != null) {
            hql.append(" AND (e.accessGlobal != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" OR (e.accessGroup != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND exists (from ").append(UserVO.class.getName())
                    .append(" as u where u.id = :userId and u.profile.usergroups.id = e.ownerGroup)) ");
            hql.append(" OR (e.accessUser != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND e.ownerUser = :userId)) ");
        }

        Query query = HibernateContext.getSession().createQuery(hql.toString());
        if (company != null)
            query.setEntity("company", company);
        if (person != null)
            query.setEntity("person", person);
        if (job != null)
            query.setEntity("job", job);
        if (subject != null)
            query.setString("subject", _subject);
        if (status != null)
            query.setParameterList("status", status);
        if (priority != null)
            query.setString("priority", priority.toString());
        if (activeUser != null) {
            query.setEntity("activeUser", activeUser);
            if (!assignedUser)
                query.setInteger("activeUserId", activeUser.getId());
        }
        if (dateStart != null)
            query.setTimestamp("dateStart", dateStart);
        if (dateEnd != null)
            query.setTimestamp("dateEnd", dateEnd);
        if (user != null)
            query.setInteger("userId", user.getId());

        count = (Long) query.uniqueResult();

        if (log.isDebugEnabled())
            log.debug("count " + count + " jobs");
    } catch (HibernateException e) {
        log.error("Could not find jobs", e);
        throw e;
    }

    return count;
}

From source file:org.opencustomer.db.dao.crm.JobDAO.java

License:Mozilla Public License

/**
 * Searches for JobVO at the given time period.
 * @param user the user//w w w .j  a  v a2s . co  m
 * @param startDate the start date of the time period
 * @param endDate the end date if the time period
 * @return a List of JobVO found for the given time period
 * @throws HibernateException if there is an error on the Hibernate connection
 * @author fbreske
 */
public List<JobVO> getByUser(UserVO user, Date startDate, Date endDate) throws HibernateException {

    List<JobVO> l = null;
    try {
        StringBuilder hql = new StringBuilder();
        hql.append(" FROM ").append(getEntityClass().getName()).append(" e ");
        hql.append(" WHERE e.assignedUser = :assignedUser ");
        hql.append(" AND e.status != '" + JobVO.Status.COMPLETED + "' ");

        if (startDate != null && endDate != null) {
            hql.append(" AND (e.dueDate BETWEEN :startDate AND :endDate) ");
        }

        Query query = HibernateContext.getSession().createQuery(hql.toString());
        query.setEntity("assignedUser", user);
        if (startDate != null && endDate != null) {
            query.setTimestamp("startDate", startDate);
            query.setTimestamp("endDate", endDate);
        }

        l = toTypeSafeList(query.list());
    } catch (HibernateException e) {
        log.error("error loading jobs", e);
        throw e;
    }
    return l;
}

From source file:org.openhie.openempi.dao.hibernate.AuditEventDaoHibernate.java

License:Open Source License

private Query buildFilterQuery(Session session, final Date startDate, final Date endDate,
        final List<Integer> auditEventTypeCodes, String prefix, String postfix) {
    StringBuffer query = new StringBuffer(prefix);
    if (startDate != null) {
        query.append(" and dateCreated >= :startDate");
    }/*from w  w  w  .  j  av  a 2  s.  com*/
    if (endDate != null) {
        query.append(" and dateCreated <= :endDate");
    }
    if (auditEventTypeCodes != null && auditEventTypeCodes.size() > 0) {
        query.append(" and auditEventType.auditEventTypeCd IN (:codes)");
    }
    query.append(postfix);
    Query q = session.createQuery(query.toString());
    if (startDate != null) {
        q.setTimestamp("startDate", startDate);
    }
    if (endDate != null) {
        q.setTimestamp("endDate", endDate);
    }
    if (auditEventTypeCodes != null && auditEventTypeCodes.size() > 0) {
        //         StringBuffer sb = new StringBuffer();
        //         for (int i=0; i < auditEventTypeCodes.size(); i++) {
        //            sb.append(auditEventTypeCodes.get(i));
        //            if (i < auditEventTypeCodes.size()-1) {
        //               sb.append(",");
        //            }
        //         }
        q.setParameterList("codes", auditEventTypeCodes);
    }
    return q;
}

From source file:org.openhie.openempi.dao.hibernate.MessageLogDaoHibernate.java

License:Open Source License

private Query buildFilterQuery(Session session, final Date startDate, final Date endDate,
        final List<Integer> messageTypeCodes, String prefix, String postfix) {
    StringBuffer query = new StringBuffer(prefix);
    if (startDate != null) {
        query.append(" and dateReceived >= :startDate");
    }/*w  w w.j  a va  2 s  .  c  o  m*/
    if (endDate != null) {
        query.append(" and dateReceived <= :endDate");
    }
    if (messageTypeCodes != null && messageTypeCodes.size() > 0) {
        query.append(" and incomingMessageType.messageTypeCd IN (:codes)");
    }
    query.append(postfix);
    Query q = session.createQuery(query.toString());
    if (startDate != null) {
        q.setTimestamp("startDate", startDate);
    }
    if (endDate != null) {
        q.setTimestamp("endDate", endDate);
    }
    if (messageTypeCodes != null && messageTypeCodes.size() > 0) {
        q.setParameterList("codes", messageTypeCodes);
    }
    return q;
}

From source file:org.openmrs.module.idcards.db.hibernate.HibernateIdcardsDAO.java

License:Open Source License

/**
 * @see org.openmrs.module.idcards.db.IdcardsDAO#printGeneratedIdentifiers(int,IdcardsTemplate)
 *///from w ww.  ja v  a2s .c  om
public List<Integer> printGeneratedIdentifiers(int quantityToPrint, IdcardsTemplate template)
        throws DAOException {
    // find the identifiers to return and mark as printed
    String sql = "select id from GeneratedIdentifier where printed = 0 order by RAND()";
    Query query = sessionFactory.getCurrentSession().createQuery(sql);
    query.setMaxResults(quantityToPrint); // this is the "limit = quantityToPrint" for sql

    List<Integer> printedIdentifiers = (List<Integer>) query.list();

    // mark the identifiers as printed
    sql = "update GeneratedIdentifier set printed = 1, printedBy = :printedBy, datePrinted = :date, printedTemplate = :template where id in (:identifiers)";
    query = sessionFactory.getCurrentSession().createQuery(sql);
    query.setParameter("printedBy", Context.getAuthenticatedUser());
    query.setTimestamp("date", new Date());
    query.setParameter("template", template);
    query.setParameterList("identifiers", printedIdentifiers);
    query.executeUpdate();

    return printedIdentifiers;
}

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#getPatientsInProgram(java.util.List, java.util.Date, java.util.Date)
  *///from  w  w w.ja  v a  2  s.  c o m
public Cohort getPatientsInProgram(List<Program> programs, Date onOrAfter, Date onOrBefore) {
    List<Integer> programIds = new ArrayList<Integer>();
    for (Program program : programs)
        programIds.add(program.getProgramId());

    // Create SQL query
    StringBuilder sql = new StringBuilder();
    sql.append("select pp.patient_id ");
    sql.append("from patient_program pp ");
    sql.append("  inner join patient p on pp.patient_id = p.patient_id ");
    sql.append("where pp.voided = false and p.voided = false ");

    // optional clauses
    if (programIds != null && !programIds.isEmpty())
        sql.append(" and pp.program_id in (:programIds) ");
    if (onOrAfter != null)
        sql.append(" and (pp.date_completed is null or pp.date_completed >= :onOrAfter) ");
    if (onOrBefore != null)
        sql.append(" and (pp.date_enrolled is null or pp.date_enrolled <= :onOrBefore) ");

    sql.append(" group by pp.patient_id ");
    log.debug("query: " + sql);

    // Execute query
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());
    if (programIds != null && !programIds.isEmpty())
        query.setParameterList("programIds", programIds);
    if (onOrAfter != null)
        query.setTimestamp("onOrAfter", onOrAfter);
    if (onOrBefore != null)
        query.setTimestamp("onOrBefore", DateUtil.getEndOfDayIfTimeExcluded(onOrBefore));
    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#getPatientsHavingStates(java.util.List, java.util.Date, java.util.Date, java.util.Date, java.util.Date)
 *///  w w  w .j  a  va  2  s  .co  m
public Cohort getPatientsHavingStates(List<ProgramWorkflowState> states, Date startedOnOrAfter,
        Date startedOnOrBefore, Date endedOnOrAfter, Date endedOnOrBefore) {
    List<Integer> stateIds = new ArrayList<Integer>();
    for (ProgramWorkflowState state : states)
        stateIds.add(state.getId());

    // Create SQL query
    StringBuilder sql = new StringBuilder();
    sql.append("select pp.patient_id ");
    sql.append("from patient_state ps ");
    sql.append("  inner join patient_program pp on ps.patient_program_id = pp.patient_program_id ");
    sql.append("  inner join patient p on pp.patient_id = p.patient_id ");
    sql.append("where ps.voided = false and pp.voided = false and p.voided = false ");

    // Create a list of clauses
    if (stateIds != null && !stateIds.isEmpty())
        sql.append(" and ps.state in (:stateIds) ");
    if (startedOnOrAfter != null)
        sql.append(" and ps.start_date >= :startedOnOrAfter ");
    if (startedOnOrBefore != null)
        sql.append(" and ps.start_date <= :startedOnOrBefore ");
    if (endedOnOrAfter != null)
        sql.append(" and ps.end_date >= :endedOnOrAfter ");
    if (endedOnOrBefore != null)
        sql.append(" and ps.end_date <= :endedOnOrBefore ");

    sql.append(" group by pp.patient_id ");
    log.debug("query: " + sql);

    // Execute query
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());

    if (stateIds != null && !stateIds.isEmpty())
        query.setParameterList("stateIds", stateIds);
    if (startedOnOrAfter != null)
        query.setTimestamp("startedOnOrAfter", startedOnOrAfter);
    if (startedOnOrBefore != null)
        query.setTimestamp("startedOnOrBefore", DateUtil.getEndOfDayIfTimeExcluded(startedOnOrBefore));
    if (endedOnOrAfter != null)
        query.setTimestamp("endedOnOrAfter", endedOnOrAfter);
    if (endedOnOrBefore != null)
        query.setTimestamp("endedOnOrBefore", DateUtil.getEndOfDayIfTimeExcluded(endedOnOrBefore));

    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#getPatientsInStates(java.util.List, java.util.Date, java.util.Date)
 *//*from w ww .j av a 2s.c  o  m*/
public Cohort getPatientsInStates(List<ProgramWorkflowState> states, Date onOrAfter, Date onOrBefore) {
    List<Integer> stateIds = new ArrayList<Integer>();
    for (ProgramWorkflowState state : states)
        stateIds.add(state.getId());

    // Create SQL query
    StringBuilder sql = new StringBuilder();
    sql.append("select pp.patient_id ");
    sql.append("from patient_state ps ");
    sql.append("  inner join patient_program pp on ps.patient_program_id = pp.patient_program_id ");
    sql.append("  inner join patient p on pp.patient_id = p.patient_id ");
    sql.append("where ps.voided = false and pp.voided = false and p.voided = false ");

    // optional clauses
    if (stateIds != null && !stateIds.isEmpty())
        sql.append(" and ps.state in (:stateIds) ");
    if (onOrAfter != null)
        sql.append(" and (ps.end_date is null or ps.end_date >= :onOrAfter) ");
    if (onOrBefore != null)
        sql.append(" and (ps.start_date is null or ps.start_date <= :onOrBefore) ");

    sql.append(" group by pp.patient_id ");
    log.debug("query: " + sql);

    // Execute query
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());
    if (stateIds != null && !stateIds.isEmpty())
        query.setParameterList("stateIds", stateIds);
    if (onOrAfter != null)
        query.setTimestamp("onOrAfter", onOrAfter);
    if (onOrBefore != null)
        query.setTimestamp("onOrBefore", DateUtil.getEndOfDayIfTimeExcluded(onOrBefore));
    return new Cohort(query.list());
}