List of usage examples for javax.persistence.criteria CriteriaQuery groupBy
CriteriaQuery<T> groupBy(List<Expression<?>> grouping);
From source file:de.hopmann.msc.slave.service.PackageInstallationBean.java
public PackageInstallationEntity getInstallationEntity(PackageResolved packageModel, Set<PackageInstallationEntity> requiredDependencies, PackageInstallerHolder packageInstallerHolder) { try {/* ww w. j a va2 s . c o m*/ // TODO repository version, flavor, etc. CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<PackageInstallationEntity> query = cb.createQuery(PackageInstallationEntity.class); Root<PackageInstallationEntity> p = query.from(PackageInstallationEntity.class); Path<PackageInstallerEntity> pInstaller = p.get(PackageInstallationEntity_.packageInstaller); List<Predicate> predicates = new ArrayList<Predicate>(); predicates.add(cb.equal(p.get(PackageInstallationEntity_.packageName), packageModel.getPackageName())); predicates.add(cb.equal(p.get(PackageInstallationEntity_.sourceVersion).get(Version_.versionNumber), packageModel.getPackageAccessor().getSourceVersion().getVersionNumber())); predicates.add(cb.between(pInstaller.get(PackageInstallerEntity_.version).get(Version_.versionNumber), packageInstallerHolder.getDependencyMinVersion(), packageInstallerHolder.getDependencyMaxVersion())); // TODO flavor arch if (requiredDependencies.isEmpty()) { query.where(cb.and(predicates.toArray(new Predicate[] {}))); } else { SetJoin<PackageInstallationEntity, PackageInstallationEntity> join = p .join(PackageInstallationEntity_.actualDependencies); predicates.add(join.in(requiredDependencies)); // Expression<Set<PackageInstallationEntity>> pDependencies = p // .get(PackageInstallationEntity_.actualDependencies); // predicates.add(pDependencies.in(requiredDependencies)); query.where(cb.and(predicates.toArray(new Predicate[] {}))); Path<Long> pId = p.get(PackageInstallationEntity_.id); query.groupBy(pId); query.having(cb.ge(cb.count(pId), requiredDependencies.size())); query.distinct(true); } return entityManager.createQuery(query).getSingleResult(); } catch (NoResultException e) { return null; } }
From source file:org.agric.oxm.utils.JpaUtils.java
/** * Copy Criteria without Selection/*from w w w . j av a2 s . c om*/ * * @param from * source Criteria * @param to * destination Criteria */ public static void copyCriteriaNoSelection(CriteriaQuery<?> from, CriteriaQuery<?> to) { // Copy Roots for (Root<?> root : from.getRoots()) { Root<?> dest = to.from(root.getJavaType()); dest.alias(getOrCreateAlias(root)); copyJoins(root, dest); } to.groupBy(from.getGroupList()); to.distinct(from.isDistinct()); to.having(from.getGroupRestriction()); to.where(from.getRestriction()); to.orderBy(from.getOrderList()); }
From source file:org.apache.ambari.server.orm.dao.ServiceConfigDAO.java
@RequiresSession public List<ServiceConfigEntity> getLastServiceConfigVersionsForGroups(Collection<Long> configGroupIds) { if (configGroupIds == null || configGroupIds.isEmpty()) { return Collections.emptyList(); }//from ww w .j ava 2 s .c o m CriteriaBuilder cb = entityManagerProvider.get().getCriteriaBuilder(); CriteriaQuery<Tuple> cq = cb.createTupleQuery(); Root<ServiceConfigEntity> groupVersion = cq.from(ServiceConfigEntity.class); cq.multiselect(groupVersion.get("groupId").alias("groupId"), cb.max(groupVersion.<Long>get("version")).alias("lastVersion")); cq.where(groupVersion.get("groupId").in(configGroupIds)); cq.groupBy(groupVersion.get("groupId")); List<Tuple> tuples = daoUtils.selectList(entityManagerProvider.get().createQuery(cq)); List<ServiceConfigEntity> result = new ArrayList<ServiceConfigEntity>(); //subquery look to be very poor, no bulk select then, cache should help here as result size is naturally limited for (Tuple tuple : tuples) { CriteriaQuery<ServiceConfigEntity> sce = cb.createQuery(ServiceConfigEntity.class); Root<ServiceConfigEntity> sceRoot = sce.from(ServiceConfigEntity.class); sce.where(cb.and(cb.equal(sceRoot.get("groupId"), tuple.get("groupId")), cb.equal(sceRoot.get("version"), tuple.get("lastVersion")))); sce.select(sceRoot); result.add(daoUtils.selectSingle(entityManagerProvider.get().createQuery(sce))); } return result; }
From source file:org.apereo.portal.i18n.dao.jpa.JpaMessageDao.java
@Override public void afterPropertiesSet() throws Exception { this.codeParameter = this.createParameterExpression(String.class, "code"); this.localeParameter = this.createParameterExpression(Locale.class, "locale"); this.findMessageByCodeAndLocaleQuery = this .createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<MessageImpl>>() { @Override//from w w w . j av a 2 s . co m public CriteriaQuery<MessageImpl> apply(CriteriaBuilder cb) { final CriteriaQuery<MessageImpl> criteriaQuery = cb.createQuery(MessageImpl.class); final Root<MessageImpl> root = criteriaQuery.from(MessageImpl.class); criteriaQuery.select(root); criteriaQuery.where(cb.and(cb.equal(root.get(MessageImpl_.code), codeParameter), cb.equal(root.get(MessageImpl_.locale), localeParameter))); criteriaQuery.orderBy(cb.asc(root.get(MessageImpl_.code)), cb.asc(root.get(MessageImpl_.locale))); return criteriaQuery; } }); this.findMessageByCodeQuery = this .createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<MessageImpl>>() { @Override public CriteriaQuery<MessageImpl> apply(CriteriaBuilder cb) { final CriteriaQuery<MessageImpl> criteriaQuery = cb.createQuery(MessageImpl.class); final Root<MessageImpl> root = criteriaQuery.from(MessageImpl.class); criteriaQuery.select(root); criteriaQuery.where(cb.equal(root.get(MessageImpl_.code), codeParameter)); criteriaQuery.orderBy(cb.asc(root.get(MessageImpl_.locale))); return criteriaQuery; } }); this.findMessageByLocaleQuery = this .createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<MessageImpl>>() { @Override public CriteriaQuery<MessageImpl> apply(CriteriaBuilder cb) { final CriteriaQuery<MessageImpl> criteriaQuery = cb.createQuery(MessageImpl.class); final Root<MessageImpl> root = criteriaQuery.from(MessageImpl.class); criteriaQuery.select(root); criteriaQuery.where(cb.equal(root.get(MessageImpl_.locale), localeParameter)); criteriaQuery.orderBy(cb.asc(root.get(MessageImpl_.code))); return criteriaQuery; } }); this.findCodes = this.createCriteriaQuery(new Function<CriteriaBuilder, CriteriaQuery<String>>() { @Override public CriteriaQuery<String> apply(CriteriaBuilder cb) { final CriteriaQuery<String> criteriaQuery = cb.createQuery(String.class); final Root<MessageImpl> root = criteriaQuery.from(MessageImpl.class); criteriaQuery.select(root.get(MessageImpl_.code)); criteriaQuery.groupBy(root.get(MessageImpl_.code)); return criteriaQuery; } }); }
From source file:org.easy.criteria.CriteriaProcessor.java
/** * Finds all the tuples for the given criteria. Make sure you have provided * columns information in CriteriaContainer that you want in this tuple * result-set.//from w w w .ja va 2 s .c o m * * @param criteria * - Criteria that you want to apply to this search. * @param distinct * - * @param startIndex * - Pass 0 or less to disable paging. * @param maxResult * - Pass 0 or less to disable paging. * @param lockMode * - Pass NULL if your are not managing transaction. * LockModeType.NONE will through exception if no transaction is * active. * @return - A list of tuples or an empty list if no result were found. */ public <T> List<Tuple> findAllTuple(CriteriaComposer<T> criteria, boolean distinct, QueryProperties properties) { log.trace("CriteriaProcessor.findAllTuple"); Preconditions.checkNotNull(criteria); Class<T> forClass = criteria.getEntityClass(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class); log.debug("root =" + forClass.getName()); Root<T> root = criteriaQuery.from(forClass); List<Predicate> wherePredicates = new ArrayList<Predicate>(); List<Selection<?>> selectPredicates = new ArrayList<Selection<?>>(); Map<Integer, Order> orderByPredicates = new HashMap<Integer, Order>(0); Map<Integer, Expression<?>> groupByPredicates = new HashMap<Integer, Expression<?>>(0); List<Predicate> havingPredicates = new ArrayList<Predicate>(0); if (criteria != null) { criteria.generateJoins(root); criteria.generateSelect(criteriaBuilder, selectPredicates); criteria.generateGroupBy(criteriaBuilder, groupByPredicates); criteria.generateWhere(criteriaBuilder, wherePredicates); criteria.generateOrderBy(criteriaBuilder, orderByPredicates); criteria.generateHaving(criteriaBuilder, havingPredicates); } Preconditions.checkState(selectPredicates != null); Preconditions.checkArgument(selectPredicates.size() > 0, "No column name found for select clause. " + "Atleast one should be provided for Tuple result. Consider using findAllEntity instead."); criteriaQuery.multiselect(selectPredicates); criteriaQuery.where(wherePredicates.toArray(new Predicate[wherePredicates.size()])); if (orderByPredicates != null && orderByPredicates.size() > 0) { Order[] orderByList = new Order[orderByPredicates.size()]; orderByPredicates.values().toArray(orderByList); criteriaQuery.orderBy(orderByList); } if (groupByPredicates != null && groupByPredicates.size() > 0) { Expression<?>[] groupByList = new Expression<?>[groupByPredicates.size()]; groupByPredicates.values().toArray(groupByList); criteriaQuery.groupBy(groupByList); } criteriaQuery.having(havingPredicates.toArray(new Predicate[havingPredicates.size()])); TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery); if (properties != null) properties.applyProperties(query); List<Tuple> tuples = query.getResultList(); if (tuples == null) tuples = new ArrayList<Tuple>(0); log.debug("CriteriaProcessor.findAllEntity result size=" + tuples.size()); return tuples; }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * {@inheritDoc}/*from www . j a v a 2 s . c o m*/ */ @Override public List<BusinessObjectFormatKey> getBusinessObjectFormats( BusinessObjectDefinitionKey businessObjectDefinitionKey, 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 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; }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * TODO: Make this method the main body of getStorageUploadStats once we migrate away from Oracle getStorageUploadStats TODO: that leverages the storage * file view to query. This method is meant to be database agnostic. * * @param storageAlternateKey the storage alternate key * @param dateRange the date range// w ww. jav a2 s .c o m * * @return the upload statistics */ private StorageDailyUploadStats getStorageUploadStatsDatabaseAgnostic( StorageAlternateKeyDto storageAlternateKey, DateRangeDto dateRange) { CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteria = builder.createTupleQuery(); Root<StorageFileViewEntity> storageFileViewEntity = criteria.from(StorageFileViewEntity.class); Path<Date> createdDate = storageFileViewEntity.get(StorageFileViewEntity_.createdDate); Expression<Long> totalFilesExpression = builder .count(storageFileViewEntity.get(StorageFileViewEntity_.storageFileId)); Expression<Long> totalBytesExpression = builder .sum(storageFileViewEntity.get(StorageFileViewEntity_.fileSizeInBytes)); Predicate storageNameRestriction = builder.equal( builder.upper(storageFileViewEntity.get(StorageFileViewEntity_.storageCode)), storageAlternateKey.getStorageName().toUpperCase()); Predicate createDateRestriction = builder.and( builder.greaterThanOrEqualTo(createdDate, dateRange.getLowerDate()), builder.lessThanOrEqualTo(createdDate, dateRange.getUpperDate())); criteria.multiselect(createdDate, totalFilesExpression, totalBytesExpression); criteria.where(builder.and(storageNameRestriction, createDateRestriction)); List<Expression<?>> grouping = new ArrayList<>(); grouping.add(createdDate); criteria.groupBy(grouping); criteria.orderBy(builder.asc(createdDate)); // Retrieve and return the storage upload statistics. List<Tuple> tuples = entityManager.createQuery(criteria).getResultList(); StorageDailyUploadStats uploadStats = new StorageDailyUploadStats(); for (Tuple tuple : tuples) { StorageDailyUploadStat uploadStat = new StorageDailyUploadStat(); uploadStats.getStorageDailyUploadStats().add(uploadStat); uploadStat.setUploadDate(DmDateUtils.getXMLGregorianCalendarValue(tuple.get(createdDate))); uploadStat.setTotalFiles(tuple.get(totalFilesExpression)); uploadStat.setTotalBytes(tuple.get(totalBytesExpression)); } return uploadStats; }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * TODO: Remove this method once we migrate away from Oracle getStorageUploadStats that uses Oracle specific 'trunc' function. * * @param storageAlternateKey the storage alternate key * @param dateRange the date range// ww w . jav a 2s . c om * * @return the upload statistics */ private StorageDailyUploadStats getStorageUploadStatsOracle(StorageAlternateKeyDto storageAlternateKey, DateRangeDto dateRange) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteria = builder.createTupleQuery(); // The criteria root is the storage file. 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 paths. Expression<Date> truncCreatedOnDateExpression = builder.function("trunc", Date.class, storageFileEntity.get(StorageFileEntity_.createdOn)); Expression<Long> totalFilesExpression = builder.count(storageFileEntity.get(StorageFileEntity_.id)); Expression<Long> totalBytesExpression = builder .sum(storageFileEntity.get(StorageFileEntity_.fileSizeBytes)); // Create the standard restrictions (i.e. the standard where clauses). Predicate storageNameRestriction = builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageAlternateKey.getStorageName().toUpperCase()); Predicate createDateRestriction = builder.and( builder.greaterThanOrEqualTo(truncCreatedOnDateExpression, dateRange.getLowerDate()), builder.lessThanOrEqualTo(truncCreatedOnDateExpression, dateRange.getUpperDate())); criteria.multiselect(truncCreatedOnDateExpression, totalFilesExpression, totalBytesExpression); criteria.where(builder.and(storageNameRestriction, createDateRestriction)); // Create the group by clause. List<Expression<?>> grouping = new ArrayList<>(); grouping.add(truncCreatedOnDateExpression); criteria.groupBy(grouping); // Create the order by clause. criteria.orderBy(builder.asc(truncCreatedOnDateExpression)); // Retrieve and return the storage upload statistics. List<Tuple> tuples = entityManager.createQuery(criteria).getResultList(); StorageDailyUploadStats uploadStats = new StorageDailyUploadStats(); for (Tuple tuple : tuples) { StorageDailyUploadStat uploadStat = new StorageDailyUploadStat(); uploadStats.getStorageDailyUploadStats().add(uploadStat); uploadStat.setUploadDate( DmDateUtils.getXMLGregorianCalendarValue(tuple.get(truncCreatedOnDateExpression))); uploadStat.setTotalFiles(tuple.get(totalFilesExpression)); uploadStat.setTotalBytes(tuple.get(totalBytesExpression)); } return uploadStats; }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * TODO: Make this method the main body of getStorageUploadStatsByBusinessObjectDefinition once we migrate away from Oracle TODO: * getStorageUploadStatsByBusinessObjectDefinition that uses storage file view. This method is meant to be database agnostic. * * @param storageAlternateKey the storage alternate key * @param dateRange the date range/*from w ww .j ava2 s .c om*/ * * @return the upload statistics */ private StorageBusinessObjectDefinitionDailyUploadStats getStorageUploadStatsByBusinessObjectDefinitionDatabaseAgnostic( StorageAlternateKeyDto storageAlternateKey, DateRangeDto dateRange) { CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteria = builder.createTupleQuery(); Root<StorageFileViewEntity> storageFileViewEntity = criteria.from(StorageFileViewEntity.class); Path<String> namespaceCode = storageFileViewEntity.get(StorageFileViewEntity_.namespaceCode); Path<String> dataProviderCode = storageFileViewEntity.get(StorageFileViewEntity_.dataProviderCode); Path<String> businessObjectDefinitionName = storageFileViewEntity .get(StorageFileViewEntity_.businessObjectDefinitionName); Path<Date> createdDate = storageFileViewEntity.get(StorageFileViewEntity_.createdDate); Expression<Long> totalFilesExpression = builder .count(storageFileViewEntity.get(StorageFileViewEntity_.storageFileId)); Expression<Long> totalBytesExpression = builder .sum(storageFileViewEntity.get(StorageFileViewEntity_.fileSizeInBytes)); Predicate storageNameRestriction = builder.equal( builder.upper(storageFileViewEntity.get(StorageFileViewEntity_.storageCode)), storageAlternateKey.getStorageName().toUpperCase()); Predicate createDateRestriction = builder.and( builder.greaterThanOrEqualTo(createdDate, dateRange.getLowerDate()), builder.lessThanOrEqualTo(createdDate, dateRange.getUpperDate())); criteria.multiselect(createdDate, namespaceCode, dataProviderCode, businessObjectDefinitionName, totalFilesExpression, totalBytesExpression); criteria.where(builder.and(storageNameRestriction, createDateRestriction)); // Create the group by clause. List<Expression<?>> grouping = new ArrayList<>(); grouping.add(createdDate); grouping.add(namespaceCode); grouping.add(dataProviderCode); grouping.add(businessObjectDefinitionName); criteria.groupBy(grouping); // Create the order by clause. criteria.orderBy(builder.asc(createdDate), builder.asc(namespaceCode), builder.asc(dataProviderCode), builder.asc(businessObjectDefinitionName)); // Retrieve and return the storage upload statistics. List<Tuple> tuples = entityManager.createQuery(criteria).getResultList(); StorageBusinessObjectDefinitionDailyUploadStats uploadStats = new StorageBusinessObjectDefinitionDailyUploadStats(); for (Tuple tuple : tuples) { StorageBusinessObjectDefinitionDailyUploadStat uploadStat = new StorageBusinessObjectDefinitionDailyUploadStat(); uploadStats.getStorageBusinessObjectDefinitionDailyUploadStats().add(uploadStat); uploadStat.setUploadDate(DmDateUtils.getXMLGregorianCalendarValue(tuple.get(createdDate))); uploadStat.setNamespace(tuple.get(namespaceCode)); uploadStat.setDataProviderName(tuple.get(dataProviderCode)); uploadStat.setBusinessObjectDefinitionName(tuple.get(businessObjectDefinitionName)); uploadStat.setTotalFiles(tuple.get(totalFilesExpression)); uploadStat.setTotalBytes(tuple.get(totalBytesExpression)); } return uploadStats; }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * TODO: Remove this method once we migrate away from Oracle getStorageUploadStatsByBusinessObjectDefinition that uses Oracle specific 'trunc' function. * * @param storageAlternateKey the storage alternate key * @param dateRange the date range/* w ww. j a v a 2 s .co m*/ * * @return the upload statistics */ private StorageBusinessObjectDefinitionDailyUploadStats getStorageUploadStatsByBusinessObjectDefinitionOracle( StorageAlternateKeyDto storageAlternateKey, DateRangeDto dateRange) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteria = builder.createTupleQuery(); // The criteria root is the storage file. 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); Join<StorageUnitEntity, BusinessObjectDataEntity> businessObjectDataEntity = storageUnitEntity .join(StorageUnitEntity_.businessObjectData); Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> businessObjectFormatEntity = businessObjectDataEntity .join(BusinessObjectDataEntity_.businessObjectFormat); Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity = businessObjectFormatEntity .join(BusinessObjectFormatEntity_.businessObjectDefinition); Join<BusinessObjectDefinitionEntity, DataProviderEntity> dataProviderEntity = businessObjectDefinitionEntity .join(BusinessObjectDefinitionEntity_.dataProvider); Join<BusinessObjectDefinitionEntity, NamespaceEntity> namespaceEntity = businessObjectDefinitionEntity .join(BusinessObjectDefinitionEntity_.namespace); // Create paths and expressions. Path<String> namespacePath = namespaceEntity.get(NamespaceEntity_.code); Path<String> dataProviderNamePath = dataProviderEntity.get(DataProviderEntity_.name); Path<String> businessObjectDefinitionNamePath = businessObjectDefinitionEntity .get(BusinessObjectDefinitionEntity_.name); Expression<Date> truncCreatedOnDateExpression = builder.function("trunc", Date.class, storageFileEntity.get(StorageFileEntity_.createdOn)); Expression<Long> totalFilesExpression = builder.count(storageFileEntity.get(StorageFileEntity_.id)); Expression<Long> totalBytesExpression = builder .sum(storageFileEntity.get(StorageFileEntity_.fileSizeBytes)); // Create the standard restrictions (i.e. the standard where clauses). Predicate storageNameRestriction = builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageAlternateKey.getStorageName().toUpperCase()); Predicate createDateRestriction = builder.and( builder.greaterThanOrEqualTo(truncCreatedOnDateExpression, dateRange.getLowerDate()), builder.lessThanOrEqualTo(truncCreatedOnDateExpression, dateRange.getUpperDate())); criteria.multiselect(truncCreatedOnDateExpression, namespacePath, dataProviderNamePath, businessObjectDefinitionNamePath, totalFilesExpression, totalBytesExpression); criteria.where(builder.and(storageNameRestriction, createDateRestriction)); // Create the group by clause. List<Expression<?>> grouping = new ArrayList<>(); grouping.add(truncCreatedOnDateExpression); grouping.add(namespacePath); grouping.add(dataProviderNamePath); grouping.add(businessObjectDefinitionNamePath); criteria.groupBy(grouping); // Create the order by clause. criteria.orderBy(builder.asc(truncCreatedOnDateExpression), builder.asc(namespacePath), builder.asc(dataProviderNamePath), builder.asc(businessObjectDefinitionNamePath)); // Retrieve and return the storage upload statistics. List<Tuple> tuples = entityManager.createQuery(criteria).getResultList(); StorageBusinessObjectDefinitionDailyUploadStats uploadStats = new StorageBusinessObjectDefinitionDailyUploadStats(); for (Tuple tuple : tuples) { StorageBusinessObjectDefinitionDailyUploadStat uploadStat = new StorageBusinessObjectDefinitionDailyUploadStat(); uploadStats.getStorageBusinessObjectDefinitionDailyUploadStats().add(uploadStat); uploadStat.setUploadDate( DmDateUtils.getXMLGregorianCalendarValue(tuple.get(truncCreatedOnDateExpression))); uploadStat.setNamespace(tuple.get(namespacePath)); uploadStat.setDataProviderName(tuple.get(dataProviderNamePath)); uploadStat.setBusinessObjectDefinitionName(tuple.get(businessObjectDefinitionNamePath)); uploadStat.setTotalFiles(tuple.get(totalFilesExpression)); uploadStat.setTotalBytes(tuple.get(totalBytesExpression)); } return uploadStats; }