private static <D extends Comparable<? super D>, E> Predicate buildRangePredicate(
     Range<E, D> range, Root<E> root, CriteriaBuilder builder) {
   if (range.isBetween()) {
     return builder.between(root.get(range.getField()), range.getFrom(), range.getTo());
   } else if (range.isFromSet()) {
     return builder.greaterThanOrEqualTo(root.get(range.getField()), range.getFrom());
   } else if (range.isToSet()) {
     return builder.lessThanOrEqualTo(root.get(range.getField()), range.getTo());
   }
   return null;
 }
示例#2
0
 @Test
 public void between() {
   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.between(builder.length(root.get(Country_.name)), 5, 10));
   List<Country> countries = entityManager.createQuery(query).getResultList();
   assertAndShow(10, countries);
 }
示例#3
0
  /** This test provides an example of using between condition */
  @Test
  public void testBetween() {
    log.info("*** testBetween() ***");

    CriteriaBuilder cb = em.getCriteriaBuilder();
    {
      CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);

      // select s from Sale s
      // where s.amount BETWEEN :low AND :high"
      Root<Sale> s = qdef.from(Sale.class);
      qdef.select(s)
          .where(
              cb.between(
                  s.<BigDecimal>get("amount"), new BigDecimal(90.00), new BigDecimal(110.00)));
      List<Sale> sales = em.createQuery(qdef).getResultList();
      for (Sale result : sales) {
        log.info("found=" + result);
      }
      assertEquals("unexpected number of rows", 1, sales.size());
    }

    {
      CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);

      // select s from Sale s
      // where s.amount NOT BETWEEN :low AND :high"
      Root<Sale> s = qdef.from(Sale.class);
      qdef.select(s)
          .where(
              cb.not(
                  cb.between(
                      s.<BigDecimal>get("amount"), new BigDecimal(90.00), new BigDecimal(110.00))));
      List<Sale> sales = em.createQuery(qdef).getResultList();
      for (Sale result : sales) {
        log.info("found=" + result);
      }
      assertEquals("unexpected number of rows", 1, sales.size());
    }
  }
示例#4
0
  @Override
  public List<Product> getProductsByConditions(
      String categoryContain, String nameContain, Float priceFrom, Float priceBefore) {
    openConnection();
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Product> cq = cb.createQuery(Product.class);
    Root<Product> productRoot = cq.from(Product.class);
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (categoryContain != null) {
      if (!categoryContain.equals("")) {
        Expression<String> catName = productRoot.get("cat").get("name");
        Predicate catNameContainPredicate = cb.like(cb.lower(catName), "%" + categoryContain + "%");
        predicates.add(catNameContainPredicate);
      }
    }

    if (nameContain != null) {
      if (!nameContain.equals("")) {
        Expression<String> productName = productRoot.get("name");
        Predicate nameContainPredicate = cb.like(cb.lower(productName), "%" + nameContain + "%");
        predicates.add(nameContainPredicate);
      }
    }

    Expression<Float> price = productRoot.get("price");
    if (priceBefore != null) {
      Predicate pricePredicate = cb.between(price, priceFrom, priceBefore);
      predicates.add(pricePredicate);
    }

    if (priceBefore == null) {
      Predicate pricePredicate = cb.gt(price, priceFrom);
      predicates.add(pricePredicate);
    }

    cq.select(productRoot);

    if (predicates.size() != 0) {
      Predicate[] predicatesArray = new Predicate[predicates.size()];
      cq.where(predicates.toArray(predicatesArray));
    }

    TypedQuery<Product> productTypedQuery = entityManager.createQuery(cq);
    List<Product> productList = productTypedQuery.getResultList();
    closeConnection();

    return DbManagerPersistence.filterProducts(productList, nameContain, categoryContain);
  }
示例#5
0
  /**
   * Take a planned operation and check if a real operation exists for this planned operation. An
   * operation matches if it's between 2 days before or after the planned date, the planned amount
   * matches exactly or the planned label is contained in the real operation.
   *
   * @param plannedOp Planned operation to check
   * @return The operation that match or null if any operation matching is found.
   */
  public Operation matchRealOp(Operation plannedOp) {
    CriteriaBuilder b = getBuilder();

    // creating criteria
    CriteriaQuery<Operation> q = b.createQuery(Operation.class);
    Root<Operation> op = q.from(Operation.class);
    q.select(op);

    // start and end date
    Calendar startDate = Calendar.getInstance();
    startDate.setTime(plannedOp.getOperationDate());
    startDate.set(
        startDate.get(Calendar.YEAR),
        startDate.get(Calendar.MONTH),
        startDate.get(Calendar.DAY_OF_MONTH),
        0,
        0,
        0);
    startDate.add(Calendar.DAY_OF_MONTH, -2);

    Calendar endDate = Calendar.getInstance();
    endDate.setTime(plannedOp.getOperationDate());
    endDate.set(
        endDate.get(Calendar.YEAR),
        endDate.get(Calendar.MONTH),
        endDate.get(Calendar.DAY_OF_MONTH),
        0,
        0,
        0);
    endDate.add(Calendar.DAY_OF_MONTH, 2);

    // adding restriction
    q.where(
        b.and(
            b.between(op.get(Operation_.operationDate), startDate.getTime(), endDate.getTime()),
            b.isNull(op.get(Operation_.planned)),
            b.or(
                b.equal(op.get(Operation_.amount), plannedOp.getPlanned()),
                b.like(op.get(Operation_.label), "%" + plannedOp.getLabel() + "%"))));

    List<Operation> ops = getEm().createQuery(q).getResultList();
    if (ops.size() > 0) {
      return ops.get(0);
    } else {
      return null;
    }
  }
示例#6
0
  /**
   * Get operations from startDay to endDay.
   *
   * @param startDay Day from which retrieve operations.
   * @param endDay Day to which retrieve operations
   * @return Operation list history sorted by operation date and id.
   */
  public List<Operation> getHistory(LocalDate startDay, LocalDate endDay) {
    CriteriaBuilder b = getBuilder();

    // creating criteria
    CriteriaQuery<Operation> q = b.createQuery(Operation.class);
    Root<Operation> op = q.from(Operation.class);
    q.select(op);

    // adding restriction
    // - every operation between the start and end date
    // - every planned operation not sync before start date
    q.where(
        b.or(
            b.between(op.get(Operation_.operationDate), startDay.toDate(), endDay.toDate()),
            b.and(
                b.lessThan(op.get(Operation_.operationDate), startDay.toDate()),
                b.isNull(op.get(Operation_.amount)))));

    // ordering
    q.orderBy(b.asc(op.get(Operation_.operationDate)), b.asc(op.get(Operation_.operationId)));

    return getEm().createQuery(q).getResultList();
  }