javax.persistence.criteria.Order Java Examples

/**
 * Creates the query language order expression for selects that must be selected in order to
 * be able to order by these expressions. This is required for ordering on case insensitive
 * expressions since
 *
 * @param orders the orders that should be created to a string.
 * @param alias  the entity alias that will be used for prefixing.
 * @return the string order expression selects or <code>null</code> if none should be used.
 */
@Nullable
public static String createSelectOrderExpression( @Nullable List<org.hisp.dhis.query.Order> orders, @Nullable String alias )
{
    if ( orders == null )
    {
        return null;
    }

    return StringUtils.defaultIfEmpty( orders.stream().filter( o -> o.isPersisted() && isIgnoreCase( o ) ).map( o -> {
        final StringBuilder sb = new StringBuilder( "lower(" );

        if ( alias != null )
        {
            sb.append( alias ).append( '.' );
        }

        sb.append( o.getProperty().getName() ).append( ')' );

        return sb.toString();
    } ).collect( Collectors.joining( "," ) ), null );
}
 

@Override
public List<TagTypeEntity> getTagTypes()
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<TagTypeEntity> criteria = builder.createQuery(TagTypeEntity.class);

    // The criteria root is the tag type entity.
    Root<TagTypeEntity> tagTypeEntityRoot = criteria.from(TagTypeEntity.class);

    // Get the columns.
    Path<String> displayNameColumn = tagTypeEntityRoot.get(TagTypeEntity_.displayName);
    Path<Integer> tagTypeOrderColumn = tagTypeEntityRoot.get(TagTypeEntity_.orderNumber);

    // Order the results by tag type's order and display name.
    List<Order> orderBy = new ArrayList<>();
    orderBy.add(builder.asc(tagTypeOrderColumn));
    orderBy.add(builder.asc(displayNameColumn));

    // Add all clauses to the query.
    criteria.select(tagTypeEntityRoot).orderBy(orderBy);

    // Run the query and return the results.
    return entityManager.createQuery(criteria).getResultList();
}
 

private <T> List<Order> getSort(Root<T> p_root, CriteriaBuilder p_builder, Sort[] p_sort) {
	
	List<Order> order = new LinkedList<Order>();
	
	if (p_sort != null && p_sort.length > 0) {				
		
		for (Sort sort : p_sort) {
			Path<?> property_path = null;		
			
			for (String hop : sort.getPropertyPath()) {
				if (property_path == null)
					property_path = p_root.get(hop);
				else
					property_path = property_path.get(hop);
			}
			if (sort.getOrderAscending()) {
				order.add(p_builder.asc(property_path));
			} else {
				order.add(p_builder.desc(property_path));
			}
		}			
	}
	
	return order;
}
 

@Override
public List<Long> getBusinessObjectFormatIdsByBusinessObjectDefinition(BusinessObjectDefinitionEntity businessObjectDefinitionEntity)
{
    // 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> businessObjectFormatIdColumn = businessObjectFormatEntityRoot.get(BusinessObjectFormatEntity_.id);

    // Create standard restrictions.
    Predicate predicate =
        builder.equal(businessObjectFormatEntityRoot.get(BusinessObjectFormatEntity_.businessObjectDefinitionId), businessObjectDefinitionEntity.getId());

    // Build an order by clause.
    Order orderBy = builder.asc(businessObjectFormatEntityRoot.get(BusinessObjectFormatEntity_.id));

    // Add all clauses to the query.
    criteria.select(businessObjectFormatIdColumn).where(predicate).orderBy(orderBy);

    // Execute the query and return the results.
    return entityManager.createQuery(criteria).getResultList();
}
 

public List<TaskView> listWithFilter( Integer maxCount, String orderField, String orderType, QueryFilter queryFilter) throws Exception {
	EntityManager em = this.entityManagerContainer().get( TaskView.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<TaskView> cq = cb.createQuery(TaskView.class);
	Root<TaskView> root = cq.from(TaskView.class);
	Predicate p = CriteriaBuilderTools.composePredicateWithQueryFilter( TaskView_.class, cb, null, root, queryFilter );
	
	//排序，添加排序列，默认使用sequence
	List<Order> orders = new ArrayList<>();
	Order orderWithField = CriteriaBuilderTools.getOrder( cb, root, TaskView_.class, orderField, orderType );
	if( orderWithField != null ){
		orders.add( orderWithField );
	}
	
	if( !TaskView.sequence_FIELDNAME.equalsIgnoreCase( orderField )) {
		//如果是其他的列，很可能排序值不唯一，所以使用多一列排序列来确定每次查询的顺序
		orderWithField = CriteriaBuilderTools.getOrder( cb, root, TaskView_.class, TaskView.id_FIELDNAME, orderType );
		if( orderWithField != null ){
			orders.add( orderWithField );
		}
	}		
	if( ListTools.isNotEmpty(  orders )){
		cq.orderBy( orders );
	}
	return em.createQuery(cq.where(p)).setMaxResults( maxCount).getResultList();
}
 

@Override
public <R> void process(CriteriaQuery<R> query, CriteriaBuilder builder, Path<P> path)
{
    List<Order> orders = new ArrayList<Order>();
    Iterator<OrderByDefinition<?>> iterator = orderByDefinitions.iterator();
    while (iterator.hasNext())
    {
        OrderByDefinition<?> orderByDefinition = iterator.next();
        switch (orderByDefinition.getDir())
        {
            case ASC:
                orders.add(builder.asc(path.get(orderByDefinition.getAtt())));
                break;
            default:
                orders.add(builder.desc(path.get(orderByDefinition.getAtt())));
        }
    }
    query.orderBy(orders);
}
 

@Override
public List<UserNamespaceAuthorizationEntity> getUserNamespaceAuthorizationsByNamespace(String namespace)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<UserNamespaceAuthorizationEntity> criteria = builder.createQuery(UserNamespaceAuthorizationEntity.class);

    // The criteria root is the user namespace authorization.
    Root<UserNamespaceAuthorizationEntity> userNamespaceAuthorizationEntity = criteria.from(UserNamespaceAuthorizationEntity.class);

    // Join to the other tables we can filter on.
    Join<UserNamespaceAuthorizationEntity, NamespaceEntity> namespaceEntity =
        userNamespaceAuthorizationEntity.join(UserNamespaceAuthorizationEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate queryRestriction = builder.equal(builder.upper(namespaceEntity.get(NamespaceEntity_.code)), namespace.toUpperCase());

    // Order by user id.
    Order orderBy = builder.asc(userNamespaceAuthorizationEntity.get(UserNamespaceAuthorizationEntity_.userId));

    // Add all clauses for the query.
    criteria.select(userNamespaceAuthorizationEntity).where(queryRestriction).orderBy(orderBy);

    // Execute the query and return the result list.
    return entityManager.createQuery(criteria).getResultList();
}
 

/**
 * Constructs the select criteria query
 * 
 * @param builder
 * @param query
 * @param root
 */
<T extends Model> void constructQuery(CriteriaBuilder builder, CriteriaQuery<?> query, Root<T> root) {
	if (!conditions.isEmpty()) {
		List<Predicate> predicates = new ArrayList<Predicate>();
		for (Condition condition : conditions) {
			predicates.add(condition.constructQuery(builder, root));
		}
		query.where(predicates.toArray(new Predicate[0]));
	}
	
	if (!sortFields.isEmpty()) {
		List<Order> orders = new ArrayList<Order>();
		for (SortField sortField : sortFields) {
			orders.add(sortField.getOrder(builder, root));
		}
		query.orderBy(orders);
	}
}
 

/**
 * Gets a NamedQuery from page, setup order, params and page result count.
 * @param page request page
 * @return a TypedQuery from a NamedQuery
 */
@SuppressWarnings("unchecked")
protected <K> TypedQuery<K> getNamedQuery(Page<K> page) {
	Filter filter = null;
	TypedQuery<K> query = null;
	
	if (page.getFilter() instanceof Filter) {
		filter = (Filter) page.getFilter();
		String queryString = getQueryString(filter.getFilterName());
		if (queryString != null) {
			String countQueryString = JpaUtils.createCountQueryString(queryString);
			TypedQuery<Long> countQuery =  em.createQuery(countQueryString, Long.class);
			applyFilter(countQuery, filter);
			page.setCount(countQuery.getSingleResult().intValue());
			// add Order
			if (page.getSortName() != null)
				queryString = JpaUtils.addOrder(queryString, page.getSortName(),
					page.getOrder() == Page.Order.ASC);
	
			query = (TypedQuery<K>) em.createQuery(queryString);
			applyFilter(query, filter);
		}
	}
	
	return query;
}
 

/**
 * 根据条件组织一个排序的语句
 * @param <T>
 * @param <T_>
 * @param cb
 * @param root
 * @param cls_
 * @param fieldName
 * @param orderType
 * @return
 */
public static <T extends JpaObject, T_ extends SliceJpaObject_>Order getOrder( CriteriaBuilder cb, Root<T> root, Class<T_> cls_, String fieldName, String orderType ) {
	Boolean fieldExists = false;
	Field[] fields = cls_.getDeclaredFields();
	for( Field field : fields ) {
		if( field.getName().equalsIgnoreCase( fieldName ) ) {
			fieldName = field.getName(); //校正排序列的名称
			fieldExists = true;
		}
	}
	if( !fieldExists ) { //如果排序列不存在，就直接返回空，不排序，让SQL可以正常执行
		return null;
	}		
	if( "desc".equalsIgnoreCase( orderType )) {
		return cb.desc( root.get( fieldName ).as(String.class) );
	}else {
		return cb.asc( root.get( fieldName ).as(String.class) );
	}
}
 

@Override
public List<JobDefinitionEntity> getJobDefinitionsByFilter(String namespace, String jobName)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<JobDefinitionEntity> criteria = builder.createQuery(JobDefinitionEntity.class);

    // The criteria root is the job definition.
    Root<JobDefinitionEntity> jobDefinitionEntityRoot = criteria.from(JobDefinitionEntity.class);

    // Join to the other tables we can filter on.
    Join<JobDefinitionEntity, NamespaceEntity> namespaceEntityJoin = jobDefinitionEntityRoot.join(JobDefinitionEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    if (StringUtils.isNotBlank(namespace))
    {
        predicates.add(builder.equal(builder.upper(namespaceEntityJoin.get(NamespaceEntity_.code)), namespace.toUpperCase()));
    }
    if (StringUtils.isNotBlank(jobName))
    {
        predicates.add(builder.equal(builder.upper(jobDefinitionEntityRoot.get(JobDefinitionEntity_.name)), jobName.toUpperCase()));
    }

    // 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(jobDefinitionEntityRoot.get(JobDefinitionEntity_.name)));

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

    // Execute the query and return the result list.
    return entityManager.createQuery(criteria).getResultList();
}
 

@Override
public List<JobDefinitionEntity> getJobDefinitionsByFilter(Collection<String> namespaces, String jobName)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<JobDefinitionEntity> criteria = builder.createQuery(JobDefinitionEntity.class);

    // The criteria root is the job definition.
    Root<JobDefinitionEntity> jobDefinitionEntityRoot = criteria.from(JobDefinitionEntity.class);

    // Join to the other tables we can filter on.
    Join<JobDefinitionEntity, NamespaceEntity> namespaceEntityJoin = jobDefinitionEntityRoot.join(JobDefinitionEntity_.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));
    }
    if (StringUtils.isNotBlank(jobName))
    {
        predicates.add(builder.equal(builder.upper(jobDefinitionEntityRoot.get(JobDefinitionEntity_.name)), jobName.toUpperCase()));
    }

    // 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(jobDefinitionEntityRoot.get(JobDefinitionEntity_.name)));

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

    // Execute the query and return the result list.
    return entityManager.createQuery(criteria).getResultList();
}
 

private List<PersonCard> list(EffectivePerson effectivePerson, Business business, Integer adjustPage, Integer adjustPageSize, String keyString) throws Exception {
	EntityManager em = business.entityManagerContainer().get(PersonCard.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<PersonCard> cq = cb.createQuery(PersonCard.class);
	Root<PersonCard> root = cq.from(PersonCard.class);

	Order _order = cb.asc(root.get(PersonCard_.orderNumber));
	Predicate pe = cb.equal(root.get(PersonCard_.distinguishedName), effectivePerson.getDistinguishedName());
	if (StringUtils.isNotEmpty(keyString)) {
		String key = StringUtils.trim(StringUtils.replaceEach(keyString, new String[] { "\u3000", "?", "%" }, new String[] { " ", "", "" }));
		if (StringUtils.isNotEmpty(key)) {
			Predicate p = cb.or(cb.like(root.get(PersonCard_.name), "%" + key + "%"), cb.like(root.get(PersonCard_.status), "%" + key + "%"),
					cb.like(root.get(PersonCard_.mobile), "%" + key + "%"), cb.like(root.get(PersonCard_.officePhone), "%" + key + "%"),
					cb.like(root.get(PersonCard_.pinyin), "%" + key + "%"), cb.like(root.get(PersonCard_.pinyinInitial), "%" + key + "%"),
					cb.like(root.get(PersonCard_.groupType), "%" + key + "%"));
			p = cb.and(p,pe);

				cq.select(root).where(p).orderBy(cb.asc(root.get(PersonCard_.orderNumber))); 
		} else {
				cq.select(root).where(pe).orderBy(cb.asc(root.get(PersonCard_.orderNumber)));				
		}

	} else {
			cq.select(root).where(pe).orderBy(cb.asc(root.get(PersonCard_.orderNumber)));
	}
	return em.createQuery(cq).setFirstResult((adjustPage - 1) * adjustPageSize).setMaxResults(adjustPageSize).getResultList();
}
 

/**
 * {@inheritDoc}
 */
@Override
protected void addOrderBy(TranslationContext criteria, String propertyPath, boolean sortAscending) {
    List<Order> orderList = criteria.query.getOrderList();
    if (orderList == null) {
        orderList = new ArrayList<Order>();
    }

    if (propertyPath.contains(".")) {
        String propertyPathStart = StringUtils.substringBefore( propertyPath, "." );
        String propertyPathEnd = StringUtils.substringAfter( propertyPath, "." );

        if (sortAscending) {
            orderList.add(criteria.builder.asc(criteria.root.get(propertyPathStart).get(propertyPathEnd)));
        } else {
            orderList.add(criteria.builder.desc(criteria.root.get(propertyPathStart).get(propertyPathEnd)));
        }
    } else {
        if (sortAscending) {
            orderList.add(criteria.builder.asc(criteria.root.get(propertyPath)));
        } else {
            orderList.add(criteria.builder.desc(criteria.root.get(propertyPath)));
        }
    }

    criteria.query.orderBy(orderList);
}
 

public CriteriaQuery<E> orderBy(List<SingularAttribute<T, ?>> attributes, boolean asc) {
    CriteriaBuilder cb = getCriteriaBuilder();

    List<Order> orders = new ArrayList<>();
    for (SingularAttribute<T, ?> attribute : attributes) {
        Path<?> selection = getRoot().get(attribute);
        Order order = asc ? cb.asc(selection) : cb.desc(selection);
        orders.add(order);
    }
    return getCriteriaQuery().orderBy(orders);
}
 

public static <E> List<Order> buildJpaOrders(final Iterable<OrderBy> orders, final Root<E> root,
        final CriteriaBuilder builder) {
    List<Order> jpaOrders = new ArrayList<Order>();

    for (OrderBy ob : orders) {
        Path<?> path = getPropertyPath(root, ob.getProperty());

        if (ob.isOrderDesc()) {
            jpaOrders.add(builder.desc(path));
        } else {
            jpaOrders.add(builder.asc(path));
        }
    }
    return jpaOrders;
}
 

@Test
public void shouldConstructCriteriaQueryWithSortFields() {
	Filter filter = new Filter().sortBy(mock(SortField.class)).sortBy(mock(SortField.class));
	CriteriaBuilder builder = mock(CriteriaBuilder.class);
	Root root = mock(Root.class);
	Order order1 = mock(Order.class);
	Order order2 = mock(Order.class);
	when(filter.getSortFields().get(0).getOrder(builder, root)).thenReturn(order1);
	when(filter.getSortFields().get(1).getOrder(builder, root)).thenReturn(order2);
	CriteriaQuery query = mock(CriteriaQuery.class);
	filter.constructQuery(builder, query, root);
	verify(query).orderBy(Arrays.asList(order1, order2));
}
 

@Override
public List<String> getStorageFilesByStorageAndFilePathPrefix(String storageName, String filePathPrefix)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageFileEntity> criteria = builder.createQuery(StorageFileEntity.class);

    // The criteria root is the storage files.
    Root<StorageFileEntity> storageFileEntity = criteria.from(StorageFileEntity.class);

    // Join to the other tables we can filter on.
    Join<StorageFileEntity, StorageUnitEntity> storageUnitEntity = storageFileEntity.join(StorageFileEntity_.storageUnit);
    Join<StorageUnitEntity, StorageEntity> storageEntity = storageUnitEntity.join(StorageUnitEntity_.storage);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate filePathRestriction = builder.like(storageFileEntity.get(StorageFileEntity_.path), String.format("%s%%", filePathPrefix));
    Predicate storageNameRestriction = builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageName.toUpperCase());

    // Order the results by file path.
    Order orderByFilePath = builder.asc(storageFileEntity.get(StorageFileEntity_.path));

    criteria.select(storageFileEntity).where(builder.and(filePathRestriction, storageNameRestriction)).orderBy(orderByFilePath);

    // Retrieve the storage files.
    List<StorageFileEntity> storageFileEntities = entityManager.createQuery(criteria).getResultList();

    // Build the result list.
    List<String> storageFilePaths = new ArrayList<>();
    for (StorageFileEntity storageFile : storageFileEntities)
    {
        storageFilePaths.add(storageFile.getPath());
    }

    return storageFilePaths;
}
 

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

    // The criteria root is the allowed attribute value.
    Root<AllowedAttributeValueEntity> allowedAttributeValueEntityRoot = criteria.from(AllowedAttributeValueEntity.class);

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

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

    // Order the results by allowed attribute value.
    Order orderByAllowedAttributeValue = builder.asc(allowedAttributeValueEntityRoot.get(AllowedAttributeValueEntity_.allowedAttributeValue));

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

    // Execute the query and return the results.
    return entityManager.createQuery(criteria).getResultList();
}
 

/**
 * {@inheritDoc}
 */
public List<Serializable> getKeys(Page<T> page) {	
	Filter filter = null;
	TypedQuery<Serializable> query = null;
	SingularAttribute<? super T, ?> id = getIdAttribute();
	// try named query
	if (page.getFilter() instanceof Filter) {
		filter = (Filter) page.getFilter();
		String queryString = getQueryString(filter.getFilterName());
		if (queryString != null) {				
			String keyQuery = JpaUtils.getKeyQuery(queryString, id.getName());
			// add Order
			if (page.getSortName() != null)
				keyQuery = JpaUtils.addOrder(keyQuery, page.getSortName(),
					page.getOrder() == Page.Order.ASC);
				
			query = em.createQuery(keyQuery, Serializable.class);
			applyFilter(query, filter);
		}
		else {
			query = getKeyCriteriaQuery(id, page); 
		}
	}
	else {
		query = getKeyCriteriaQuery(id, page);
	}
	
	query.setFirstResult(page.getStartIndex());
	query.setMaxResults(page.getPageSize());
	
	return query.getResultList();
}
 

/**
 * Get JPA Order from a page order for a CriteriaQuery
 * @param page request page
 * @param criteria CriteriaQuery to apply Order on.
 * @return new Order
 */
private Order getOrder(Page<?> page, CriteriaQuery<?> criteria) {
	CriteriaBuilder cb = em.getCriteriaBuilder();
	Root<T> root = JpaUtils.findRoot(criteria, getEntityClass());
	String propertyPath = page.getSortName();

	if (log.isDebugEnabled())
		log.debug("Setting order as: " + propertyPath);

	Path<?> path = JpaUtils.getPath(root, propertyPath);

	return page.getOrder() == Page.Order.ASC ? cb.asc(path) : cb.desc(path);
}
 

public <E> List<Order> buildJpaOrders(Iterable<OrderBy> orders, Root<E> root, CriteriaBuilder builder, SearchParameters sp) {
    List<Order> jpaOrders = newArrayList();
    for (OrderBy ob : orders) {
        Path<?> path = jpaUtil.getPath(root, ob.getAttributes());
        jpaOrders.add(ob.isOrderDesc() ? builder.desc(path) : builder.asc(path));
    }
    return jpaOrders;
}
 

@Override
public List<StorageUnitEntity> getStorageUnitsByStoragePlatformAndBusinessObjectData(String storagePlatform,
    BusinessObjectDataEntity businessObjectDataEntity)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageUnitEntity> criteria = builder.createQuery(StorageUnitEntity.class);

    // The criteria root is the storage unit.
    Root<StorageUnitEntity> storageUnitEntity = criteria.from(StorageUnitEntity.class);

    // Join to the other tables we can filter on.
    Join<StorageUnitEntity, StorageEntity> storageEntity = storageUnitEntity.join(StorageUnitEntity_.storage);
    Join<StorageEntity, StoragePlatformEntity> storagePlatformEntity = storageEntity.join(StorageEntity_.storagePlatform);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(storagePlatformEntity.get(StoragePlatformEntity_.name)), storagePlatform.toUpperCase()));
    predicates.add(builder.equal(storageUnitEntity.get(StorageUnitEntity_.businessObjectData), businessObjectDataEntity));

    // Order by storage name.
    Order orderBy = builder.asc(storageEntity.get(StorageEntity_.name));

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

    // Execute the query and return the results.
    return entityManager.createQuery(criteria).getResultList();
}
 

@Override
public List<StorageUnitEntity> getS3StorageUnitsToRestore(int maxResult)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageUnitEntity> criteria = builder.createQuery(StorageUnitEntity.class);

    // The criteria root is the storage unit.
    Root<StorageUnitEntity> storageUnitEntityRoot = criteria.from(StorageUnitEntity.class);

    // Join to the other tables we can filter on.
    Join<StorageUnitEntity, StorageEntity> storageEntityJoin = storageUnitEntityRoot.join(StorageUnitEntity_.storage);
    Join<StorageEntity, StoragePlatformEntity> storagePlatformEntityJoin = storageEntityJoin.join(StorageEntity_.storagePlatform);
    Join<StorageUnitEntity, StorageUnitStatusEntity> storageUnitStatusEntityJoin = storageUnitEntityRoot.join(StorageUnitEntity_.status);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(storagePlatformEntityJoin.get(StoragePlatformEntity_.name), StoragePlatformEntity.S3));
    predicates.add(builder.equal(storageUnitStatusEntityJoin.get(StorageUnitStatusEntity_.code), StorageUnitStatusEntity.RESTORING));

    // Order the results by storage unit updated on timestamp.
    Order orderBy = builder.asc(storageUnitEntityRoot.get(StorageUnitEntity_.updatedOn));

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

    // Execute the query and return the results.
    return entityManager.createQuery(criteria).setMaxResults(maxResult).getResultList();
}
 

@Override
public List<StorageUnitEntity> getS3StorageUnitsToExpire(int maxResult)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageUnitEntity> criteria = builder.createQuery(StorageUnitEntity.class);

    // The criteria root is the storage unit.
    Root<StorageUnitEntity> storageUnitEntityRoot = criteria.from(StorageUnitEntity.class);

    // Join to the other tables we can filter on.
    Join<StorageUnitEntity, StorageEntity> storageEntityJoin = storageUnitEntityRoot.join(StorageUnitEntity_.storage);
    Join<StorageEntity, StoragePlatformEntity> storagePlatformEntityJoin = storageEntityJoin.join(StorageEntity_.storagePlatform);
    Join<StorageUnitEntity, StorageUnitStatusEntity> storageUnitStatusEntityJoin = storageUnitEntityRoot.join(StorageUnitEntity_.status);

    // Get the current time.
    Timestamp currentTime = new Timestamp(System.currentTimeMillis());

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(storagePlatformEntityJoin.get(StoragePlatformEntity_.name), StoragePlatformEntity.S3));
    predicates.add(builder.equal(storageUnitStatusEntityJoin.get(StorageUnitStatusEntity_.code), StorageUnitStatusEntity.RESTORED));
    predicates.add(builder.lessThan(storageUnitEntityRoot.get(StorageUnitEntity_.restoreExpirationOn), currentTime));

    // Order the results.
    Order orderBy = builder.asc(storageUnitEntityRoot.get(StorageUnitEntity_.restoreExpirationOn));

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

    // Execute the query and return the results.
    return entityManager.createQuery(criteria).setMaxResults(maxResult).getResultList();
}
 

@Override
public List<StorageUnitEntity> getLatestVersionStorageUnitsByStoragePlatformAndFileType(String storagePlatform, String businessObjectFormatFileType)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageUnitEntity> criteria = builder.createQuery(StorageUnitEntity.class);

    // The criteria root is the storage unit.
    Root<StorageUnitEntity> storageUnitEntityRoot = criteria.from(StorageUnitEntity.class);

    // Join to the other tables we can filter on.
    Join<StorageUnitEntity, StorageEntity> storageEntityJoin = storageUnitEntityRoot.join(StorageUnitEntity_.storage);
    Join<StorageEntity, StoragePlatformEntity> storagePlatformEntityJoin = storageEntityJoin.join(StorageEntity_.storagePlatform);
    Join<StorageUnitEntity, BusinessObjectDataEntity> businessObjectDataEntityJoin = storageUnitEntityRoot.join(StorageUnitEntity_.businessObjectData);
    Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> businessObjectFormatEntityJoin =
        businessObjectDataEntityJoin.join(BusinessObjectDataEntity_.businessObjectFormat);
    Join<BusinessObjectFormatEntity, FileTypeEntity> fileTypeEntityJoin = businessObjectFormatEntityJoin.join(BusinessObjectFormatEntity_.fileType);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(storagePlatformEntityJoin.get(StoragePlatformEntity_.name)), storagePlatform.toUpperCase()));
    predicates.add(builder.equal(builder.upper(fileTypeEntityJoin.get(FileTypeEntity_.code)), businessObjectFormatFileType.toUpperCase()));
    predicates.add(builder.isTrue(businessObjectFormatEntityJoin.get(BusinessObjectFormatEntity_.latestVersion)));
    predicates.add(builder.isTrue(businessObjectDataEntityJoin.get(BusinessObjectDataEntity_.latestVersion)));

    // Order by storage unit created on timestamp.
    Order orderBy = builder.asc(storageUnitEntityRoot.get(StorageUnitEntity_.createdOn));

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

    // Execute the query and return the results.
    return entityManager.createQuery(criteria).getResultList();
}
 

@Override
public List<BusinessObjectDataEntity> getBusinessObjectDataFromStorageOlderThan(StorageEntity storageEntity, int thresholdMinutes,
    List<String> businessObjectDataStatuses)
{
    // 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);

    // Join to the other tables we can filter on.
    Join<BusinessObjectDataEntity, StorageUnitEntity> storageUnitEntityJoin = businessObjectDataEntityRoot.join(BusinessObjectDataEntity_.storageUnits);

    // Compute threshold timestamp based on the current database timestamp and threshold minutes.
    Timestamp thresholdTimestamp = HerdDateUtils.addMinutes(getCurrentTimestamp(), -thresholdMinutes);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(storageUnitEntityJoin.get(StorageUnitEntity_.storageName), storageEntity.getName()));
    predicates.add(businessObjectDataEntityRoot.get(BusinessObjectDataEntity_.statusCode).in(businessObjectDataStatuses));
    predicates.add(builder.lessThanOrEqualTo(businessObjectDataEntityRoot.get(BusinessObjectDataEntity_.createdOn), thresholdTimestamp));

    // Order results by "created on" timestamp.
    Order orderBy = builder.asc(businessObjectDataEntityRoot.get(BusinessObjectDataEntity_.createdOn));

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

    // Execute the query and return the results.
    return entityManager.createQuery(criteria).getResultList();
}
 

@Override
public List<UserNamespaceAuthorizationEntity> getUserNamespaceAuthorizationsByUserId(String userId)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<UserNamespaceAuthorizationEntity> criteria = builder.createQuery(UserNamespaceAuthorizationEntity.class);

    // The criteria root is the user namespace authorization.
    Root<UserNamespaceAuthorizationEntity> userNamespaceAuthorizationEntity = criteria.from(UserNamespaceAuthorizationEntity.class);

    // Join to the other tables we can filter on.
    Join<UserNamespaceAuthorizationEntity, NamespaceEntity> namespaceEntity =
        userNamespaceAuthorizationEntity.join(UserNamespaceAuthorizationEntity_.namespace);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate queryRestriction =
        builder.equal(builder.upper(userNamespaceAuthorizationEntity.get(UserNamespaceAuthorizationEntity_.userId)), userId.toUpperCase());

    // Order by namespace code.
    Order orderBy = builder.asc(namespaceEntity.get(NamespaceEntity_.code));

    // Add all clauses for the query.
    criteria.select(userNamespaceAuthorizationEntity).where(queryRestriction).orderBy(orderBy);

    // Execute the query and return the result list.
    return entityManager.createQuery(criteria).getResultList();
}
 

@Override
public List<String> getUserIdsWithWriteOrWriteDescriptiveContentPermissionsByNamespace(NamespaceEntity namespaceEntity)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<String> criteria = builder.createQuery(String.class);

    // The criteria root is the user namespace authorization.
    Root<UserNamespaceAuthorizationEntity> userNamespaceAuthorizationEntityRoot = criteria.from(UserNamespaceAuthorizationEntity.class);

    // Get the user id column.
    Path<String> userIdColumn = userNamespaceAuthorizationEntityRoot.get(UserNamespaceAuthorizationEntity_.userId);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(userNamespaceAuthorizationEntityRoot.get(UserNamespaceAuthorizationEntity_.namespace), namespaceEntity));
    predicates.add(builder.or(builder.isTrue(userNamespaceAuthorizationEntityRoot.get(UserNamespaceAuthorizationEntity_.writePermission)),
        builder.isTrue(userNamespaceAuthorizationEntityRoot.get(UserNamespaceAuthorizationEntity_.writeDescriptiveContentPermission))));

    // Order by user id.
    Order orderBy = builder.asc(userNamespaceAuthorizationEntityRoot.get(UserNamespaceAuthorizationEntity_.userId));

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

    // Execute the query and return the result list.
    return entityManager.createQuery(criteria).getResultList();
}
 

/**
 * Get executable TypedQuery from JpaQueryParameter.
 *
 * @return executable TypedQuery
 */
protected final TypedQuery<T> getTypedQuery( CriteriaBuilder builder, JpaQueryParameters<T> parameters )
{
    List<Function<Root<T>, Predicate>> predicateProviders = parameters.getPredicates();
    List<Function<Root<T>, Order>> orderProviders = parameters.getOrders();
    preProcessPredicates( builder, predicateProviders );

    CriteriaQuery<T> query = builder.createQuery( getClazz() );
    Root<T> root = query.from( getClazz() );
    query.select( root );

    if ( !predicateProviders.isEmpty() )
    {
        List<Predicate> predicates = predicateProviders.stream().map( t -> t.apply( root ) ).collect( Collectors.toList() );
        query.where( predicates.toArray( new Predicate[0] ) );
    }

    if ( !orderProviders.isEmpty() )
    {
        List<Order> orders = orderProviders.stream().map( o -> o.apply( root ) ).collect( Collectors.toList() );
        query.orderBy( orders );
    }

    TypedQuery<T> typedQuery = getExecutableTypedQuery( query );

    if ( parameters.hasFirstResult() )
    {
        typedQuery.setFirstResult( parameters.getFirstResult() );
    }

    if ( parameters.hasMaxResult() )
    {
        typedQuery.setMaxResults( parameters.getMaxResults() );
    }

    return typedQuery
        .setHint( QueryHints.CACHEABLE, parameters.isCacheable( cacheable ) );
}