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; }
builder.greaterThan(sku.get("activeEndDate").as(Date.class), currentDate))); if (lastId != null) { restrictions.add(builder.gt(sku.get("id").as(Long.class), lastId));
protected <E extends Object> void assertFindByNameAndAge(EntityManager em, Class clazz, E e, String name, String minVal, String fieldName) { CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<E> query = criteriaBuilder.createQuery(clazz); Root<E> from = query.from(clazz); query.select(from.alias("p")); query.where(criteriaBuilder.and(criteriaBuilder.equal(from.get(fieldName), name), criteriaBuilder.gt((Expression) from.get("age"), Integer.parseInt(minVal)))); TypedQuery<E> q = em.createQuery(query); List<E> results = q.getResultList(); Assert.assertNotNull(results); Assert.assertFalse(results.isEmpty()); Assert.assertEquals(2, results.size()); }
@Test public void testWithANDGTClause() { String expected = "Select p from Person p where p.personName = \"vivek\" AND p.age > 32"; 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"), new Integer(32)))); String actual = CriteriaQueryTranslator.translate(personQuery); Assert.assertEquals(expected.trim(), actual.trim()); }
protected <E extends Object> void assertFindByNameAndAgeGTAndLT(EntityManager em, Class clazz, E e, String name, String minVal, String maxVal, String fieldName) { CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<E> query = criteriaBuilder.createQuery(clazz); Root<E> from = query.from(clazz); query.select(from.alias("p")); query.where(criteriaBuilder.and(criteriaBuilder.equal(from.get(fieldName), name), criteriaBuilder.gt((Expression) from.get("age"), Integer.parseInt(minVal)), criteriaBuilder.lt((Expression) from.get("age"), Integer.parseInt(maxVal)))); // // // find by name, age clause TypedQuery<E> q = em.createQuery(query); List<E> results = q.getResultList(); Assert.assertNotNull(results); Assert.assertFalse(results.isEmpty()); Assert.assertEquals(1, results.size()); }
@Test public void testWithANDGTLTClause() { String expected = "Select p from Person p where p.personName = \"vivek\" AND p.age > 32 AND p.salary <= 3200.01"; 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))); 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()); }
/** * Apply a "greater than" constraint to the property path. * * @param propertyPath Property path that we want to compare. * @param argument Argument number. * @param manager JPA EntityManager. * @return Predicate a predicate representation. */ private static Predicate createGreaterThan(Expression<? extends Number> propertyPath, Number argument, EntityManager manager) { CriteriaBuilder builder = manager.getCriteriaBuilder(); return builder.gt(propertyPath, argument); }
/** *获取对应属性比输入值大的集合涉及int值、long值、double值 * @param attribute * @param value * @return * */ public static Specification greater(String attribute, Long value) { return (root, query, cb) -> cb.gt(root.get(attribute),value); } public static Specification greater(String attribute, long value) {
public static Specification greater(String attribute, double value) { return (root, query, cb) -> cb.gt(root.get(attribute),value); } /**
public static Specification greater(String attribute, long value) { return (root, query, cb) -> cb.gt(root.get(attribute),value); } public static Specification greater(String attribute, Integer value) {
public static Specification greater(String attribute, int value) { return (root, query, cb) -> cb.gt(root.get(attribute),value); } public static Specification greater(String attribute, Double value) {
public static Specification greater(String attribute, Double value) { return (root, query, cb) -> cb.gt(root.get(attribute),value); } public static Specification greater(String attribute, double value) {
public static Specification greater(String attribute, Integer value) { return (root, query, cb) -> cb.gt(root.get(attribute),value); } public static Specification greater(String attribute, int value) {
@Override public List<Predicate> build(CriteriaBuilder builder, Path<E> path) { return Arrays.asList(builder.gt(path.get(att), value)); }
/** * 大于 * * @param propertyName * 属性名称 * @param value * 属性值 */ public void gt(String propertyName, Number value) { if (isNullOrEmpty(value)) { return; } this.predicates.add(criteriaBuilder.gt(from.get(propertyName), value)); }
public List<BankAccount> findWithBalance(int amount) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<BankAccount> cq = cb.createQuery(BankAccount.class); Root<BankAccount> from = cq.from(BankAccount.class); ParameterExpression<Integer> balance = cb.parameter(Integer.class); cq.select(from); //Here is the trick! Predicate predicate = cb.gt(from.<Integer> get("balance"), balance); cq.where(predicate); cq.orderBy(cb.asc(from.get("ownerName"))); TypedQuery<BankAccount> query = em.createQuery(cq); query.setParameter(balance, amount); return query.getResultList(); }
private void gt(String propertyName, Object value) { if (value instanceof Date) { predicates.add(criteriaBuilder.greaterThan(path(propertyName), (Date) value)); } else if (value instanceof Calendar) { predicates.add(criteriaBuilder.greaterThan(path(propertyName), (Calendar) value)); } else if (value instanceof Number) { predicates.add(criteriaBuilder.gt(path(propertyName), (Number) value)); } }
/** * Add a Field Search Condition that will check if the size of a collection in an entity is greater than the specified size. * * @param propertyName The name of the collection as defined in the Entity mapping class. * @param size The size that the collection should be greater than. */ protected void addSizeGreaterThanCondition(final String propertyName, final Integer size) { final Expression<Integer> propertySizeExpression = getCriteriaBuilder().size(getRootPath().get(propertyName).as(Set.class)); fieldConditions.add(getCriteriaBuilder().gt(propertySizeExpression, size)); }