List of usage examples for org.hibernate.criterion Subqueries notExists
public static Criterion notExists(DetachedCriteria dc)
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<ConsentDetailsDataRow> getStudyCompConsentList(ConsentDetailsReportVO cdrVO) { // NB: There should only ever be one Consent record for a particular Subject for a particular StudyComp List<ConsentDetailsDataRow> results = new ArrayList<ConsentDetailsDataRow>(); Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class, "lss"); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("lss." + "subjectUID"), "subjectUID"); criteria.add(/* w w w.j av a 2 s. c om*/ Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_STUDY, cdrVO.getLinkSubjectStudy().getStudy())); if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) { criteria.add(Restrictions.ilike("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTUID, cdrVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE)); } if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) { criteria.add(Restrictions.eq("lss." + Constants.LINKSUBJECTSTUDY_SUBJECTSTATUS, cdrVO.getLinkSubjectStudy().getSubjectStatus())); } if (cdrVO.getConsentDate() != null) { // NB: constraint on consentDate or consentStatus automatically removes "Not Consented" state // So LinkSubjectStudy inner join to Consent ok for populated consentDate criteria.createAlias("lss." + Constants.LINKSUBJECTSTUDY_CONSENT, "c"); criteria.createAlias("c." + Constants.CONSENT_CONSENTSTATUS, "cs"); // constrain on studyComp criteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp())); // constrain on consentDate criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTDATE, cdrVO.getConsentDate())); // ConsentStatus is optional for this query... if (cdrVO.getConsentStatus() != null) { criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus())); } projectionList.add(Projections.property("cs.name"), "consentStatus"); projectionList.add(Projections.property("c." + Constants.CONSENT_CONSENTDATE), "consentDate"); } else if (cdrVO.getConsentStatus() != null) { if (cdrVO.getConsentStatus().getName().equals(Constants.NOT_CONSENTED)) { // Need to handle "Not Consented" status differently (since it doesn't have a Consent record) // Helpful website: http://www.cereslogic.com/pages/2008/09/22/hibernate-criteria-subqueries-exists/ // Build subquery to find all Consent records for a Study Comp DetachedCriteria consentCriteria = DetachedCriteria.forClass(Consent.class, "c"); // Constrain on StudyComponent consentCriteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp())); // Just in case "Not Consented" is erroneously entered into a row in the Consent table // consentCriteria.add(Restrictions.ne("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus())); // Join LinkSubjectStudy and Consent on ID FK consentCriteria.add(Property.forName("c.linkSubjectStudy.id").eqProperty("lss." + "id")); criteria.add(Subqueries.notExists(consentCriteria.setProjection(Projections.property("c.id")))); // If Consent records follows design for "Not Consented", then: // - consentStatus and consentDate are not populated } else { // NB: constraint on consentDate or consentStatus automatically removes "Not Consented" state // So LinkSubjectStudy inner join to Consent ok for all recordable consentStatus criteria.createAlias("lss." + Constants.LINKSUBJECTSTUDY_CONSENT, "c"); criteria.createAlias("c." + Constants.CONSENT_CONSENTSTATUS, "cs"); // Constrain on StudyComponent criteria.add(Restrictions.eq("c." + Constants.CONSENT_STUDYCOMP, cdrVO.getStudyComp())); // ConsentStatus is NOT optional for this query! criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTSTATUS, cdrVO.getConsentStatus())); if (cdrVO.getConsentDate() != null) { criteria.add(Restrictions.eq("c." + Constants.CONSENT_CONSENTDATE, cdrVO.getConsentDate())); } projectionList.add(Projections.property("cs.name"), "consentStatus"); projectionList.add(Projections.property("c." + Constants.CONSENT_CONSENTDATE), "consentDate"); } } else { // Should not attempt to run this query with no consentDate nor consentStatus criteria provided log.error( "reportDao.getStudyCompConsentList(..) is missing consentDate or consentStatus parameters in the VO"); return null; } criteria.addOrder(Order.asc("lss." + "subjectUID")); criteria.setProjection(projectionList); criteria.setResultTransformer(Transformers.aliasToBean(ConsentDetailsDataRow.class)); // This gives a list of subjects matching the specific studyComp and consentStatus results = criteria.list(); return results; }
From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java
@Override public List<EmpData> getAllDataNotExistInUserByParam(String param, int firstResult, int maxResults, Order order) {// ww w. java2s. co m DetachedCriteria subQuery = DetachedCriteria.forClass(HrmUser.class, "user") .setProjection(Projections.property("user.id")); subQuery.add(Property.forName("employee.id").eqProperty("user.empData.id")); Criteria criteria = getCurrentSession().createCriteria(getEntityClass(), "employee"); criteria.add(Subqueries.notExists(subQuery)); criteria = this.doSearchNotExistInUserByParam(param, criteria); criteria.setFirstResult(firstResult); criteria.setMaxResults(maxResults); criteria.addOrder(order); return criteria.list(); }
From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java
@Override public Long getTotalNotExistInUserByParam(String param) { DetachedCriteria subQuery = DetachedCriteria.forClass(HrmUser.class, "user") .setProjection(Projections.property("user.id")); subQuery.add(Property.forName("employee.id").eqProperty("user.empData.id")); Criteria criteria = getCurrentSession().createCriteria(getEntityClass(), "employee"); criteria.add(Subqueries.notExists(subQuery)); criteria = this.doSearchNotExistInUserByParam(param, criteria); return (Long) criteria.setProjection(Projections.rowCount()).uniqueResult(); }
From source file:com.qcadoo.model.api.search.SearchSubqueries.java
License:Open Source License
/** * Creates criterion which checks if none row exists in given subquery. * //ww w . j a v a 2 s.c o m * @param criteria * subcriteria * @return criteria */ public static SearchCriterion notExists(final SearchCriteriaBuilder criteria) { return new SearchCriterionImpl( Subqueries.notExists(((SearchCriteria) criteria).getHibernateDetachedCriteria())); }
From source file:org.broadleafcommerce.cms.common.AbstractContentService.java
License:Apache License
private <T> void addSandboxCriteria(SandBox sandbox, Criteria c, Class<T> type, String originalIdProperty) { Criterion originalSandboxExpression = Restrictions.eq("originalSandBox", sandbox); Criterion currentSandboxExpression = Restrictions.eq("sandbox", sandbox); Criterion userSandboxExpression = Restrictions.or(currentSandboxExpression, originalSandboxExpression); Criterion productionSandboxExpression = null; if (sandbox.getSite() == null || sandbox.getSite().getProductionSandbox() == null) { productionSandboxExpression = Restrictions.isNull("sandbox"); } else {//w w w . j av a 2 s . c o m productionSandboxExpression = Restrictions.eq("sandbox", sandbox.getSite().getProductionSandbox()); } if (productionSandboxExpression != null) { c.add(Restrictions.or(userSandboxExpression, productionSandboxExpression)); } else { c.add(userSandboxExpression); } // Build a sub-query to exclude items from production that are also in my sandbox. // (e.g. my sandbox always wins even if the items in my sandbox don't match the // current criteria.) // // This subquery prevents the following: // 1. Duplicate items (one for sbox, one for prod) // 2. Filter issues where the production item qualifies for the passed in criteria // but has been modified so that the item in the sandbox no longer does. // 3. Inverse of #2. DetachedCriteria existsInSboxCriteria = DetachedCriteria.forClass(type, "sboxItem"); existsInSboxCriteria.add(userSandboxExpression); existsInSboxCriteria.add(Restrictions.eq("archivedFlag", false)); String outerAlias = c.getAlias(); existsInSboxCriteria.add(Property.forName(outerAlias + ".id").eqProperty("sboxItem." + originalIdProperty)); existsInSboxCriteria.setProjection(Projections.id()); c.add(Subqueries.notExists(existsInSboxCriteria)); }
From source file:org.candlepin.policy.criteria.CriteriaRules.java
License:Open Source License
/** * Create a List of JPA criterion that can filter out pools that are not * applicable to consumer. Helps to scale down large numbers of pools * specifically with virt_limit subscriptions. * * @param consumer The consumer we are filtering pools for * @return List of Criterion/* www. j a v a2s. c om*/ */ public List<Criterion> availableEntitlementCriteria(Consumer consumer) { // avoid passing in a consumerCurator just to get the host // consumer UUID Consumer hostConsumer = null; if (consumer.getFact("virt.uuid") != null) { hostConsumer = consumerCurator.getHost(consumer.getFact("virt.uuid"), consumer.getOwner()); } List<Criterion> criteriaFilters = new LinkedList<Criterion>(); // Don't load virt_only pools if this consumer isn't a guest // or a manifest consumer if (consumer.getType().isManifest()) { DetachedCriteria noRequiresHost = DetachedCriteria.forClass(PoolAttribute.class, "attr") .add(Restrictions.eq("name", "requires_host")) .add(Property.forName("this.id").eqProperty("attr.pool.id")) .setProjection(Projections.property("attr.id")); // we do want everything else criteriaFilters.add(Subqueries.notExists(noRequiresHost)); } else if (!"true".equalsIgnoreCase(consumer.getFact("virt.is_guest"))) { // not a guest DetachedCriteria noVirtOnlyPoolAttr = DetachedCriteria.forClass(PoolAttribute.class, "pool_attr") .add(Restrictions.eq("name", "virt_only")).add(Restrictions.eq("value", "true").ignoreCase()) .add(Property.forName("this.id").eqProperty("pool_attr.pool.id")) .setProjection(Projections.property("pool_attr.id")); criteriaFilters.add(Subqueries.notExists(noVirtOnlyPoolAttr)); // same criteria but for PoolProduct attributes // not sure if this should be two separate criteria, or if it's // worth it to combine in some clever fashion DetachedCriteria noVirtOnlyProductAttr = DetachedCriteria .forClass(ProductPoolAttribute.class, "prod_attr").add(Restrictions.eq("name", "virt_only")) .add(Restrictions.eq("value", "true").ignoreCase()) .add(Property.forName("this.id").eqProperty("prod_attr.pool.id")) .setProjection(Projections.property("prod_attr.id")); criteriaFilters.add(Subqueries.notExists(noVirtOnlyProductAttr)); } else { // we are a virt guest // add criteria for filtering out pools that are not for this guest if (consumer.hasFact("virt.uuid")) { String hostUuid = ""; // need a default value in case there is no host if (hostConsumer != null) { hostUuid = hostConsumer.getUuid(); } DetachedCriteria noRequiresHost = DetachedCriteria.forClass(PoolAttribute.class, "attr") .add(Restrictions.eq("name", "requires_host")) // Note: looking for pools that are not for this guest .add(Restrictions.ne("value", hostUuid).ignoreCase()) .add(Property.forName("this.id").eqProperty("attr.pool.id")) .setProjection(Projections.property("attr.id")); // we do want everything else criteriaFilters.add(Subqueries.notExists(noRequiresHost)); } // no virt.uuid, we can't try to filter } return criteriaFilters; }
From source file:org.codehaus.groovy.grails.orm.hibernate.query.AbstractHibernateCriterionAdapter.java
License:Apache License
protected void addSubqueryCriterionAdapters() { criterionAdaptors.put(Query.GreaterThanAll.class, new CriterionAdaptor<Query.GreaterThanAll>() { @Override//from ww w . j av a 2 s . c o m public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.GreaterThanAll criterion, String alias) { QueryableCriteria subQuery = criterion.getValue(); String propertyName = getPropertyName(criterion, alias); DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, subQuery); return Property.forName(propertyName).gtAll(detachedCriteria); } }); criterionAdaptors.put(Query.GreaterThanEqualsAll.class, new CriterionAdaptor<Query.GreaterThanEqualsAll>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.GreaterThanEqualsAll criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getValue()); return Property.forName(getPropertyName(criterion, alias)).geAll(detachedCriteria); } }); criterionAdaptors.put(Query.LessThanAll.class, new CriterionAdaptor<Query.LessThanAll>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.LessThanAll criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getValue()); return Property.forName(getPropertyName(criterion, alias)).ltAll(detachedCriteria); } }); criterionAdaptors.put(Query.LessThanEqualsAll.class, new CriterionAdaptor<Query.LessThanEqualsAll>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.LessThanEqualsAll criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getValue()); return Property.forName(getPropertyName(criterion, alias)).leAll(detachedCriteria); } }); criterionAdaptors.put(Query.GreaterThanSome.class, new CriterionAdaptor<Query.GreaterThanSome>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.GreaterThanSome criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getValue()); return Property.forName(getPropertyName(criterion, alias)).gtSome(detachedCriteria); } }); criterionAdaptors.put(Query.GreaterThanEqualsSome.class, new CriterionAdaptor<Query.GreaterThanEqualsSome>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.GreaterThanEqualsSome criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getValue()); return Property.forName(getPropertyName(criterion, alias)).geSome(detachedCriteria); } }); criterionAdaptors.put(Query.LessThanSome.class, new CriterionAdaptor<Query.LessThanSome>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.LessThanSome criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getValue()); return Property.forName(getPropertyName(criterion, alias)).ltSome(detachedCriteria); } }); criterionAdaptors.put(Query.LessThanEqualsSome.class, new CriterionAdaptor<Query.LessThanEqualsSome>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.LessThanEqualsSome criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getValue()); return Property.forName(getPropertyName(criterion, alias)).leSome(detachedCriteria); } }); criterionAdaptors.put(Query.NotIn.class, new CriterionAdaptor<Query.NotIn>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.NotIn criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getSubquery()); return Property.forName(getPropertyName(criterion, alias)).notIn(detachedCriteria); } }); criterionAdaptors.put(Query.Exists.class, new CriterionAdaptor<Query.Exists>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.Exists criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getSubquery()); return Subqueries.exists(detachedCriteria); } }); criterionAdaptors.put(Query.NotExists.class, new CriterionAdaptor<Query.NotExists>() { @Override public Criterion toHibernateCriterion(AbstractHibernateQuery hibernateQuery, Query.NotExists criterion, String alias) { DetachedCriteria detachedCriteria = toHibernateDetachedCriteria(hibernateQuery, criterion.getSubquery()); return Subqueries.notExists(detachedCriteria); } }); }
From source file:org.dspace.content.dao.impl.ItemDAOImpl.java
License:BSD License
@Override public Iterator<Item> findByMetadataQuery(Context context, List<List<MetadataField>> listFieldList, List<String> query_op, List<String> query_val, List<UUID> collectionUuids, String regexClause, int offset, int limit) throws SQLException { Criteria criteria = createCriteria(context, Item.class, "item"); criteria.setFirstResult(offset);//w ww. j av a 2 s . c o m criteria.setMaxResults(limit); if (!collectionUuids.isEmpty()) { DetachedCriteria dcollCriteria = DetachedCriteria.forClass(Collection.class, "coll"); dcollCriteria.setProjection(Projections.property("coll.id")); dcollCriteria.add(Restrictions.eqProperty("coll.id", "item.owningCollection")); dcollCriteria.add(Restrictions.in("coll.id", collectionUuids)); criteria.add(Subqueries.exists(dcollCriteria)); } int index = Math.min(listFieldList.size(), Math.min(query_op.size(), query_val.size())); StringBuilder sb = new StringBuilder(); for (int i = 0; i < index; i++) { OP op = OP.valueOf(query_op.get(i)); if (op == null) { log.warn("Skipping Invalid Operator: " + query_op.get(i)); continue; } if (op == OP.matches || op == OP.doesnt_match) { if (regexClause.isEmpty()) { log.warn("Skipping Unsupported Regex Operator: " + query_op.get(i)); continue; } } DetachedCriteria subcriteria = DetachedCriteria.forClass(MetadataValue.class, "mv"); subcriteria.add(Property.forName("mv.dSpaceObject").eqProperty("item.id")); subcriteria.setProjection(Projections.property("mv.dSpaceObject")); if (!listFieldList.get(i).isEmpty()) { subcriteria.add(Restrictions.in("metadataField", listFieldList.get(i))); } sb.append(op.name() + " "); if (op == OP.equals || op == OP.not_equals) { subcriteria.add(Property.forName("mv.value").eq(query_val.get(i))); sb.append(query_val.get(i)); } else if (op == OP.like || op == OP.not_like) { subcriteria.add(Property.forName("mv.value").like(query_val.get(i))); sb.append(query_val.get(i)); } else if (op == OP.contains || op == OP.doesnt_contain) { subcriteria.add(Property.forName("mv.value").like("%" + query_val.get(i) + "%")); sb.append(query_val.get(i)); } else if (op == OP.matches || op == OP.doesnt_match) { subcriteria .add(Restrictions.sqlRestriction(regexClause, query_val.get(i), StandardBasicTypes.STRING)); sb.append(query_val.get(i)); } if (op == OP.exists || op == OP.equals || op == OP.like || op == OP.contains || op == OP.matches) { criteria.add(Subqueries.exists(subcriteria)); } else { criteria.add(Subqueries.notExists(subcriteria)); } } log.debug(String.format("Running custom query with %d filters", index)); return list(criteria).iterator(); }
From source file:org.motechproject.server.model.db.hibernate.HibernateMotechDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Obs> getActivePregnanciesDueDateObs(Facility facility, Date fromDueDate, Date toDueDate, Concept pregnancyDueDateConcept, Concept pregnancyConcept, Concept pregnancyStatusConcept, Integer maxResults) {//from ww w.java 2 s .c o m Session session = sessionFactory.getCurrentSession(); Criteria criteria = session.createCriteria(Obs.class, "o"); criteria.add(Restrictions.eq("o.voided", false)); criteria.add(Restrictions.eq("o.concept", pregnancyDueDateConcept)); if (fromDueDate != null) { criteria.add(Restrictions.ge("o.valueDatetime", fromDueDate)); } if (toDueDate != null) { criteria.add(Restrictions.le("o.valueDatetime", toDueDate)); } criteria.createAlias("o.person", "p"); criteria.add(Restrictions.eq("p.personVoided", false)); criteria.createAlias("o.obsGroup", "g"); criteria.add(Restrictions.eq("g.concept", pregnancyConcept)); criteria.add(Restrictions.eq("g.voided", false)); DetachedCriteria pregnancyActiveCriteria = DetachedCriteria.forClass(Obs.class, "s") .setProjection(Projections.id()).add(Restrictions.eq("s.voided", false)) .add(Restrictions.eq("s.concept", pregnancyStatusConcept)) .add(Restrictions.eq("s.valueNumeric", 1.0)) .add(Restrictions.eqProperty("s.obsGroup.obsId", "g.obsId")) .add(Restrictions.eqProperty("s.person.personId", "p.personId")); criteria.add(Subqueries.exists(pregnancyActiveCriteria)); DetachedCriteria pregnancyInactiveCriteria = DetachedCriteria.forClass(Obs.class, "e") .setProjection(Projections.id()).add(Restrictions.eq("e.voided", false)) .add(Restrictions.eq("e.concept", pregnancyStatusConcept)) .add(Restrictions.eq("e.valueNumeric", 0.0)) .add(Restrictions.eqProperty("e.obsGroup.obsId", "g.obsId")) .add(Restrictions.eqProperty("e.person.personId", "p.personId")); criteria.add(Subqueries.notExists(pregnancyInactiveCriteria)); if (facility != null) { criteria.add(Restrictions.sqlRestriction( "exists (select f.id from motechmodule_facility f " + "inner join motechmodule_facility_patient fp " + "on f.id = fp.facility_id " + "where f.facility_id = ? and fp.patient_id = {alias}.person_id)", facility.getFacilityId(), Hibernate.INTEGER)); } criteria.addOrder(Order.asc("o.valueDatetime")); if (maxResults != null) { criteria.setMaxResults(maxResults); } return criteria.list(); }
From source file:org.nema.medical.mint.server.domain.ChangeDAO.java
License:Apache License
/** * Get a list of the most recent changes for each study * @return the list of changes/*from w w w . java2s. co m*/ */ @SuppressWarnings("unchecked") public List<Change> findLastChanges() { List<Change> changes; final DetachedCriteria subquery = DetachedCriteria.forClass(Change.class, "change2") .add(Property.forName("change2.studyID").eqProperty("change1.studyID")) .add(Property.forName("change2.changeIndex").gtProperty("change1.changeIndex")) .setProjection(Projections.property("studyID")); final DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Change.class, "change1") .add(Subqueries.notExists(subquery)); changes = getHibernateTemplate().findByCriteria(detachedCriteria); return changes; }