List of usage examples for javax.persistence.criteria Join join
<Y> Join<X, Y> join(SingularAttribute<? super X, Y> attribute);
From source file:gov.guilin.dao.impl.ProductDaoImpl.java
public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class); Root<Product> product = criteriaQuery.from(Product.class); Join<Product, OrderItem> orderItems = product.join("orderItems"); Join<Product, gov.guilin.entity.Order> order = orderItems.join("order"); criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price"), criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))); Predicate restrictions = criteriaBuilder.conjunction(); if (beginDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate)); }//from w w w . j a v a2s .c om if (endDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate)); } restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed), criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid)); criteriaQuery.where(restrictions); criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price")); criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum( criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))))); TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT); if (count != null && count >= 0) { query.setMaxResults(count); } return query.getResultList(); }
From source file:org.broadleafcommerce.core.catalog.dao.ProductDaoImpl.java
protected List<Product> readFilteredActiveProductsByCategoryInternal(Long categoryId, Date currentDate, SearchCriteria searchCriteria) { // Set up the criteria query that specifies we want to return Products CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Product> criteria = builder.createQuery(Product.class); // The root of our search is Category since we are browsing Root<CategoryProductXrefImpl> productXref = criteria.from(CategoryProductXrefImpl.class); // We want to filter on attributes from product and sku Join<CategoryProductXref, Product> product = productXref.join("product"); Join<Product, Sku> sku = product.join("defaultSku"); Join<CategoryProductXref, Category> category = productXref.join("category"); // Product objects are what we want back criteria.select(product);/*from w w w. ja v a 2s. c o m*/ // We only want results from the determine category List<Predicate> restrictions = new ArrayList<Predicate>(); restrictions.add(category.get("id").in(sandBoxHelper.mergeCloneIds(CategoryImpl.class, categoryId))); attachSearchCriteria(searchCriteria, product, sku, restrictions); attachActiveRestriction(currentDate, product, sku, restrictions); attachOrderBy(searchCriteria, product, sku, criteria); // Execute the query with the restrictions criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Product> typedQuery = em.createQuery(criteria); //don't cache - not really practical for open ended search //typedQuery.setHint(SandBoxHelper.QueryHints.FILTER_INCLUDE, ".*CategoryProductXrefImpl"); return typedQuery.getResultList(); }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * {@inheritDoc}/*from www. j a v a 2 s.c om*/ */ @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
/** * {@inheritDoc}/*from w ww .j a v a 2 s .c o m*/ */ @Override public CustomDdlEntity getCustomDdlByKey(CustomDdlKey customDdlKey) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<CustomDdlEntity> criteria = builder.createQuery(CustomDdlEntity.class); // The criteria root is the custom DDL. Root<CustomDdlEntity> customDdlEntity = criteria.from(CustomDdlEntity.class); // Join to the other tables we can filter on. Join<CustomDdlEntity, BusinessObjectFormatEntity> businessObjectFormatEntity = customDdlEntity .join(CustomDdlEntity_.businessObjectFormat); Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> businessObjectDefinitionEntity = businessObjectFormatEntity .join(BusinessObjectFormatEntity_.businessObjectDefinition); Join<BusinessObjectFormatEntity, FileTypeEntity> fileTypeEntity = businessObjectFormatEntity .join(BusinessObjectFormatEntity_.fileType); Join<BusinessObjectDefinitionEntity, NamespaceEntity> namespaceEntity = businessObjectDefinitionEntity .join(BusinessObjectDefinitionEntity_.namespace); // Create the standard restrictions (i.e. the standard where clauses). Predicate queryRestriction = builder.equal(builder.upper(namespaceEntity.get(NamespaceEntity_.code)), customDdlKey.getNamespace().toUpperCase()); queryRestriction = builder.and(queryRestriction, builder.equal( builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)), customDdlKey.getBusinessObjectDefinitionName().toUpperCase())); queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage)), customDdlKey.getBusinessObjectFormatUsage().toUpperCase())); queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(fileTypeEntity.get(FileTypeEntity_.code)), customDdlKey.getBusinessObjectFormatFileType().toUpperCase())); queryRestriction = builder.and(queryRestriction, builder.equal( businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion), customDdlKey.getBusinessObjectFormatVersion())); queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(customDdlEntity.get(CustomDdlEntity_.customDdlName)), customDdlKey.getCustomDdlName().toUpperCase())); // Add select and where clauses. criteria.select(customDdlEntity).where(queryRestriction); return executeSingleResultQuery(criteria, String.format( "Found more than one custom DDL instance with parameters {businessObjectDefinitionName=\"%s\"," + " businessObjectFormatUsage=\"%s\", businessObjectFormatFileType=\"%s\", businessObjectFormatVersion=\"%d\", customDdlName=\"%s\"}.", customDdlKey.getBusinessObjectDefinitionName(), customDdlKey.getBusinessObjectFormatUsage(), customDdlKey.getBusinessObjectFormatFileType(), customDdlKey.getBusinessObjectFormatVersion(), customDdlKey.getCustomDdlName())); }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * {@inheritDoc}/*www . j a v a 2s . co m*/ */ @Override public List<CustomDdlKey> getCustomDdls(BusinessObjectFormatKey businessObjectFormatKey) { // 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<CustomDdlEntity> customDdlEntity = criteria.from(CustomDdlEntity.class); // Join to the other tables we can filter on. Join<CustomDdlEntity, BusinessObjectFormatEntity> businessObjectFormatEntity = customDdlEntity .join(CustomDdlEntity_.businessObjectFormat); 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); Path<String> customDdlNameColumn = customDdlEntity.get(CustomDdlEntity_.customDdlName); // 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())); // Add the select clause. criteria.multiselect(namespaceCodeColumn, businessObjectDefinitionNameColumn, businessObjectFormatUsageColumn, fileTypeCodeColumn, businessObjectFormatVersionColumn, customDdlNameColumn); // Add the where clause. criteria.where(queryRestriction); // Add the order by clause. criteria.orderBy(builder.asc(customDdlNameColumn)); // 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<CustomDdlKey> customDdlKeys = new ArrayList<>(); for (Tuple tuple : tuples) { CustomDdlKey customDdlKey = new CustomDdlKey(); customDdlKeys.add(customDdlKey); customDdlKey.setNamespace(tuple.get(namespaceCodeColumn)); customDdlKey.setBusinessObjectDefinitionName(tuple.get(businessObjectDefinitionNameColumn)); customDdlKey.setBusinessObjectFormatUsage(tuple.get(businessObjectFormatUsageColumn)); customDdlKey.setBusinessObjectFormatFileType(tuple.get(fileTypeCodeColumn)); customDdlKey.setBusinessObjectFormatVersion(tuple.get(businessObjectFormatVersionColumn)); customDdlKey.setCustomDdlName(tuple.get(customDdlNameColumn)); } return customDdlKeys; }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * {@inheritDoc}/*from w w w . j av a 2 s .c o m*/ */ @Override public BusinessObjectDataEntity getBusinessObjectDataByAltKeyAndStatus( BusinessObjectDataKey businessObjectDataKey, String businessObjectDataStatus) { // 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> businessObjectDataEntity = criteria.from(BusinessObjectDataEntity.class); // Join to other tables that we need to 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); // Create the standard restrictions (i.e. the standard where clauses). Predicate mainQueryRestriction = getQueryRestriction(builder, businessObjectDataEntity, businessObjectFormatEntity, fileTypeEntity, businessObjectDefinitionEntity, businessObjectDataKey); // If a format version was specified, use the latest available for this partition value. if (businessObjectDataKey.getBusinessObjectFormatVersion() == null) { // Business object format version is not specified, so just use the latest available for this set of partition values. Subquery<Integer> subQuery = criteria.subquery(Integer.class); // The criteria root is the business object data. Root<BusinessObjectDataEntity> subBusinessObjectDataEntity = subQuery .from(BusinessObjectDataEntity.class); // Join to the other tables we can filter on. Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> subBusinessObjectFormatEntity = subBusinessObjectDataEntity .join(BusinessObjectDataEntity_.businessObjectFormat); Join<BusinessObjectFormatEntity, BusinessObjectDefinitionEntity> subBusinessObjectDefinitionEntity = subBusinessObjectFormatEntity .join(BusinessObjectFormatEntity_.businessObjectDefinition); Join<BusinessObjectFormatEntity, FileTypeEntity> subBusinessObjectFormatFileTypeEntity = subBusinessObjectFormatEntity .join(BusinessObjectFormatEntity_.fileType); Join<BusinessObjectDataEntity, BusinessObjectDataStatusEntity> subBusinessObjectDataStatusEntity = subBusinessObjectDataEntity .join(BusinessObjectDataEntity_.status); // Create the standard restrictions (i.e. the standard where clauses). Predicate subQueryRestriction = builder.equal(subBusinessObjectDefinitionEntity, businessObjectDefinitionEntity); subQueryRestriction = builder.and(subQueryRestriction, builder.equal(subBusinessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage), businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage))); subQueryRestriction = builder.and(subQueryRestriction, builder.equal(subBusinessObjectFormatFileTypeEntity, fileTypeEntity)); // Create and add standard restrictions on primary and sub-partition values. subQueryRestriction = builder.and(subQueryRestriction, getQueryRestrictionOnPartitionValues(builder, subBusinessObjectDataEntity, businessObjectDataEntity)); // Add restrictions on business object data version and business object data status. Predicate subQueryRestrictionOnBusinessObjectDataVersionAndStatus = getQueryRestrictionOnBusinessObjectDataVersionAndStatus( builder, subBusinessObjectDataEntity, subBusinessObjectDataStatusEntity, businessObjectDataKey.getBusinessObjectDataVersion(), businessObjectDataStatus); if (subQueryRestrictionOnBusinessObjectDataVersionAndStatus != null) { subQueryRestriction = builder.and(subQueryRestriction, subQueryRestrictionOnBusinessObjectDataVersionAndStatus); } subQuery.select(builder.max( subBusinessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion))) .where(subQueryRestriction); mainQueryRestriction = builder.and(mainQueryRestriction, builder.in( businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion)) .value(subQuery)); } // If a data version was not specified, use the latest one as per specified business object data status. if (businessObjectDataKey.getBusinessObjectDataVersion() == null) { // Since business object data version is not specified, just use the latest one as per specified business object data status. if (businessObjectDataStatus != null) { // Business object data version is not specified, so get the latest one as per specified business object data status. Subquery<Integer> subQuery = criteria.subquery(Integer.class); // The criteria root is the business object data. Root<BusinessObjectDataEntity> subBusinessObjectDataEntity = subQuery .from(BusinessObjectDataEntity.class); // Join to the other tables we can filter on. Join<BusinessObjectDataEntity, BusinessObjectDataStatusEntity> subBusinessObjectDataStatusEntity = subBusinessObjectDataEntity .join(BusinessObjectDataEntity_.status); Join<BusinessObjectDataEntity, BusinessObjectFormatEntity> subBusinessObjectFormatEntity = subBusinessObjectDataEntity .join(BusinessObjectDataEntity_.businessObjectFormat); // Create the standard restrictions (i.e. the standard where clauses). Predicate subQueryRestriction = builder.equal(subBusinessObjectFormatEntity, businessObjectFormatEntity); // Create and add standard restrictions on primary and sub-partition values. subQueryRestriction = builder.and(subQueryRestriction, getQueryRestrictionOnPartitionValues(builder, subBusinessObjectDataEntity, businessObjectDataEntity)); // Create and add standard restrictions on business object data status. subQueryRestriction = builder.and(subQueryRestriction, builder.equal( builder.upper(subBusinessObjectDataStatusEntity.get(BusinessObjectDataStatusEntity_.code)), businessObjectDataStatus.toUpperCase())); subQuery.select(builder.max(subBusinessObjectDataEntity.get(BusinessObjectDataEntity_.version))) .where(subQueryRestriction); mainQueryRestriction = builder.and(mainQueryRestriction, builder .in(businessObjectDataEntity.get(BusinessObjectDataEntity_.version)).value(subQuery)); } else { // Both business object data version and business object data status are not specified, so just use the latest business object data version. mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal(businessObjectDataEntity.get(BusinessObjectDataEntity_.latestVersion), true)); } } criteria.select(businessObjectDataEntity).where(mainQueryRestriction); return executeSingleResultQuery(criteria, String.format( "Found more than one business object data instance with parameters {namespace=\"%s\", businessObjectDefinitionName=\"%s\"," + " businessObjectFormatUsage=\"%s\", businessObjectFormatFileType=\"%s\", businessObjectFormatVersion=\"%d\"," + " businessObjectDataPartitionValue=\"%s\", businessObjectDataSubPartitionValues=\"%s\", businessObjectDataVersion=\"%d\"," + " businessObjectDataStatus=\"%s\"}.", businessObjectDataKey.getNamespace(), businessObjectDataKey.getBusinessObjectDefinitionName(), businessObjectDataKey.getBusinessObjectFormatUsage(), businessObjectDataKey.getBusinessObjectFormatFileType(), businessObjectDataKey.getBusinessObjectFormatVersion(), businessObjectDataKey.getPartitionValue(), CollectionUtils.isEmpty(businessObjectDataKey.getSubPartitionValues()) ? "" : StringUtils.join(businessObjectDataKey.getSubPartitionValues(), ","), businessObjectDataKey.getBusinessObjectDataVersion(), businessObjectDataStatus)); }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * {@inheritDoc}/*from w w w .ja v a 2s . co m*/ */ @Override public Integer getBusinessObjectDataMaxVersion(BusinessObjectDataKey businessObjectDataKey) { // 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 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 the path. Expression<Integer> maxBusinessObjectDataVersion = builder .max(businessObjectDataEntity.get(BusinessObjectDataEntity_.version)); // Create the standard restrictions (i.e. the standard where clauses). Predicate queryRestriction = builder.equal(builder.upper(namespaceEntity.get(NamespaceEntity_.code)), businessObjectDataKey.getNamespace().toUpperCase()); queryRestriction = builder.and(queryRestriction, builder.equal( builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)), businessObjectDataKey.getBusinessObjectDefinitionName().toUpperCase())); queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage)), businessObjectDataKey.getBusinessObjectFormatUsage().toUpperCase())); queryRestriction = builder.and(queryRestriction, builder.equal(builder.upper(fileTypeEntity.get(FileTypeEntity_.code)), businessObjectDataKey.getBusinessObjectFormatFileType().toUpperCase())); queryRestriction = builder.and(queryRestriction, builder.equal( businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion), businessObjectDataKey.getBusinessObjectFormatVersion())); queryRestriction = builder.and(queryRestriction, builder.equal(businessObjectDataEntity.get(BusinessObjectDataEntity_.partitionValue), businessObjectDataKey.getPartitionValue())); for (int i = 0; i < BusinessObjectDataEntity.MAX_SUBPARTITIONS; i++) { queryRestriction = builder.and(queryRestriction, i < businessObjectDataKey.getSubPartitionValues().size() ? builder.equal(businessObjectDataEntity.get(BUSINESS_OBJECT_DATA_SUBPARTITIONS.get(i)), businessObjectDataKey.getSubPartitionValues().get(i)) : builder.isNull( businessObjectDataEntity.get(BUSINESS_OBJECT_DATA_SUBPARTITIONS.get(i)))); } criteria.select(maxBusinessObjectDataVersion).where(queryRestriction); return entityManager.createQuery(criteria).getSingleResult(); }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * Retrieves partition value per specified parameters that includes the aggregate function. * * @param partitionColumnPosition the partition column position (1-based numbering) * @param businessObjectFormatKey the business object format key (case-insensitive). If a business object format version isn't specified, the latest * available format version for each partition value will be used. * @param businessObjectDataVersion the business object data version. If a business object data version isn't specified, the latest data version for each * partition value will be used.// w ww . ja va 2s. c o m * @param storageName the name of the storage where the business object data storage unit is located (case-insensitive) * @param aggregateFunction the aggregate function to use against partition values * @param upperBoundPartitionValue the optional inclusive upper bound for the maximum available partition value * @param lowerBoundPartitionValue the optional inclusive lower bound for the maximum available partition value * * @return the partition value */ private String getBusinessObjectDataPartitionValue(int partitionColumnPosition, BusinessObjectFormatKey businessObjectFormatKey, Integer businessObjectDataVersion, String storageName, AggregateFunction aggregateFunction, String upperBoundPartitionValue, String lowerBoundPartitionValue) { // Create the criteria builder and the criteria. CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<String> criteria = builder.createQuery(String.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); Join<BusinessObjectDataEntity, StorageUnitEntity> storageUnitEntity = businessObjectDataEntity .join(BusinessObjectDataEntity_.storageUnits); Join<StorageUnitEntity, StorageEntity> storageEntity = storageUnitEntity.join(StorageUnitEntity_.storage); // Create the path. Expression<String> partitionValue; SingularAttribute<BusinessObjectDataEntity, String> singleValuedAttribute = BUSINESS_OBJECT_DATA_PARTITIONS .get(partitionColumnPosition - 1); switch (aggregateFunction) { case GREATEST: partitionValue = builder.greatest(businessObjectDataEntity.get(singleValuedAttribute)); break; case LEAST: partitionValue = builder.least(businessObjectDataEntity.get(singleValuedAttribute)); break; default: throw new IllegalArgumentException("Invalid aggregate function found: \"" + aggregateFunction + "\"."); } // Create the standard restrictions (i.e. the standard where clauses). Predicate mainQueryRestriction = builder.equal(builder.upper(namespaceEntity.get(NamespaceEntity_.code)), businessObjectFormatKey.getNamespace().toUpperCase()); mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal( builder.upper(businessObjectDefinitionEntity.get(BusinessObjectDefinitionEntity_.name)), businessObjectFormatKey.getBusinessObjectDefinitionName().toUpperCase())); mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal(builder.upper(businessObjectFormatEntity.get(BusinessObjectFormatEntity_.usage)), businessObjectFormatKey.getBusinessObjectFormatUsage().toUpperCase())); mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal(builder.upper(fileTypeEntity.get(FileTypeEntity_.code)), businessObjectFormatKey.getBusinessObjectFormatFileType().toUpperCase())); // If a business object format version was specified, use it. if (businessObjectFormatKey.getBusinessObjectFormatVersion() != null) { mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal( businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion), businessObjectFormatKey.getBusinessObjectFormatVersion())); } // If a data version was specified, use it. Otherwise, use the latest one. if (businessObjectDataVersion != null) { mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal( businessObjectDataEntity.get(BusinessObjectDataEntity_.version), businessObjectDataVersion)); } else { // Business object data version is not specified, so get the latest one regardless of the business object data status in the specified storage. Subquery<Integer> subQuery = getMaximumBusinessObjectDataVersionSubQuery(builder, criteria, businessObjectDataEntity, businessObjectFormatEntity, null, storageEntity); mainQueryRestriction = builder.and(mainQueryRestriction, builder.in(businessObjectDataEntity.get(BusinessObjectDataEntity_.version)).value(subQuery)); } // Add an inclusive upper bound partition value restriction if specified. if (upperBoundPartitionValue != null) { mainQueryRestriction = builder.and(mainQueryRestriction, builder.lessThanOrEqualTo( businessObjectDataEntity.get(singleValuedAttribute), upperBoundPartitionValue)); } // Add an inclusive lower bound partition value restriction if specified. if (lowerBoundPartitionValue != null) { mainQueryRestriction = builder.and(mainQueryRestriction, builder.greaterThanOrEqualTo( businessObjectDataEntity.get(singleValuedAttribute), lowerBoundPartitionValue)); } // Add a storage name restriction to the query where clause. mainQueryRestriction = builder.and(mainQueryRestriction, builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageName.toUpperCase())); criteria.select(partitionValue).where(mainQueryRestriction); return entityManager.createQuery(criteria).getSingleResult(); }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * {@inheritDoc}/*from w ww . j a v a2 s . c o m*/ */ @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(); }
From source file:org.finra.dm.dao.impl.DmDaoImpl.java
/** * {@inheritDoc}/*from w w w . j av a 2 s .c om*/ */ @Override public List<BusinessObjectDataEntity> getBusinessObjectDataEntities( BusinessObjectDataKey businessObjectDataKey) { // 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> 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); // Create the standard restrictions (i.e. the standard where clauses). Predicate queryRestriction = getQueryRestriction(builder, businessObjectDataEntity, businessObjectFormatEntity, fileTypeEntity, businessObjectDefinitionEntity, businessObjectDataKey); // Add the clauses for the query. criteria.select(businessObjectDataEntity).where(queryRestriction); // Order by business object format and data versions. criteria.orderBy( builder.asc( businessObjectFormatEntity.get(BusinessObjectFormatEntity_.businessObjectFormatVersion)), builder.asc(businessObjectDataEntity.get(BusinessObjectDataEntity_.version))); return entityManager.createQuery(criteria).getResultList(); }