@Override public List<IndexField> readAllIndexFieldsByFieldId(Long fieldId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<IndexField> criteria = builder.createQuery(IndexField.class); Root<IndexFieldImpl> search = criteria.from(IndexFieldImpl.class); criteria.select(search); criteria.where( builder.equal(search.join("field").get("id").as(Long.class), fieldId) ); TypedQuery<IndexField> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Search"); return query.getResultList(); }
@Override public List<SearchFacet> readAllSearchFacets(FieldEntity entityType) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<SearchFacet> criteria = builder.createQuery(SearchFacet.class); Root<SearchFacetImpl> facet = criteria.from(SearchFacetImpl.class); criteria.select(facet); Path<Character> archived = facet.get("archiveStatus").get("archived"); criteria.where( builder.equal(facet.get("showOnSearch").as(Boolean.class), true), builder.or(builder.isNull(archived.as(String.class)), builder.notEqual(archived.as(Character.class), 'Y')), facet.join("fieldType") .join("indexField") .join("field") .get("entityType") .as(String.class) .in(entityType.getAllLookupTypes()) ); TypedQuery<SearchFacet> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Search"); return query.getResultList(); }
if( join.getJavaType().equals(toAttrJoinType) ) { if( join.getAttribute().equals(parentJoinAttr) ) { fieldParentJoin = (Join<F, T>) join; if( ! JoinType.INNER.equals(fieldParentJoin.getJoinType()) ) { return fieldParentJoin.get(fieldAttr);
private <T> void useDistinctWhenLefOuterJoinsPresent(CriteriaQuery<T> criteriaQuery) { boolean useDistinct = false; Root<TaskImpl> taskRoot = null; ROOTS_FOR: for( Root root : criteriaQuery.getRoots() ) { if( TaskImpl.class.equals(root.getJavaType()) ) { taskRoot = (Root<TaskImpl>) root; for( Join<TaskImpl, ?> taskJoin : taskRoot.getJoins() ) { if( PeopleAssignmentsImpl.class.equals(taskJoin.getJavaType()) ) { Join<TaskImpl, PeopleAssignmentsImpl> peopleAssignJoin = (Join<TaskImpl, PeopleAssignmentsImpl>) taskJoin; if( JoinType.LEFT.equals(peopleAssignJoin.getJoinType()) ) { useDistinct = true; break ROOTS_FOR; } for( Join peopleAssignJoinJoin : peopleAssignJoin.getJoins() ) { if( JoinType.LEFT.equals(peopleAssignJoinJoin.getJoinType()) ) { useDistinct = true; break ROOTS_FOR; } } } } } } if( useDistinct ) { criteriaQuery.distinct(true); } }
@SuppressWarnings({ "unchecked" }) private void renderJoins( StringBuilder jpaqlQuery, RenderingContext renderingContext, Collection<Join<?,?>> joins) { if ( joins == null ) { return; } for ( Join join : joins ) { ( (FromImplementor) join ).prepareAlias( renderingContext ); jpaqlQuery.append( renderJoinType( join.getJoinType() ) ) .append( ( (FromImplementor) join ).renderTableExpression( renderingContext ) ); renderJoins( jpaqlQuery, renderingContext, join.getJoins() ); renderFetches( jpaqlQuery, renderingContext, join.getFetches() ); } }
@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(); }
protected List<Product> readFilteredActiveProductsByQueryInternal(String query, Date currentDate, SearchCriteria searchCriteria) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Product> criteria = builder.createQuery(Product.class); Root<ProductImpl> product = criteria.from(ProductImpl.class); Join<Product, Sku> sku = product.join("defaultSku"); criteria.select(product); String lq = query.toLowerCase(); restrictions.add( builder.or( builder.like(builder.lower(sku.get("name").as(String.class)), '%' + lq + '%'), builder.like(builder.lower(sku.get("longDescription").as(String.class)), '%' + lq + '%') criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Product> typedQuery = em.createQuery(criteria); return typedQuery.getResultList();
@Test public void test_criteria_from_join_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::criteria-from-join-example[] CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Phone> criteria = builder.createQuery( Phone.class ); Root<Phone> root = criteria.from( Phone.class ); // Phone.person is a @ManyToOne Join<Phone, Person> personJoin = root.join( Phone_.person ); // Person.addresses is an @ElementCollection Join<Person, String> addressesJoin = personJoin.join( Person_.addresses ); criteria.where( builder.isNotEmpty( root.get( Phone_.calls ) ) ); List<Phone> phones = entityManager.createQuery( criteria ).getResultList(); //end::criteria-from-join-example[] assertEquals(2, phones.size()); }); }
private TypedQuery<Long> getProductIdsUsingProductOptionByIdQuery(Long productOptionId, boolean count) { // Set up the criteria query that specifies we want to return Products CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); // The root of our search is ProductOptionXref Root<ProductOptionXrefImpl> productOptionXref = criteria.from(ProductOptionXrefImpl.class); Join<ProductOptionXref, Product> product = productOptionXref.join("product"); Join<ProductOptionXref, ProductOption> productOption = productOptionXref.join("productOption"); if (count) { criteria.select(builder.count(product)); } else { // Product IDs are what we want back criteria.select(product.get("id").as(Long.class)); } criteria.distinct(true); List<Predicate> restrictions = new ArrayList<Predicate>(); restrictions.add(productOption.get("id").in(sandBoxHelper.mergeCloneIds(ProductOptionImpl.class, productOptionId))); // Execute the query with the restrictions criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); return em.createQuery(criteria); }
private Query applicationServersForSuggestBox(String input) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<String> q = cb.createQuery(String.class); Root<ResourceEntity> r = q.from(ResourceEntity.class); Join<ResourceEntity, ResourceTypeEntity> resType = r.join("resourceType"); Predicate appServerNamePred = cb.like(resType.<String> get("name"), DefaultResourceTypeDefinition.APPLICATIONSERVER.name()); input = input + "%"; q.where(cb.and(appServerNamePred, cb.like(r.<String> get("name"), input))); q.select(r.<String> get("name")); q.distinct(true); q.orderBy(cb.asc(r.get("name"))); return entityManager.createQuery(q); }
/** * @param userName * @return */ public List<FavoriteResourceEntity> fetchFavoriteResources(String userName) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<FavoriteResourceEntity> q = cb.createQuery(FavoriteResourceEntity.class); Root<FavoriteResourceEntity> r = q.from(FavoriteResourceEntity.class); Join<FavoriteResourceEntity, UserSettingsEntity> userSettings = r.join("user"); Join<FavoriteResourceEntity, ResourceGroupEntity> resourceGroup = r.join("resourceGroup"); Predicate userNamePred = cb.like(userSettings.<String> get("userName"), userName); q.where(userNamePred); q.orderBy(cb.asc(resourceGroup.get("name"))); r.fetch("resourceGroup", JoinType.LEFT); return entityManager.createQuery(q).getResultList(); }
outerQuery.orderBy(orders); outerQuery = myBuilder.createQuery(Long.class); myResourceTableQuery = outerQuery; myResourceTableRoot = myResourceTableQuery.from(ResourceTable.class); outerQuery.multiselect(myBuilder.countDistinct(myResourceTableRoot)); } else { outerQuery.multiselect(myResourceTableRoot.get("myId").as(Long.class)); Join<ResourceTable, ResourceLink> join = myResourceTableRoot.join("myResourceLinks", JoinType.LEFT); myPredicates.add(myBuilder.equal(join.get("myTargetResourcePid").as(Long.class), pid)); } else { Predicate targetTypePredicate = myBuilder.equal(join.get("myTargetResourceType").as(String.class), myResourceName); Predicate sourceTypePredicate = myBuilder.equal(myResourceTableRoot.get("myResourceType").as(String.class), myResourceName); myPredicates.add(myBuilder.or(sourceTypePredicate, targetTypePredicate)); final TypedQuery<Long> query = myEntityManager.createQuery(outerQuery); query.setMaxResults(theMaximumResults);
private long doJoinQuery(EntityManager em, String userId, List<String> groupIds, int total) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<TaskImpl> joinQuery = builder.createQuery(TaskImpl.class); Root<TaskImpl> taskRoot = joinQuery.from(TaskImpl.class); Join<TaskImpl, TaskDataImpl> join = taskRoot.join(TaskImpl_.taskData); joinQuery.select(select); Join<TaskImpl, PeopleAssignmentsImpl> peopleAssign = taskRoot.join(TaskImpl_.peopleAssignments); ListJoin<PeopleAssignmentsImpl,OrganizationalEntityImpl> busAdmins = peopleAssign.join(PeopleAssignmentsImpl_.businessAdministrators, JoinType.LEFT); ListJoin<PeopleAssignmentsImpl,OrganizationalEntityImpl> potOwners = peopleAssign.join(PeopleAssignmentsImpl_.potentialOwners, JoinType.LEFT); ListJoin<PeopleAssignmentsImpl,OrganizationalEntityImpl> stakeHols = peopleAssign.join(PeopleAssignmentsImpl_.taskStakeholders, JoinType.LEFT); predicates.add( builder.equal(taskRoot.get(TaskImpl_.taskData).get(TaskDataImpl_.actualOwner).get(UserImpl_.id), userId) ); predicates.add( builder.equal(taskRoot.get(TaskImpl_.taskData).get(TaskDataImpl_.createdBy).get(UserImpl_.id), userId) ); predicates.add( builder.or( joinQuery.where(builder.or(predicates.toArray(new Predicate[predicates.size()])));
@Override public StorageFileEntity getStorageFileByStorageNameAndFilePath(String storageName, String filePath) { // 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.equal(storageFileEntity.get(StorageFileEntity_.path), filePath); Predicate storageNameRestriction = builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageName.toUpperCase()); criteria.select(storageFileEntity).where(builder.and(filePathRestriction, storageNameRestriction)); return executeSingleResultQuery(criteria, String.format("Found more than one storage file with parameters {storageName=\"%s\"," + " filePath=\"%s\"}.", storageName, filePath)); }
@Override public List<WorkspaceItem> findBySupervisedGroupMember(Context context, EPerson ePerson) throws SQLException { CriteriaBuilder criteriaBuilder = getCriteriaBuilder(context); CriteriaQuery criteriaQuery = getCriteriaQuery(criteriaBuilder, WorkspaceItem.class); Root<WorkspaceItem> workspaceItemRoot = criteriaQuery.from(WorkspaceItem.class); Join<WorkspaceItem, Group> join = workspaceItemRoot.join("supervisorGroups"); Join<Group, EPerson> secondJoin = join.join("epeople"); criteriaQuery.select(workspaceItemRoot); criteriaQuery.where(criteriaBuilder.equal(secondJoin.get(EPerson_.id), ePerson.getID())); criteriaQuery.orderBy(criteriaBuilder.asc(workspaceItemRoot.get(WorkspaceItem_.workspaceItemId))); return list(context, criteriaQuery, false, WorkspaceItem.class, -1, -1); }
public static Predicate<BuildRecord> withBuildConfigSetId(Integer buildConfigSetId) { return (root, query, cb) -> { Join<BuildRecord, BuildConfigSetRecord> builtConfigSetRecord = root.join(BuildRecord_.buildConfigSetRecord); Join<BuildConfigSetRecord, BuildConfigurationSet> buildConfigSet = builtConfigSetRecord.join(BuildConfigSetRecord_.buildConfigurationSet); return cb.equal(buildConfigSet.get(BuildConfigurationSet_.id), buildConfigSetId); }; }
@Override public List<XmlWorkflowItem> findBySubmitter(Context context, EPerson ep) throws SQLException { CriteriaBuilder criteriaBuilder = getCriteriaBuilder(context); CriteriaQuery criteriaQuery = getCriteriaQuery(criteriaBuilder, XmlWorkflowItem.class); Root<XmlWorkflowItem> xmlWorkflowItemRoot = criteriaQuery.from(XmlWorkflowItem.class); Join<XmlWorkflowItem, Item> join = xmlWorkflowItemRoot.join("item"); criteriaQuery.select(xmlWorkflowItemRoot); criteriaQuery.where(criteriaBuilder.equal(join.get(Item_.submitter), ep)); return list(context, criteriaQuery, false, XmlWorkflowItem.class, -1, -1); }
@Override public Predicate getNotMatchTagString(final Integer tagId) { final CriteriaBuilder criteriaBuilder = getCriteriaBuilder(); final Subquery<TopicToTag> subQuery = getCriteriaQuery().subquery(TopicToTag.class); final Root<TopicToTag> from = subQuery.from(TopicToTag.class); final Predicate topic = criteriaBuilder.equal(from.get("topic").get("topicId"), translatedTopic.get("topicId")); final Predicate tag = criteriaBuilder.equal(from.get("tag").get("tagId"), tagId); subQuery.select(from); subQuery.where(criteriaBuilder.and(topic, tag)); return criteriaBuilder.not(criteriaBuilder.exists(subQuery)); }
} else { ourLog.debug("Searching for resource link with target URL: {}", dt.getValue()); Predicate eq = myBuilder.equal(join.get("myTargetResourceUrl"), dt.getValue()); codePredicates.add(eq); continue; Predicate pidPredicate = myBuilder.equal(join.get("myTargetResourcePid"), next); codePredicates.add(myBuilder.and(pathPredicate, pidPredicate)); subQ.select(subQfrom.get("myId").as(Long.class)); myPredicates.add(myBuilder.equal(myResourceTableRoot.get("myResourceType"), subResourceName)); myPredicates.add(myBuilder.isNull(myResourceTableRoot.get("myDeleted"))); Predicate pidPredicate = join.get("myTargetResourcePid").in(subQ); codePredicates.add(myBuilder.and(pathPredicate, pidPredicate));
From<?, ?> forcedIdJoin = theFrom.join("myForcedId", JoinType.LEFT); if (theSort.getOrder() == null || theSort.getOrder() == SortOrderEnum.ASC) { theOrders.add(theBuilder.asc(forcedIdJoin.get("myForcedId"))); theOrders.add(theBuilder.asc(theFrom.get("myId"))); } else { theOrders.add(theBuilder.desc(forcedIdJoin.get("myForcedId"))); theOrders.add(theBuilder.desc(theFrom.get("myId"))); thePredicates.add(join.get("mySourcePath").as(String.class).in(param.getPathsSplit())); } else { if (myDontUseHashesForSearch) { Predicate joinParam1 = theBuilder.equal(join.get("myParamName"), theSort.getParamName()); thePredicates.add(joinParam1); } else { Long hashIdentity = BaseResourceIndexedSearchParam.calculateHashIdentity(myResourceName, theSort.getParamName()); Predicate joinParam1 = theBuilder.equal(join.get("myHashIdentity"), hashIdentity); thePredicates.add(joinParam1); theOrders.add(theBuilder.asc(join.get(next))); } else { theOrders.add(theBuilder.desc(join.get(next)));