List of usage examples for javax.persistence.criteria Join join
<Y> Join<X, Y> join(SingularAttribute<? super X, Y> attribute, JoinType jt);
From source file:org.verinice.persistence.CnaTreeElementDaoImpl.java
private TypedQuery<CnaTreeElement> createQueryForScopeKeyValue(Integer scopeId, String key, String value) { CriteriaQuery<CnaTreeElement> query = getCriteriaBuilder().createQuery(CnaTreeElement.class); Root<CnaTreeElement> rootelement = query.from(CnaTreeElement.class); query.select(rootelement);//from w ww . ja v a 2 s . com Join<CnaTreeElement, Entity> entityJoin = rootelement.join("entity", JoinType.LEFT); Join<PropertyList, Entity> propertyListJoin = entityJoin.join("propertyLists", JoinType.LEFT); Join<PropertyList, Property> propertyJoin = propertyListJoin.join("properties", JoinType.LEFT); List<Predicate> conditions = new ArrayList<>(); if (key != null) { conditions.add(getCriteriaBuilder().like(propertyJoin.get("propertytype"), key)); } if (value != null) { conditions.add(getCriteriaBuilder().like(propertyJoin.get("propertyvalue"), value)); } if (scopeId != null) { conditions.add(getCriteriaBuilder().equal(rootelement.get("scopeId"), scopeId)); } query.where(conditions.toArray(new Predicate[conditions.size()])); query.distinct(true); return entityManager.createQuery(query); }
From source file:ch.puzzle.itc.mobiliar.business.server.boundary.ServerView.java
public List<ServerTuple> getNodeServers(String hostFilter, String appServerFilter, String runtimeFilter, String nodeFilter, String contextFilter) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<ServerTuple> q = cb.createQuery(ServerTuple.class); //get Node ResType Root<ResourceEntity> node = q.from(ResourceEntity.class); Join<ResourceEntity, ResourceTypeEntity> nodeType = node.join("resourceType", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> nodeRelease = node.join("release", JoinType.LEFT); //get Props on node Join<ResourceEntity, ResourceContextEntity> resCtx = node.join("contexts", JoinType.LEFT); Join<ResourceContextEntity, ContextEntity> nodeCtx = resCtx.join("context", JoinType.LEFT); Join<ContextEntity, ContextEntity> nodeDomain = nodeCtx.join("parent", JoinType.LEFT); Join<ResourceContextEntity, PropertyEntity> nodeProp = resCtx.join("properties", JoinType.LEFT); Join<PropertyEntity, PropertyDescriptorEntity> nodePropType = nodeProp.join("descriptor", JoinType.LEFT); //get AppServer Join<ResourceEntity, ConsumedResourceRelationEntity> nodeResRel = node.join("consumedSlaveRelations", JoinType.LEFT);/* w ww .ja va 2s . co m*/ Join<ConsumedResourceRelationEntity, ResourceEntity> appServer = nodeResRel.join("masterResource", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> asRelease = appServer.join("release", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> asType = appServer.join("resourceType", JoinType.LEFT); //get Runtime of as Join<ResourceEntity, ConsumedResourceRelationEntity> asResRel = appServer.join("consumedMasterRelations", JoinType.LEFT); Join<ConsumedResourceRelationEntity, ResourceEntity> asRuntime = asResRel.join("slaveResource", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> runtimeType = asRuntime.join("resourceType", JoinType.LEFT); q.select(cb.construct(ServerTuple.class, nodeProp.get("value"), appServer.get("name"), appServer.get("id"), asRelease.get("name"), asRuntime.get("name"), node.get("name"), node.get("id"), nodeRelease.get("name"), nodeDomain.get("name"), nodeDomain.get("id"), nodeCtx.get("name"), nodeCtx.get("id"), cb.literal(1) // true )); Predicate p = cb.and(cb.equal(nodeType.get("name"), DefaultResourceTypeDefinition.NODE.name()), cb.or(cb.equal(asType.get("name"), DefaultResourceTypeDefinition.APPLICATIONSERVER.name()), cb.isNull(asType.get("name")) //nodes without appServer ), cb.or(cb.equal(runtimeType.get("name"), ResourceTypeEntity.RUNTIME), cb.isNull(runtimeType.get("name"))), cb.isNotNull(nodeProp.get("value")), cb.equal(nodePropType.get("propertyName"), "hostName") ); p = addFilters(p, cb, hostFilter, appServerFilter, runtimeFilter, nodeFilter, contextFilter, nodeProp.<String>get("value"), appServer.<String>get("name"), asRuntime.<String>get("name"), node.<String>get("name"), nodeCtx.<String>get("name")); q.where(p); TypedQuery<ServerTuple> query = entityManager.createQuery(q); List<ServerTuple> servers = query.getResultList(); return servers; }
From source file:ch.puzzle.itc.mobiliar.business.server.boundary.ServerView.java
public List<ServerTuple> getAppServers(String hostFilter, String appServerFilter, String runtimeFilter, String nodeFilter, String contextFilter) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<ServerTuple> q = cb.createQuery(ServerTuple.class); //get Node ResType Root<ResourceEntity> node = q.from(ResourceEntity.class); Join<ResourceEntity, ResourceTypeEntity> nodeType = node.join("resourceType", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> nodeRelease = node.join("release", JoinType.LEFT); //get AppServer Join<ResourceEntity, ConsumedResourceRelationEntity> nodeResRel = node.join("consumedSlaveRelations", JoinType.LEFT);//from ww w .j a va 2s . c o m Join<ConsumedResourceRelationEntity, ResourceEntity> appServer = nodeResRel.join("masterResource", JoinType.LEFT); Join<ResourceEntity, ReleaseEntity> asRelease = appServer.join("release", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> asType = appServer.join("resourceType", JoinType.LEFT); //get Runtime of as Join<ResourceEntity, ConsumedResourceRelationEntity> asResRel = appServer.join("consumedMasterRelations", JoinType.LEFT); Join<ConsumedResourceRelationEntity, ResourceEntity> asRuntime = asResRel.join("slaveResource", JoinType.LEFT); Join<ResourceEntity, ResourceTypeEntity> runtimeType = asRuntime.join("resourceType", JoinType.LEFT); //get Props between as and node Join<ConsumedResourceRelationEntity, ResourceRelationContextEntity> resRelCtx = nodeResRel.join("contexts", JoinType.LEFT); Join<ResourceRelationContextEntity, ContextEntity> asCtx = resRelCtx.join("context", JoinType.LEFT); Join<ContextEntity, ContextEntity> asDomain = asCtx.join("parent", JoinType.LEFT); Join<ResourceRelationContextEntity, PropertyEntity> asProp = resRelCtx.join("properties", JoinType.LEFT); //here an on clause should be added, so we don't get hostnames that are divined directly on the node multiple times (and descriptor.propertyName = 'hostName') //on support was added in jpa 2.1 which is part of JEE 7 Join<PropertyEntity, PropertyDescriptorEntity> asPropType = asProp.join("descriptor", JoinType.LEFT); q.select(cb.construct(ServerTuple.class, asProp.get("value"), appServer.get("name"), appServer.get("id"), asRelease.get("name"), asRuntime.get("name"), node.get("name"), node.get("id"), nodeRelease.get("name"), asDomain.get("name"), asDomain.get("id"), asCtx.get("name"), asCtx.get("id"), cb.literal(0) //false )); Predicate p = cb.and(cb.equal(nodeType.get("name"), DefaultResourceTypeDefinition.NODE.name()), cb.equal(asType.get("name"), DefaultResourceTypeDefinition.APPLICATIONSERVER.name()), cb.or(cb.equal(runtimeType.get("name"), ResourceTypeEntity.RUNTIME), cb.isNull(runtimeType.get("name"))), cb.isNotNull(asProp.get("value")), cb.equal(asPropType.get("propertyName"), "hostName")); p = addFilters(p, cb, hostFilter, appServerFilter, runtimeFilter, nodeFilter, contextFilter, asProp.<String>get("value"), appServer.<String>get("name"), asRuntime.<String>get("name"), node.<String>get("name"), asCtx.<String>get("name")); q.where(p); TypedQuery<ServerTuple> query = entityManager.createQuery(q); List<ServerTuple> servers = query.getResultList(); return servers; }
From source file:org.niord.core.message.MessageService.java
/** * Searches out the ID's of the paged result set of messages defined by the search parameters. * Also fills out the total result count of the message search result. * * @param param the search parameters/*from w w w . jav a2s .c om*/ * @param result the search result to update with the total result count * @return the paged list of message ID's */ @SuppressWarnings("all") List<Integer> searchPagedMessageIds(MessageSearchParams param, PagedSearchResultVo<Message> result) throws Exception { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Tuple> tupleQuery = builder.createTupleQuery(); // Select messages Root<Message> msgRoot = tupleQuery.from(Message.class); // Build the predicates based on the search parameters CriteriaHelper<Tuple> criteriaHelper = CriteriaHelper.initWithTupleQuery(em).between(msgRoot.get("updated"), param.getUpdatedFrom(), param.getUpdatedTo()); // Filter by dates if (param.getFrom() != null || param.getTo() != null) { DateType dateType = param.getDateType() != null ? param.getDateType() : DateType.PUBLISH_DATE; Date from = param.getFrom(); Date to = param.getTo(); switch (dateType) { case PUBLISH_DATE: criteriaHelper.overlaps(msgRoot.get("publishDateFrom"), msgRoot.get("publishDateTo"), from, to); break; case EVENT_DATE: criteriaHelper.overlaps(msgRoot.get("eventDateFrom"), msgRoot.get("eventDateTo"), from, to); break; case CREATED_DATE: criteriaHelper.between(msgRoot.get("created"), from, to); break; case UPDATED_DATE: criteriaHelper.between(msgRoot.get("updated"), from, to); break; case PUBLISH_FROM_DATE: criteriaHelper.between(msgRoot.get("publishDateFrom"), from, to); break; case PUBLISH_TO_DATE: criteriaHelper.between(msgRoot.get("publishDateTo"), from, to); break; } } // Main types and sub-types if (!param.getMainTypes().isEmpty()) { criteriaHelper.in(msgRoot.get("mainType"), param.getMainTypes()); } if (!param.getTypes().isEmpty()) { criteriaHelper.in(msgRoot.get("type"), param.getTypes()); } // Statuses if (!param.getStatuses().isEmpty()) { criteriaHelper.in(msgRoot.get("status"), param.getStatuses()); } // Search the Lucene index for free text search if (param.requiresLuceneSearch()) { List<Long> ids = null; try { ids = messageLuceneIndex.searchIndex(param.getQuery(), param.getLanguage(), Integer.MAX_VALUE); } catch (Exception e) { log.warn("Error searching lucene index for query " + param.getQuery()); ids = Collections.emptyList(); } criteriaHelper.in(msgRoot.get("id"), ids); } // Message series if (!param.getSeriesIds().isEmpty()) { criteriaHelper.in(msgRoot.get("messageSeries").get("seriesId"), param.getSeriesIds()); } // Filter by area, join over... if (!param.getAreaIds().isEmpty()) { Join<Message, Area> areas = msgRoot.join("areas", JoinType.LEFT); if (!param.getAreaIds().isEmpty()) { Predicate[] areaMatch = param.getAreaIds().stream().map(aid -> areaService.findByAreaId(aid)) .filter(Objects::nonNull).map(a -> builder.like(areas.get("lineage"), a.getLineage() + "%")) .toArray(Predicate[]::new); criteriaHelper.add(builder.or(areaMatch)); } } // Filter on categories if (!param.getCategoryIds().isEmpty()) { Join<Message, Category> categories = msgRoot.join("categories", JoinType.LEFT); Predicate[] categoryMatch = param.getCategoryIds().stream() .map(cid -> categoryService.findByCategoryId(cid)).filter(Objects::nonNull) .map(c -> builder.like(categories.get("lineage"), c.getLineage() + "%")) .toArray(Predicate[]::new); criteriaHelper.add(builder.or(categoryMatch)); } // Filter on charts if (!param.getChartNumbers().isEmpty()) { Join<Message, Chart> charts = msgRoot.join("charts", JoinType.LEFT); Predicate[] chartMatch = param.getChartNumbers().stream() .map(m -> builder.equal(charts.get("chartNumber"), m)).toArray(Predicate[]::new); criteriaHelper.add(builder.or(chartMatch)); } // Geometry if (param.getExtent() != null) { param.getExtent().setSRID(WGS84_SRID); Join<Message, MessagePart> partRoot = msgRoot.join("parts", JoinType.LEFT); Join<Message, FeatureCollection> fcRoot = partRoot.join("geometry", JoinType.LEFT); Join<FeatureCollection, Feature> fRoot = fcRoot.join("features", JoinType.LEFT); Predicate geomPredicate = new SpatialIntersectsPredicate(criteriaHelper.getCriteriaBuilder(), fRoot.get("geometry"), param.getExtent()); if (param.getIncludeNoPos() != null && param.getIncludeNoPos().booleanValue()) { // search for message with no geometry in addition to messages within extent criteriaHelper.add(builder.or(builder.equal(msgRoot.get("hasGeometry"), false), geomPredicate)); } else { // Only search for messages within extent criteriaHelper.add(geomPredicate); } } // Tags if (!param.getTags().isEmpty()) { Join<Message, MessageTag> tags = msgRoot.join("tags", JoinType.LEFT); String[] tagIds = param.getTags().toArray(new String[param.getTags().size()]); Predicate[] tagMatch = messageTagService.findTags(tagIds).stream() .map(t -> builder.equal(tags.get("id"), t.getId())).toArray(Predicate[]::new); criteriaHelper.add(builder.or(tagMatch)); } // User if (StringUtils.isNotBlank(param.getUsername())) { UserType userType = param.getUserType() == null ? UserType.UPDATED_BY : param.getUserType(); if (userType == UserType.CREATED_BY || userType == UserType.LAST_UPDATED_BY) { String joinCol = userType == UserType.CREATED_BY ? "createdBy" : "lastUpdatedBy"; Join<Message, User> userRoot = msgRoot.join(joinCol, JoinType.LEFT); criteriaHelper.equals(userRoot.get("username"), param.getUsername()); } else { Join<Message, MessageHistory> historyRoot = msgRoot.join("history", JoinType.LEFT); Join<MessageHistory, User> userRoot = historyRoot.join("user", JoinType.LEFT); criteriaHelper.equals(userRoot.get("username"), param.getUsername()); } } // Comments if (param.getCommentsType() != null) { Join<Message, Comment> comments = msgRoot.join("comments", JoinType.LEFT); User user = userService.currentUser(); Predicate own = user != null ? builder.equal(comments.get("user"), user) : null; Predicate exists = builder.isNotNull(comments.get("id")); Predicate unack = builder.isNull(comments.get("acknowledgedBy")); if (user != null && param.getCommentsType() == OWN) { criteriaHelper.add(own); } else if (user != null && param.getCommentsType() == OWN_UNACK) { criteriaHelper.add(builder.and(own, unack)); } else if (param.getCommentsType() == ANY_UNACK) { criteriaHelper.add(builder.and(exists, unack)); } else if (param.getCommentsType() == ANY) { criteriaHelper.add(exists); } } // Refenced messages if (StringUtils.isNotBlank(param.getMessageId())) { int levels = param.getReferenceLevels() == null ? 1 : param.getReferenceLevels(); // NB: This is expensive queries - limit the levels levels = Math.max(0, Math.min(5, levels)); // First, find messages referenced by the message ID Set<Integer> referencedIds = findReferencedMessageIds(new HashSet<>(), param.getMessageId(), levels); // Next, add messages referencing the message ID findReferencingMessageIds(referencedIds, param.getMessageId(), levels); criteriaHelper.in(msgRoot.get("id"), referencedIds); } // Determine the fields to fetch Join<Message, Area> areaRoot = null; Expression<?> treeSortOrder = null; List<Selection<?>> fields = new ArrayList<>(); fields.add(msgRoot.get("id")); if (param.sortByEventDate()) { fields.add(msgRoot.get("eventDateFrom")); fields.add(msgRoot.get("eventDateTo")); } else if (param.sortByPublishDate()) { fields.add(msgRoot.get("publishDateFrom")); fields.add(msgRoot.get("publishDateTo")); } else if (param.sortByFollowUpDate()) { fields.add(msgRoot.get("followUpDate")); } else if (param.sortById()) { fields.add(msgRoot.get("year")); fields.add(msgRoot.get("number")); fields.add(msgRoot.get("publishDateFrom")); } else if (param.sortByArea()) { areaRoot = msgRoot.join("area", JoinType.LEFT); // General messages (without an associated area) should be sorted last treeSortOrder = builder.selectCase().when(builder.isNull(areaRoot.get("treeSortOrder")), 999999) .otherwise(areaRoot.get("treeSortOrder")); fields.add(treeSortOrder); fields.add(msgRoot.get("areaSortOrder")); fields.add(msgRoot.get("year")); fields.add(msgRoot.get("number")); } Selection[] f = fields.toArray(new Selection<?>[fields.size()]); // Complete the query and fetch the message id's (and fields used for sorting) tupleQuery.multiselect(f).distinct(true).where(criteriaHelper.where()); // Sort the query if (param.sortByEventDate()) { if (param.getSortOrder() == SortOrder.ASC) { tupleQuery.orderBy(builder.asc(msgRoot.get("eventDateFrom")), builder.asc(msgRoot.get("eventDateTo")), builder.asc(msgRoot.get("id"))); } else { tupleQuery.orderBy(builder.desc(msgRoot.get("eventDateFrom")), builder.desc(msgRoot.get("eventDateTo")), builder.desc(msgRoot.get("id"))); } } else if (param.sortByPublishDate()) { if (param.getSortOrder() == SortOrder.ASC) { tupleQuery.orderBy(builder.asc(msgRoot.get("publishDateFrom")), builder.asc(msgRoot.get("publishDateTo")), builder.asc(msgRoot.get("id"))); } else { tupleQuery.orderBy(builder.desc(msgRoot.get("publishDateFrom")), builder.desc(msgRoot.get("publishDateTo")), builder.desc(msgRoot.get("id"))); } } else if (param.sortByFollowUpDate()) { if (param.getSortOrder() == SortOrder.ASC) { tupleQuery.orderBy(builder.asc(msgRoot.get("followUpDate")), builder.asc(msgRoot.get("id"))); } else { tupleQuery.orderBy(builder.desc(msgRoot.get("followUpDate")), builder.desc(msgRoot.get("id"))); } } else if (param.sortById()) { if (param.getSortOrder() == SortOrder.ASC) { tupleQuery.orderBy(builder.asc(msgRoot.get("year")), builder.asc(msgRoot.get("number")), builder.asc(msgRoot.get("publishDateFrom")), builder.asc(msgRoot.get("id"))); } else { tupleQuery.orderBy(builder.desc(msgRoot.get("year")), builder.desc(msgRoot.get("number")), builder.desc(msgRoot.get("publishDateFrom")), builder.desc(msgRoot.get("id"))); } } else if (param.sortByArea()) { if (param.getSortOrder() == SortOrder.ASC) { tupleQuery.orderBy(builder.asc(treeSortOrder), builder.asc(msgRoot.get("areaSortOrder")), builder.asc(msgRoot.get("year")), builder.asc(msgRoot.get("number")), builder.asc(msgRoot.get("id"))); } else { tupleQuery.orderBy(builder.desc(treeSortOrder), builder.desc(msgRoot.get("areaSortOrder")), builder.desc(msgRoot.get("year")), builder.desc(msgRoot.get("number")), builder.desc(msgRoot.get("id"))); } } // Execute the query List<Tuple> totalResult = em.createQuery(tupleQuery).getResultList(); // Register the total result result.setTotal(totalResult.size()); List<Integer> msgIds = totalResult.stream().map(t -> (Integer) t.get(0)).collect(Collectors.toList()); // Extract and return the paged sub-list int startIndex = Math.min(msgIds.size(), param.getPage() * param.getMaxSize()); int endIndex = Math.min(msgIds.size(), startIndex + param.getMaxSize()); return msgIds.subList(startIndex, endIndex); }