@Override public List<SandBox> retrieveChildSandBoxesByParentId(Long parentSandBoxId) { CriteriaBuilder builder = sandBoxEntityManager.getCriteriaBuilder(); CriteriaQuery<SandBox> criteria = builder.createQuery(SandBox.class); Root<SandBoxManagementImpl> sandbox = criteria.from(SandBoxManagementImpl.class); criteria.select(sandbox.get("sandBox").as(SandBox.class)); criteria.where( builder.and(sandbox.get("sandBox").get("parentSandBox").in(parentSandBoxId), builder.or(builder.isNotNull(sandbox.get("sandBox").get("name")), builder.notEqual(sandbox.get("sandBox").get("name").as(String.class), "")), builder.or(builder.isNull(sandbox.get("sandBox").get("archiveStatus").get("archived").as(String.class)), builder.notEqual(sandbox.get("sandBox").get("archiveStatus").get("archived").as(Character.class), 'Y'))) ); TypedQuery<SandBox> query = sandBoxEntityManager.createQuery(criteria); return query.getResultList(); }
@Override public Predicate buildPredicate(CriteriaBuilder builder, FieldPathBuilder fieldPathBuilder, From root, String ceilingEntity, String fullPropertyName, Path<Serializable> explicitPath, List<String> directValues) { if (String.class.isAssignableFrom(explicitPath.getJavaType())) { return builder.equal(explicitPath, directValues.get(0)); } else { return builder.equal(explicitPath, Long.parseLong(directValues.get(0))); } } })
@Override public Predicate buildPredicate(CriteriaBuilder builder, FieldPathBuilder fieldPathBuilder, From root, String ceilingEntity, String fullPropertyName, Path explicitPath, List directValues) { //expect it to be allParentCategoryXrefs.category.name, so we need to restrict on allParentCategoryXrefs - 2 levels up Predicate equal = builder.equal(explicitPath.getParentPath().getParentPath().get("defaultReference"), Boolean.TRUE); return equal; } })
protected void attachActiveRestriction(Date currentDate, Path<? extends Product> product, Path<? extends Sku> sku, List<Predicate> restrictions) { CriteriaBuilder builder = em.getCriteriaBuilder(); // Add the product archived status flag restriction restrictions.add(builder.or( builder.isNull(product.get("archiveStatus").get("archived")), builder.equal(product.get("archiveStatus").get("archived"), 'N'))); // Add the active start/end date restrictions restrictions.add(builder.lessThan(sku.get("activeStartDate").as(Date.class), currentDate)); restrictions.add(builder.or( builder.isNull(sku.get("activeEndDate")), builder.greaterThan(sku.get("activeEndDate").as(Date.class), currentDate))); }
@Override public List<Page> readOnlineAndIncludedPages(int limit, int offset, String sortBy) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Page> criteria = builder.createQuery(Page.class); Root<PageImpl> page = criteria.from(PageImpl.class); criteria.select(page); criteria.where(builder.and( builder.or(builder.isFalse(page.get("offlineFlag").as(Boolean.class)), builder.isNull(page.get("offlineFlag").as(Boolean.class))), builder.or(builder.isFalse(page.get("excludeFromSiteMap").as(Boolean.class)), builder.isNull(page.get("excludeFromSiteMap").as(Boolean.class))))); criteria.orderBy(builder.asc(page.get(sortBy))); TypedQuery<Page> query = em.createQuery(criteria); query.setFirstResult(offset); query.setMaxResults(limit); query.setHint(QueryHints.HINT_CACHEABLE, true); return query.getResultList(); }
@Override public List<Order> readOrdersForCustomersInDateRange(List<Long> customerIds, Date startDate, Date endDate) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Order> criteria = builder.createQuery(Order.class); Root<OrderImpl> order = criteria.from(OrderImpl.class); criteria.select(order); List<Predicate> restrictions = new ArrayList<>(); restrictions.add(builder.between(order.<Date>get("submitDate"), startDate, endDate)); restrictions.add(order.get("customer").get("id").in(customerIds)); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); criteria.orderBy(builder.desc(order.get("customer")), builder.asc(order.get("submitDate"))); TypedQuery<Order> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Order"); return query.getResultList(); }
@Override public void removedExpiredAccounts(LocalDate reference) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Account> query = cb.createQuery(Account.class); Root<Account> account = query.from(Account.class); query.where(cb.lessThan(account.get("expiryDate").as(Date.class), reference.toDateTimeAtStartOfDay().toDate())); for (Account each : em.createQuery(query).getResultList()) { em.remove(each); } } }
protected <T> TypedQuery<T> buildCartQuery(String[] names, OrderStatus[] statuses, Date dateCreatedMinThreshold, Boolean isPreview, Class<T> returnType, List<Long> excludedIds) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<T> criteria = builder.createQuery(returnType); Root<OrderImpl> root = criteria.from(OrderImpl.class); if (Long.class.equals(returnType)) { criteria.select((Selection<? extends T>) builder.count(root)); } else { criteria.select((Selection<? extends T>) root); statusList.add("IN_PROCESS"); restrictions.add(root.get("status").in(statusList)); if (names != null) { restrictions.add(root.get("name").in(Arrays.asList(names))); restrictions.add(builder.lessThan(root.get("auditable").get("dateCreated").as(Date.class), dateCreatedMinThreshold)); restrictions.add(builder.isTrue(root.get("previewable").get("isPreview").as(Boolean.class))); } else { restrictions.add(builder.or(builder.isNull(root.get("previewable").get("isPreview")), builder.isFalse(root.get("previewable").get("isPreview").as(Boolean.class)))); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); return em.createQuery(criteria);
public static List<Dashboard> findDashboardsByOwnerMeta(EntityManager em, PrincipalUser user, String version) { requireArgument(em != null, "Entity manager can not be null."); try { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Tuple> cq = cb.createTupleQuery(); Root<Dashboard> e = cq.from(Dashboard.class); List<Selection<?>> fieldsToSelect = new ArrayList<>(); for(Field field : FieldUtils.getFieldsListWithAnnotation(Dashboard.class, Metadata.class)) { fieldsToSelect.add(e.get(field.getName()).alias(field.getName())); } cq.multiselect(fieldsToSelect); cq.where(cb.equal(e.get("owner"), user),version==null?cb.isNull(e.get("version")):cb.equal(e.get("version"), version)); return _readDashboards(em, cq, null); } catch (NoResultException ex) { return new ArrayList<>(0); } }
@Override public List<Long> readAllGenericEntityId(Class<?> clazz) { clazz = DynamicDaoHelperImpl.getNonProxyImplementationClassIfNecessary(clazz); CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root root = criteria.from(clazz); criteria.select(root.get(getIdField(clazz).getName()).as(Long.class)); criteria.orderBy(builder.asc(root.get(getIdField(clazz).getName()))); return em.createQuery(criteria).getResultList(); }
@Override public Long countAllowedValuesForProductOptionById(Long productOptionId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<ProductOptionValueImpl> root = criteria.from(ProductOptionValueImpl.class); criteria.select(builder.count(root)); List<Predicate> restrictions = new ArrayList<>(); List<Long> mergedIds = sandBoxHelper.mergeCloneIds(ProductOptionImpl.class, productOptionId); restrictions.add(root.get("productOption").in(mergedIds)); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Long> query = em.createQuery(criteria); return query.getSingleResult(); }
@Override public Long findTaskIdByContentId( Long contentId ) { check(); CriteriaBuilder builder = this.em.getCriteriaBuilder(); CriteriaQuery<Long> query = builder.createQuery(Long.class); Root<TaskImpl> taskRoot = query.from(TaskImpl.class); Root<ContentImpl> contentRoot = query.from(ContentImpl.class); query.select(taskRoot.get(TaskImpl_.id)); Predicate taskContentJoinPred = builder.equal( contentRoot.get(ContentImpl_.id), taskRoot.get(TaskImpl_.taskData).get(TaskDataImpl_.outputContentId)); Predicate contentIdPred = builder.equal( contentRoot.get(ContentImpl_.id), contentId); query.where(builder.and(taskContentJoinPred, contentIdPred)); Query choppedLiver = em.createQuery(query); return (Long) choppedLiver.getSingleResult(); }
@Test public void testInClauseParameterPadding() { sqlStatementInterceptor.clear(); doInJPA( this::entityManagerFactory, entityManager -> { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Long> query = cb.createQuery( Long.class ); Root<Document> document = query.from( Document.class ); ParameterExpression<List> inClauseParams = cb.parameter( List.class, "ids" ); query .select( document.get( "id" ) ) .where( document.get( "id" ).in( inClauseParams ) ); List<Long> ids = entityManager.createQuery( query ) .setParameter( "ids", Arrays.asList( 1, 2, 3, 4, 5 ) ) .getResultList(); assertEquals( 1, ids.size() ); } ); assertTrue( sqlStatementInterceptor.getSqlQueries().get( 0 ).endsWith( "in (? , ? , ? , ? , ? , ? , ? , ?)" ) ); }
protected List<Product> readFilteredActiveProductsByCategoryInternal(Long categoryId, Date currentDate, SearchCriteria searchCriteria) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Product> criteria = builder.createQuery(Product.class); Root<CategoryProductXrefImpl> productXref = criteria.from(CategoryProductXrefImpl.class); Join<CategoryProductXref, Product> product = productXref.join("product"); Join<Product, Sku> sku = product.join("defaultSku"); Join<CategoryProductXref, Category> category = productXref.join("category"); criteria.select(product); restrictions.add(category.get("id").in(sandBoxHelper.mergeCloneIds(CategoryImpl.class, categoryId))); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Product> typedQuery = em.createQuery(criteria); return typedQuery.getResultList();
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 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); 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( builder.equal( busAdmins.get(OrganizationalEntityImpl_.id), userId ), busAdmins.get(OrganizationalEntityImpl_.id).in(groupIds) ) ); predicates.add( builder.or( builder.equal( potOwners.get(OrganizationalEntityImpl_.id), userId ), potOwners.get(OrganizationalEntityImpl_.id).in(groupIds) ) ); predicates.add( builder.or( builder.equal( stakeHols.get(OrganizationalEntityImpl_.id), userId ), stakeHols.get(OrganizationalEntityImpl_.id).in(groupIds) ) ); joinQuery.where(builder.or(predicates.toArray(new Predicate[predicates.size()])));
@Test public void testFetchObjectArrayToDTO() { doInJPA(entityManager -> { CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object[]> criteria = builder.createQuery(Object[].class); Root<PostComment> root = criteria.from(PostComment.class); Join<PostComment, Post> postJoin = root.join("post"); criteria.multiselect( root.get(PostComment_.id).alias("id"), root.get(PostComment_.review).alias("review"), postJoin.get(Post_.title).alias("title") ); criteria.where(builder.like(postJoin.get(Post_.title), "high-performance%")); List<PostCommentSummary> comments = entityManager .createQuery(criteria) .unwrap(Query.class) .setResultTransformer(Transformers.aliasToBean(PostCommentSummary.class)) .getResultList(); assertEquals(5, comments.size()); }); } }
@Override @Transactional(value="defaultTransactionManager") public void clearDuplicateAccessTokens() { Query query = manager.createQuery("select a.jwt, count(1) as c from OAuth2AccessTokenEntity a GROUP BY a.jwt HAVING count(1) > 1"); @SuppressWarnings("unchecked") List<Object[]> resultList = query.getResultList(); List<JWT> values = new ArrayList<>(); for (Object[] r : resultList) { logger.warn("Found duplicate access tokens: {}, {}", ((JWT)r[0]).serialize(), r[1]); values.add((JWT) r[0]); } if (values.size() > 0) { CriteriaBuilder cb = manager.getCriteriaBuilder(); CriteriaDelete<OAuth2AccessTokenEntity> criteriaDelete = cb.createCriteriaDelete(OAuth2AccessTokenEntity.class); Root<OAuth2AccessTokenEntity> root = criteriaDelete.from(OAuth2AccessTokenEntity.class); criteriaDelete.where(root.get("jwt").in(values)); int result = manager.createQuery(criteriaDelete).executeUpdate(); logger.warn("Deleted {} duplicate access tokens", result); } }
@Override public CriteriaQuery<Integer> apply(CriteriaBuilder cb) { final CriteriaQuery<Integer> criteriaQuery = cb.createQuery(VersionImpl_.local.getBindableJavaType()); final Root<VersionImpl> versionRoot = criteriaQuery.from(VersionImpl.class); criteriaQuery.select( versionRoot .get(VersionImpl_.local) .alias(VersionImpl_.local.getName())); criteriaQuery.where( cb.equal( versionRoot.get(VersionImpl_.product), productParameter)); return criteriaQuery; } });
@Test public void testSelectedClause() { String expected = "Select p.personName from Person p"; CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); // Check for multi column select CriteriaQuery<Person> personQuery = criteriaBuilder.createQuery(Person.class); Root<Person> from = personQuery.from(Person.class); personQuery.select((Selection) from.get("personName").alias("p")); String actual = CriteriaQueryTranslator.translate(personQuery); Assert.assertEquals(expected.trim(), actual.trim()); }