List of usage examples for org.hibernate.criterion Subqueries exists
public static Criterion exists(DetachedCriteria dc)
From source file:alpha.portal.dao.hibernate.AlphaCardDaoHibernate.java
License:Apache License
/** * List alpha cards by criterion./*from w ww . j a v a 2s.c o m*/ * * @param caseId * the case id * @param criteriaArray * the criteria array * @return the list * @see alpha.portal.dao.AlphaCardDao#listAlphaCardsByCriterion(org.hibernate.criterion.Criterion) */ public List<AlphaCard> listAlphaCardsByCriterion(final String caseId, final Criterion... criteriaArray) { Session session; boolean sessionOwn = false; try { session = this.getSessionFactory().getCurrentSession(); } catch (final Exception e) { session = this.getSessionFactory().openSession(); sessionOwn = true; } // get newest sequenceNumber for each cardId final DetachedCriteria version = DetachedCriteria.forClass(AlphaCard.class, "cardVersion") .add(Property.forName("card.alphaCardIdentifier.cardId") .eqProperty("cardVersion.alphaCardIdentifier.cardId")) .setProjection( Projections.projectionList().add(Projections.max("alphaCardIdentifier.sequenceNumber"))); final Criteria crit = session.createCriteria(AlphaCard.class, "card"); for (final Criterion c : criteriaArray) { // Create the subquery (somehow we need to use the Descriptor or // else the subquery-JOIN is not done) final DetachedCriteria subcrit = DetachedCriteria.forClass(AlphaCardDescriptor.class, "crit"); // Join the adornments subcrit.createAlias("crit.adornmentList", "ad"); // Add adornment condition subcrit.add(c); // Map the subquery back to the outer query subcrit.add(Restrictions.eqProperty("card.alphaCardIdentifier", "crit.alphaCardIdentifier")); // Narrow down subquery or else we get a NullPointer-Exception subcrit.setProjection(Projections.property("crit.alphaCardIdentifier.cardId")); // Add this subquery to the outer query. crit.add(Subqueries.exists(subcrit)); } crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY) .add(Property.forName("alphaCardIdentifier.sequenceNumber").eq(version)) .createAlias("alphaCase", "case").add(Restrictions.eq("case.caseId", caseId)); List<AlphaCard> list = crit.list(); if (list.size() > 1) { final List<AlphaCard> order = (list.get(0)).getAlphaCase().getAlphaCards(); final List<AlphaCard> orderedList = new LinkedList<AlphaCard>(); for (final AlphaCard cc : order) { for (final AlphaCard c : list) { if (c.getAlphaCardIdentifier().equals(cc.getAlphaCardIdentifier())) { orderedList.add(c); break; } } } list = orderedList; } if (sessionOwn) { session.close(); } return list; }
From source file:au.org.theark.core.dao.StudyDao.java
License:Open Source License
private Criteria buildGeneralSubjectCriteria(SubjectVO subjectVO) { Criteria criteria = getSession().createCriteria(LinkSubjectStudy.class); criteria.createAlias("person", "p"); if (subjectVO.getLinkSubjectStudy().getStudy() != null) { criteria.add(Restrictions.eq("study.id", subjectVO.getLinkSubjectStudy().getStudy().getId())); } else {//from w w w . j a v a 2 s . c o m criteria.add(Restrictions.in("study", subjectVO.getStudyList())); criteria.createAlias("study", "st"); criteria.addOrder(Order.asc("st.name")); } if (subjectVO.getLinkSubjectStudy().getPerson() != null) { if (subjectVO.getLinkSubjectStudy().getPerson().getId() != null) { criteria.add(Restrictions.eq("p.id", subjectVO.getLinkSubjectStudy().getPerson().getId())); } if (subjectVO.getLinkSubjectStudy().getPerson().getFirstName() != null) { criteria.add(Restrictions.ilike("p.firstName", subjectVO.getLinkSubjectStudy().getPerson().getFirstName(), MatchMode.ANYWHERE)); } if (subjectVO.getLinkSubjectStudy().getPerson().getMiddleName() != null) { criteria.add(Restrictions.ilike("p.middleName", subjectVO.getLinkSubjectStudy().getPerson().getMiddleName(), MatchMode.ANYWHERE)); } if (subjectVO.getLinkSubjectStudy().getPerson().getLastName() != null) { /* old code pre George adding personlastname lookup criteria.add(Restrictions.ilike("p.lastName", subjectVO.getLinkSubjectStudy().getPerson().getLastName(), MatchMode.ANYWHERE));*/ //log.info("Lastname: " + subjectVO.getLinkSubjectStudy().getPerson().getLastName()); DetachedCriteria previousLastNames = DetachedCriteria.forClass(PersonLastnameHistory.class, "l") .setProjection(Projections.property("l.lastName")) .add(Restrictions.ilike("l.lastName", subjectVO.getLinkSubjectStudy().getPerson().getLastName(), MatchMode.ANYWHERE)) .add(Restrictions.eqProperty("p.id", "l.person.id")); criteria.add(Restrictions.or(Restrictions.ilike("p.lastName", subjectVO.getLinkSubjectStudy().getPerson().getLastName(), MatchMode.ANYWHERE), Subqueries.exists(previousLastNames))); } if (subjectVO.getLinkSubjectStudy().getPerson().getDateOfBirth() != null) { criteria.add(Restrictions.eq("p.dateOfBirth", subjectVO.getLinkSubjectStudy().getPerson().getDateOfBirth())); } if (subjectVO.getLinkSubjectStudy().getPerson().getGenderType() != null) { criteria.add(Restrictions.eq("p.genderType.id", subjectVO.getLinkSubjectStudy().getPerson().getGenderType().getId())); } if (subjectVO.getLinkSubjectStudy().getPerson().getVitalStatus() != null) { criteria.add(Restrictions.eq("p.vitalStatus.id", subjectVO.getLinkSubjectStudy().getPerson().getVitalStatus().getId())); } if (!subjectVO.getLinkSubjectStudy().getPerson().getOtherIDs().isEmpty()) { OtherID o = (OtherID) subjectVO.getLinkSubjectStudy().getPerson().getOtherIDs().toArray()[0]; if (o != null && o.getOtherID() != null && !o.getOtherID().isEmpty()) { log.info("OtherID search"); // DetachedCriteria otherID = DetachedCriteria.forClass(OtherID.class, "O") // .setProjection(Projections.projectionList().add(Projections.property("O.otherID"))) // .add(Restrictions.ilike("O.otherID", ((OtherID) subjectVO.getLinkSubjectStudy().getPerson().getOtherIDs().toArray()[0]).getOtherID(), MatchMode.EXACT)) // .add(Restrictions.eqProperty("p.id", "O.person.id")); // criteria.add(Subqueries.exists(otherID)); criteria.createAlias("p.otherIDs", "o"); criteria.add(Restrictions.ilike("o.otherID", ((OtherID) subjectVO.getLinkSubjectStudy().getPerson().getOtherIDs().toArray()[0]) .getOtherID(), MatchMode.ANYWHERE)); criteria.setProjection(Projections.distinct( Projections.projectionList().add(Projections.property("o.personid"), "lss.person.id"))); } } } if (subjectVO.getLinkSubjectStudy().getSubjectUID() != null && subjectVO.getLinkSubjectStudy().getSubjectUID().length() > 0) { criteria.add(Restrictions.ilike("subjectUID", subjectVO.getLinkSubjectStudy().getSubjectUID(), MatchMode.ANYWHERE)); } if (subjectVO.getLinkSubjectStudy().getSubjectStatus() != null) { criteria.add(Restrictions.eq("subjectStatus", subjectVO.getLinkSubjectStudy().getSubjectStatus())); SubjectStatus subjectStatus = getSubjectStatus("Archive"); if (subjectStatus != null) { criteria.add(Restrictions.ne("subjectStatus", subjectStatus)); } } else { SubjectStatus subjectStatus = getSubjectStatus("Archive"); if (subjectStatus != null) { criteria.add(Restrictions.ne("subjectStatus", subjectStatus)); } } if (subjectVO.getRelativeUIDs().size() > 0) { criteria.add(Restrictions.not(Restrictions.in("subjectUID", subjectVO.getRelativeUIDs().toArray()))); } criteria.setProjection(Projections.distinct(Projections.projectionList().add(Projections.id()))); criteria.addOrder(Order.asc("subjectUID")); return criteria; }
From source file:au.org.theark.lims.model.dao.BioCollectionDao.java
License:Open Source License
public Boolean hasBioCollections(LinkSubjectStudy linkSubjectStudy) { // Use WHERE EXIST to optimise query even further StatelessSession session = getStatelessSession(); Criteria criteria = session.createCriteria(LinkSubjectStudy.class, "lss"); DetachedCriteria sizeCriteria = DetachedCriteria.forClass(BioCollection.class, "bc"); criteria.add(Restrictions.eq("lss.id", linkSubjectStudy.getId())); sizeCriteria.add(Property.forName("lss.id").eqProperty("bc.linkSubjectStudy.id")); criteria.add(Subqueries.exists(sizeCriteria.setProjection(Projections.property("bc.id")))); criteria.setProjection(Projections.rowCount()); Boolean result = ((Long) criteria.uniqueResult()) > 0L; session.close();/*from w ww . jav a2s . c o m*/ return result; }
From source file:au.org.theark.lims.model.dao.BioCollectionDao.java
License:Open Source License
public Boolean hasBiospecimens(BioCollection bioCollection) { // Use WHERE EXIST to optimise query even further StatelessSession session = getStatelessSession(); Criteria criteria = session.createCriteria(BioCollection.class, "bc"); DetachedCriteria sizeCriteria = DetachedCriteria.forClass(Biospecimen.class, "b"); criteria.add(Restrictions.eq("bc.id", bioCollection.getId())); sizeCriteria.add(Property.forName("bc.id").eqProperty("b.bioCollection.id")); criteria.add(Subqueries.exists(sizeCriteria.setProjection(Projections.property("b.id")))); criteria.setProjection(Projections.rowCount()); Boolean result = ((Long) criteria.uniqueResult()) > 0L; session.close();/* w ww. j ava 2s. c o m*/ return result; }
From source file:au.org.theark.phenotypic.model.dao.PhenotypicDao.java
License:Open Source License
/** * The method checks if the given questionnaire's fields have data linked to it. * //from w w w . j ava2 s . co m * @param customFieldGroup */ public void isDataAvailableForQuestionnaire(CustomFieldGroup customFieldGroup) { Criteria criteria = getSession().createCriteria(CustomField.class, "cf"); criteria.createAlias("customFieldDisplay", "cfd", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldDisplay criteria.createAlias("cfd.customFieldGroup", "cfg", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldGroup criteria.add(Restrictions.eq("cf.study", customFieldGroup.getStudy())); ArkFunction function = iArkCommonService .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_DATA_DICTIONARY); criteria.add(Restrictions.eq("cf.arkFunction", function)); criteria.add(Restrictions.eq("cfg.id", customFieldGroup.getId())); DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "pd"); // Join CustomFieldDisplay and PhenoData on ID FK fieldDataCriteria.add(Property.forName("cfd.id").eqProperty("pd." + "customFieldDisplay.id")); criteria.add( Subqueries.exists(fieldDataCriteria.setProjection(Projections.property("pd.customFieldDisplay")))); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("cfg.name"), "questionnaire"); projectionList.add(Projections.property("cf.name"), "fieldName"); projectionList.add(Projections.property("cf.description"), "description"); }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<FieldDetailsDataRow> getPhenoFieldDetailsList(FieldDetailsReportVO fdrVO) { List<FieldDetailsDataRow> results = new ArrayList<FieldDetailsDataRow>(); Criteria criteria = getSession().createCriteria(PhenoDataSetCollection.class, "fpc"); criteria.createAlias("phenoCollection", "pc"); // Inner join to Field criteria.createAlias("field", "f"); // Inner join to Field criteria.createAlias("f.fieldType", "ft"); // Inner join to FieldType criteria.add(Restrictions.eq("study", fdrVO.getStudy())); if (fdrVO.getPhenoCollection() != null) { criteria.add(Restrictions.eq("phenoCollection", fdrVO.getPhenoCollection())); }/*from w ww. j a v a2s . c o m*/ if (fdrVO.getFieldDataAvailable()) { DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "fd"); // Join FieldPhenoCollection and FieldData on ID FK fieldDataCriteria.add(Property.forName("f.id").eqProperty("fd." + "field.id")); fieldDataCriteria.add(Property.forName("pc.id").eqProperty("fd." + "collection.id")); criteria.add(Subqueries.exists(fieldDataCriteria.setProjection(Projections.property("fd.id")))); } ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("pc.name"), "collection"); projectionList.add(Projections.property("f.name"), "fieldName"); projectionList.add(Projections.property("f.description"), "description"); projectionList.add(Projections.property("f.minValue"), "minValue"); projectionList.add(Projections.property("f.maxValue"), "maxValue"); projectionList.add(Projections.property("f.encodedValues"), "encodedValues"); projectionList.add(Projections.property("f.missingValue"), "missingValue"); projectionList.add(Projections.property("f.units"), "units"); projectionList.add(Projections.property("ft.name"), "type"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(FieldDetailsDataRow.class)); criteria.addOrder(Order.asc("pc.id")); criteria.addOrder(Order.asc("f.name")); results = criteria.list(); return results; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<CustomFieldDetailsDataRow> getPhenoCustomFieldDetailsList(CustomFieldDetailsReportVO fdrVO) { List<CustomFieldDetailsDataRow> results = new ArrayList<CustomFieldDetailsDataRow>(); if (fdrVO.getCustomFieldDisplay() != null) { /*//from w w w . jav a 2 s.co m * Following query returns customFields whether or not they are * associated with a customFieldGroups (via customFieldDisplay) */ Criteria criteria = getSession().createCriteria(CustomField.class, "cf"); criteria.createAlias("customFieldDisplay", "cfd", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldDisplay criteria.createAlias("cfd.customFieldGroup", "cfg", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldGroup criteria.createAlias("fieldType", "ft", JoinType.LEFT_OUTER_JOIN); // Left join to FieldType criteria.createAlias("unitType", "ut", JoinType.LEFT_OUTER_JOIN); // Left join to UnitType criteria.add(Restrictions.eq("cf.study", fdrVO.getStudy())); ArkFunction function = iArkCommonService .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_PHENO_COLLECTION); criteria.add(Restrictions.eq("cf.arkFunction", function)); if (fdrVO.getCustomFieldDisplay().getCustomFieldGroup() != null) { criteria.add( Restrictions.eq("cfg.id", fdrVO.getCustomFieldDisplay().getCustomFieldGroup().getId())); } if (fdrVO.getFieldDataAvailable()) { DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "pd"); // Join CustomFieldDisplay and PhenoData on ID FK fieldDataCriteria.add(Property.forName("cfd.id").eqProperty("pd." + "customFieldDisplay.id")); criteria.add(Subqueries .exists(fieldDataCriteria.setProjection(Projections.property("pd.customFieldDisplay")))); } ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("cfg.name"), "questionnaire"); projectionList.add(Projections.property("cf.name"), "fieldName"); projectionList.add(Projections.property("cf.description"), "description"); projectionList.add(Projections.property("cf.minValue"), "minValue"); projectionList.add(Projections.property("cf.maxValue"), "maxValue"); projectionList.add(Projections.property("cf.encodedValues"), "encodedValues"); projectionList.add(Projections.property("cf.missingValue"), "missingValue"); projectionList.add(Projections.property("ut.name"), "units"); projectionList.add(Projections.property("ft.name"), "type"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(CustomFieldDetailsDataRow.class)); criteria.addOrder(Order.asc("cfg.id")); criteria.addOrder(Order.asc("cfd.sequence")); results = criteria.list(); } return results; }
From source file:au.org.theark.report.model.dao.ReportDao.java
License:Open Source License
public List<PhenoDataSetFieldDetailsDataRow> getPhenoDataSetFieldDetailsList( PhenoDataSetFieldDetailsReportVO reportVO) { List<PhenoDataSetFieldDetailsDataRow> results = new ArrayList<PhenoDataSetFieldDetailsDataRow>(); if (reportVO.getPhenoDataSetFieldDisplay() != null) { /*//from www. jav a2 s.c o m * Following query returns customFields whether or not they are * associated with a customFieldGroups (via customFieldDisplay) */ Criteria criteria = getSession().createCriteria(PhenoDataSetField.class, "pf"); criteria.createAlias("phenoDatasetFieldDisplay", "pdfd", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldDisplay criteria.createAlias("pdfd.phenoDatasetFieldGroup", "pdfg", JoinType.LEFT_OUTER_JOIN); // Left join to CustomFieldGroup criteria.createAlias("fieldType", "ft", JoinType.LEFT_OUTER_JOIN); // Left join to FieldType criteria.createAlias("unitType", "ut", JoinType.LEFT_OUTER_JOIN); // Left join to UnitType criteria.add(Restrictions.eq("pf.study", reportVO.getStudy())); ArkFunction function = iArkCommonService .getArkFunctionByName(au.org.theark.core.Constants.FUNCTION_KEY_VALUE_PHENO_COLLECTION); criteria.add(Restrictions.eq("pf.arkFunction", function)); if (reportVO.getPhenoDataSetFieldDisplay().getPhenoDataSetGroup() != null) { criteria.add(Restrictions.eq("pdfg.id", reportVO.getPhenoDataSetFieldDisplay().getPhenoDataSetGroup().getId())); } if (reportVO.getFieldDataAvailable()) { DetachedCriteria fieldDataCriteria = DetachedCriteria.forClass(PhenoDataSetData.class, "pd"); // Join CustomFieldDisplay and PhenoData on ID FK fieldDataCriteria .add(Property.forName("pdfd.id").eqProperty("pd." + "phenoDatasetFieldDisplay.id")); criteria.add(Subqueries.exists( fieldDataCriteria.setProjection(Projections.property("pd.phenoDatasetFieldDisplay")))); } ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("pdfg.name"), "questionnaire"); projectionList.add(Projections.property("pf.name"), "fieldName"); projectionList.add(Projections.property("pf.description"), "description"); projectionList.add(Projections.property("pf.minValue"), "minValue"); projectionList.add(Projections.property("pf.maxValue"), "maxValue"); projectionList.add(Projections.property("pf.encodedValues"), "encodedValues"); projectionList.add(Projections.property("pf.missingValue"), "missingValue"); projectionList.add(Projections.property("ut.name"), "units"); projectionList.add(Projections.property("ft.name"), "type"); criteria.setProjection(projectionList); // only return fields required for report criteria.setResultTransformer(Transformers.aliasToBean(PhenoDataSetFieldDetailsDataRow.class)); criteria.addOrder(Order.asc("pdfg.id")); criteria.addOrder(Order.asc("pdfd.sequence")); results = criteria.list(); } return results; }
From source file:com.hmsinc.epicenter.model.workflow.impl.WorkflowRepositoryImpl.java
License:Open Source License
/** * Basic filter for investigations.// w w w . j av a 2 s . co m * * @param c * @param startDate * @param endDate * @param geometry * @param organizations * @param showAll * @return */ private Criteria applyInvestigationCriteria(final Criteria c, DateTime startDate, DateTime endDate, Geometry geometry, Collection<Organization> organizations, boolean showAll) { if (startDate != null) { if (endDate == null) { c.add(Restrictions.gt("timestamp", startDate)); } else { c.add(Restrictions.between("timestamp", startDate, endDate)); } } if (organizations != null && organizations.size() > 0) { c.add(Restrictions.in("organization", organizations)); } if (geometry != null) { c.add(Subqueries.exists(getInvestigationGeometryFilter(geometry))); } if (showAll == false) { c.createCriteria("state").add(Restrictions.ne("stateType", WorkflowStateType.TERMINAL)); } return c; }
From source file:com.qcadoo.model.api.search.SearchSubqueries.java
License:Open Source License
/** * Creates criterion which checks if any row exists in given subquery. * /*from w w w .j av a 2 s . c om*/ * @param criteria * subcriteria * @return criteria */ public static SearchCriterion exists(final SearchCriteriaBuilder criteria) { return new SearchCriterionImpl( Subqueries.exists(((SearchCriteria) criteria).getHibernateDetachedCriteria())); }