List of usage examples for org.hibernate.criterion Subqueries propertyIn
public static Criterion propertyIn(String propertyName, DetachedCriteria dc)
From source file:au.edu.uts.eng.remotelabs.schedserver.reports.intf.Reports.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/*from w ww.j a v a 2 s.c o m*/ public QueryInfoResponse queryInfo(final QueryInfo queryInfo) { /** Request parameters. **/ final QueryInfoType qIReq = queryInfo.getQueryInfo(); String debug = "Received " + this.getClass().getSimpleName() + "#queryInfo with params:"; debug += "Requestor: " + qIReq.getRequestor() + ", QuerySelect: " + qIReq.getQuerySelect(); if (qIReq.getQueryFilter() != null) { debug += ", QueryFilter: " + qIReq.getQueryFilter(); } debug += ", limit: " + qIReq.getLimit(); this.logger.debug(debug); final RequestorType uid = qIReq.getRequestor(); /** Response parameters. */ final QueryInfoResponse resp = new QueryInfoResponse(); final QueryInfoResponseType respType = new QueryInfoResponseType(); respType.setTypeForQuery(TypeForQuery.RIG); resp.setQueryInfoResponse(respType); final org.hibernate.Session ses = DataAccessActivator.getNewSession(); /* First Query Filter - this contains the main selection type to be selected */ final QueryFilterType query0 = qIReq.getQuerySelect(); try { Criteria cri; /* ---------------------------------------------------------------- * -- Load the requestor. -- * ---------------------------------------------------------------- */ final User user = this.getUserFromUserID(uid, ses); if (user == null) { this.logger.info("Unable to generate report because the user has not been found. Supplied " + "credentials ID=" + uid.getUserID() + ", namespace=" + uid.getUserNamespace() + ", " + "name=" + uid.getUserName() + '.'); return resp; } final String persona = user.getPersona(); /* Check for type of query to determine selection parameters */ if (query0.getTypeForQuery() == TypeForQuery.RIG) { /* ---------------------------------------------------------------- * -- Rig Information only available to ADMIN, to be mediated -- * -- by interface. No criteria on Requestor -- * ---------------------------------------------------------------- */ cri = ses.createCriteria(Rig.class); if (query0.getQueryLike() != null) { cri.add(Restrictions.like("name", query0.getQueryLike(), MatchMode.ANYWHERE)); } if (qIReq.getLimit() > 0) cri.setMaxResults(qIReq.getLimit()); cri.addOrder(Order.asc("name")); for (final Rig o : (List<Rig>) cri.list()) { respType.addSelectionResult(o.getName()); } } else if (query0.getTypeForQuery() == TypeForQuery.RIG_TYPE) { /* ---------------------------------------------------------------- * -- Rig Type Information only available to ADMIN, to be -- * -- mediated by interface. No criteria on Requestor -- * ---------------------------------------------------------------- */ cri = ses.createCriteria(RigType.class); if (query0.getQueryLike() != null) { cri.add(Restrictions.like("name", query0.getQueryLike(), MatchMode.ANYWHERE)); } if (qIReq.getLimit() > 0) cri.setMaxResults(qIReq.getLimit()); cri.addOrder(Order.asc("name")); for (final RigType o : (List<RigType>) cri.list()) { respType.addSelectionResult(o.getName()); } } else if (query0.getTypeForQuery() == TypeForQuery.USER_CLASS) { cri = ses.createCriteria(UserClass.class); if (query0.getQueryLike() != null) { cri.add(Restrictions.like("name", query0.getQueryLike(), MatchMode.ANYWHERE)); } if (qIReq.getLimit() > 0) cri.setMaxResults(qIReq.getLimit()); cri.addOrder(Order.asc("name")); /* ---------------------------------------------------------------- * Check that the requestor has permissions to request the report. * If persona = USER, no reports (USERs will not get here) * If persona = ADMIN, any report * If persona = ACADEMIC, only for classes they own if they can generate reports * ---------------------------------------------------------------- */ if (User.ACADEMIC.equals(persona)) { DetachedCriteria apList = DetachedCriteria.forClass(AcademicPermission.class, "ap") .add(Restrictions.eq("ap.canGenerateReports", true)) .setProjection(Property.forName("userClass")); cri.add(Subqueries.propertyIn("id", apList)); for (final UserClass o : (List<UserClass>) cri.list()) { respType.addSelectionResult(o.getName()); } } else if (User.ADMIN.equals(persona)) { for (final UserClass o : (List<UserClass>) cri.list()) { respType.addSelectionResult(o.getName()); } } } else if (query0.getTypeForQuery() == TypeForQuery.USER) { cri = ses.createCriteria(User.class, "u"); /* ---------------------------------------------------------------- * If persona = USER, no reports (USERs will not get here) * If persona = ADMIN, any report * If persona = ACADEMIC, only for users in classes they own if they can genrate reports * ---------------------------------------------------------------- */ if (query0.getQueryLike() != null) { cri.add(Restrictions.like("name", query0.getQueryLike(), MatchMode.ANYWHERE)); } if (qIReq.getLimit() > 0) cri.setMaxResults(qIReq.getLimit()); cri.addOrder(Order.asc("name")); if (User.ACADEMIC.equals(persona)) { DetachedCriteria apList = DetachedCriteria.forClass(AcademicPermission.class, "ap") .add(Restrictions.eq("ap.canGenerateReports", true)) .setProjection(Property.forName("userClass")); DetachedCriteria userList = DetachedCriteria.forClass(UserAssociation.class, "ua") .add(Subqueries.propertyIn("ua.userClass", apList)) .setProjection(Property.forName("user.id")); cri.add(Subqueries.propertyIn("id", userList)); for (final User o : (List<User>) cri.list()) { respType.addSelectionResult(o.getNamespace() + ':' + o.getName()); } } else if (User.ADMIN.equals(persona)) { for (final User o : (List<User>) cri.list()) { respType.addSelectionResult(o.getNamespace() + ':' + o.getName()); } } } else if (query0.getTypeForQuery() == TypeForQuery.REQUEST_CAPABILITIES) { cri = ses.createCriteria(RequestCapabilities.class); if (query0.getQueryLike() != null) { cri.add(Restrictions.like("capabilities", query0.getQueryLike(), MatchMode.ANYWHERE)); } if (qIReq.getLimit() > 0) cri.setMaxResults(qIReq.getLimit()); cri.addOrder(Order.asc("capabilities")); final List<RequestCapabilities> list = cri.list(); for (final RequestCapabilities o : list) { respType.addSelectionResult(o.getCapabilities()); } } else { this.logger.error( "QueryInfo request failed because TypeForQuery does not have a valid value. Value is " + query0.getTypeForQuery().toString()); return resp; } final QueryFilterType filter[] = qIReq.getQueryFilter(); if (filter != null) { //DODGY Designed, not implemented } } finally { ses.close(); } respType.setTypeForQuery(query0.getTypeForQuery()); resp.setQueryInfoResponse(respType); return resp; }
From source file:co.utb.softeng.contactsapp.dao.impl.CitaDAOImpl.java
@Override public List<Cita> getByContactName(String contactName) { DetachedCriteria subQuery = DetachedCriteria.forClass(Contact.class).createCriteria("citas") .add(Restrictions.ilike("name", contactName, MatchMode.ANYWHERE)).setProjection(Projections.id()); Criteria mainCriteria = getSession().createCriteria(Cita.class).add(Subqueries.propertyIn("id", subQuery)) .setFetchMode("contacts", FetchMode.JOIN); return mainCriteria.list(); }
From source file:co.utb.softeng.contactsapp.dao.impl.ContactDAOImpl.java
@Override public List<Contact> getByCitaName(String citaName) { Criterion crit1 = Restrictions.ilike("asunto", citaName, MatchMode.ANYWHERE); Criterion crit2 = Restrictions.ilike("lugar", citaName, MatchMode.ANYWHERE); DetachedCriteria subQuery = DetachedCriteria.forClass(Contact.class).createCriteria("citas") .add(Restrictions.or(crit1, crit2)).setProjection(Projections.id()); Criteria mainCriteria = getSession().createCriteria(Contact.class) .add(Subqueries.propertyIn("id", subQuery)).setFetchMode("citas", FetchMode.JOIN); return mainCriteria.list(); }
From source file:com.ephesoft.dcma.da.dao.hibernate.BatchClassDaoImpl.java
License:Open Source License
/** * API to get the list of Batch Classes specifying startindex, no of results and sorting if any. * //from w w w .j a va 2 s .c om * @param firstResult int * @param maxResults int * @param order List<Order> * @param userRoles Set<String> * @return List of batch class. */ @Override public List<BatchClass> getBatchClassList(final int firstResult, final int maxResults, final List<Order> order, final Set<String> userRoles) { EphesoftCriteria criteria = criteria(); List<BatchClass> batchClassList = null; if (userRoles == null) { batchClassList = new ArrayList<BatchClass>(0); } else { List<String> roleList = new ArrayList<String>(); for (String userRole : userRoles) { if (null == userRole || userRole.isEmpty()) { continue; } roleList.add(userRole); } DetachedCriteria detachedCriteria = criteria(); detachedCriteria.createAlias(ASSIGNED_GROUPS, ASSIGNED_GROUPS); detachedCriteria.add(Restrictions.in(ASSIGNED_GROUPS_NAME, roleList)); detachedCriteria.setProjection(Projections.distinct(Projections.property(BATCH_ID))); criteria.add(Subqueries.propertyIn(BATCH_ID, detachedCriteria)); criteria.add(Restrictions.or(Restrictions.isNull(IS_DELETED), Restrictions.eq(IS_DELETED, false))); batchClassList = find(criteria, firstResult, maxResults, order.toArray(new Order[order.size()])); } return batchClassList; }
From source file:com.ephesoft.dcma.da.dao.hibernate.DocumentTypeDaoImpl.java
License:Open Source License
/** * An API to fetch all DocumentType by batch instance id. * //from w ww. j a v a 2 s .com * @param batchInstanceIdentifier String * @return List<DocumentType> */ @Override public List<DocumentType> getDocTypeByBatchInstanceIdentifier(String batchInstanceIdentifier) { LOG.info("batchInstanceID : " + batchInstanceIdentifier); DetachedCriteria criteria = criteria(); DetachedCriteria subQuery = criteria(BatchInstance.class); subQuery.add(Restrictions.eq(DataAccessConstant.IDENTIFIER, batchInstanceIdentifier)); subQuery.setProjection(Projections.property(DataAccessConstant.BATCH_CLASS_ID)); criteria.add(Subqueries.propertyIn(DataAccessConstant.BATCH_CLASS_ID, subQuery)); return find(criteria); }
From source file:com.evolveum.midpoint.repo.sql.query.custom.ShadowQueryWithDisjunction.java
License:Apache License
@Override public RQuery createQuery(ObjectQuery objectQuery, Class<? extends ObjectType> type, Collection<SelectorOptions<GetOperationOptions>> options, boolean countingObjects, Session session) { DetachedCriteria c1 = DetachedCriteria.forClass(ClassMapper.getHQLTypeClass(ShadowType.class), "s"); c1.createCriteria("strings", "s1", JoinType.LEFT_OUTER_JOIN); ParsedQuery parsedQuery = parse(objectQuery); Conjunction conjunction = Restrictions.conjunction(); conjunction/*from w w w .j ava 2 s. co m*/ .add(Restrictions.eq("resourceRef.targetOid", parsedQuery.refFilter.getValues().get(0).getOid())); Disjunction disjunction = Restrictions.disjunction(); disjunction.add(createAttributeEq(parsedQuery.eqUidFilter, parsedQuery.eqUidFilter.getPath().lastNamed().getName())); disjunction.add(createAttributeEq(parsedQuery.eqNameFilter, SchemaConstantsGenerated.ICF_S_NAME)); conjunction.add(disjunction); c1.add(conjunction); if (countingObjects) { c1.setProjection(Projections.countDistinct("s.oid")); return new RQueryCriteriaImpl(c1.getExecutableCriteria(session)); } c1.setProjection(Projections.distinct(Projections.property("s.oid"))); Criteria cMain = session.createCriteria(ClassMapper.getHQLTypeClass(ShadowType.class), "o"); cMain.add(Subqueries.propertyIn("oid", c1)); if (objectQuery != null && objectQuery.getPaging() != null) { cMain = updatePagingAndSorting(cMain, type, objectQuery.getPaging()); } ProjectionList projections = Projections.projectionList(); projections.add(Projections.property("fullObject")); projections.add(Projections.property("stringsCount")); projections.add(Projections.property("longsCount")); projections.add(Projections.property("datesCount")); projections.add(Projections.property("referencesCount")); projections.add(Projections.property("polysCount")); projections.add(Projections.property("booleansCount")); cMain.setProjection(projections); cMain.setResultTransformer(GetObjectResult.RESULT_TRANSFORMER); return new RQueryCriteriaImpl(cMain); }
From source file:com.evolveum.midpoint.repo.sql.query.restriction.OrgRestriction.java
License:Apache License
@Override public Criterion interpret(OrgFilter filter) throws QueryException { if (filter.isRoot()) { // Criteria pCriteria = getInterpreter().getCriteria(null); DetachedCriteria dc = DetachedCriteria.forClass(ROrgClosure.class); String[] strings = new String[1]; strings[0] = "descendant.oid"; Type[] type = new Type[1]; type[0] = StringType.INSTANCE;// w w w.j a v a 2 s.c o m dc.setProjection(Projections.sqlGroupProjection("descendant_oid", "descendant_oid having count(descendant_oid)=1", strings, type)); // pCriteria.add(Subqueries.in("this.oid", dc)); return Subqueries.propertyIn("oid", dc); // Query rootOrgQuery = session.createQuery("select org from ROrg as org where org.oid in (select descendant.oid from ROrgClosure group by descendant.oid having count(descendant.oid)=1)"); } if (filter.getOrgRef() == null) { throw new QueryException("No organization reference defined in the search query."); } if (filter.getOrgRef().getOid() == null) { throw new QueryException( "No oid specified in organization reference " + filter.getOrgRef().debugDump()); } DetachedCriteria detached; switch (filter.getScope()) { case ONE_LEVEL: detached = DetachedCriteria.forClass(RParentOrgRef.class, "p"); detached.setProjection(Projections.distinct(Projections.property("p.ownerOid"))); detached.add(Restrictions.eq("p.targetOid", filter.getOrgRef().getOid())); break; case SUBTREE: default: detached = DetachedCriteria.forClass(ROrgClosure.class, "cl"); detached.setProjection(Projections.distinct(Projections.property("cl.descendantOid"))); detached.add(Restrictions.eq("cl.ancestorOid", filter.getOrgRef().getOid())); detached.add(Restrictions.ne("cl.descendantOid", filter.getOrgRef().getOid())); } String mainAlias = getContext().getAlias(null); return Subqueries.propertyIn(mainAlias + ".oid", detached); }
From source file:com.evolveum.midpoint.repo.sql.QueryInterpreterTest.java
License:Apache License
@Test public void queryOrgTreeFindOrgs() throws Exception { Session session = open();// w w w . j a va 2s .c om try { Criteria main = session.createCriteria(ROrg.class, "o"); ProjectionList projections = Projections.projectionList(); addFullObjectProjectionList("o", projections, false); main.setProjection(projections); DetachedCriteria detached = DetachedCriteria.forClass(RParentOrgRef.class, "p"); detached.setProjection(Projections.distinct(Projections.property("p.ownerOid"))); detached.add(Restrictions.eq("p.targetOid", "some oid")); main.add(Subqueries.propertyIn("o.oid", detached)); main.addOrder(Order.asc("o.name.orig")); String expected = HibernateToSqlTranslator.toSql(main); OrgFilter orgFilter = OrgFilter.createOrg("some oid", OrgFilter.Scope.ONE_LEVEL); ObjectQuery objectQuery = ObjectQuery.createObjectQuery(orgFilter); objectQuery .setPaging(ObjectPaging.createPaging(null, null, ObjectType.F_NAME, OrderDirection.ASCENDING)); String real = getInterpretedQuery(session, OrgType.class, objectQuery); LOGGER.info("exp. query>\n{}\nreal query>\n{}", new Object[] { expected, real }); OperationResult result = new OperationResult("query org structure"); repositoryService.searchObjects(OrgType.class, objectQuery, null, result); AssertJUnit.assertEquals(expected, real); } finally { close(session); } }
From source file:com.evolveum.midpoint.repo.sql.QueryInterpreterTest.java
License:Apache License
@Test public void test310QueryNameAndOrg() throws Exception { Session session = open();//from w w w .j av a 2 s . co m try { DetachedCriteria detached = DetachedCriteria.forClass(ROrgClosure.class, "cl"); detached.setProjection(Projections.distinct(Projections.property("cl.descendantOid"))); detached.add(Restrictions.eq("cl.ancestorOid", "1234")); detached.add(Restrictions.ne("cl.descendantOid", "1234")); Criteria main = session.createCriteria(RUser.class, "u"); String mainAlias = "u"; ProjectionList projections = Projections.projectionList(); projections.add(Projections.property("fullObject")); projections.add(Projections.property("stringsCount")); projections.add(Projections.property("longsCount")); projections.add(Projections.property("datesCount")); projections.add(Projections.property("referencesCount")); projections.add(Projections.property("polysCount")); main.setProjection(projections); Conjunction c = Restrictions.conjunction(); c.add(Restrictions.and(Restrictions.eq("u.name.orig", "cpt. Jack Sparrow"), Restrictions.eq("u.name.norm", "cpt jack sparrow"))); c.add(Subqueries.propertyIn(mainAlias + ".oid", detached)); main.add(c); main.addOrder(Order.asc("u.name.orig")); String expected = HibernateToSqlTranslator.toSql(main); EqualFilter eqFilter = EqualFilter.createEqual(ObjectType.F_NAME, ObjectType.class, prismContext, null, new PolyString("cpt. Jack Sparrow", "cpt jack sparrow")); OrgFilter orgFilter = OrgFilter.createOrg("12341234-1234-1234-1234-123412341234"); ObjectQuery query = ObjectQuery.createObjectQuery(AndFilter.createAnd(eqFilter, orgFilter)); query.setPaging(ObjectPaging.createPaging(null, null, ObjectType.F_NAME, OrderDirection.ASCENDING)); String real = getInterpretedQuery(session, UserType.class, query); LOGGER.info("exp. query>\n{}\nreal query>\n{}", new Object[] { expected, real }); AssertJUnit.assertEquals(expected, real); } finally { close(session); } }
From source file:com.mec.DAO.Postgre.LocalidadPostgreDAO.java
public List<LocalidadTipo> getAll() { DetachedCriteria subCriteria = DetachedCriteria.forClass(DepartamentoTipo.class, "departamento") .add(Restrictions.eq("c_provincia", 18)).setProjection(Projections.property("departamento.id")); Criteria cr = getSessionPostgre().createCriteria(LocalidadTipo.class, "localidad"); cr.add(Subqueries.propertyIn("localidad.cDepartamento.id", subCriteria));//localidades de Corrientes return lazyInit(cr.list()); }