List of usage examples for javax.persistence.criteria CriteriaBuilder isNotNull
Predicate isNotNull(Expression<?> x);
From source file:org.broadleafcommerce.admin.server.service.handler.ISOCountryPersistenceHandler.java
@Override public DynamicResultSet fetch(PersistencePackage persistencePackage, CriteriaTransferObject cto, DynamicEntityDao dynamicEntityDao, RecordHelper helper) throws ServiceException { FilterMapping filterMapping = new FilterMapping().withFieldPath(new FieldPath().withTargetProperty("name")) .withDirectFilterValues(new EmptyFilterValues()).withRestriction( new Restriction().withPredicateProvider(new PredicateProvider<Character, Character>() { @Override public Predicate buildPredicate(CriteriaBuilder builder, FieldPathBuilder fieldPathBuilder, From root, String ceilingEntity, String fullPropertyName, Path<Character> explicitPath, List<Character> directValues) { return builder.isNotNull(explicitPath); }//from w ww.j a v a2s . c o m })); cto.getAdditionalFilterMappings().add(filterMapping); FilterMapping countryRestrictionMapping = new FilterMapping() .withDirectFilterValues(new EmptyFilterValues()).withRestriction( new Restriction().withPredicateProvider(new PredicateProvider<Character, Character>() { @Override public Predicate buildPredicate(CriteriaBuilder builder, FieldPathBuilder fieldPathBuilder, From root, String ceilingEntity, String fullPropertyName, Path<Character> explicitPath, List<Character> directValues) { CriteriaQuery<Serializable> criteria = fieldPathBuilder.getCriteria(); Root<CountryImpl> blcCountry = criteria.from(CountryImpl.class); Predicate join = builder.equal(root.get("alpha2").as(String.class), blcCountry.get("abbreviation").as(String.class)); return join; } })); cto.getAdditionalFilterMappings().add(countryRestrictionMapping); PersistenceModule myModule = helper.getCompatibleModule( persistencePackage.getPersistencePerspective().getOperationTypes().getFetchType()); return myModule.fetch(persistencePackage, cto); }
From source file:org.broadleafcommerce.admin.server.service.handler.SkuRestrictionFactoryImpl.java
protected Predicate buildCompositePredicate(CriteriaBuilder builder, Path targetPropertyPath, Path productPath, Predicate propertyExpression, Predicate defaultSkuExpression) { return builder.or( builder.or(builder.and(builder.isNotNull(targetPropertyPath), propertyExpression), builder.and(builder.and(builder.isNull(targetPropertyPath), builder.isNotNull(productPath)), defaultSkuExpression)), builder.and(builder.isNull(productPath), propertyExpression)); }
From source file:org.eclipse.jubula.client.core.persistence.TestDataCubePM.java
/** * @param tdc// w w w . ja v a 2s . c o m * the test data cube to search for reusage * @param session * The session into which the test cases will be loaded. * @return list of ITestDataCubePO */ @SuppressWarnings({ "rawtypes", "unchecked" }) public static List<ITestDataCubePO> computeReuser(IParameterInterfacePO tdc, EntityManager session) { CriteriaBuilder builder = session.getCriteriaBuilder(); CriteriaQuery query = builder.createQuery(); Root from = query.from(PoMaker.getTestDataCubeClass()); query.select(from).where(builder.isNotNull(from.get("hbmReferencedDataCube"))); //$NON-NLS-1$ List<ITestDataCubePO> queryResult = session.createQuery(query).getResultList(); List<ITestDataCubePO> result = new ArrayList<ITestDataCubePO>(); for (ITestDataCubePO pio : queryResult) { if (areEqual(pio.getReferencedDataCube(), tdc)) { result.add(pio); } } return result; }
From source file:org.finra.herd.dao.impl.BusinessObjectDataDaoImpl.java
/** * Creates a predicate for partition value filters. * * @param businessDataSearchKey businessDataSearchKey * @param businessObjectDataEntity businessObjectDataEntity * @param businessObjectFormatEntity businessObjectFormatEntity * @param builder builder// ww w . j av a 2s . c o m * @param predicatePram predicate parameter * * @return the predicate */ private Predicate createPartitionValueFilters(BusinessObjectDataSearchKey businessDataSearchKey, Root<BusinessObjectDataEntity> businessObjectDataEntity, Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> businessObjectFormatEntity, CriteriaBuilder builder, Predicate predicatePram) { Predicate predicate = predicatePram; if (businessDataSearchKey.getPartitionValueFilters() != null && !businessDataSearchKey.getPartitionValueFilters().isEmpty()) { for (PartitionValueFilter partitionFilter : businessDataSearchKey.getPartitionValueFilters()) { Join<BusinessObjectFormatEntity, SchemaColumnEntity> schemaEntity = businessObjectFormatEntity .join(BusinessObjectFormatEntity_.schemaColumns); List<String> partitionValues = partitionFilter.getPartitionValues(); predicate = builder.and(predicate, builder.equal(builder.upper(schemaEntity.get(SchemaColumnEntity_.name)), partitionFilter.getPartitionKey().toUpperCase())); predicate = builder.and(predicate, builder.isNotNull(schemaEntity.get(SchemaColumnEntity_.partitionLevel))); if (partitionValues != null && !partitionValues.isEmpty()) { predicate = builder.and(predicate, builder.or( builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 1), businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue) .in(partitionValues)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 2), businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue2) .in(partitionValues)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 3), businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue3) .in(partitionValues)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 4), businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue4) .in(partitionValues)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 5), businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue5) .in(partitionValues)))); } else if (partitionFilter.getPartitionValueRange() != null) { PartitionValueRange partitionRange = partitionFilter.getPartitionValueRange(); String startPartitionValue = partitionRange.getStartPartitionValue(); String endPartitionValue = partitionRange.getEndPartitionValue(); predicate = builder.and(predicate, builder.or( builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 1), builder.greaterThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue), startPartitionValue), builder.lessThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue), endPartitionValue)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 2), builder.greaterThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue2), startPartitionValue), builder.lessThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue2), endPartitionValue)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 3), builder.greaterThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue3), startPartitionValue), builder.lessThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue3), endPartitionValue)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 4), builder.greaterThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue4), startPartitionValue), builder.lessThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue4), endPartitionValue)), builder.and(builder.equal(schemaEntity.get(SchemaColumnEntity_.partitionLevel), 5), builder.greaterThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue5), startPartitionValue), builder.lessThanOrEqualTo( businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue5), endPartitionValue)))); } } } return predicate; }
From source file:org.finra.herd.dao.impl.BusinessObjectFormatDaoImpl.java
@Override public Long getBusinessObjectFormatCountByPartitionKeys(String namespace, String businessObjectDefinitionName, String businessObjectFormatUsage, String businessObjectFormatFileType, Integer businessObjectFormatVersion, List<String> partitionKeys) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); // The criteria root is the business object format. Root<BusinessObjectFormatEntity> businessObjectFormatEntityRoot = criteria .from(BusinessObjectFormatEntity.class); // Create path. Expression<Long> businessObjectFormatRecordCount = builder.count(businessObjectFormatEntityRoot); // Namespace is a required parameter, so fetch the relative entity to optimize the main query. NamespaceEntity namespaceEntity = namespaceDao.getNamespaceByCd(namespace); // If specified namespace does not exist, then return a zero record count. if (namespaceEntity == null) { return 0L; }/*from w w w . j a v a 2s . co m*/ // If file type is specified, fetch the relative entity to optimize the main query. FileTypeEntity fileTypeEntity = null; if (StringUtils.isNotBlank(businessObjectFormatFileType)) { fileTypeEntity = fileTypeDao.getFileTypeByCode(businessObjectFormatFileType); // If specified file type does not exist, then return a zero record count. if (fileTypeEntity == null) { return 0L; } } // Join to the other tables we can filter on. Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity = businessObjectFormatEntityRoot .join(BusinessObjectFormatEntity_.businessObjectDefinition); // Create main query restrictions based on the specified parameters. List<Predicate> predicates = new ArrayList<>(); // Create restriction on namespace code and business object definition name. predicates.add(builder.equal(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.namespace), namespaceEntity)); predicates.add(builder.equal( builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)), businessObjectDefinitionName.toUpperCase())); // If specified, create restriction on business object format usage. if (!StringUtils.isEmpty(businessObjectFormatUsage)) { predicates.add(builder.equal( builder.upper(businessObjectFormatEntityRoot.get(BusinessObjectFormatEntity_.usage)), businessObjectFormatUsage.toUpperCase())); } // If specified, create restriction on business object format file type. if (fileTypeEntity != null) { predicates .add(builder.equal(businessObjectFormatEntityRoot.get(BusinessObjectFormatEntity_.fileTypeCode), fileTypeEntity.getCode())); } // If specified, create restriction on business object format version. if (businessObjectFormatVersion != null) { predicates.add(builder.equal( businessObjectFormatEntityRoot.get(BusinessObjectFormatEntity_.businessObjectFormatVersion), businessObjectFormatVersion)); } // If specified, create restriction on partition keys. if (CollectionUtils.isNotEmpty(partitionKeys)) { for (String partitionKey : partitionKeys) { // Add restriction on partition key (partition column). // Partition key must identify a partition column that is at partition level that could be explicitly registered. // Partition level uses one-based numbering. Join<BusinessObjectFormatEntity, SchemaColumnEntity> schemaColumnEntityJoin = businessObjectFormatEntityRoot .join(BusinessObjectFormatEntity_.schemaColumns); predicates.add(builder.equal(builder.upper(schemaColumnEntityJoin.get(SchemaColumnEntity_.name)), partitionKey.toUpperCase())); predicates.add(builder.isNotNull(schemaColumnEntityJoin.get(SchemaColumnEntity_.partitionLevel))); predicates.add(builder.lessThan(schemaColumnEntityJoin.get(SchemaColumnEntity_.partitionLevel), BusinessObjectDataEntity.MAX_SUBPARTITIONS + 2)); } } // Add all clauses for the query. criteria.select(businessObjectFormatRecordCount) .where(builder.and(predicates.toArray(new Predicate[predicates.size()]))).distinct(true); // Execute the query and return the result. return entityManager.createQuery(criteria).getSingleResult(); }
From source file:org.finra.herd.dao.impl.TagDaoImpl.java
@Override public List<TagEntity> getTagsByTagTypeEntityAndParentTagCode(TagTypeEntity tagTypeEntity, String parentTagCode, Boolean isParentTagNull) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<TagEntity> criteria = builder.createQuery(TagEntity.class); // The criteria root is the tag entity. Root<TagEntity> tagEntityRoot = criteria.from(TagEntity.class); // Get the columns. Path<String> displayNameColumn = tagEntityRoot.get(TagEntity_.displayName); // Create the standard restrictions (i.e. the standard where clauses). List<Predicate> predicates = new ArrayList<>(); predicates.add(builder.equal(tagEntityRoot.get(TagEntity_.tagType), tagTypeEntity)); if (StringUtils.isNotBlank(parentTagCode)) { // Return all tags that are immediate children of the specified parent tag. predicates.add(builder.equal(/*from www . j a v a 2 s .c o m*/ builder.upper(tagEntityRoot.get(TagEntity_.parentTagEntity).get(TagEntity_.tagCode)), parentTagCode.toUpperCase())); } else if (BooleanUtils.isTrue(isParentTagNull)) { // The flag is non-null and true, return all tags with no parents, i.e. root tags. predicates.add(builder.isNull(tagEntityRoot.get(TagEntity_.parentTagEntity))); } else if (BooleanUtils.isFalse(isParentTagNull)) { // The flag is non-null and false, return all tags with parents. predicates.add(builder.isNotNull(tagEntityRoot.get(TagEntity_.parentTagEntity))); } // Add all clauses to the query. criteria.select(tagEntityRoot).where(builder.and(predicates.toArray(new Predicate[predicates.size()]))) .orderBy(builder.asc(displayNameColumn)); // Run the query to get the results. return entityManager.createQuery(criteria).getResultList(); }
From source file:org.jboss.pnc.datastore.predicates.rsql.RSQLNodeTravellerPredicate.java
public RSQLNodeTravellerPredicate(Class<Entity> entityClass, String rsql) throws RSQLParserException { operations.put(RSQLOperators.EQUAL, new AbstractTransformer<Entity>() { @Override/*www .j ava 2 s .c o m*/ Predicate transform(Root<Entity> r, Path<?> selectedPath, CriteriaBuilder cb, String operand, List<Object> convertedArguments) { return cb.equal(selectedPath, convertedArguments.get(0)); } }); operations.put(RSQLOperators.NOT_EQUAL, new AbstractTransformer<Entity>() { @Override Predicate transform(Root<Entity> r, Path<?> selectedPath, CriteriaBuilder cb, String operand, List<Object> convertedArguments) { return cb.notEqual(selectedPath, convertedArguments.get(0)); } }); operations.put(RSQLOperators.GREATER_THAN, (r, cb, clazz, operand, arguments) -> cb .greaterThan((Path) selectWithOperand(r, operand, clazz), arguments.get(0))); operations.put(RSQLOperators.GREATER_THAN_OR_EQUAL, (r, cb, clazz, operand, arguments) -> cb .greaterThanOrEqualTo((Path) selectWithOperand(r, operand, clazz), arguments.get(0))); operations.put(RSQLOperators.LESS_THAN, (r, cb, clazz, operand, arguments) -> cb .lessThan((Path) selectWithOperand(r, operand, clazz), arguments.get(0))); operations.put(RSQLOperators.LESS_THAN_OR_EQUAL, (r, cb, clazz, operand, arguments) -> cb .lessThanOrEqualTo((Path) selectWithOperand(r, operand, clazz), arguments.get(0))); operations.put(RSQLOperators.IN, (r, cb, clazz, operand, arguments) -> ((Path) selectWithOperand(r, operand, clazz)).in(arguments)); operations.put(RSQLOperators.NOT_IN, (r, cb, clazz, operand, arguments) -> cb .not(((Path) selectWithOperand(r, operand, clazz)).in(arguments))); operations.put(LIKE, (r, cb, clazz, operand, arguments) -> cb.like(cb.lower((Path) selectWithOperand(r, operand, clazz)), preprocessLikeOperatorArgument(arguments.get(0).toLowerCase()))); operations.put(IS_NULL, (r, cb, clazz, operand, arguments) -> { if (Boolean.parseBoolean(arguments.get(0))) { return cb.isNull((Path) selectWithOperand(r, operand, clazz)); } else { return cb.isNotNull((Path) selectWithOperand(r, operand, clazz)); } }); Set<ComparisonOperator> operators = RSQLOperators.defaultOperators(); operators.add(LIKE); operators.add(IS_NULL); rootNode = new RSQLParser(operators).parse(preprocessRSQL(rsql)); selectingClass = entityClass; }
From source file:org.medici.bia.dao.image.ImageDAOJpaImpl.java
/** * {@inheritDoc}/* www. ja v a2 s .c o m*/ */ //@SuppressWarnings("unchecked") public List<VolumeInsert> findVolumeInserts(Integer volNum, String volLetExt) { CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder(); ParameterExpression<Integer> parameterVolNum = criteriaBuilder.parameter(Integer.class, "volNum"); ParameterExpression<String> parameterVolLeText = StringUtils.isEmpty("volLetExt") ? null : criteriaBuilder.parameter(String.class, "volLetExt"); CriteriaQuery<VolumeInsert> cq = criteriaBuilder.createQuery(VolumeInsert.class); Root<Image> root = cq.from(Image.class); cq.select(criteriaBuilder.construct(VolumeInsert.class, root.get("volNum"), root.get("volLetExt"), root.get("insertNum"), root.get("insertLet"))).distinct(true); cq.where(criteriaBuilder.and(criteriaBuilder.equal(root.get("volNum"), parameterVolNum), StringUtils.isEmpty(volLetExt) ? criteriaBuilder.isNull(root.get("volLetExt")) : criteriaBuilder.equal(root.get("volLetExt"), parameterVolLeText), criteriaBuilder.isNotNull(root.get("insertNum")))); cq.orderBy(criteriaBuilder.asc(root.get("insertNum")), criteriaBuilder.asc(root.get("insertLet"))); /* The above query is equivalent to the following: * SELECT DISTINCT volNum, volLetExt, insertNum, insertLet FROM Image * WHERE * volNum = :volNum * AND volLetExt (<nullable> ? 'IS NULL' : ':volLetExt') * AND insertNum IS NOT NULL * ORDER BY * insertNum ASC, * insertLet ASC */ TypedQuery<VolumeInsert> tq = getEntityManager().createQuery(cq); tq.setParameter("volNum", volNum); if (!StringUtils.isEmpty(volLetExt)) tq.setParameter("volLetExt", volLetExt); return tq.getResultList(); }
From source file:org.niord.core.area.AreaService.java
/** * Searches for areas matching the given search params * * @param params the sesarch params/*from w w w . j av a 2 s .c om*/ * @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.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.ja v a2s.c o m * @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); }