public <T> List<T> findAllEntitiesOrderedBy(Class<T> entityClass, String orderByColumn, boolean ascending) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<T> criteria = builder.createQuery(entityClass); Root<T> entityRoot = criteria.from(entityClass); criteria.select(entityRoot); javax.persistence.criteria.Order order = ascending ? builder.asc(entityRoot.get(orderByColumn)) : builder.desc(entityRoot.get(orderByColumn)); criteria.orderBy(order); return em.createQuery(criteria).getResultList(); }
@Override public List<Order> readOrdersByDateRange(final Date startDate, final Date endDate) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Order> criteria = builder.createQuery(Order.class); Root<OrderImpl> order = criteria.from(OrderImpl.class); criteria.select(order); criteria.where(builder.between(order.<Date>get("submitDate"), startDate, endDate)); criteria.orderBy(builder.desc(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<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(); }
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(); }
private List<RootEntity> getLimitedRows(EntityManager em, int start, int end) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<RootEntity> cq = cb.createQuery( RootEntity.class ); Root<RootEntity> c = cq.from( RootEntity.class ); CriteriaQuery<RootEntity> select = cq.select( c ).orderBy( cb.desc( c.get( "status" ) ) ); TypedQuery<RootEntity> typedQuery = em.createQuery( select ); typedQuery.setFirstResult( start ); typedQuery.setMaxResults( end ); return typedQuery.getResultList(); }
sorts.add(builder.asc(pathToUse.get(key))); } else { sorts.add(builder.desc(pathToUse.get(key)));
protected void addSorting(CriteriaBuilder criteriaBuilder, List<Order> sorts, FilterMapping filterMapping, Path path) { Expression exp = path; if (filterMapping.getNullsLast() != null && filterMapping.getNullsLast()) { Object largeValue = getAppropriateLargeSortingValue(path.getJavaType()); if (largeValue != null) { exp = criteriaBuilder.coalesce(path, largeValue); } } if (SortDirection.ASCENDING == filterMapping.getSortDirection()) { sorts.add(criteriaBuilder.asc(exp)); } else { sorts.add(criteriaBuilder.desc(exp)); } }
/** * Creates a criteria API {@link javax.persistence.criteria.Order} from the given {@link Order}. * * @param order the order to transform into a JPA {@link javax.persistence.criteria.Order} * @param from the {@link From} the {@link Order} expression is based on * @param cb the {@link CriteriaBuilder} to build the {@link javax.persistence.criteria.Order} with * @return */ @SuppressWarnings("unchecked") private static javax.persistence.criteria.Order toJpaOrder(Order order, From<?, ?> from, CriteriaBuilder cb) { PropertyPath property = PropertyPath.from(order.getProperty(), from.getJavaType()); Expression<?> expression = toExpressionRecursively(from, property); if (order.isIgnoreCase() && String.class.equals(expression.getJavaType())) { Expression<String> lower = cb.lower((Expression<String>) expression); return order.isAscending() ? cb.asc(lower) : cb.desc(lower); } else { return order.isAscending() ? cb.asc(expression) : cb.desc(expression); } }
/** * 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); } }
private Order[] orderClause() { ArrayList<Order> orders = new ArrayList<Order>(orderInfos.size()); Order[] orderArr = (Order[]) Array.newInstance(Order.class, orderInfos.size()); for (OrderInfo info : orderInfos) { if (info.od == Od.ASC) { orders.add(_builder.asc(_root.get(info.attr))); } else if (info.od == Od.DESC) { orders.add(_builder.desc(_root.get(info.attr))); } } return orders.toArray(orderArr); }
CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Tuple> cq= cb.createTupleQuery(); Root<Documents> root = cq.from(Documents.class); Expression<Integer> userId = root.get("USERID"); Expression<String> userType = root.get("USERTYPE"); Expression<Long> count = cb.count(userId); cq.multiselect(userId.alias("USERID"), count.alias("CNT")); cq.where(cb.equal(userType, "COMPANY"); cq.groupBy(userId); cq.orderBy(cb.desc(count)); TypedQuery<Tuple> tq = em.createQuery(cq); for (Tuple t : tq.getResultsList()) { System.out.println(t.get("USERID")); System.out.println(t.get("CNT")); }
q.orderBy(cb.desc(from.get("myUpdated")));
@Test public void testOrderByClause() { String expected = "Select p from Person p ORDER BY p.personName DESC"; 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(from.alias("p")); personQuery.orderBy(criteriaBuilder.desc(from.get("personName"))); String actual = CriteriaQueryTranslator.translate(personQuery); Assert.assertEquals(expected.trim(), actual.trim()); }
@Test public void testOrderByWithWhereClause() { String expected = "Select p from Person p where p.personName = \"vivek\" AND p.age > 32 AND p.salary <= 3200.01 ORDER BY p.personName DESC"; CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<Person> personQuery = criteriaBuilder.createQuery(Person.class); Root<Person> from = personQuery.from(Person.class); personQuery.select(from.alias("p")); personQuery.where(criteriaBuilder.and(criteriaBuilder.equal(from.get("personName"), "vivek"), criteriaBuilder.gt((Expression) from.get("age"), 32), criteriaBuilder.le((Expression) from.get("salary"), 3200.01))); personQuery.orderBy(criteriaBuilder.desc(from.get("personName"))); String actual = CriteriaQueryTranslator.translate(personQuery); Assert.assertEquals(expected.trim(), actual.trim()); }
theOrders.add(theBuilder.asc(theFrom.get("myId"))); } else { theOrders.add(theBuilder.desc(forcedIdJoin.get("myForcedId"))); theOrders.add(theBuilder.desc(theFrom.get("myId"))); theOrders.add(theBuilder.asc(theFrom.get("myUpdated"))); } else { theOrders.add(theBuilder.desc(theFrom.get("myUpdated"))); theOrders.add(theBuilder.asc(join.get(next))); } else { theOrders.add(theBuilder.desc(join.get(next)));
public TypedQuery<DiskCacheEntry> getByUrlQuery(String url) { final CriteriaBuilder cb = em.getCriteriaBuilder(); final CriteriaQuery<DiskCacheEntry> criteriaQuery = cb.createQuery(DiskCacheEntry.class); final Root<DiskCacheEntry> root = criteriaQuery.from(DiskCacheEntry.class); Predicate p1 = cb.equal(root.get(DiskCacheEntry_.url), url); Predicate p2 = cb.ge(root.get(DiskCacheEntry_.size), 0); criteriaQuery.where(cb.and(p1, p2)); criteriaQuery.orderBy(cb.desc(root.get(DiskCacheEntry_.createdAt))); return em.createQuery(criteriaQuery); }
private void applyOrders(Root<T> from, CriteriaQuery<T> query) { List<Order> orderList = new ArrayList<>(); for (Map.Entry<String, Boolean> me : orders.entrySet()) { Path<?> path = getCompoundJoinedPath(from, me.getKey(), true); if (me.getValue() == null || me.getValue().equals(true)) { orderList.add(criteriaBuilder.asc(path)); } else { orderList.add(criteriaBuilder.desc(path)); } } query.orderBy(orderList); }
@Override public HarvestedCollection findByStatusAndMinimalTypeOrderByLastHarvestedDesc(Context context, int status, int type, int limit) throws SQLException { CriteriaBuilder criteriaBuilder = getCriteriaBuilder(context); CriteriaQuery criteriaQuery = getCriteriaQuery(criteriaBuilder, HarvestedCollection.class); Root<HarvestedCollection> harvestedCollectionRoot = criteriaQuery.from(HarvestedCollection.class); criteriaQuery.select(harvestedCollectionRoot); List<javax.persistence.criteria.Order> orderList = new LinkedList<>(); orderList.add(criteriaBuilder.desc(harvestedCollectionRoot.get(HarvestedCollection_.lastHarvested))); criteriaQuery.orderBy(orderList); return singleResult(context, criteriaQuery); }
public CriteriaQuery<E> orderBy(List<SingularAttribute<T, ?>> attributes, boolean asc) { CriteriaBuilder cb = getCriteriaBuilder(); List<Order> orders = new ArrayList<>(); for (SingularAttribute<T, ?> attribute : attributes) { Path<?> selection = getRoot().get(attribute); Order order = asc ? cb.asc(selection) : cb.desc(selection); orders.add(order); } return getCriteriaQuery().orderBy(orders); }