@Override public List<PageField> readPageFieldsByPageId(Long pageId) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<PageField> criteria = builder.createQuery(PageField.class); Root<PageFieldImpl> pageField = criteria.from(PageFieldImpl.class); criteria.select(pageField); Path<Object> path = pageField.get("page").get("id"); criteria.where(builder.equal(pageField.get("page").get("id"), pageId)); TypedQuery<PageField> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); return query.getResultList(); }
@Override public List<OrderItem> readOrderItemsForCustomersInDateRange(List<Long> customerIds, Date startDate, Date endDate) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<OrderItem> criteria = builder.createQuery(OrderItem.class); Root<OrderImpl> order = criteria.from(OrderImpl.class); Join<Order, OrderItem> orderItems = order.join("orderItems"); criteria.select(orderItems); 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<OrderItem> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Order"); return query.getResultList(); }
protected CriteriaQuery<Product> getCriteriaForActiveProducts(Date currentDate, Long lastId) { // 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 Product Root<ProductImpl> product = criteria.from(ProductImpl.class); // We need to filter on active date on the sku Join<Product, Sku> sku = product.join("defaultSku"); product.fetch("defaultSku"); // Product objects are what we want back criteria.select(product); // Ensure the product is currently active List<Predicate> restrictions = new ArrayList<Predicate>(); attachActiveRestriction(currentDate, product, sku, restrictions); if (lastId != null) { restrictions.add(builder.gt(product.get("id").as(Long.class), lastId)); } // Add the restrictions to the criteria query criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); //Add ordering so that paginated queries are consistent criteria.orderBy(builder.asc(product.get("id"))); return criteria; }
@Override protected <T> Expression getEntityField(CriteriaQuery<T> query, String listId, Attribute attr) { if( attr == null ) { return null; } Root<TaskImpl> taskRoot = null; Join<TaskImpl, TaskDataImpl> taskDataJoin = null; Join<TaskImpl, PeopleAssignmentsImpl> peopAssignJoin = null; for( Root root : query.getRoots() ) { if( TaskImpl.class.equals(root.getJavaType()) ) { taskRoot = (Root<TaskImpl>) root; for( Join<TaskImpl, ?> join : taskRoot.getJoins() ) { if( TaskDataImpl.class.equals(join.getJavaType()) ) { taskDataJoin = (Join<TaskImpl, TaskDataImpl>) join; } else if( PeopleAssignmentsImpl.class.equals(join.getJavaType()) ) { peopAssignJoin = (Join<TaskImpl, PeopleAssignmentsImpl>) join; } } } } assert taskRoot != null : "Unable to find TaskImpl Root in query!"; if( taskDataJoin == null ) { taskDataJoin = taskRoot.join(TaskImpl_.taskData); } assert taskDataJoin != null : "Unable to find TaskDataImpl Join in query!"; return taskImplSpecificGetEntityField(query, taskRoot, taskDataJoin, peopAssignJoin, listId, attr); }
/** * Returns a list of all users the given user is owner of. * * @param owner the user that owns other users */ public static List<MCRUser> listUsers(MCRUser owner) { EntityManager em = MCREntityManagerProvider.getCurrentEntityManager(); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<MCRUser> query = cb.createQuery(MCRUser.class); Root<MCRUser> users = query.from(MCRUser.class); users.fetch(MCRUser_.owner); return em.createQuery( query .distinct(true) .where(cb.equal(users.get(MCRUser_.owner), owner))) .getResultList(); }
@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 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); } } }
Department department = new Department(); department.id = 1L; entityManager.persist( department ); employee1.accessLevel = 0; employee1.department = department; entityManager.persist( employee1 ); employee2.accessLevel = 1; employee2.department = department; entityManager.persist( employee2 ); " e.password = :password", Employee.class) .setParameter( "username", username) .setParameter( "password", password) .getSingleResult(); CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Employee> query = builder.createQuery( Employee.class ); Root<Employee> root = query.from( Employee.class ); root.fetch( "projects", JoinType.LEFT); query.select(root).where( builder.and( builder.equal(root.get("username"), username), builder.equal(root.get("password"), password)
private List<RootEntity> getAllRows(EntityManager em) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<RootEntity> cq = cb.createQuery( RootEntity.class ); Root<RootEntity> c = cq.from( RootEntity.class ); return em.createQuery( cq.select( c ).orderBy( cb.desc( c.get( "status" ) ) ) ).getResultList(); }
protected Long readCountAllActiveSkusInternal(Date currentDate) { // Set up the criteria query that specifies we want to return a Long CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); // The root of our search is sku Root<SkuImpl> sku = criteria.from(SkuImpl.class); // We want the count of products criteria.select(builder.count(sku)); // Ensure the sku is currently active List<Predicate> restrictions = new ArrayList<Predicate>(); // 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))); // Add the restrictions to the criteria query criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Long> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Catalog"); return query.getSingleResult(); }
@Test public void testFetchObjectArray() { doInJPA(entityManager -> { CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object[]> criteria = builder.createQuery(Object[].class); Root<PostComment> root = criteria.from(PostComment.class); criteria.multiselect(root.get(PostComment_.id), root.get(PostComment_.review)); Join<PostComment, Post> postJoin = root.join("post"); criteria.where(builder.like(postJoin.get(Post_.title), "high-performance%")); List<Object[]> comments = entityManager.createQuery(criteria).getResultList(); assertEquals(5, comments.size()); }); }
@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(); }
@TestForIssue(jiraKey = "HHH-9296") @Test public void selectByParent() { doInJPA( this::entityManagerFactory, entityManager -> { Post post = entityManager.find( Post.class, 1 ); CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<PostDetails> query = cb.createQuery( PostDetails.class ); Root<PostDetails> root = query.from( PostDetails.class ); query.where( cb.equal( root.get( "post" ), post ) ); final PostDetails result = entityManager.createQuery( query ).getSingleResult(); assertNotNull( result ); }); }
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_param_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::criteria-param-example[] CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Person> criteria = builder.createQuery( Person.class ); Root<Person> root = criteria.from( Person.class ); ParameterExpression<String> nickNameParameter = builder.parameter( String.class ); criteria.where( builder.equal( root.get( Person_.nickName ), nickNameParameter ) ); TypedQuery<Person> query = entityManager.createQuery( criteria ); query.setParameter( nickNameParameter, "JD" ); List<Person> persons = query.getResultList(); //end::criteria-param-example[] assertEquals(1, persons.size()); }); }
@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 (? , ? , ? , ? , ? , ? , ? , ?)" ) ); }
@Test public void test_criteria_from_fetch_example() { doInJPA( this::entityManagerFactory, entityManager -> { //tag::criteria-from-fetch-example[] CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Phone> criteria = builder.createQuery( Phone.class ); Root<Phone> root = criteria.from( Phone.class ); // Phone.person is a @ManyToOne Fetch<Phone, Person> personFetch = root.fetch( Phone_.person ); // Person.addresses is an @ElementCollection Fetch<Person, String> addressesJoin = personFetch.fetch( Person_.addresses ); criteria.where( builder.isNotEmpty( root.get( Phone_.calls ) ) ); List<Phone> phones = entityManager.createQuery( criteria ).getResultList(); //end::criteria-from-fetch-example[] assertEquals(2, phones.size()); }); }
protected Long readCountAllActiveProductsInternal(Date currentDate) { // Set up the criteria query that specifies we want to return a Long CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> criteria = builder.createQuery(Long.class); // The root of our search is Product Root<ProductImpl> product = criteria.from(ProductImpl.class); // We need to filter on active date on the sku Join<Product, Sku> sku = product.join("defaultSku"); // We want the count of products criteria.select(builder.count(product)); // Ensure the product is currently active List<Predicate> restrictions = new ArrayList<Predicate>(); attachActiveRestriction(currentDate, product, sku, restrictions); // Add the restrictions to the criteria query criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); TypedQuery<Long> query = em.createQuery(criteria); query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Catalog"); return query.getSingleResult(); }
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); }