Java Code Examples for javax.persistence.criteria.Root#join()

private TypedQuery<Long> createCountGroupsQuery(String search) {
    // we query ids only as the group  might be cached and following the @ManyToOne will result in a load
    // even if we're getting just the id.
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Long> queryBuilder = builder.createQuery(Long.class);
    Root<UserGroupMembershipEntity> root = queryBuilder.from(UserGroupMembershipEntity.class);

    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(root.get("user"), getEntity()));
    if (Objects.nonNull(search) && !search.isEmpty()) {
        Join<UserGroupMembershipEntity, GroupEntity> join = root.join("group");
        predicates.add(builder.like(join.get("name"), builder.literal("%" + search + "%")));
    }

    queryBuilder.select(builder.count(root));
    queryBuilder.where(predicates.toArray(new Predicate[0]));
    return em.createQuery(queryBuilder);
}
 

@Override
public Predicate getPredicate(Root<Build> root, CriteriaBuilder builder) {
	Join<?, ?> join = root.join(Build.PROP_VERIFICATIONS, JoinType.LEFT);
	join.on(builder.and(
			builder.equal(join.get(PullRequestVerification.PROP_REQUEST), request), 
			builder.equal(join.get(PullRequestVerification.PROP_REQUIRED), true)));
	return builder.equal(root.get(Build.PROP_PROJECT), request.getTargetProject());
}
 

public static Specification<User> byKeyword(String keyword, String role, String active){
    return (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {

        List<Predicate> predicates = new ArrayList<>();

        if (StringUtils.hasText(keyword)) {
            predicates.add(
                cb.or(
                    cb.like(root.get(User_.email), "%" + keyword + "%"),
                    cb.like(root.get(User_.username), "%" + keyword + "%")
                ));
        }

        if (StringUtils.hasText(role) && !"ALL".equals(role)) {

            ListJoin<User, String> roleJoin = root.join(User_.roles);
            predicates.add(cb.equal(roleJoin, role));
        }
        if (StringUtils.hasText(active)) {
            predicates.add(cb.equal(root.get(User_.active), Boolean.valueOf(active)));
        }
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}
 

@Override
public StorageUnitEntity getStorageUnitByBusinessObjectDataAndStorageName(BusinessObjectDataEntity businessObjectDataEntity, String storageName)
{
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageUnitEntity> criteria = builder.createQuery(StorageUnitEntity.class);
    Root<StorageUnitEntity> storageUnitEntity = criteria.from(StorageUnitEntity.class);

    // join storage unit to storage to retrieve name
    Join<StorageUnitEntity, StorageEntity> storageEntity = storageUnitEntity.join(StorageUnitEntity_.storage);

    // where business object data equals
    Predicate businessObjectDataRestriction = builder.equal(storageUnitEntity.get(StorageUnitEntity_.businessObjectData), businessObjectDataEntity);
    // where storage name equals, ignoring case
    Predicate storageNameRestriction = builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageName.toUpperCase());

    criteria.select(storageUnitEntity).where(builder.and(businessObjectDataRestriction, storageNameRestriction));

    List<StorageUnitEntity> resultList = entityManager.createQuery(criteria).getResultList();

    // return single result or null
    return resultList.size() >= 1 ? resultList.get(0) : null;
}
 

@Decision(EXISTS)
public boolean exists(Parameters params, RestContext context, EntityManager em) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Assignment> query = cb.createQuery(Assignment.class);
    Root<Assignment> root = query.from(Assignment.class);
    Join<Assignment, Group> groupJoin = root.join("group");
    Join<Assignment, Role> roleJoin = root.join("role");
    Join<Assignment, Realm> realmJoin = root.join("realm");
    query.where(
            cb.equal(groupJoin.get("name"), params.get("group")),
            cb.equal(roleJoin.get("name"), params.get("role")),
            cb.equal(realmJoin.get("name"), params.get("realm")));

    Assignment assignment = em.createQuery(query).getResultStream().findAny().orElse(null);
    if (assignment != null) {
        context.putValue(assignment);
    }
    return assignment != null;
}
 

@Override
public BusinessObjectDefinitionTagEntity getBusinessObjectDefinitionTagByKey(BusinessObjectDefinitionTagKey businessObjectDefinitionTagKey)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<BusinessObjectDefinitionTagEntity> criteria = builder.createQuery(BusinessObjectDefinitionTagEntity.class);

    // The criteria root is the business object definition tag.
    Root<BusinessObjectDefinitionTagEntity> businessObjectDefinitionTagEntityRoot = criteria.from(BusinessObjectDefinitionTagEntity.class);

    // Join to the other tables we can filter on.
    Join<BusinessObjectDefinitionTagEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntityJoin =
        businessObjectDefinitionTagEntityRoot.join(BusinessObjectDefinitionTagEntity_.businessObjectDefinition);
    Join<BusinessObjectDefinitionEntity, NamespaceEntity> namespaceEntityJoin =
        businessObjectDefinitionEntityJoin.join(BusinessObjectDefinitionEntity_.namespace);
    Join<BusinessObjectDefinitionTagEntity, TagEntity> tagEntityJoin = businessObjectDefinitionTagEntityRoot.join(BusinessObjectDefinitionTagEntity_.tag);
    Join<TagEntity, TagTypeEntity> tagTypeEntityJoin = tagEntityJoin.join(TagEntity_.tagType);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(namespaceEntityJoin.get(NamespaceEntity_.code)),
        businessObjectDefinitionTagKey.getBusinessObjectDefinitionKey().getNamespace().toUpperCase()));
    predicates.add(builder.equal(builder.upper(businessObjectDefinitionEntityJoin.get(BusinessObjectDefinitionEntity_.name)),
        businessObjectDefinitionTagKey.getBusinessObjectDefinitionKey().getBusinessObjectDefinitionName().toUpperCase()));
    predicates.add(builder
        .equal(builder.upper(tagTypeEntityJoin.get(TagTypeEntity_.code)), businessObjectDefinitionTagKey.getTagKey().getTagTypeCode().toUpperCase()));
    predicates
        .add(builder.equal(builder.upper(tagEntityJoin.get(TagEntity_.tagCode)), businessObjectDefinitionTagKey.getTagKey().getTagCode().toUpperCase()));

    // Add the clauses for the query.
    criteria.select(businessObjectDefinitionTagEntityRoot).where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

    return executeSingleResultQuery(criteria, String.format(
        "Found more than one business object definition tag instance with parameters {namespace=\"%s\", businessObjectDefinitionName=\"%s\"," +
            " tagType=\"%s\", tagCode=\"%s\"}.", businessObjectDefinitionTagKey.getBusinessObjectDefinitionKey().getNamespace(),
        businessObjectDefinitionTagKey.getBusinessObjectDefinitionKey().getBusinessObjectDefinitionName(),
        businessObjectDefinitionTagKey.getTagKey().getTagTypeCode(), businessObjectDefinitionTagKey.getTagKey().getTagCode()));
}
 

@Override
public List<AttributeValueListKey> getAttributeValueLists(Collection<String> namespaces)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<AttributeValueListEntity> criteria = builder.createQuery(AttributeValueListEntity.class);

    // The criteria root is the attribute value list entity.
    Root<AttributeValueListEntity> attributeValueListEntityRoot = criteria.from(AttributeValueListEntity.class);

    // Join to the other tables we can filter on.
    Join<AttributeValueListEntity, NamespaceEntity> namespaceEntityJoin = attributeValueListEntityRoot.join(AttributeValueListEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    if (CollectionUtils.isNotEmpty(namespaces))
    {
        predicates.add(namespaceEntityJoin.get(NamespaceEntity_.code).in(namespaces));
    }

    // Order the results by namespace and job name.
    List<Order> orderBy = new ArrayList<>();
    orderBy.add(builder.asc(namespaceEntityJoin.get(NamespaceEntity_.code)));
    orderBy.add(builder.asc(attributeValueListEntityRoot.get(AttributeValueListEntity_.name)));

    // Add all clauses to the query.
    criteria.select(attributeValueListEntityRoot).where(builder.and(predicates.toArray(new Predicate[predicates.size()]))).orderBy(orderBy);

    // Execute the query and build a list of keys.
    List<AttributeValueListKey> attributeValueListKeys = new ArrayList<>();
    for (AttributeValueListEntity attributeValueListEntity : entityManager.createQuery(criteria).getResultList())
    {
        attributeValueListKeys.add(new AttributeValueListKey(attributeValueListEntity.getNamespace().getCode(), attributeValueListEntity.getName()));
    }

    return attributeValueListKeys;
}
 

@Override
public AttributeValueListEntity getAttributeValueListByKey(AttributeValueListKey attributeValueListKey)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<AttributeValueListEntity> criteria = builder.createQuery(AttributeValueListEntity.class);

    // The criteria root is the attribute value list.
    Root<AttributeValueListEntity> attributeValueListEntityRoot = criteria.from(AttributeValueListEntity.class);

    // Join to the other tables we can filter on.
    Join<AttributeValueListEntity, NamespaceEntity> namespaceEntityJoin = attributeValueListEntityRoot.join(AttributeValueListEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(namespaceEntityJoin.get(NamespaceEntity_.code)), attributeValueListKey.getNamespace().toUpperCase()));
    predicates.add(builder.equal(builder.upper(attributeValueListEntityRoot.get(AttributeValueListEntity_.name)),
        attributeValueListKey.getAttributeValueListName().toUpperCase()));

    // Add all clauses to the query.
    criteria.select(attributeValueListEntityRoot).where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

    // Execute the query and return the results.
    return executeSingleResultQuery(criteria, String
        .format("Found more than one attribute value list with parameters {namespace=\"%s\", attribute_value_name=\"%s\"}.",
            attributeValueListKey.getNamespace(), attributeValueListKey.getAttributeValueListName()));
}
 

@Override
public BusinessObjectDefinitionSubjectMatterExpertEntity getBusinessObjectDefinitionSubjectMatterExpertByKey(
    BusinessObjectDefinitionSubjectMatterExpertKey key)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<BusinessObjectDefinitionSubjectMatterExpertEntity> criteria =
        builder.createQuery(BusinessObjectDefinitionSubjectMatterExpertEntity.class);

    // The criteria root is the business object definition subject matter expert.
    Root<BusinessObjectDefinitionSubjectMatterExpertEntity> businessObjectDefinitionSubjectMatterExpertEntityRoot =
        criteria.from(BusinessObjectDefinitionSubjectMatterExpertEntity.class);

    // Join to the other tables we can filter on.
    Join<BusinessObjectDefinitionSubjectMatterExpertEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntityJoin =
        businessObjectDefinitionSubjectMatterExpertEntityRoot.join(BusinessObjectDefinitionSubjectMatterExpertEntity_.businessObjectDefinition);
    Join<BusinessObjectDefinitionEntity, NamespaceEntity> namespaceEntityJoin =
        businessObjectDefinitionEntityJoin.join(BusinessObjectDefinitionEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(namespaceEntityJoin.get(NamespaceEntity_.code)), key.getNamespace().toUpperCase()));
    predicates.add(builder.equal(builder.upper(businessObjectDefinitionEntityJoin.get(BusinessObjectDefinitionEntity_.name)),
        key.getBusinessObjectDefinitionName().toUpperCase()));
    predicates.add(builder
        .equal(builder.upper(businessObjectDefinitionSubjectMatterExpertEntityRoot.get(BusinessObjectDefinitionSubjectMatterExpertEntity_.userId)),
            key.getUserId().toUpperCase()));

    // Add the clauses for the query.
    criteria.select(businessObjectDefinitionSubjectMatterExpertEntityRoot).where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

    // Execute the query and return the results.
    return executeSingleResultQuery(criteria, String.format(
        "Found more than one business object definition subject matter expert instance with parameters {namespace=\"%s\", " +
            "businessObjectDefinitionName=\"%s\", userId=\"%s\"}.", key.getNamespace(), key.getBusinessObjectDefinitionName(), key.getUserId()));
}
 

@Decision(HANDLE_OK)
public OtpKey find(UserPermissionPrincipal principal, EntityManager em) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<OtpKey> query = cb.createQuery(OtpKey.class);
    Root<OtpKey> root = query.from(OtpKey.class);
    Join<OtpKey, User> userRoot = root.join("user");
    query.where(cb.equal(userRoot.get("account"), principal.getName()));
    return em.createQuery(query).getResultStream().findAny()
            .orElse(new OtpKey());
}
 

@Override
public Integer getBusinessObjectFormatMaxVersion(BusinessObjectFormatKey businessObjectFormatKey)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Integer> criteria = builder.createQuery(Integer.class);

    // The criteria root is the business object format.
    Root<BusinessObjectFormatEntity> businessObjectFormatEntity = criteria.from(BusinessObjectFormatEntity.class);

    // Join to the other tables we can filter on.
    Join<BusinessObjectFormatEntity, FileTypeEntity> fileTypeEntity = businessObjectFormatEntity.join(BusinessObjectFormatEntity_.fileType);
    Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity =
        businessObjectFormatEntity.join(BusinessObjectFormatEntity_.businessObjectDefinition);

    // Create the standard restrictions (i.e. the standard where clauses).
    // Business object format version should be ignored when building the query restriction.
    Predicate queryRestriction =
        getQueryRestriction(builder, businessObjectFormatEntity, fileTypeEntity, businessObjectDefinitionEntity, businessObjectFormatKey, true);

    // Create the path.
    Expression<Integer> maxBusinessObjectFormatVersion =
        builder.max(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion));

    criteria.select(maxBusinessObjectFormatVersion).where(queryRestriction);

    return entityManager.createQuery(criteria).getSingleResult();
}
 

@Override
public Page<TargetWithActionStatus> findAllTargetsOfRolloutGroupWithActionStatus(final Pageable pageRequest,
        final long rolloutGroupId) {
    throwExceptionIfRolloutGroupDoesNotExist(rolloutGroupId);

    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    final CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
    final CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);

    final Root<RolloutTargetGroup> targetRoot = query.distinct(true).from(RolloutTargetGroup.class);
    final Join<RolloutTargetGroup, JpaTarget> targetJoin = targetRoot.join(RolloutTargetGroup_.target);
    final ListJoin<RolloutTargetGroup, JpaAction> actionJoin = targetRoot.join(RolloutTargetGroup_.actions,
            JoinType.LEFT);

    final Root<RolloutTargetGroup> countQueryFrom = countQuery.distinct(true).from(RolloutTargetGroup.class);
    countQueryFrom.join(RolloutTargetGroup_.target);
    countQueryFrom.join(RolloutTargetGroup_.actions, JoinType.LEFT);
    countQuery.select(cb.count(countQueryFrom)).where(cb
            .equal(countQueryFrom.get(RolloutTargetGroup_.rolloutGroup).get(JpaRolloutGroup_.id), rolloutGroupId));
    final Long totalCount = entityManager.createQuery(countQuery).getSingleResult();

    final CriteriaQuery<Object[]> multiselect = query.multiselect(targetJoin, actionJoin.get(JpaAction_.status))
            .where(cb.equal(targetRoot.get(RolloutTargetGroup_.rolloutGroup).get(JpaRolloutGroup_.id),
                    rolloutGroupId));
    final List<TargetWithActionStatus> targetWithActionStatus = entityManager.createQuery(multiselect)
            .setFirstResult((int) pageRequest.getOffset()).setMaxResults(pageRequest.getPageSize()).getResultList()
            .stream().map(o -> new TargetWithActionStatus((Target) o[0], (Action.Status) o[1]))
            .collect(Collectors.toList());

    return new PageImpl<>(targetWithActionStatus, pageRequest, totalCount);
}
 

private static Path<?> getInnerJoinFieldPath(final Root<?> subqueryRoot, final Path<?> fieldPath,
        final String fieldNameSplit) {
    if (fieldPath instanceof Join) {
        return subqueryRoot.join(fieldNameSplit, JoinType.INNER);
    }
    return fieldPath;
}
 

@Override
public GlobalAttributeDefinitionEntity getGlobalAttributeDefinitionByKey(GlobalAttributeDefinitionKey globalAttributeDefinitionKey)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<GlobalAttributeDefinitionEntity> criteria = builder.createQuery(GlobalAttributeDefinitionEntity.class);

    // The criteria root is the global attribute definition entity.
    Root<GlobalAttributeDefinitionEntity> globalAttributeDefinitionEntityRoot = criteria.from(GlobalAttributeDefinitionEntity.class);

    //Join on the other tables that we filter on
    Join<GlobalAttributeDefinitionEntity, GlobalAttributeDefinitionLevelEntity> globalAttributeDefinitionLevelEntityJoin =
        globalAttributeDefinitionEntityRoot.join(GlobalAttributeDefinitionEntity_.globalAttributeDefinitionLevel);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder
        .equal(builder.upper(globalAttributeDefinitionLevelEntityJoin.get(GlobalAttributeDefinitionLevelEntity_.globalAttributeDefinitionLevel)),
            globalAttributeDefinitionKey.getGlobalAttributeDefinitionLevel().toUpperCase()));
    predicates.add(builder.equal(builder.upper(globalAttributeDefinitionEntityRoot.get(GlobalAttributeDefinitionEntity_.globalAttributeDefinitionName)),
        globalAttributeDefinitionKey.getGlobalAttributeDefinitionName().toUpperCase()));

    // Add all clauses to the query.
    criteria.select(globalAttributeDefinitionEntityRoot).where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

    return executeSingleResultQuery(criteria, String.format(
        "Found more than one global attribute definition with parameters {globalAttributeDefinitionLevel=\"%s\", globalAttributeDefinitionLevel=\"%s\"}.",
        globalAttributeDefinitionKey.getGlobalAttributeDefinitionLevel(), globalAttributeDefinitionKey.getGlobalAttributeDefinitionName()));
}
 

@Override
public Path<?> getQueryPath( Root<?> root, Schema schema, String path )
{
    Schema curSchema = schema;
    Property curProperty;
    String[] pathComponents = path.split( "\\." );

    Path<?> currentPath = root;

    if ( pathComponents.length == 0 )
    {
        return null;
    }

    for ( int idx = 0; idx < pathComponents.length; idx++ )
    {
        String name = pathComponents[idx];
        curProperty = curSchema.getProperty( name );

        if ( curProperty == null )
        {
            throw new RuntimeException( "Invalid path property: " + name );
        }

        if ( (!curProperty.isSimple() && idx == pathComponents.length - 1) )
        {
            return root.join( curProperty.getFieldName() );
        }

        if ( curProperty.isCollection() )
        {
            currentPath = root.join( curProperty.getFieldName() );
            curSchema = schemaService.getDynamicSchema( curProperty.getItemKlass() );
        }
        else if ( !curProperty.isSimple() )
        {
            curSchema = schemaService.getDynamicSchema( curProperty.getKlass() );
            currentPath = root.join( curProperty.getFieldName() );
        }
        else
        {
            return currentPath.get( curProperty.getFieldName() );
        }
    }

    return currentPath;
}
 

@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;
    }

    // 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();
}
 

@Override
public List<NotificationRegistrationKey> getBusinessObjectDataNotificationRegistrationKeysByNotificationFilter(
    BusinessObjectDataNotificationFilter businessObjectDataNotificationFilter)
{
    // Create the criteria builder and a tuple style criteria query.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> criteria = builder.createTupleQuery();

    // The criteria root is the business object data notification registration.
    Root<BusinessObjectDataNotificationRegistrationEntity> notificationRegistrationEntityRoot =
        criteria.from(BusinessObjectDataNotificationRegistrationEntity.class);

    // Join to the other tables we can filter on.
    Join<BusinessObjectDataNotificationRegistrationEntity, NamespaceEntity> notificationRegistrationNamespaceEntityJoin =
        notificationRegistrationEntityRoot.join(BusinessObjectDataNotificationRegistrationEntity_.namespace);
    Join<BusinessObjectDataNotificationRegistrationEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity =
        notificationRegistrationEntityRoot.join(BusinessObjectDataNotificationRegistrationEntity_.businessObjectDefinition);
    Join<BusinessObjectDefinitionEntity, NamespaceEntity> businessObjectDefinitionNamespaceEntity =
        businessObjectDefinitionEntity.join(BusinessObjectDefinitionEntity_.namespace);
    Join<BusinessObjectDataNotificationRegistrationEntity, FileTypeEntity> fileTypeEntity =
        notificationRegistrationEntityRoot.join(BusinessObjectDataNotificationRegistrationEntity_.fileType, JoinType.LEFT);

    // Get the columns.
    Path<String> notificationRegistrationNamespaceColumn = notificationRegistrationNamespaceEntityJoin.get(NamespaceEntity_.code);
    Path<String> notificationRegistrationNameColumn = notificationRegistrationEntityRoot.get(BusinessObjectDataNotificationRegistrationEntity_.name);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(businessObjectDefinitionNamespaceEntity.get(NamespaceEntity_.code)),
        businessObjectDataNotificationFilter.getNamespace().toUpperCase()));
    predicates.add(builder.equal(builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)),
        businessObjectDataNotificationFilter.getBusinessObjectDefinitionName().toUpperCase()));
    if (StringUtils.isNotBlank(businessObjectDataNotificationFilter.getBusinessObjectFormatUsage()))
    {
        predicates.add(builder.or(builder.isNull(notificationRegistrationEntityRoot.get(BusinessObjectDataNotificationRegistrationEntity_.usage)), builder
            .equal(builder.upper(notificationRegistrationEntityRoot.get(BusinessObjectDataNotificationRegistrationEntity_.usage)),
                businessObjectDataNotificationFilter.getBusinessObjectFormatUsage().toUpperCase())));
    }
    if (StringUtils.isNotBlank(businessObjectDataNotificationFilter.getBusinessObjectFormatFileType()))
    {
        predicates.add(builder.or(builder.isNull(notificationRegistrationEntityRoot.get(BusinessObjectDataNotificationRegistrationEntity_.fileType)),
            builder.equal(builder.upper(fileTypeEntity.get(FileTypeEntity_.code)),
                businessObjectDataNotificationFilter.getBusinessObjectFormatFileType().toUpperCase())));
    }

    // Add the select and where clauses to the query.
    criteria.multiselect(notificationRegistrationNamespaceColumn, notificationRegistrationNameColumn)
        .where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

    // Add the order by clause to the query.
    criteria.orderBy(builder.asc(notificationRegistrationNamespaceColumn), builder.asc(notificationRegistrationNameColumn));

    // Run the query to get a list of tuples back.
    List<Tuple> tuples = entityManager.createQuery(criteria).getResultList();

    // Populate the list of keys from the returned tuples.
    return getNotificationRegistrationKeys(tuples, notificationRegistrationNamespaceColumn, notificationRegistrationNameColumn);
}
 

@Override
public List<NotificationRegistrationKey> getStorageUnitNotificationRegistrationKeysByNotificationFilter(
    StorageUnitNotificationFilter businessObjectDataNotificationFilter)
{
    // Create the criteria builder and a tuple style criteria query.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> criteria = builder.createTupleQuery();

    // The criteria root is the storage unit notification registration.
    Root<StorageUnitNotificationRegistrationEntity> notificationRegistrationEntityRoot = criteria.from(StorageUnitNotificationRegistrationEntity.class);

    // Join to the other tables we can filter on.
    Join<StorageUnitNotificationRegistrationEntity, NamespaceEntity> notificationRegistrationNamespaceEntityJoin =
        notificationRegistrationEntityRoot.join(StorageUnitNotificationRegistrationEntity_.namespace);
    Join<StorageUnitNotificationRegistrationEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity =
        notificationRegistrationEntityRoot.join(StorageUnitNotificationRegistrationEntity_.businessObjectDefinition);
    Join<BusinessObjectDefinitionEntity, NamespaceEntity> businessObjectDefinitionNamespaceEntity =
        businessObjectDefinitionEntity.join(BusinessObjectDefinitionEntity_.namespace);
    Join<StorageUnitNotificationRegistrationEntity, FileTypeEntity> fileTypeEntity =
        notificationRegistrationEntityRoot.join(StorageUnitNotificationRegistrationEntity_.fileType, JoinType.LEFT);

    // Get the columns.
    Path<String> notificationRegistrationNamespaceColumn = notificationRegistrationNamespaceEntityJoin.get(NamespaceEntity_.code);
    Path<String> notificationRegistrationNameColumn = notificationRegistrationEntityRoot.get(StorageUnitNotificationRegistrationEntity_.name);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(businessObjectDefinitionNamespaceEntity.get(NamespaceEntity_.code)),
        businessObjectDataNotificationFilter.getNamespace().toUpperCase()));
    predicates.add(builder.equal(builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)),
        businessObjectDataNotificationFilter.getBusinessObjectDefinitionName().toUpperCase()));
    if (StringUtils.isNotBlank(businessObjectDataNotificationFilter.getBusinessObjectFormatUsage()))
    {
        predicates.add(builder.or(builder.isNull(notificationRegistrationEntityRoot.get(StorageUnitNotificationRegistrationEntity_.usage)), builder
            .equal(builder.upper(notificationRegistrationEntityRoot.get(StorageUnitNotificationRegistrationEntity_.usage)),
                businessObjectDataNotificationFilter.getBusinessObjectFormatUsage().toUpperCase())));
    }
    if (StringUtils.isNotBlank(businessObjectDataNotificationFilter.getBusinessObjectFormatFileType()))
    {
        predicates.add(builder.or(builder.isNull(notificationRegistrationEntityRoot.get(StorageUnitNotificationRegistrationEntity_.fileType)), builder
            .equal(builder.upper(fileTypeEntity.get(FileTypeEntity_.code)),
                businessObjectDataNotificationFilter.getBusinessObjectFormatFileType().toUpperCase())));
    }

    // Add the select and where clauses to the query.
    criteria.multiselect(notificationRegistrationNamespaceColumn, notificationRegistrationNameColumn)
        .where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

    // Add the order by clause to the query.
    criteria.orderBy(builder.asc(notificationRegistrationNamespaceColumn), builder.asc(notificationRegistrationNameColumn));

    // Run the query to get a list of tuples back.
    List<Tuple> tuples = entityManager.createQuery(criteria).getResultList();

    // Populate the list of keys from the returned tuples.
    return getNotificationRegistrationKeys(tuples, notificationRegistrationNamespaceColumn, notificationRegistrationNameColumn);
}
 

@Override
public List<BusinessObjectFormatKey> getBusinessObjectFormatsWithFilters(BusinessObjectDefinitionKey businessObjectDefinitionKey,
    String businessObjectFormatUsage, boolean latestBusinessObjectFormatVersion)
{
    // Create the criteria builder and a tuple style criteria query.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> criteria = builder.createTupleQuery();

    // The criteria root is the business object format.
    Root<BusinessObjectFormatEntity> businessObjectFormatEntity = criteria.from(BusinessObjectFormatEntity.class);

    // Join to the other tables we can filter on.
    Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity =
        businessObjectFormatEntity.join(BusinessObjectFormatEntity_.businessObjectDefinition);
    Join<BusinessObjectFormatEntity, FileTypeEntity> fileTypeEntity = businessObjectFormatEntity.join(BusinessObjectFormatEntity_.fileType);
    Join<BusinessObjectDefinitionEntity, NamespaceEntity> namespaceEntity = businessObjectDefinitionEntity.join(BusinessObjectDefinitionEntity_.namespace);

    // Get the columns.
    Path<String> namespaceCodeColumn = namespaceEntity.get(NamespaceEntity_.code);
    Path<String> businessObjectDefinitionNameColumn = businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name);
    Path<String> businessObjectFormatUsageColumn = businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage);
    Path<String> fileTypeCodeColumn = fileTypeEntity.get(FileTypeEntity_.code);
    Path<Integer> businessObjectFormatVersionColumn = businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion);
    Expression<Integer> maxBusinessObjectFormatVersionExpression =
        builder.max(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion));

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate queryRestriction =
        builder.equal(builder.upper(namespaceEntity.get(NamespaceEntity_.code)), businessObjectDefinitionKey.getNamespace().toUpperCase());
    queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)),
        businessObjectDefinitionKey.getBusinessObjectDefinitionName().toUpperCase()));

    // Add the business object format usage where parameter is not empty
    if (StringUtils.isNotEmpty(businessObjectFormatUsage))
    {
        queryRestriction = builder.and(queryRestriction,
            builder.equal(builder.upper(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage)), businessObjectFormatUsage.toUpperCase()));
    }

    // Add the select clause.
    criteria.multiselect(namespaceCodeColumn, businessObjectDefinitionNameColumn, businessObjectFormatUsageColumn, fileTypeCodeColumn,
        latestBusinessObjectFormatVersion ? maxBusinessObjectFormatVersionExpression : businessObjectFormatVersionColumn);

    // Add the where clause.
    criteria.where(queryRestriction);

    // If only the latest (maximum) business object format versions to be returned, create and apply the group by clause.
    if (latestBusinessObjectFormatVersion)
    {
        List<Expression<?>> grouping = new ArrayList<>();
        grouping.add(namespaceCodeColumn);
        grouping.add(businessObjectDefinitionNameColumn);
        grouping.add(businessObjectFormatUsageColumn);
        grouping.add(fileTypeCodeColumn);
        criteria.groupBy(grouping);
    }

    // Add the order by clause.
    List<Order> orderBy = new ArrayList<>();
    orderBy.add(builder.asc(businessObjectFormatUsageColumn));
    orderBy.add(builder.asc(fileTypeCodeColumn));
    if (!latestBusinessObjectFormatVersion)
    {
        orderBy.add(builder.asc(businessObjectFormatVersionColumn));
    }
    criteria.orderBy(orderBy);

    // Run the query to get a list of tuples back.
    List<Tuple> tuples = entityManager.createQuery(criteria).getResultList();

    // Populate the "keys" objects from the returned tuples (i.e. 1 tuple for each row).
    List<BusinessObjectFormatKey> businessObjectFormatKeys = new ArrayList<>();
    for (Tuple tuple : tuples)
    {
        BusinessObjectFormatKey businessObjectFormatKey = new BusinessObjectFormatKey();
        businessObjectFormatKeys.add(businessObjectFormatKey);
        businessObjectFormatKey.setNamespace(tuple.get(namespaceCodeColumn));
        businessObjectFormatKey.setBusinessObjectDefinitionName(tuple.get(businessObjectDefinitionNameColumn));
        businessObjectFormatKey.setBusinessObjectFormatUsage(tuple.get(businessObjectFormatUsageColumn));
        businessObjectFormatKey.setBusinessObjectFormatFileType(tuple.get(fileTypeCodeColumn));
        businessObjectFormatKey.setBusinessObjectFormatVersion(
            tuple.get(latestBusinessObjectFormatVersion ? maxBusinessObjectFormatVersionExpression : businessObjectFormatVersionColumn));
    }

    return businessObjectFormatKeys;
}
 

@Override
public Slice<AssignedSoftwareModule> findAllOrderBySetAssignmentAndModuleNameAscModuleVersionAsc(
        final Pageable pageable, final long orderByDistributionId, final String searchText, final Long typeId) {

    final List<AssignedSoftwareModule> resultList = new ArrayList<>();
    final int pageSize = pageable.getPageSize();
    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();

    // get the assigned software modules
    final CriteriaQuery<JpaSoftwareModule> assignedQuery = cb.createQuery(JpaSoftwareModule.class);
    final Root<JpaSoftwareModule> assignedRoot = assignedQuery.from(JpaSoftwareModule.class);
    assignedQuery.distinct(true);
    final ListJoin<JpaSoftwareModule, JpaDistributionSet> assignedDsJoin = assignedRoot
            .join(JpaSoftwareModule_.assignedTo);
    // build the specifications and then to predicates necessary by the
    // given filters
    final Predicate[] specPredicate = specificationsToPredicate(buildSpecificationList(searchText, typeId),
            assignedRoot, assignedQuery, cb,
            cb.equal(assignedDsJoin.get(JpaDistributionSet_.id), orderByDistributionId));
    // if we have some predicates then add it to the where clause of the
    // multi select
    assignedQuery.where(specPredicate);
    assignedQuery.orderBy(cb.asc(assignedRoot.get(JpaSoftwareModule_.name)),
            cb.asc(assignedRoot.get(JpaSoftwareModule_.version)));
    // don't page the assigned query on database, we need all assigned
    // software modules to filter
    // them out in the unassigned query
    final List<JpaSoftwareModule> assignedSoftwareModules = entityManager.createQuery(assignedQuery)
            .getResultList();
    // map result
    if (pageable.getOffset() < assignedSoftwareModules.size()) {
        assignedSoftwareModules
                .subList((int) pageable.getOffset(),
                        Math.min(assignedSoftwareModules.size(), pageable.getPageSize()))
                .forEach(sw -> resultList.add(new AssignedSoftwareModule(sw, true)));
    }

    if (assignedSoftwareModules.size() >= pageSize) {
        return new SliceImpl<>(resultList);
    }

    // get the unassigned software modules
    final CriteriaQuery<JpaSoftwareModule> unassignedQuery = cb.createQuery(JpaSoftwareModule.class);
    unassignedQuery.distinct(true);
    final Root<JpaSoftwareModule> unassignedRoot = unassignedQuery.from(JpaSoftwareModule.class);

    Predicate[] unassignedSpec;
    if (!assignedSoftwareModules.isEmpty()) {
        unassignedSpec = specificationsToPredicate(buildSpecificationList(searchText, typeId), unassignedRoot,
                unassignedQuery, cb, cb.not(unassignedRoot.get(JpaSoftwareModule_.id).in(
                        assignedSoftwareModules.stream().map(SoftwareModule::getId).collect(Collectors.toList()))));
    } else {
        unassignedSpec = specificationsToPredicate(buildSpecificationList(searchText, typeId), unassignedRoot,
                unassignedQuery, cb);
    }

    unassignedQuery.where(unassignedSpec);
    unassignedQuery.orderBy(cb.asc(unassignedRoot.get(JpaSoftwareModule_.name)),
            cb.asc(unassignedRoot.get(JpaSoftwareModule_.version)));
    final List<JpaSoftwareModule> unassignedSoftwareModules = entityManager.createQuery(unassignedQuery)
            .setFirstResult((int) Math.max(0, pageable.getOffset() - assignedSoftwareModules.size()))
            .setMaxResults(pageSize).getResultList();
    // map result
    unassignedSoftwareModules.forEach(sw -> resultList.add(new AssignedSoftwareModule(sw, false)));

    return new SliceImpl<>(resultList);
}