public void testMetamodelOnClauseWithLeftJoin() { EntityManager em = createEntityManager(); Query query = em.createQuery( "Select e from Employee e left join e.address a on a.city = 'Ottawa' " + "where a.postalCode is not null"); List baseResult = query.getResultList(); Metamodel metamodel = em.getMetamodel(); EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class); EntityType<Address> entityAddr_ = metamodel.entity(Address.class); CriteriaBuilder qb = em.getCriteriaBuilder(); CriteriaQuery<Employee> cq = qb.createQuery(Employee.class); Root<Employee> root = cq.from(entityEmp_); Join address = root.join(entityEmp_.getSingularAttribute("address"), JoinType.LEFT); address.on(qb.equal(address.get(entityAddr_.getSingularAttribute("city")), "Ottawa")); cq.where(qb.isNotNull(address.get(entityAddr_.getSingularAttribute("postalCode")))); List testResult = em.createQuery(cq).getResultList(); clearCache(); closeEntityManager(em); if (baseResult.size() != testResult.size()) { fail( "Criteria query using ON clause with a left join did not match JPQL results; " + baseResult.size() + " were expected, while criteria query returned " + testResult.size()); } }
private int getNumberOfInstancesEnded(final ProcessIdentifier identifier) { final CriteriaBuilder cb = em.getCriteriaBuilder(); final CriteriaQuery<Long> cq = cb.createQuery(Long.class); final Root<MeasuredProcessInstance> processInstanceRoot = cq.from(MeasuredProcessInstance.class); cq.where( cb.equal( processInstanceRoot .get(MeasuredProcessInstance_.identifier) .get(ProcessInstanceIdentifier_.metricsId), identifier.getMetricsId()), cb.equal( processInstanceRoot .get(MeasuredProcessInstance_.identifier) .get(ProcessInstanceIdentifier_.packageName), identifier.getPackageName()), cb.equal( processInstanceRoot .get(MeasuredProcessInstance_.identifier) .get(ProcessInstanceIdentifier_.processId), identifier.getProcessId()), cb.isNotNull(processInstanceRoot.get(MeasuredProcessInstance_.endingTime))); cq.select(cb.countDistinct(processInstanceRoot)); return em.createQuery(cq).getSingleResult().intValue(); }
public static <E> Predicate byRanges( Root<E> root, CriteriaQuery<?> query, CriteriaBuilder builder, final List<Range<?, ?>> ranges, final Class<E> type) { List<Predicate> predicates = newArrayList(); for (Range<?, ?> r : ranges) { @SuppressWarnings("unchecked") Range<E, ?> range = (Range<E, ?>) r; if (range.isSet()) { Predicate rangePredicate = buildRangePredicate(range, root, builder); if (rangePredicate != null) { if (!range.isIncludeNullSet() || range.getIncludeNull() == FALSE) { predicates.add(rangePredicate); } else { predicates.add(builder.or(rangePredicate, builder.isNull(root.get(range.getField())))); } } // no range at all, let's take the opportunity to keep only null... if (TRUE == range.getIncludeNull()) { predicates.add(builder.isNull(root.get(range.getField()))); } else if (FALSE == range.getIncludeNull()) { predicates.add(builder.isNotNull(root.get(range.getField()))); } } } return JpaUtil.andPredicate(builder, predicates); }
public <E> Predicate byEntitySelectors( Root<E> root, CriteriaBuilder builder, SearchParameters sp) { List<EntitySelector<?, ?, ?>> selectors = sp.getEntities(); List<Predicate> predicates = newArrayList(); for (EntitySelector<?, ?, ?> s : selectors) { @SuppressWarnings("unchecked") EntitySelector<? super E, ? extends Identifiable<?>, ?> selector = (EntitySelector<? super E, ? extends Identifiable<?>, ?>) s; if (selector.isNotEmpty()) { List<Predicate> selectorPredicates = newArrayList(); for (Identifiable<?> selection : selector.getSelected()) { selectorPredicates.add(builder.equal(getExpression(root, selector), selection.getId())); } if (TRUE == selector.getIncludeNull()) { selectorPredicates.add(builder.or(builder.isNull(getExpression(root, selector)))); } predicates.add(JpaUtil.orPredicate(builder, selectorPredicates)); } else if (selector.isIncludeNullSet()) { if (selector.getIncludeNull()) { predicates.add(builder.isNull(getExpression(root, selector))); } else { predicates.add(builder.isNotNull(getExpression(root, selector))); } } } return JpaUtil.concatPredicate(sp, builder, predicates); }
@Test public void isNotNull() { EntityManager entityManager = factory.createEntityManager(); CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<Country> query = builder.createQuery(Country.class); Root<Country> root = query.from(Country.class); query.select(root); query.where(builder.isNotNull(root.get(Country_.name))); List<Country> countries = entityManager.createQuery(query).getResultList(); assertAndShow(19, countries); }
public void testMetamodelCriteriaDelete() { if ((getPersistenceUnitServerSession()).getPlatform().isSymfoware()) { getPersistenceUnitServerSession() .logMessage( "Test simpleDelete skipped for this platform, " + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193)."); return; } EntityManager em = createEntityManager(); try { beginTransaction(em); int nrOfEmps = ((Number) em.createQuery( "SELECT COUNT(phone) FROM PhoneNumber phone where phone.owner.firstName is not null") .getSingleResult()) .intValue(); Metamodel metamodel = em.getMetamodel(); EntityType<PhoneNumber> entityPhone_ = metamodel.entity(PhoneNumber.class); EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class); // test query "Delete Employee e where e.firstName is not null"; CriteriaBuilder qb = em.getCriteriaBuilder(); CriteriaDelete<PhoneNumber> cq = qb.createCriteriaDelete(PhoneNumber.class); Root<PhoneNumber> root = cq.from(entityPhone_); cq.where( qb.isNotNull( root.get(entityPhone_.getSingularAttribute("owner", Employee.class)) .get(entityEmp_.getSingularAttribute("firstName")))); Query testQuery = em.createQuery(cq); int updated = testQuery.executeUpdate(); assertEquals( "testCriteriaDelete: wrong number of deleted instances" + updated, nrOfEmps, updated); // check database changes int nr = ((Number) em.createQuery( "SELECT COUNT(phone) FROM PhoneNumber phone where phone.owner.firstName is not null") .getSingleResult()) .intValue(); assertEquals("testCriteriaDelete: found " + nr + " PhoneNumbers after delete all", 0, nr); } finally { if (isTransactionActive(em)) { rollbackTransaction(em); } closeEntityManager(em); } }
public void testMetamodelCriteriaUpdate() { if ((getPersistenceUnitServerSession()).getPlatform().isSymfoware()) { getPersistenceUnitServerSession() .logMessage( "Test simpleUpdate skipped for this platform, " + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193)."); return; } EntityManager em = createEntityManager(); int nrOfEmps = ((Number) em.createQuery("SELECT COUNT(e) FROM Employee e where e.firstName is not null") .getSingleResult()) .intValue(); Metamodel metamodel = em.getMetamodel(); EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class); // test query "UPDATE Employee e SET e.firstName = 'CHANGED' where e.firstName is not null"; CriteriaBuilder qb = em.getCriteriaBuilder(); CriteriaUpdate<Employee> cq = qb.createCriteriaUpdate(Employee.class); Root<Employee> root = cq.from(entityEmp_); cq.set(root.get(entityEmp_.getSingularAttribute("firstName", String.class)), "CHANGED"); cq.where(qb.isNotNull(root.get(entityEmp_.getSingularAttribute("firstName")))); beginTransaction(em); try { Query q = em.createQuery(cq); int updated = q.executeUpdate(); assertEquals( "simpleCriteriaUpdateTest: wrong number of updated instances", nrOfEmps, updated); // check database changes int nr = ((Number) em.createQuery("SELECT COUNT(e) FROM Employee e WHERE e.firstName = 'CHANGED'") .getSingleResult()) .intValue(); assertEquals( "simpleCriteriaUpdateTest: unexpected number of changed values in the database", nrOfEmps, nr); } finally { if (isTransactionActive(em)) { rollbackTransaction(em); } closeEntityManager(em); } }
/** This test provides a demonstration of testing for a null value. */ @Test public void testIsNull() { log.info("*** testIsNull() ***"); CriteriaBuilder cb = em.getCriteriaBuilder(); { CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class); // select s from Sale s // where s.store IS NULL Root<Sale> s = qdef.from(Sale.class); qdef.select(s).where(cb.isNull(s.get("store"))); // .where(cb.equal(s.get("store"), cb.nullLiteral(Store.class))); List<Sale> sales = em.createQuery(qdef).getResultList(); for (Sale result : sales) { log.info("found=" + result); } assertEquals("unexpected number of rows", 0, sales.size()); } { CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class); // select s from Sale s // where s.store IS NOT NULL Root<Sale> s = qdef.from(Sale.class); qdef.select(s).where(cb.isNotNull(s.get("store"))); // .where(cb.not(cb.equal(s.get("store"), cb.nullLiteral(Store.class)))); List<Sale> sales = em.createQuery(qdef).getResultList(); for (Sale result : sales) { log.info("found=" + result); } assertEquals("unexpected number of rows", 2, sales.size()); } }
public static Predicate createWhereQuery(CriteriaBuilder cb, Root<?> r, QueryParameters q) { Predicate predicate = cb.conjunction(); for (QueryFilter f : q.getFilters()) { Predicate np = null; try { Path<String> stringField = getCriteraField(f.getField(), r); Path<Date> dateField = getCriteraField(f.getField(), r); switch (f.getOperation()) { case EQ: if (f.getDateValue() != null) { np = cb.equal(stringField, f.getDateValue()); } else { np = cb.equal(stringField, getValueForPath(stringField, f.getValue())); } break; case EQIC: if (f.getDateValue() != null) { np = cb.equal(stringField, f.getDateValue()); } else if (f.getValue() != null) { np = cb.equal(cb.lower(stringField), f.getValue().toLowerCase()); } break; case NEQ: if (f.getDateValue() != null) { np = cb.notEqual(stringField, f.getDateValue()); } else { np = cb.notEqual(stringField, getValueForPath(stringField, f.getValue())); } break; case NEQIC: if (f.getDateValue() != null) { np = cb.notEqual(stringField, f.getDateValue()); } else if (f.getValue() != null) { np = cb.notEqual(cb.lower(stringField), f.getValue().toLowerCase()); } break; case LIKE: np = cb.like(stringField, f.getValue()); break; case LIKEIC: np = cb.like(cb.lower(stringField), f.getValue().toLowerCase()); break; case GT: if (f.getDateValue() != null) { np = cb.greaterThan(dateField, f.getDateValue()); } else { np = cb.greaterThan(stringField, f.getValue()); } break; case GTE: if (f.getDateValue() != null) { np = cb.greaterThanOrEqualTo(dateField, f.getDateValue()); } else { np = cb.greaterThanOrEqualTo(stringField, f.getValue()); } break; case LT: if (f.getDateValue() != null) { np = cb.lessThan(dateField, f.getDateValue()); } else { np = cb.lessThan(stringField, f.getValue()); } break; case LTE: if (f.getDateValue() != null) { np = cb.lessThanOrEqualTo(dateField, f.getDateValue()); } else { np = cb.lessThanOrEqualTo(stringField, f.getValue()); } break; case IN: np = stringField.in( f.getValues() .stream() .map(s -> getValueForPath(stringField, s)) .collect(Collectors.toList())); break; case INIC: np = cb.lower(stringField) .in( f.getValues() .stream() .map(String::toLowerCase) .collect(Collectors.toList())); break; case NIN: np = cb.not( stringField.in( f.getValues() .stream() .map(s -> getValueForPath(stringField, s)) .collect(Collectors.toList()))); break; case NINIC: np = cb.not( cb.lower(stringField) .in( f.getValues() .stream() .map(String::toLowerCase) .collect(Collectors.toList()))); break; case ISNULL: np = cb.isNull(stringField); break; case ISNOTNULL: np = cb.isNotNull(stringField); break; } } catch (IllegalArgumentException e) { throw new NoSuchEntityFieldException( e.getMessage(), f.getField(), r.getJavaType().getSimpleName()); } predicate = cb.and(predicate, np); } return predicate; }
@Override public List<Task> findTaskByQuery(TaskQuery query) { EntityManager em = getEntityManager(); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Task> q = cb.createQuery(Task.class); Root<Task> task = q.from(Task.class); q.select(task); List<Predicate> predicates = new ArrayList<Predicate>(); if (query.getTaskId() > 0) { predicates.add(cb.equal(task.get("id"), query.getTaskId())); } if (query.getTitle() != null && !query.getTitle().isEmpty()) { predicates.add(cb.like(task.<String>get("title"), "%" + query.getTitle() + "%")); } if (query.getDescription() != null && !query.getDescription().isEmpty()) { predicates.add(cb.like(task.<String>get("description"), '%' + query.getDescription() + '%')); } Predicate assignPred = null; if (query.getAssignee() != null && !query.getAssignee().isEmpty()) { assignPred = cb.like(task.<String>get("assignee"), '%' + query.getAssignee() + '%'); } Predicate msPred = null; if (query.getMemberships() != null) { msPred = cb.or( task.join("status") .join("project") .join("manager", JoinType.LEFT) .in(query.getMemberships()), task.join("status") .join("project") .join("participator", JoinType.LEFT) .in(query.getMemberships())); } Predicate projectPred = null; if (query.getProjectIds() != null) { if (query.getProjectIds().size() == 1 && query.getProjectIds().get(0) == 0) { projectPred = cb.isNotNull(task.get("status")); } else if (query.getProjectIds().isEmpty()) { return Collections.emptyList(); } else { projectPred = task.get("status").get("project").get("id").in(query.getProjectIds()); } } List<Predicate> tmp = new LinkedList<Predicate>(); for (String or : query.getOrFields()) { if (or.equals(TaskUtil.ASSIGNEE)) { tmp.add(assignPred); } if (or.equals(TaskUtil.MEMBERSHIP)) { tmp.add(msPred); } if (or.equals(TaskUtil.PROJECT)) { tmp.add(projectPred); } } if (!tmp.isEmpty()) { predicates.add(cb.or(tmp.toArray(new Predicate[tmp.size()]))); } if (!query.getOrFields().contains(TaskUtil.ASSIGNEE) && assignPred != null) { predicates.add(assignPred); } if (!query.getOrFields().contains(TaskUtil.MEMBERSHIP) && msPred != null) { predicates.add(msPred); } if (!query.getOrFields().contains(TaskUtil.PROJECT) && projectPred != null) { predicates.add(projectPred); } if (query.getKeyword() != null && !query.getKeyword().isEmpty()) { List<Predicate> keyConditions = new LinkedList<Predicate>(); for (String k : query.getKeyword().split(" ")) { if (!(k = k.trim()).isEmpty()) { k = "%" + k.toLowerCase() + "%"; keyConditions.add( cb.or( cb.like(cb.lower(task.<String>get("title")), k), cb.like(cb.lower(task.<String>get("description")), k), cb.like(cb.lower(task.<String>get("assignee")), k))); } } predicates.add(cb.or(keyConditions.toArray(new Predicate[keyConditions.size()]))); } if (query.getCompleted() != null) { if (query.getCompleted()) { predicates.add(cb.equal(task.get("completed"), query.getCompleted())); } else { predicates.add(cb.notEqual(task.get("completed"), !query.getCompleted())); } } if (query.getCalendarIntegrated() != null) { if (query.getCalendarIntegrated()) { predicates.add(cb.equal(task.get("calendarIntegrated"), query.getCalendarIntegrated())); } else { predicates.add(cb.notEqual(task.get("calendarIntegrated"), !query.getCalendarIntegrated())); } } if (query.getStartDate() != null) { predicates.add(cb.greaterThanOrEqualTo(task.<Date>get("endDate"), query.getStartDate())); } if (query.getEndDate() != null) { predicates.add(cb.lessThanOrEqualTo(task.<Date>get("startDate"), query.getEndDate())); } if (predicates.size() > 0) { Iterator<Predicate> it = predicates.iterator(); Predicate p = it.next(); while (it.hasNext()) { p = cb.and(p, it.next()); } q.where(p); } if (query.getOrderBy() != null && !query.getOrderBy().isEmpty()) { List<OrderBy> orderBies = query.getOrderBy(); Order[] orders = new Order[orderBies.size()]; for (int i = 0; i < orders.length; i++) { OrderBy orderBy = orderBies.get(i); Path p = task.get(orderBy.getFieldName()); orders[i] = orderBy.isAscending() ? cb.asc(p) : cb.desc(p); } q.orderBy(orders); } return em.createQuery(q).getResultList(); }
public List<Goods> findList( Goods.Type type, ProductCategory productCategory, Brand brand, Promotion promotion, Tag tag, Map<Attribute, String> attributeValueMap, BigDecimal startPrice, BigDecimal endPrice, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isOutOfStock, Boolean isStockAlert, Boolean hasPromotion, Goods.OrderType orderType, Integer count, List<Filter> filters, List<Order> orders) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Goods> criteriaQuery = criteriaBuilder.createQuery(Goods.class); Root<Goods> root = criteriaQuery.from(Goods.class); criteriaQuery.select(root); Predicate restrictions = criteriaBuilder.conjunction(); if (type != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type)); } if (productCategory != null) { Subquery<ProductCategory> subquery = criteriaQuery.subquery(ProductCategory.class); Root<ProductCategory> subqueryRoot = subquery.from(ProductCategory.class); subquery.select(subqueryRoot); subquery.where( criteriaBuilder.or( criteriaBuilder.equal(subqueryRoot, productCategory), criteriaBuilder.like( subqueryRoot.<String>get("treePath"), "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId() + ProductCategory.TREE_PATH_SEPARATOR + "%"))); restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.in(root.get("productCategory")).value(subquery)); } if (brand != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand)); } if (promotion != null) { restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.equal(root.join("promotions"), promotion)); } if (tag != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("tags"), tag)); } if (attributeValueMap != null) { for (Map.Entry<Attribute, String> entry : attributeValueMap.entrySet()) { String propertyName = Goods.ATTRIBUTE_VALUE_PROPERTY_NAME_PREFIX + entry.getKey().getPropertyIndex(); restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.equal(root.get(propertyName), entry.getValue())); } } if (startPrice != null && endPrice != null && startPrice.compareTo(endPrice) > 0) { BigDecimal temp = startPrice; startPrice = endPrice; endPrice = temp; } if (startPrice != null && startPrice.compareTo(BigDecimal.ZERO) >= 0) { restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.ge(root.<Number>get("price"), startPrice)); } if (endPrice != null && endPrice.compareTo(BigDecimal.ZERO) >= 0) { restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.le(root.<Number>get("price"), endPrice)); } if (isMarketable != null) { restrictions = criteriaBuilder.and( restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable)); } if (isList != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList)); } if (isTop != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop)); } if (isOutOfStock != null) { Subquery<Product> subquery = criteriaQuery.subquery(Product.class); Root<Product> subqueryRoot = subquery.from(Product.class); subquery.select(subqueryRoot); Path<Integer> stock = subqueryRoot.get("stock"); Path<Integer> allocatedStock = subqueryRoot.get("allocatedStock"); if (isOutOfStock) { subquery.where( criteriaBuilder.equal(subqueryRoot.get("goods"), root), criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock)); } else { subquery.where( criteriaBuilder.equal(subqueryRoot.get("goods"), root), criteriaBuilder.greaterThan(stock, allocatedStock)); } restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery)); } if (isStockAlert != null) { Subquery<Product> subquery = criteriaQuery.subquery(Product.class); Root<Product> subqueryRoot = subquery.from(Product.class); subquery.select(subqueryRoot); Path<Integer> stock = subqueryRoot.get("stock"); Path<Integer> allocatedStock = subqueryRoot.get("allocatedStock"); Setting setting = SystemUtils.getSetting(); if (isStockAlert) { subquery.where( criteriaBuilder.equal(subqueryRoot.get("goods"), root), criteriaBuilder.lessThanOrEqualTo( stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))); } else { subquery.where( criteriaBuilder.equal(subqueryRoot.get("goods"), root), criteriaBuilder.greaterThan( stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))); } restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery)); } if (hasPromotion != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(root.join("promotions"))); } criteriaQuery.where(restrictions); if (orderType != null) { switch (orderType) { case topDesc: criteriaQuery.orderBy( criteriaBuilder.desc(root.get("isTop")), criteriaBuilder.desc(root.get("createDate"))); break; case priceAsc: criteriaQuery.orderBy( criteriaBuilder.asc(root.get("price")), criteriaBuilder.desc(root.get("createDate"))); break; case priceDesc: criteriaQuery.orderBy( criteriaBuilder.desc(root.get("price")), criteriaBuilder.desc(root.get("createDate"))); break; case salesDesc: criteriaQuery.orderBy( criteriaBuilder.desc(root.get("sales")), criteriaBuilder.desc(root.get("createDate"))); break; case scoreDesc: criteriaQuery.orderBy( criteriaBuilder.desc(root.get("score")), criteriaBuilder.desc(root.get("createDate"))); break; case dateDesc: criteriaQuery.orderBy(criteriaBuilder.desc(root.get("createDate"))); break; } } else if (CollectionUtils.isEmpty(orders)) { criteriaQuery.orderBy( criteriaBuilder.desc(root.get("isTop")), criteriaBuilder.desc(root.get("createDate"))); } return super.findList(criteriaQuery, null, count, filters, orders); }