List of usage examples for org.hibernate Query setTimestamp
@Deprecated @SuppressWarnings("unchecked") default Query<R> setTimestamp(String name, Date value)
From source file:org.jbpm.pvm.internal.hibernate.DbSessionImpl.java
License:Open Source License
public JobImpl<?> findFirstAcquirableJob() { Query query = session.getNamedQuery("findFirstAcquirableJob"); query.setTimestamp("now", Clock.getCurrentTime()); query.setMaxResults(1);/* w ww . j a v a 2 s .c o m*/ //query.setLockMode("job", LockMode.READ); query.setLockMode("job", LockMode.NONE); return (JobImpl<?>) query.uniqueResult(); }
From source file:org.jbpm.pvm.internal.hibernate.DbSessionImpl.java
License:Open Source License
public List<JobImpl<?>> findExclusiveJobs(Execution processInstance) { Query query = session.getNamedQuery("findExclusiveJobs"); query.setTimestamp("now", Clock.getCurrentTime()); query.setLockMode("job", LockMode.NONE); query.setEntity("processInstance", processInstance); return query.list(); }
From source file:org.onebusaway.users.impl.UserDaoImpl.java
License:Apache License
@Override public List<Integer> getStaleUserIdsInRange(final Date lastAccessTime, final int firstResult, final int maxResults) { return _template.execute(new HibernateCallback<List<Integer>>() { @SuppressWarnings("unchecked") @Override/*from www. ja v a 2 s. c o m*/ public List<Integer> doInHibernate(Session session) throws HibernateException, SQLException { Query query = session .createQuery("SELECT user.id FROM User user WHERE lastAccessTime < :lastAccessTime"); query.setFirstResult(firstResult); query.setMaxResults(maxResults); query.setTimestamp("lastAccessTime", lastAccessTime); return query.list(); } }); }
From source file:org.openbravo.costing.CostingRuleProcess.java
License:Open Source License
private ScrollableResults getStockLines(Set<String> childOrgs, Date date) { StringBuffer select = new StringBuffer(); select.append("select trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); select.append(", loc." + Locator.PROPERTY_WAREHOUSE + ".id"); select.append(", sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ")"); select.append(", sum(trx." + MaterialTransaction.PROPERTY_ORDERQUANTITY + ")"); select.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as loc"); select.append(" where trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); if (date != null) { select.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " < :date"); }// ww w. j ava 2 s . c om select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + ".productType = 'I'"); select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + ".stocked = true"); select.append(" group by trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); select.append(", loc." + Locator.PROPERTY_WAREHOUSE + ".id"); select.append(" having "); select.append(" sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") <> 0"); select.append(" or sum(trx." + MaterialTransaction.PROPERTY_ORDERQUANTITY + ") <> 0"); select.append(" order by loc." + Locator.PROPERTY_WAREHOUSE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); Query stockLinesQry = OBDal.getInstance().getSession().createQuery(select.toString()); stockLinesQry.setParameterList("orgs", childOrgs); if (date != null) { stockLinesQry.setTimestamp("date", date); } stockLinesQry.setFetchSize(1000); ScrollableResults stockLines = stockLinesQry.scroll(ScrollMode.FORWARD_ONLY); return stockLines; }
From source file:org.openbravo.costing.FixBackdatedTransactionsProcess.java
License:Open Source License
private ScrollableResults getTransactions(Set<String> childOrgs, Date startDate, Date endDate) { StringBuffer select = new StringBuffer(); select.append("select trx as trx"); select.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); select.append(" where trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); select.append(" and trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = true"); select.append(" and trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " >= (:startDate)"); if (endDate != null) { select.append(" and trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " < (:endDate)"); }// w w w .j av a 2 s. c om select.append(" order by trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE); Query stockLinesQry = OBDal.getInstance().getSession().createQuery(select.toString()); stockLinesQry.setParameterList("orgs", childOrgs); stockLinesQry.setTimestamp("startDate", startDate); if (endDate != null) { stockLinesQry.setTimestamp("endDate", endDate); } stockLinesQry.setFetchSize(1000); ScrollableResults stockLines = stockLinesQry.scroll(ScrollMode.FORWARD_ONLY); return stockLines; }
From source file:org.openbravo.costing.InventoryAmountUpdateProcess.java
License:Open Source License
private ScrollableResults getStockLines(Set<String> childOrgs, Date date, Product product, Warehouse warehouse, boolean backdatedTransactionsFixed) { StringBuffer select = new StringBuffer(); StringBuffer subSelect = new StringBuffer(); select.append("select trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); select.append(", loc." + Locator.PROPERTY_WAREHOUSE + ".id"); select.append(", sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ")"); select.append(", sum(trx." + MaterialTransaction.PROPERTY_ORDERQUANTITY + ")"); select.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); select.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as loc"); select.append(" where trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); if (date != null) { if (backdatedTransactionsFixed) { select.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <= :date"); } else {//from w w w .j av a2 s. com subSelect.append("select min(trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + ")"); subSelect.append(" from " + MaterialTransaction.ENTITY_NAME + " as trx"); subSelect.append(" join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator"); subSelect.append(" where trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id = :product"); subSelect.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " > :date"); // Include only transactions that have its cost calculated subSelect.append(" and trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = true"); if (warehouse != null) { subSelect.append(" and locator." + Locator.PROPERTY_WAREHOUSE + ".id = :warehouse"); } subSelect.append(" and trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)"); Query trxsubQry = OBDal.getInstance().getSession().createQuery(subSelect.toString()); trxsubQry.setParameter("date", date); trxsubQry.setParameter("product", product.getId()); if (warehouse != null) { trxsubQry.setParameter("warehouse", warehouse.getId()); } trxsubQry.setParameterList("orgs", childOrgs); Object trxprocessDate = trxsubQry.uniqueResult(); if (trxprocessDate != null) { date = (Date) trxprocessDate; select.append(" and trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " < :date"); } else { select.append(" and trx." + MaterialTransaction.PROPERTY_MOVEMENTDATE + " <= :date"); } } } if (warehouse != null) { select.append(" and loc." + Locator.PROPERTY_WAREHOUSE + " = :warehouse"); } select.append(" and trx." + MaterialTransaction.PROPERTY_PRODUCT + " = :product"); select.append(" group by trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); select.append(", loc." + Locator.PROPERTY_WAREHOUSE + ".id"); select.append(" having "); select.append(" sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") <> 0"); select.append(" order by loc." + Locator.PROPERTY_WAREHOUSE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_STORAGEBIN + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_UOM + ".id"); select.append(", trx." + MaterialTransaction.PROPERTY_ORDERUOM + ".id"); Query stockLinesQry = OBDal.getInstance().getSession().createQuery(select.toString()); stockLinesQry.setParameterList("orgs", childOrgs); if (date != null) { stockLinesQry.setTimestamp("date", date); } if (warehouse != null) { stockLinesQry.setParameter("warehouse", warehouse); } stockLinesQry.setParameter("product", product); stockLinesQry.setFetchSize(1000); ScrollableResults stockLines = stockLinesQry.scroll(ScrollMode.FORWARD_ONLY); return stockLines; }
From source file:org.opencustomer.db.dao.calendar.EventDAO.java
License:Mozilla Public License
public List<EventVO> getByTimePeriod(CalendarVO calendar, Date periodStart, Date periodEnd, UserVO user, boolean withReminderDate) { List<EventVO> events = new ArrayList<EventVO>(); try {//w w w.j a va2 s. c o m StringBuilder hql = new StringBuilder(); hql.append(" select e "); hql.append(" FROM ").append(getEntityClass().getName()).append(" e "); hql.append(" left join e.eventCalendars ec "); hql.append(" WHERE ec.calendar.id = :calendarId "); hql.append(" AND ec.invitationStatus <> '").append(EventCalendarVO.InvitationStatus.DELETED) .append("' "); hql.append(" AND (((e.startDate BETWEEN :startDate AND :endDate "); hql.append(" OR e.endDate BETWEEN :startDate AND :endDate "); hql.append(" OR (e.startDate < :startDate AND e.endDate > :endDate)) "); hql.append(" AND e.recurrenceType = '" + EventVO.RecurrenceType.NONE + "') "); if (withReminderDate) hql.append(" OR (e.reminderDate BETWEEN :startDate AND :endDate )"); hql.append(" OR ((e.recurrenceStartDate BETWEEN :startDate AND :endDate "); hql.append(" OR e.recurrenceEndDate BETWEEN :startDate AND :endDate "); hql.append(" OR (e.recurrenceStartDate < :startDate AND e.recurrenceEndDate > :endDate)) "); hql.append(" AND e.recurrenceType <> '" + EventVO.RecurrenceType.NONE + "') "); hql.append(" OR (e.recurrenceStartDate < :endDate AND e.recurrenceEndDate is null "); hql.append(" AND e.recurrenceType <> '" + EventVO.RecurrenceType.NONE + "')) "); if (user != null) { hql.append(" AND (e.occupied = 1 "); hql.append(" OR (ec.accessGlobal != '" + EntityAccess.Access.NONE + "' "); hql.append(" OR (ec.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 = ec.ownerGroup)) "); hql.append(" OR (ec.accessUser != '" + EntityAccess.Access.NONE + "' "); hql.append(" AND ec.ownerUser = :userId)) "); hql.append(" ) "); } hql.append(" ORDER BY e.startDate, e.endDate"); Query query = HibernateContext.getSession().createQuery(hql.toString()); query.setInteger("calendarId", calendar.getId()); query.setTimestamp("startDate", periodStart); query.setTimestamp("endDate", periodEnd); if (user != null) query.setInteger("userId", user.getId()); events = toTypeSafeList(query.list()); if (log.isDebugEnabled()) log.debug("found " + events.size() + " events in time period from " + periodStart + " to " + periodEnd); } catch (HibernateException e) { log.error("Could not read events in time period from " + periodStart + " to " + periodEnd, e); throw e; } return events; }
From source file:org.opencustomer.db.dao.crm.ContactDAO.java
License:Mozilla Public License
public List<ContactVO> getListForPerson(Integer personId, String subject, String contactType, String boundType, Date contactTimestampStart, Date contactTimestampEnd, String name, Sort sort, Page page, UserVO user) { String _subject = toLower(adjustWildcards(subject)); String _name = toLower(adjustWildcards(name)); List<ContactVO> list = new ArrayList<ContactVO>(); try {/*from w w w .j a va 2s . co m*/ StringBuilder hql = new StringBuilder(); hql.append(" select distinct e "); 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 e in ( "); hql.append(" select e1 "); hql.append(" from ").append(getEntityClass().getName()).append(" e1 "); hql.append(" inner join e1.personContacts pc1 "); hql.append(" inner join pc1.person p1 "); hql.append(" where p1.id = :personId "); hql.append(" ) "); 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)) "); } if (sort != null) hql.append(" order by " + getSortString(sort)); Query query = HibernateContext.getSession().createQuery(hql.toString()); if (personId != null) query.setInteger("personId", personId); 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()); if (page != null) { query.setFirstResult(getFirstResult(page)); query.setMaxResults(getMaxResults(page)); } list = toTypeSafeList(query.list()); if (log.isDebugEnabled()) log.debug("found " + list.size() + " contacts"); } catch (HibernateException e) { log.error("Could not find contacts", e); throw e; } return list; }
From source file:org.opencustomer.db.dao.crm.ContactDAO.java
License:Mozilla Public License
public long countListForPerson(Integer personId, 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 a va 2s . 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 e in ( "); hql.append(" select e1 "); hql.append(" from ").append(getEntityClass().getName()).append(" e1 "); hql.append(" inner join e1.personContacts pc1 "); hql.append(" inner join pc1.person p1 "); hql.append(" where p1.id = :personId "); hql.append(" ) "); 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 (personId != null) query.setInteger("personId", personId); 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.ContactDAO.java
License:Mozilla Public License
public List<ContactVO> getListForCompany(Integer companyId, String subject, String contactType, String boundType, Date contactTimestampStart, Date contactTimestampEnd, String name, Sort sort, Page page, UserVO user) {//www. j a va 2 s . c o m String _subject = toLower(adjustWildcards(subject)); String _name = toLower(adjustWildcards(name)); List<ContactVO> list = new ArrayList<ContactVO>(); try { StringBuilder hql = new StringBuilder(); hql.append(" select distinct e "); 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)) "); } if (sort != null) hql.append(" order by " + getSortString(sort)); 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()); if (page != null) { query.setFirstResult(getFirstResult(page)); query.setMaxResults(getMaxResults(page)); } list = toTypeSafeList(query.list()); if (log.isDebugEnabled()) log.debug("found " + list.size() + " contacts"); } catch (HibernateException e) { log.error("Could not find contacts", e); throw e; } return list; }