Refine search
@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 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 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; }
private CriteriaQuery<SearchRedirect> buildFindSearchRedirectBySearchTermCriteria(String searchTerm) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<SearchRedirect> criteria = builder.createQuery(SearchRedirect.class); Root<SearchRedirectImpl> redirect = criteria.from(SearchRedirectImpl.class); List<Predicate> restrictions = new ArrayList<>(); restrictions.add(builder.equal(builder.upper(redirect.<String>get("searchTerm")), searchTerm.toUpperCase())); // Add the active start/end date restrictions Date currentDate = getCurrentDateAfterFactoringInDateResolution(); if (isNullActiveStartDateActive) { restrictions.add(builder.or(builder.isNull(redirect.get("activeStartDate")), builder.lessThanOrEqualTo(redirect.get("activeStartDate").as(Date.class), currentDate))); } else { restrictions.add(builder.and(builder.isNotNull(redirect.get("activeStartDate")), builder.lessThanOrEqualTo(redirect.get("activeStartDate").as(Date.class), currentDate))); } restrictions.add(builder.or(builder.isNull(redirect.get("activeEndDate")), builder.greaterThan(redirect.get("activeEndDate").as(Date.class), currentDate))); // Add the restrictions to the criteria query criteria.select(redirect); criteria.where(restrictions.toArray(new Predicate[restrictions.size()])); return criteria.orderBy(builder.asc(redirect.get("searchPriority"))); }
@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(); }
/** * This is the main ("highest"? "most abstract"?) method that is used to create a {@link CriteriaQuery} from a {@link QueryWhere} instance. * * @param query The (empty) {@link CriteriaQuery} that will be filled using the {@link QueryCriteria} and other information in the {@link QueryWhere} instance * @param queryWhere The {@link QueryWhere} instance, with abstract information that should be added to the {@link CriteriaQuery} * @param builder The {@link CriteriaBuilder}, helpful when creating {@link Predicate}s to add to the {@link CriteriaQuery} * @param queryType The {@link Class} indicating the main {@link Root} of the {@link CriteriaQuery} */ protected <R,T> void fillCriteriaQuery( CriteriaQuery<R> query, QueryWhere queryWhere, CriteriaBuilder builder, Class<T> queryType ) { Predicate queryPredicate = createPredicateFromCriteriaList(query, builder, queryType, queryWhere.getCriteria(), queryWhere ); if( queryPredicate != null ) { query.where(queryPredicate); } if( queryWhere.getAscOrDesc() != null ) { String orderByListId = queryWhere.getOrderByListId(); assert orderByListId != null : "Ascending boolean is set but no order by list Id has been specified!"; Expression orderByPath = getOrderByExpression(query, queryType, orderByListId); Order order; if( queryWhere.getAscOrDesc() ) { order = builder.asc(orderByPath); } else { order = builder.desc(orderByPath); } query.orderBy(order); } }
@Test public void testOrderByWithCompositeClause() { final String expected = "Select u from PersonnelEmbedded u where u.personalDetail.phoneNo = 91234567 ORDER BY u.personalDetail.emailId ASC"; CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<PersonnelEmbedded> embedQuery = criteriaBuilder.createQuery(PersonnelEmbedded.class); Root<PersonnelEmbedded> from = embedQuery.from(PersonnelEmbedded.class); embedQuery.select(from.alias("u")); embedQuery.orderBy(criteriaBuilder.asc(from.get("personalDetail").get("emailId"))); embedQuery.where(criteriaBuilder.equal(from.get("personalDetail").get("phoneNo"), "91234567")); String actual = CriteriaQueryTranslator.translate(embedQuery); Assert.assertEquals(expected.trim(), actual.trim()); }
@Override public CriteriaQuery<DateDimensionImpl> apply(CriteriaBuilder cb) { final CriteriaQuery<DateDimensionImpl> criteriaQuery = cb.createQuery(DateDimensionImpl.class); final Root<DateDimensionImpl> dimensionRoot = criteriaQuery.from(DateDimensionImpl.class); criteriaQuery.orderBy( cb.asc(dimensionRoot.get(DateDimensionImpl_.date))); return criteriaQuery; } });
@Override public CriteriaQuery<TimeDimensionImpl> apply(CriteriaBuilder cb) { final CriteriaQuery<TimeDimensionImpl> criteriaQuery = cb.createQuery(TimeDimensionImpl.class); final Root<TimeDimensionImpl> dimensionRoot = criteriaQuery.from(TimeDimensionImpl.class); criteriaQuery.select(dimensionRoot); criteriaQuery.orderBy( cb.asc(dimensionRoot.get(TimeDimensionImpl_.time))); return criteriaQuery; } });
@Override public CriteriaQuery<QuarterDetailImpl> apply(CriteriaBuilder cb) { final CriteriaQuery<QuarterDetailImpl> criteriaQuery = cb.createQuery(QuarterDetailImpl.class); final Root<QuarterDetailImpl> entityRoot = criteriaQuery.from(QuarterDetailImpl.class); criteriaQuery.select(entityRoot); criteriaQuery.orderBy( cb.asc(entityRoot.get(QuarterDetailImpl_.quarterId))); return criteriaQuery; } });
@Override public List<DbConfigurationParam> findAllParameters() { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<DbConfigurationParam> cq = cb.createQuery(DbConfigurationParam.class); Root<DbConfigurationParam> from = cq.from(DbConfigurationParam.class); cq.select(from).orderBy(cb.asc(from.get("categoryCode")), cb.asc(from.get("code"))); TypedQuery<DbConfigurationParam> q = em.createQuery(cq); return q.getResultList(); } }
@Override public CriteriaQuery<AcademicTermDetailImpl> apply(CriteriaBuilder cb) { final CriteriaQuery<AcademicTermDetailImpl> criteriaQuery = cb.createQuery(AcademicTermDetailImpl.class); final Root<AcademicTermDetailImpl> entityRoot = criteriaQuery.from(AcademicTermDetailImpl.class); criteriaQuery.select(entityRoot); criteriaQuery.orderBy( cb.asc(entityRoot.get(AcademicTermDetailImpl_.start))); return criteriaQuery; } });
@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; } });
private Specification<EventEntity> eventPeriodSpecification(OffsetDateTime start, OffsetDateTime end) { return (root, criteriaQuery, criteriaBuilder) -> { criteriaQuery.orderBy(criteriaBuilder.asc(root.get(TIMESTAMP_ID_ATTRIBUTE))); return criteriaBuilder.between(root.get(TIMESTAMP_ID_ATTRIBUTE), start, end); }; } }
/** * Adds ORDER BY ... ASC/DESC to query * Mutates provided criteria query * @param sortFieldOpt - field to sort by (field name in JPA Entity class) * @param asc - true if order should be ASC, false otherwise */ public static void order(CriteriaBuilder cb, CriteriaQuery<?> cq, Root<?> from, Optional<String> sortFieldOpt, boolean asc) { sortFieldOpt.ifPresent(sortField -> { Order order = asc ? cb.asc(from.get(sortField)) : cb.desc(from.get(sortField)); cq.orderBy(order); }); }