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);
  }
Пример #5
0
 @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);
    }
  }
Пример #8
0
  /** 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());
    }
  }
Пример #9
0
  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;
  }
Пример #10
0
  @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();
  }
Пример #11
0
 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);
 }