List of usage examples for javax.persistence.criteria CriteriaBuilder desc
Order desc(Expression<?> x);
From source file:org.finra.herd.dao.impl.BusinessObjectDataDaoImpl.java
@Override public List<BusinessObjectDataKey> getBusinessObjectDataByBusinessObjectFormat( BusinessObjectFormatEntity businessObjectFormatEntity, Integer maxResults) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<BusinessObjectDataEntity> criteria = builder.createQuery(BusinessObjectDataEntity.class); // The criteria root is the business object data. Root<BusinessObjectDataEntity> businessObjectDataEntityRoot = criteria.from(BusinessObjectDataEntity.class); // Create the standard restrictions (i.e. the standard where clauses). Predicate predicate = builder.equal( businessObjectDataEntityRoot.get(BusinessObjectDataEntity_.businessObjectFormat), businessObjectFormatEntity); // Build the order by clause. The sort order is consistent with the search business object data implementation. List<Order> orderBy = new ArrayList<>(); for (SingularAttribute<BusinessObjectDataEntity, String> businessObjectDataPartition : BUSINESS_OBJECT_DATA_PARTITIONS) { orderBy.add(builder.desc(businessObjectDataEntityRoot.get(businessObjectDataPartition))); }//w w w . j a v a 2s. c o m orderBy.add(builder.desc(businessObjectDataEntityRoot.get(BusinessObjectDataEntity_.version))); // Add the clauses for the query. criteria.select(businessObjectDataEntityRoot).where(predicate).orderBy(orderBy); // Create a query. TypedQuery<BusinessObjectDataEntity> query = entityManager.createQuery(criteria); // If specified, set the maximum number of results for query to return. if (maxResults != null) { query.setMaxResults(maxResults); } // Run the query to get a list of entities back. List<BusinessObjectDataEntity> businessObjectDataEntities = query.getResultList(); // Populate the "keys" objects from the returned entities. List<BusinessObjectDataKey> businessObjectDataKeys = new ArrayList<>(); for (BusinessObjectDataEntity businessObjectDataEntity : businessObjectDataEntities) { BusinessObjectDataKey businessObjectDataKey = new BusinessObjectDataKey(); businessObjectDataKeys.add(businessObjectDataKey); businessObjectDataKey.setNamespace(businessObjectDataEntity.getBusinessObjectFormat() .getBusinessObjectDefinition().getNamespace().getCode()); businessObjectDataKey.setBusinessObjectDefinitionName( businessObjectDataEntity.getBusinessObjectFormat().getBusinessObjectDefinition().getName()); businessObjectDataKey .setBusinessObjectFormatUsage(businessObjectDataEntity.getBusinessObjectFormat().getUsage()); businessObjectDataKey.setBusinessObjectFormatFileType( businessObjectDataEntity.getBusinessObjectFormat().getFileType().getCode()); businessObjectDataKey.setBusinessObjectFormatVersion( businessObjectDataEntity.getBusinessObjectFormat().getBusinessObjectFormatVersion()); businessObjectDataKey.setPartitionValue(businessObjectDataEntity.getPartitionValue()); businessObjectDataKey.setSubPartitionValues(getSubPartitionValues(businessObjectDataEntity)); businessObjectDataKey.setBusinessObjectDataVersion(businessObjectDataEntity.getVersion()); } return businessObjectDataKeys; }
From source file:org.finra.herd.dao.impl.BusinessObjectDefinitionDaoImpl.java
@Override public List<BusinessObjectDefinitionEntity> getMostRecentBusinessObjectDefinitions(int numberOfResults) { // Create the criteria builder and a tuple style criteria query. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<BusinessObjectDefinitionEntity> criteria = builder .createQuery(BusinessObjectDefinitionEntity.class); // The criteria root is the business object definition. Root<BusinessObjectDefinitionEntity> businessObjectDefinitionEntityRoot = criteria .from(BusinessObjectDefinitionEntity.class); // Get the columns. Path<Timestamp> businessObjectDefinitionUpdatedOnColumn = businessObjectDefinitionEntityRoot .get(BusinessObjectDefinitionEntity_.updatedOn); // Select the business object definitions and order descending by the updated on column criteria.select(businessObjectDefinitionEntityRoot) .orderBy(builder.desc(businessObjectDefinitionUpdatedOnColumn)); return entityManager.createQuery(criteria).setMaxResults(numberOfResults).getResultList(); }
From source file:org.finra.herd.dao.impl.ExpectedPartitionValueDaoImpl.java
@Override public ExpectedPartitionValueEntity getExpectedPartitionValue( ExpectedPartitionValueKey expectedPartitionValueKey, int offset) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<ExpectedPartitionValueEntity> criteria = builder .createQuery(ExpectedPartitionValueEntity.class); // The criteria root is the expected partition value. Root<ExpectedPartitionValueEntity> expectedPartitionValueEntity = criteria .from(ExpectedPartitionValueEntity.class); // Join to the other tables we can filter on. Join<ExpectedPartitionValueEntity, PartitionKeyGroupEntity> partitionKeyGroupEntity = expectedPartitionValueEntity .join(ExpectedPartitionValueEntity_.partitionKeyGroup); // Add a restriction to filter case insensitive groups that match the user specified group. Predicate whereRestriction = builder.equal( builder.upper(partitionKeyGroupEntity.get(PartitionKeyGroupEntity_.partitionKeyGroupName)), expectedPartitionValueKey.getPartitionKeyGroupName().toUpperCase()); // Depending on the offset, we might need to order the records in the query. Order orderByExpectedPartitionValue = null; // Add additional restrictions to handle expected partition value and an optional offset. if (offset == 0) { // Since there is no offset, we need to match the expected partition value exactly. whereRestriction = builder.and(whereRestriction, builder.equal(expectedPartitionValueEntity.get(ExpectedPartitionValueEntity_.partitionValue), expectedPartitionValueKey.getExpectedPartitionValue())); } else if (offset > 0) { // For a positive offset value, add a restriction to filter expected partition values that are >= the user specified expected partition value. whereRestriction = builder.and(whereRestriction, builder.greaterThanOrEqualTo( expectedPartitionValueEntity.get(ExpectedPartitionValueEntity_.partitionValue), expectedPartitionValueKey.getExpectedPartitionValue())); // Order by expected partition value in ascending order. orderByExpectedPartitionValue = builder .asc(expectedPartitionValueEntity.get(ExpectedPartitionValueEntity_.partitionValue)); } else {/*from www .ja v a 2 s. c o m*/ // For a negative offset value, add a restriction to filter expected partition values that are <= the user specified expected partition value. whereRestriction = builder.and(whereRestriction, builder.lessThanOrEqualTo( expectedPartitionValueEntity.get(ExpectedPartitionValueEntity_.partitionValue), expectedPartitionValueKey.getExpectedPartitionValue())); // Order by expected partition value in descending order. orderByExpectedPartitionValue = builder .desc(expectedPartitionValueEntity.get(ExpectedPartitionValueEntity_.partitionValue)); } // Add the clauses for the query and execute the query. if (offset == 0) { criteria.select(expectedPartitionValueEntity).where(whereRestriction); return executeSingleResultQuery(criteria, String.format( "Found more than one expected partition value with parameters {partitionKeyGroupName=\"%s\", expectedPartitionValue=\"%s\"}.", expectedPartitionValueKey.getPartitionKeyGroupName(), expectedPartitionValueKey.getExpectedPartitionValue())); } else { criteria.select(expectedPartitionValueEntity).where(whereRestriction) .orderBy(orderByExpectedPartitionValue); List<ExpectedPartitionValueEntity> resultList = entityManager.createQuery(criteria) .setFirstResult(Math.abs(offset)).setMaxResults(1).getResultList(); return resultList.size() > 0 ? resultList.get(0) : null; } }
From source file:org.finra.herd.dao.impl.TagDaoImpl.java
@Override public List<TagEntity> getMostRecentTags(int numberOfResults) { // Create the criteria builder and a tuple style criteria query. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<TagEntity> criteria = builder.createQuery(TagEntity.class); // The criteria root is the tag. Root<TagEntity> tagEntityRoot = criteria.from(TagEntity.class); // Get the columns. Path<Timestamp> tagUpdatedOnColumn = tagEntityRoot.get(TagEntity_.updatedOn); // Select the tags and order descending by the updated on column criteria.select(tagEntityRoot).orderBy(builder.desc(tagUpdatedOnColumn)); return entityManager.createQuery(criteria).setMaxResults(numberOfResults).getResultList(); }
From source file:org.jasig.portal.events.aggr.JpaBaseAggregationDao.java
@Override public final void afterPropertiesSet() throws Exception { this.timeDimensionParameter = this.createParameterExpression(TimeDimension.class, "timeDimension"); this.dateDimensionParameter = this.createParameterExpression(DateDimension.class, "dateDimension"); this.intervalParameter = this.createParameterExpression(AggregationInterval.class, "interval"); this.aggregatedGroupParameter = this.createParameterExpression(AggregatedGroupMapping.class, "aggregatedGroup"); this.aggregatedGroupsParameter = this.createParameterExpression(Set.class, "aggregatedGroups"); this.startDate = this.createParameterExpression(LocalDate.class, "startDate"); this.endMinusOneDate = this.createParameterExpression(LocalDate.class, "endMinusOneDate"); this.endDate = this.createParameterExpression(LocalDate.class, "endDate"); this.startTime = this.createParameterExpression(LocalTime.class, "startTime"); this.endTime = this.createParameterExpression(LocalTime.class, "endTime"); this.createParameterExpressions(); this.findAggregationByDateTimeIntervalQuery = this .createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<T>>() { @Override// ww w . j a v a 2s . com public CriteriaQuery<T> apply(CriteriaBuilder cb) { final CriteriaQuery<T> criteriaQuery = cb.createQuery(aggregationEntityType); final Root<T> ba = criteriaQuery.from(aggregationEntityType); addFetches(ba); criteriaQuery.select(ba); criteriaQuery.where( cb.equal(ba.get(BaseAggregationImpl_.dateDimension), dateDimensionParameter), cb.equal(ba.get(BaseAggregationImpl_.timeDimension), timeDimensionParameter), cb.equal(ba.get(BaseAggregationImpl_.interval), intervalParameter)); return criteriaQuery; } }); this.findAggregationByDateTimeIntervalGroupQuery = this .createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<T>>() { @Override public CriteriaQuery<T> apply(CriteriaBuilder cb) { final CriteriaQuery<T> criteriaQuery = cb.createQuery(aggregationEntityType); final Root<T> ba = criteriaQuery.from(aggregationEntityType); final List<Predicate> keyPredicates = new ArrayList<Predicate>(); keyPredicates .add(cb.equal(ba.get(BaseAggregationImpl_.dateDimension), dateDimensionParameter)); keyPredicates .add(cb.equal(ba.get(BaseAggregationImpl_.timeDimension), timeDimensionParameter)); keyPredicates.add(cb.equal(ba.get(BaseAggregationImpl_.interval), intervalParameter)); keyPredicates.add( cb.equal(ba.get(BaseAggregationImpl_.aggregatedGroup), aggregatedGroupParameter)); addAggregationSpecificKeyPredicate(cb, ba, keyPredicates); criteriaQuery.select(ba); criteriaQuery.where(keyPredicates.toArray(new Predicate[keyPredicates.size()])); return criteriaQuery; } }); this.findAggregationsByDateRangeQuery = this .createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<T>>() { @Override public CriteriaQuery<T> apply(CriteriaBuilder cb) { final CriteriaQuery<T> criteriaQuery = cb.createQuery(aggregationEntityType); final Root<T> ba = criteriaQuery.from(aggregationEntityType); final Join<T, DateDimensionImpl> dd = ba.join(BaseAggregationImpl_.dateDimension, JoinType.LEFT); final Join<T, TimeDimensionImpl> td = ba.join(BaseAggregationImpl_.timeDimension, JoinType.LEFT); final List<Predicate> keyPredicates = new ArrayList<Predicate>(); keyPredicates.add(cb.and( //Restrict results by outer date range cb.greaterThanOrEqualTo(dd.get(DateDimensionImpl_.date), startDate), cb.lessThan(dd.get(DateDimensionImpl_.date), endDate))); keyPredicates.add(cb.or( //Restrict start of range by time as well cb.greaterThan(dd.get(DateDimensionImpl_.date), startDate), cb.greaterThanOrEqualTo(td.get(TimeDimensionImpl_.time), startTime))); keyPredicates.add(cb.or( //Restrict end of range by time as well cb.lessThan(dd.get(DateDimensionImpl_.date), endMinusOneDate), cb.lessThan(td.get(TimeDimensionImpl_.time), endTime))); keyPredicates.add(cb.equal(ba.get(BaseAggregationImpl_.interval), intervalParameter)); keyPredicates .add(ba.get(BaseAggregationImpl_.aggregatedGroup).in(aggregatedGroupsParameter)); addAggregationSpecificKeyPredicate(cb, ba, keyPredicates); criteriaQuery.select(ba); criteriaQuery.where(keyPredicates.toArray(new Predicate[keyPredicates.size()])); criteriaQuery.orderBy(cb.desc(dd.get(DateDimensionImpl_.date)), cb.desc(td.get(TimeDimensionImpl_.time))); return criteriaQuery; } }); /* * Similar to the previous query but only returns aggregates that also match the unclosed predicate generated * by the subclass. This is used for finding aggregates that missed having intervalComplete called due to * interval boundary placement. */ this.findUnclosedAggregationsByDateRangeQuery = this .createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<T>>() { @Override public CriteriaQuery<T> apply(CriteriaBuilder cb) { final CriteriaQuery<T> criteriaQuery = cb.createQuery(aggregationEntityType); final Root<T> ba = criteriaQuery.from(aggregationEntityType); final Join<T, DateDimensionImpl> dd = ba.join(BaseAggregationImpl_.dateDimension, JoinType.LEFT); final Join<T, TimeDimensionImpl> td = ba.join(BaseAggregationImpl_.timeDimension, JoinType.LEFT); addFetches(ba); final List<Predicate> keyPredicates = new ArrayList<Predicate>(); keyPredicates.add(cb.and( //Restrict results by outer date range cb.greaterThanOrEqualTo(dd.get(DateDimensionImpl_.date), startDate), cb.lessThan(dd.get(DateDimensionImpl_.date), endDate))); keyPredicates.add(cb.or( //Restrict start of range by time as well cb.greaterThan(dd.get(DateDimensionImpl_.date), startDate), cb.greaterThanOrEqualTo(td.get(TimeDimensionImpl_.time), startTime))); keyPredicates.add(cb.or( //Restrict end of range by time as well cb.lessThan(dd.get(DateDimensionImpl_.date), endMinusOneDate), cb.lessThan(td.get(TimeDimensionImpl_.time), endTime))); keyPredicates.add(cb.equal(ba.get(BaseAggregationImpl_.interval), intervalParameter)); //No aggregation specific key bits here, we only have start/end/interval parameters to work with addUnclosedPredicate(cb, ba, keyPredicates); criteriaQuery.select(ba); criteriaQuery.where(keyPredicates.toArray(new Predicate[keyPredicates.size()])); return criteriaQuery; } }); this.createCriteriaQueries(); }
From source file:org.niord.core.area.AreaService.java
/** * Searches for areas matching the given search params * * @param params the sesarch params// ww w . ja v a 2 s .c o m * @return the search result */ @SuppressWarnings("all") public List<Area> searchAreas(AreaSearchParams params) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Area> areaQuery = cb.createQuery(Area.class); Root<Area> areaRoot = areaQuery.from(Area.class); // Build the predicate CriteriaHelper<Area> criteriaHelper = new CriteriaHelper<>(cb, areaQuery); // Match the name Join<Area, AreaDesc> descs = areaRoot.join("descs", JoinType.LEFT); if (params.isExact()) { criteriaHelper.equalsIgnoreCase(descs.get("name"), params.getName()); } else { criteriaHelper.like(descs.get("name"), params.getName()); } // Optionally, match the language if (StringUtils.isNotBlank(params.getLanguage())) { criteriaHelper.equals(descs.get("lang"), params.getLanguage()); } // Optionally, match the parent if (params.getParentId() != null) { areaRoot.join("parent", JoinType.LEFT); Path<Area> parent = areaRoot.get("parent"); criteriaHelper.equals(parent.get("id"), params.getParentId()); } // Assemple lineage filters from search domain and areas Set<String> lineages = new HashSet<>(); // Optionally, filter by the areas associated with the specified domain if (StringUtils.isNotBlank(params.getDomain())) { Domain d = domainService.findByDomainId(params.getDomain()); if (d != null && d.getAreas().size() > 0) { d.getAreas().forEach(a -> lineages.add(a.getLineage())); } } // Optionally, filter by area subtrees if (params.getAreaIds() != null && !params.getAreaIds().isEmpty()) { getAreaDetails(params.getAreaIds()).forEach(a -> lineages.add(a.getLineage())); } // If defined, apply the area lineage filter if (!lineages.isEmpty()) { Predicate[] areaMatch = lineages.stream() .map(lineage -> cb.like(areaRoot.get("lineage"), lineage + "%")).toArray(Predicate[]::new); criteriaHelper.add(cb.or(areaMatch)); } // Optionally, search by type if (params.getType() != null) { criteriaHelper.add(cb.equal(areaRoot.get("type"), params.getType())); } // Optionally, require that the area has an associated geometry if (params.isGeometry()) { criteriaHelper.add(cb.isNotNull(areaRoot.get("geometry"))); } // Optionally, require that the area has an messageSorting type if (params.isMessageSorting()) { criteriaHelper.add(cb.isNotNull(areaRoot.get("messageSorting"))); } // Unless the "inactive" search flag is set, only include active areas. if (!params.isInactive()) { criteriaHelper.add(cb.equal(areaRoot.get("active"), true)); } // Compute the sort order List<Order> sortOrders = new ArrayList<>(); if (TREE_SORT_ORDER.equals(params.getSortBy())) { Arrays.asList("treeSortOrder", "siblingSortOrder", "id").forEach(field -> { if (params.getSortOrder() == PagedSearchParamsVo.SortOrder.ASC) { sortOrders.add(cb.asc(areaRoot.get(field))); } else { sortOrders.add(cb.desc(areaRoot.get(field))); } }); } // Complete the query areaQuery.select(areaRoot).distinct(true).where(criteriaHelper.where()).orderBy(sortOrders); // Execute the query and update the search result return em.createQuery(areaQuery).setMaxResults(params.getMaxSize()).getResultList(); }
From source file:org.niord.core.mail.ScheduledMailService.java
/** * Searches the filtered set of scheduled mails * @param params the search parameters//ww w .ja v a2 s . c om * @return the search result */ public PagedSearchResultVo<ScheduledMail> search(ScheduledMailSearchParams params) { long t0 = System.currentTimeMillis(); PagedSearchResultVo<ScheduledMail> result = new PagedSearchResultVo<>(); CriteriaBuilder cb = em.getCriteriaBuilder(); // First compute the total number of matching mails CriteriaQuery<Long> countQuery = cb.createQuery(Long.class); Root<ScheduledMail> countMailRoot = countQuery.from(ScheduledMail.class); countQuery.select(cb.count(countMailRoot)) .where(buildQueryPredicates(cb, countQuery, countMailRoot, params)) .orderBy(cb.desc(countMailRoot.get("created"))); result.setTotal(em.createQuery(countQuery).getSingleResult()); // Then, extract the current page of matches CriteriaQuery<ScheduledMail> query = cb.createQuery(ScheduledMail.class); Root<ScheduledMail> mailRoot = query.from(ScheduledMail.class); query.select(mailRoot).where(buildQueryPredicates(cb, query, mailRoot, params)) .orderBy(cb.desc(countMailRoot.get("created"))); List<ScheduledMail> mails = em.createQuery(query).setMaxResults(params.getMaxSize()) .setFirstResult(params.getPage() * params.getMaxSize()).getResultList(); result.setData(mails); result.updateSize(); log.info("Search [" + params + "] returned " + result.getSize() + " of " + result.getTotal() + " in " + (System.currentTimeMillis() - t0) + " ms"); return result; }
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 ww w .java2s .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); }
From source file:org.niord.core.message.MessageTagService.java
/** * Searches for message tags matching the given search parameters * * @param params the search parameters/* ww w . jav a2 s. co m*/ * @return the search result */ @SuppressWarnings("ResultOfMethodCallIgnored") public List<MessageTag> searchMessageTags(MessageTagSearchParams params) { User user = userService.currentUser(); Domain domain = domainService.currentDomain(); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<MessageTag> query = cb.createQuery(MessageTag.class); Root<MessageTag> tagRoot = query.from(MessageTag.class); // Build the predicate CriteriaHelper<MessageTag> criteriaHelper = new CriteriaHelper<>(cb, query); // Name filtering criteriaHelper.like(tagRoot.get("name"), params.getName()); // Locked filtering criteriaHelper.equals(tagRoot.get("locked"), params.getLocked()); // Type filtering Set<MessageTagType> types = params.getTypes() != null ? params.getTypes() : new HashSet<>(); if (types.isEmpty()) { types.add(PUBLIC); types.add(DOMAIN); types.add(PRIVATE); } List<Predicate> typePredicates = new LinkedList<>(); if (types.contains(PUBLIC)) { typePredicates.add(cb.equal(tagRoot.get("type"), MessageTagType.PUBLIC)); } if (types.contains(DOMAIN) && domain != null) { Join<MessageTag, Domain> domains = tagRoot.join("domain", JoinType.LEFT); typePredicates.add(cb.and(cb.equal(tagRoot.get("type"), MessageTagType.DOMAIN), cb.equal(domains.get("id"), domain.getId()))); } if (types.contains(PRIVATE) && user != null) { Join<MessageTag, User> users = tagRoot.join("user", JoinType.LEFT); typePredicates.add(cb.and(cb.equal(tagRoot.get("type"), MessageTagType.PRIVATE), cb.equal(users.get("id"), user.getId()))); } if (types.contains(TEMP)) { typePredicates.add(cb.equal(tagRoot.get("type"), MessageTagType.TEMP)); } criteriaHelper.add(cb.or(typePredicates.toArray(new Predicate[typePredicates.size()]))); // Compute the sorting List<Order> sortOrders = new ArrayList<>(); Order nameAscSortOrder = cb.asc(cb.lower(tagRoot.get("name"))); if (params.sortByType()) { Expression sortBy = tagRoot.get("type"); sortOrders.add(params.getSortOrder() == DESC ? cb.desc(sortBy) : cb.asc(sortBy)); sortOrders.add(nameAscSortOrder); } else if (params.sortByCreated()) { Expression sortBy = tagRoot.get("created"); sortOrders.add(params.getSortOrder() == DESC ? cb.desc(sortBy) : cb.asc(sortBy)); sortOrders.add(nameAscSortOrder); } else if (params.sortByExpiryDate()) { Expression sortBy = tagRoot.get("expiryDate"); sortOrders.add(params.getSortOrder() == DESC ? cb.desc(sortBy) : cb.asc(sortBy)); sortOrders.add(nameAscSortOrder); } else if (params.sortByMessageCount()) { Expression sortBy = tagRoot.get("messageCount"); sortOrders.add(params.getSortOrder() == DESC ? cb.desc(sortBy) : cb.asc(sortBy)); sortOrders.add(nameAscSortOrder); } else { if (StringUtils.isNotBlank(params.getName())) { sortOrders.add(cb.asc(cb.locate(cb.lower(tagRoot.get("name")), params.getName().toLowerCase()))); } String name = StringUtils.defaultIfBlank(params.getName(), ""); Expression sortBy = cb.lower(tagRoot.get("name")); sortOrders.add(params.getSortOrder() == DESC ? cb.desc(sortBy) : cb.asc(sortBy)); } // Complete the query query.select(tagRoot).distinct(true).where(criteriaHelper.where()).orderBy(sortOrders); // Execute the query and update the search result return em.createQuery(query).setMaxResults(params.getMaxSize()).getResultList(); }
From source file:org.niord.core.publication.PublicationService.java
/** * Searches for a page of publication IDs matching the given search parameters * Will also update the search result with the total number of matches * * @param params the search parameters//from w ww. jav a 2 s . c o m * @param result the result structure to update with the total number of matches * @return a page of publication IDs matching the given search parameters */ @SuppressWarnings("all") public List<Integer> searchPagedPublicationIds(PublicationSearchParams params, PagedSearchResultVo<Publication> result) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Tuple> tupleQuery = cb.createTupleQuery(); Root<Publication> publicationRoot = tupleQuery.from(Publication.class); // Build the predicate CriteriaHelper<Tuple> criteriaHelper = CriteriaHelper.initWithTupleQuery(em); // Match the main type criteriaHelper.equals(publicationRoot.get("mainType"), params.getMainType()); // Match the file type criteriaHelper.equals(publicationRoot.get("type"), params.getType()); // Match the status if (params.getStatuses() != null && !params.getStatuses().isEmpty()) { criteriaHelper.in(publicationRoot.get("status"), params.getStatuses()); } // Match the title if (StringUtils.isNotBlank(params.getTitle())) { Join<Publication, PublicationDesc> descs = publicationRoot.join("descs", JoinType.LEFT); criteriaHelper.like(descs.get("title"), params.getTitle()); // Optionally, match the language if (StringUtils.isNotBlank(params.getLanguage())) { criteriaHelper.equals(descs.get("lang"), params.getLanguage()); } } // Match the category Join<Publication, PublicationCategory> typeJoin = publicationRoot.join("category", JoinType.LEFT); if (StringUtils.isNotBlank(params.getCategory())) { criteriaHelper.equals(typeJoin.get("categoryId"), params.getCategory()); } // Match the publish flag of the category if (params.getPublished() != null) { criteriaHelper.equals(typeJoin.get("publish"), params.getPublished()); } // Match the domain if (StringUtils.isNotBlank(params.getDomain())) { Join<Publication, Domain> domainJoin = publicationRoot.join("domain", JoinType.LEFT); criteriaHelper.add(cb.or(cb.isNull(publicationRoot.get("domain")), cb.equal(domainJoin.get("domainId"), params.getDomain()))); } // Match the message publication category criteriaHelper.equals(publicationRoot.get("messagePublication"), params.getMessagePublication()); // Filter by dates if (params.getFrom() != null || params.getTo() != null) { Date from = params.getFrom(); //TimeUtils.resetTime(params.getFrom()); Date to = params.getTo(); //TimeUtils.endOfDay(params.getTo()); criteriaHelper.overlaps(publicationRoot.get("publishDateFrom"), publicationRoot.get("publishDateTo"), from, to); } // Compute the sort order List<Order> sortOrders = new ArrayList<>(); sortOrders.add(cb.asc(typeJoin.get("priority"))); sortOrders.add(cb.desc(publicationRoot.get("publishDateFrom"))); // Select ID field + fields used for sorting List<Selection<?>> fields = new ArrayList<>(); fields.add(publicationRoot.get("id")); fields.add(typeJoin.get("priority")); fields.add(publicationRoot.get("publishDateFrom")); Selection[] f = fields.toArray(new Selection<?>[fields.size()]); // Complete the query tupleQuery.multiselect(f).distinct(true).where(criteriaHelper.where()).orderBy(sortOrders); // Execute the query List<Tuple> totalResult = em.createQuery(tupleQuery).getResultList(); // Register the total result result.setTotal(totalResult.size()); List<Integer> publicationIds = totalResult.stream().map(t -> (Integer) t.get(0)) .collect(Collectors.toList()); // Extract and return the paged sub-list int startIndex = Math.min(publicationIds.size(), params.getPage() * params.getMaxSize()); int endIndex = Math.min(publicationIds.size(), startIndex + params.getMaxSize()); return publicationIds.subList(startIndex, endIndex); }