Java Code Examples for javax.persistence.criteria.CriteriaBuilder#count()

private List<String> entries(Business business, List<String> keys) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Word.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
	Root<Word> root = cq.from(Word.class);
	Expression<Long> express_count = cb.count(root.get(Word_.entry));
	Expression<String> express_entry = root.get(Word_.entry);
	cq.multiselect(express_entry, express_count).where(cb.isMember(root.get(Word_.value), cb.literal(keys)))
			.groupBy(express_entry).orderBy(cb.desc(express_count));
	List<Tuple> os = em.createQuery(cq).setMaxResults(500).getResultList();
	List<String> list = new ArrayList<>();
	for (Tuple t : os) {
		list.add(t.get(express_entry));
	}
	return list;
}
 

@Override
public Long getStorageFileCount(String storageName, String filePathPrefix)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> criteria = builder.createQuery(Long.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 path.
    Expression<Long> storageFileCount = builder.count(storageFileEntity.get(StorageFileEntity_.id));

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

    // Add the clauses for the query.
    criteria.select(storageFileCount).where(builder.and(storageNameRestriction, filePathRestriction));

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

@Override
public Long getBusinessObjectFormatCountByPartitionKeyGroup(PartitionKeyGroupEntity partitionKeyGroupEntity)
{
    // 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> businessObjectFormatEntity = criteria.from(BusinessObjectFormatEntity.class);

    // Create path.
    Expression<Long> businessObjectFormatCount = builder.count(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.id));

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate partitionKeyGroupRestriction =
        builder.equal(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.partitionKeyGroup), partitionKeyGroupEntity);

    criteria.select(businessObjectFormatCount).where(partitionKeyGroupRestriction);

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

@SuppressWarnings("unchecked")
public static void applyAggregation(Map<String, String> aggregationFuction, CriteriaBuilder cb, CriteriaQuery query, Map<String, Expression> selectionMap, Root root) {
    Selection<?> selection = null;
    for (String key : aggregationFuction.keySet()) {
        Expression expression = selectionMap.get(aggregationFuction.get(key));
        switch (AggregationFuction.fromCode(key)) {
        case MAX:
            //selection = cb.tuple(root,cb.max(expression));
            selection = cb.max(expression);
            break;
        case MIN:
            selection = cb.tuple(root,cb.min(expression));
            break;
        case SUM:
            selection = cb.tuple(root,cb.sum(expression));
            break;
        case AVG:
            selection = cb.tuple(root,cb.avg(expression));
            break;
        case COUNT:
            selection = cb.count(root);
            break;
        default:
            throw new InvalidArgumentException(String.format("Aggregation Fuction [%s] is unsupportted.", key));
        }
    }
    query.select(selection);
}
 

@Override
@SuppressWarnings({"rawtypes"})
public long getTotalRowCount() {
    Selection<T> selection = query.getSelection();
    List<Order> orderList = query.getOrderList();
    try {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        Expression<Long> countExpr;

        Set<Root<?>> roots = query.getRoots();
        if (roots.size() != 1) {
            throw new IllegalStateException("cannot compute totalRowCount in case of multiple query roots");
        }
        if (!query.getGroupList().isEmpty()) {
            throw new IllegalStateException("cannot compute totalRowCount for grouped queries");
        }

        // transform query to a count query
        Root root = roots.iterator().next();
        countExpr = isDistinct() ? builder.countDistinct(root) : builder.count(root);
        query.multiselect(countExpr);
        query.orderBy(new ArrayList<>());

        TypedQuery countQuery = em.createQuery(query);

        return (Long) countQuery.getSingleResult();
    } finally {
        // transform count query back to regular query
        query.multiselect(selection);
        query.orderBy(orderList);
    }
}
 

@Override
@SuppressWarnings({ "rawtypes" })
public long getTotalRowCount() {
	Selection<T> selection = query.getSelection();
	List<Order> orderList = query.getOrderList();
	try {
		CriteriaBuilder builder = em.getCriteriaBuilder();
		Expression<Long> countExpr;

		Set<Root<?>> roots = query.getRoots();
		if (roots.size() != 1) {
			throw new IllegalStateException("cannot compute totalRowCount in case of multiple query roots");
		}
		if (!query.getGroupList().isEmpty()) {
			throw new IllegalStateException("cannot compute totalRowCount for grouped queries");
		}

		// transform query to a count query
		Root root = roots.iterator().next();
		countExpr = builder.count(root);
		query.multiselect(countExpr);
		query.orderBy(new ArrayList<Order>());
		TypedQuery countQuery = em.createQuery(query);

		return (Long) countQuery.getSingleResult();
	}
	finally {
		// transform count query back to regular query
		query.multiselect(selection);
		query.orderBy(orderList);
	}
}
 

@Override
public Long getBusinessObjectDataCount(BusinessObjectFormatKey businessObjectFormatKey)
{
    // 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 data.
    Root<BusinessObjectDataEntity> businessObjectDataEntity = criteria.from(BusinessObjectDataEntity.class);

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

    // Create path.
    Expression<Long> businessObjectDataCount = builder.count(businessObjectDataEntity.get(BusinessObjectDataEntity_.id));

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate queryRestriction =
        builder.equal(builder.upper(namespaceEntity.get(NamespaceEntity_.code)), businessObjectFormatKey.getNamespace().toUpperCase());
    queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)),
        businessObjectFormatKey.getBusinessObjectDefinitionName().toUpperCase()));
    queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage)),
        businessObjectFormatKey.getBusinessObjectFormatUsage().toUpperCase()));
    queryRestriction = builder.and(queryRestriction,
        builder.equal(builder.upper(fileTypeEntity.get(FileTypeEntity_.code)), businessObjectFormatKey.getBusinessObjectFormatFileType().toUpperCase()));
    queryRestriction = builder.and(queryRestriction, builder.equal(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion),
        businessObjectFormatKey.getBusinessObjectFormatVersion()));

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

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

@Override
public int getUsersCount(Map<String, String> params, RealmModel realm) {
    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery<Long> userQuery = qb.createQuery(Long.class);
    Root<UserEntity> from = userQuery.from(UserEntity.class);
    Expression<Long> count = qb.count(from);

    userQuery = userQuery.select(count);
    List<Predicate> restrictions = new ArrayList<>();
    restrictions.add(qb.equal(from.get("realmId"), realm.getId()));

    for (Map.Entry<String, String> entry : params.entrySet()) {
        String key = entry.getKey();
        String value = entry.getValue();
        if (key == null || value == null) {
            continue;
        }

        switch (key) {
            case UserModel.USERNAME:
                restrictions.add(qb.like(from.get("username"), "%" + value + "%"));
                break;
            case UserModel.FIRST_NAME:
                restrictions.add(qb.like(from.get("firstName"), "%" + value + "%"));
                break;
            case UserModel.LAST_NAME:
                restrictions.add(qb.like(from.get("lastName"), "%" + value + "%"));
                break;
            case UserModel.EMAIL:
                restrictions.add(qb.like(from.get("email"), "%" + value + "%"));
                break;
        }
    }

    userQuery = userQuery.where(restrictions.toArray(new Predicate[0]));
    TypedQuery<Long> query = em.createQuery(userQuery);
    Long result = query.getSingleResult();

    return result.intValue();
}
 

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

protected List<T> searchResourcesUsingCriteria(
		SearchCriteria searchCriteria, List<SearchField> searchFieldList,
		List<SortField> sortFieldList, VList vList) {
	// boolean filterEnabled = getDao().enableVisiblityFilters(tClass,
	// true);

	EntityManager em = getDao().getEntityManager();
	CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
	CriteriaQuery criteria = criteriaBuilder.createQuery();
	Root<T> from = criteria.from(tEntityClass);

	Predicate resourceConditions = buildResourceSpecificConditions(
			criteriaBuilder, from, searchCriteria);
	Predicate userConditions = buildUserConditions(
			searchCriteria.getParamList(), searchFieldList,
			criteriaBuilder, from);

	if (resourceConditions != null) {
		criteria.where(criteriaBuilder.and(resourceConditions,
				userConditions));
	} else {
		criteria.where(criteriaBuilder.and(userConditions));
	}

	// Get total count of the rows which meet the search criteria
	long count = -1;
	if (searchCriteria.isGetCount()) {

		Expression<Long> countExpression = criteriaBuilder.count(from
				.get("id"));
		criteria.select(countExpression);
		TypedQuery<Long> countQuery = em.createQuery(criteria);
		count = getDao().executeCountQueryInSecurityContext(tEntityClass,
				countQuery);
		if (count == 0) {
			return Collections.emptyList();
		}
	}

	// construct the sort clause
	setSortClause(searchCriteria, sortFieldList, criteriaBuilder, criteria,
			from);

	criteria.select(from);
	TypedQuery<T> typedQuery = em.createQuery(criteria);
	searchUtil.updateQueryPageSize(typedQuery, searchCriteria);

	List<T> resultList = getDao().executeQueryInSecurityContext(
			tEntityClass, typedQuery);

	if (vList != null) {
		// Set the meta values for the query result
		vList.setPageSize(typedQuery.getMaxResults());
		vList.setSortBy(searchCriteria.getSortBy());
		vList.setSortType(searchCriteria.getSortType());
		vList.setStartIndex(typedQuery.getFirstResult());
		vList.setTotalCount(count);
	}

	// if (filterEnabled) {
	// getDao().disableVisiblityFilters(tClass);
	// }

	return resultList;
}
 

@Override
public int getUsersCount(Map<String, String> params, RealmModel realm, Set<String> groupIds) {
    if (groupIds == null || groupIds.isEmpty()) {
        return 0;
    }

    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery<Long> userQuery = qb.createQuery(Long.class);
    Root<UserGroupMembershipEntity> from = userQuery.from(UserGroupMembershipEntity.class);
    Expression<Long> count = qb.count(from.get("user"));
    userQuery = userQuery.select(count);

    List<Predicate> restrictions = new ArrayList<>();
    restrictions.add(qb.equal(from.get("user").get("realmId"), realm.getId()));
    restrictions.add(from.get("groupId").in(groupIds));

    for (Map.Entry<String, String> entry : params.entrySet()) {
        String key = entry.getKey();
        String value = entry.getValue();
        if (key == null || value == null) {
            continue;
        }

        switch (key) {
            case UserModel.USERNAME:
                restrictions.add(qb.like(from.get("user").get("username"), "%" + value + "%"));
                break;
            case UserModel.FIRST_NAME:
                restrictions.add(qb.like(from.get("user").get("firstName"), "%" + value + "%"));
                break;
            case UserModel.LAST_NAME:
                restrictions.add(qb.like(from.get("user").get("lastName"), "%" + value + "%"));
                break;
            case UserModel.EMAIL:
                restrictions.add(qb.like(from.get("user").get("email"), "%" + value + "%"));
                break;
        }
    }

    userQuery = userQuery.where(restrictions.toArray(new Predicate[0]));
    TypedQuery<Long> query = em.createQuery(userQuery);
    Long result = query.getSingleResult();

    return result.intValue();
}
 

@Override
public <R> Selection<Long> toSelection(CriteriaQuery<R> query, CriteriaBuilder builder, Path<? extends P> path)
{
    return builder.count(path.get(attribute));
}