Example #1
0
  /** This test provides a demonstration of using an explicit subquery */
  @Test
  public void testSubqueries() {
    log.info("*** testSubqueries() ***");

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

    // select c from Customer c
    // where c.id IN
    //    (select s.buyerId from Sale s
    //     where s.amount > 100)

    // form subquery
    Subquery<Long> sqdef = qdef.subquery(Long.class);
    Root<Sale> s = sqdef.from(Sale.class);
    sqdef
        .select(s.<Long>get("buyerId"))
        .where(cb.greaterThan(s.<BigDecimal>get("amount"), new BigDecimal(100)));

    // form outer query
    Root<Customer> c = qdef.from(Customer.class);
    qdef.select(c).where(cb.in(c.get("id")).value(sqdef));

    int rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows", 1, rows);
  }
Example #2
0
  /** This test provides a demonstration for using the ANY subquery result evaluation */
  @Test
  public void testAny() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
    Root<Clerk> c = qdef.from(Clerk.class);
    qdef.select(c);

    // select c from Clerk c
    // where 125 < ANY " +
    // (select s.amount from c.sales s)",
    Subquery<BigDecimal> sqdef = qdef.subquery(BigDecimal.class);
    Root<Clerk> c1 = sqdef.from(Clerk.class);
    Join<Clerk, Sale> s = c1.join("sales");
    sqdef.select(s.<BigDecimal>get("amount")).where(cb.equal(c, c1));

    Predicate p1 = cb.lessThan(cb.literal(new BigDecimal(125)), cb.any(sqdef));

    qdef.where(p1);
    List<Clerk> results1 = executeQuery(qdef);
    assertEquals("unexpected number of rows", 2, results1.size());

    // select c from Clerk c
    // where 125 > ANY
    // (select s.amount from c.sales s)
    Predicate p2 = cb.greaterThan(cb.literal(new BigDecimal(125)), cb.any(sqdef));

    qdef.where(p2);
    List<Clerk> results2 = executeQuery(qdef);
    assertEquals("unexpected number of rows", 1, results2.size());
  }
  private <C, X> Subquery<?> createCountSubQuery(
      Root<T> root,
      CriteriaQuery<Long> countQuery,
      Selections<T, C, X> selection,
      CriteriaBuilder builder,
      Specification<T> spec,
      List<Expression<?>> groupBy,
      Predicate having) {

    Subquery<?> subquery = countQuery.subquery(entityInformation.getIdType());
    Root subRoot = subquery.from(entityInformation.getJavaType());
    Expression[] select = selection.select(subRoot, (AbstractQuery<C>) subquery, builder);
    subquery.select(select[0]);
    selection.join(subRoot, builder);
    Predicate predicate =
        builder.equal(
            subRoot.get(entityInformation.getIdAttribute()),
            root.get(entityInformation.getIdAttribute()));
    if (predicate != null) {
      subquery.where(predicate);
    }
    if (groupBy != null) {
      subquery.groupBy(groupBy);
    }
    if (having != null) {
      subquery.having(having);
    }
    return subquery;
  }
Example #4
0
  @Test
  @Transactional
  public void testCopy() {
    EntityManager em = bookDao.getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Book> criteria = cb.createQuery(Book.class);

    // Fetch join
    Root<Book> root = criteria.from(Book.class);
    Path<String> path = root.join("author").<String>get("name");
    root.fetch("author");
    criteria.select(root);

    // SubQuery
    Subquery<String> sq = criteria.subquery(String.class);
    Root<Author> author = sq.from(Author.class);
    sq.select(author.<String>get("name"));
    sq.where(cb.equal(author.<String>get("name"), "Rod"));

    criteria.where(cb.in(path).value(sq));

    CriteriaQuery<Book> copy = cb.createQuery(Book.class);
    JpaUtils.copyCriteria(criteria, copy);

    List<Book> copyBooks = em.createQuery(copy).getResultList();
    List<Book> books = em.createQuery(criteria).getResultList();
    assertEquals(books, copyBooks);
  }
Example #5
0
  @SuppressWarnings("unchecked")
  public Predicate getCanReadOrgPredicate(
      User user,
      OrgType type,
      CriteriaBuilder cb,
      CriteriaQuery<Organization> query,
      boolean containRoot) {

    // 一。获得可管理的顶层机构,不区分机构类型
    List<Organization> topCanReadOrgs = getTopCanReadOrgs(user);
    Root<Organization> from;
    if (query.getRoots().size() > 0) {
      from = (Root<Organization>) query.getRoots().iterator().next();
    } else {
      from = query.from(Organization.class);
    }
    if (topCanReadOrgs.size() == 0) {
      return cb.isNull(from);
    }

    // 二。应用条件
    // 1.机构范围限制(如果有全部数据权限不做限制)
    Subquery<Organization> subquery = query.subquery(Organization.class);
    Root<Organization> subfrom = subquery.from(Organization.class);
    subquery.select(subfrom);
    ListJoin<Organization, Organization> join =
        subfrom.join(Organization_.ancestors, JoinType.INNER);
    In<String> subin = cb.in(join.get(Organization_.id));
    for (Organization o : topCanReadOrgs) {
      subin = subin.value(o.getId());
    }

    // 2.应用机构类别
    if (type != null) {
      Predicate p = cb.equal(subfrom.get(Organization_.orgType), type);
      subquery.where(cb.and(subin, p));
    } else {
      subquery.where(subin);
    }
    // 3.增加祖先节点
    if (containRoot) {
      In<String> in = cb.in(from.get(Organization_.id));
      boolean hasdata = false;
      for (Organization o : topCanReadOrgs) {
        Organization parento = o.getParent();
        while (parento != null) {
          hasdata = true;
          in = in.value(parento.getId());
          parento = parento.getParent();
        }
      }
      if (hasdata) {
        return cb.or(cb.in(from).value(subquery), in);
      }
    }
    return cb.in(from).value(subquery);
  }
Example #6
0
 public List<Goods> findList(
     ProductCategory productCategory,
     Boolean isMarketable,
     Goods.GenerateMethod generateMethod,
     Date beginDate,
     Date endDate,
     Integer first,
     Integer count) {
   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 (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 (isMarketable != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
   }
   if (generateMethod != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions, criteriaBuilder.equal(root.get("generateMethod"), generateMethod));
   }
   if (beginDate != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions,
             criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
   }
   if (endDate != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions,
             criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
   }
   criteriaQuery.where(restrictions);
   return super.findList(criteriaQuery, first, count, null, null);
 }
  public List<Employee> getEmployeesUsingCanonicalMetamodelQuery() {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> emp = c.from(Employee.class);
    Subquery<Integer> sq = c.subquery(Integer.class);
    Root<Department> dept = sq.from(Department.class);
    Join<Employee, Project> project = dept.join(Department_.employees).join(Employee_.projects);
    sq.select(dept.get(Department_.id))
        .distinct(true)
        .where(cb.like(project.get(Project_.name), "QA%"));
    c.select(emp).where(cb.in(emp.get(Employee_.dept).get(Department_.id)).value(sq));

    TypedQuery<Employee> q = em.createQuery(c);
    return q.getResultList();
  }
  public List<Employee> getEmployeesUsingStringBasedQuery() {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> emp = c.from(Employee.class);
    Subquery<Integer> sq = c.subquery(Integer.class);
    Root<Department> dept = sq.from(Department.class);
    Join<Employee, Project> project = dept.join("employees").join("projects");
    sq.select(dept.<Integer>get("id"))
        .distinct(true)
        .where(cb.like(project.<String>get("name"), "QA%"));
    c.select(emp).where(cb.in(emp.get("dept").get("id")).value(sq));

    TypedQuery<Employee> q = em.createQuery(c);
    return q.getResultList();
  }
  public List<BookPresentationInformation> updateBookPresentationPriceForBooksWithMultipleFormats(
      int numberOfFormats, double amount) {

    EntityManager entityManager = Utilities.getEntityManagerFactory().createEntityManager();
    EntityTransaction entityTransaction = null;
    try {
      entityTransaction = entityManager.getTransaction();
      entityTransaction.begin();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Book> criteriaQueryBook = criteriaBuilder.createQuery(Book.class);
      Root<Book> rootBook = criteriaQueryBook.from(Book.class);
      criteriaQueryBook.select(rootBook);

      CriteriaQuery<BookPresentation> criteriaQueryBookPresentation =
          criteriaBuilder.createQuery(BookPresentation.class);
      Subquery<Long> subqueryBookPresentation = criteriaQueryBookPresentation.subquery(Long.class);
      Root<BookPresentation> rootBookPresentation =
          subqueryBookPresentation.from(BookPresentation.class);
      subqueryBookPresentation.select(
          criteriaBuilder.count(rootBookPresentation.get(BookPresentation_.id)));
      subqueryBookPresentation.where(
          criteriaBuilder.equal(
              rootBookPresentation.get(BookPresentation_.book), rootBook.get(Book_.id)));

      criteriaQueryBook.where(criteriaBuilder.gt(subqueryBookPresentation, numberOfFormats));
      TypedQuery<Book> typedQueryBook = entityManager.createQuery(criteriaQueryBook);
      List<Book> booksList = typedQueryBook.getResultList();

      entityManager
          .createNativeQuery(
              "CREATE TABLE IF NOT EXISTS bookstore.book_tmp ( "
                  + "book_id INT(10) UNSIGNED NOT NULL "
                  + ");",
              Book.class)
          .executeUpdate();

      for (Book book : booksList) {
        entityManager
            .createNativeQuery(
                "INSERT INTO bookstore.book_tmp VALUES (" + book.getId() + ");", Book.class)
            .executeUpdate();
      }

      entityManager
          .createNativeQuery(
              "UPDATE bookstore.book_presentation SET price = price * :amount WHERE book_id IN (SELECT book_id FROM bookstore.book_tmp)",
              Book.class)
          .setParameter("amount", amount)
          .executeUpdate();

      CriteriaQuery<BookPresentationInformation> criteriaQueryBookPresentationInformation =
          criteriaBuilder.createQuery(BookPresentationInformation.class);
      Root<BookPresentation> root =
          criteriaQueryBookPresentationInformation.from(BookPresentation.class);
      Join<BookPresentation, Book> joinBookPresentation = root.join(BookPresentation_.book);
      Join<BookPresentation, Format> joinFormat = root.join(BookPresentation_.format);
      criteriaQueryBookPresentationInformation.multiselect(
          root.get(BookPresentation_.id),
          joinBookPresentation.get(Book_.id),
          joinFormat.get(Format_.id),
          root.get(BookPresentation_.price));
      criteriaQueryBookPresentationInformation.where(
          root.get(BookPresentation_.book).in(booksList));
      TypedQuery<BookPresentationInformation> typedQueryBookPresentationInformation =
          entityManager.createQuery(criteriaQueryBookPresentationInformation);
      List<BookPresentationInformation> bookPresentationInformationList =
          typedQueryBookPresentationInformation.getResultList();

      entityManager.createNativeQuery("DROP TABLE bookstore.book_tmp;", Book.class).executeUpdate();

      entityTransaction.commit();
      return bookPresentationInformationList;
    } catch (Exception exception) {
      System.out.println("An exception has occurred: " + exception.getMessage());
      if (Constants.DEBUG) {
        exception.printStackTrace();
      }
      entityTransaction.rollback();
    } finally {
      entityManager.close();
    }
    return null;
  }
Example #10
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);
 }
Example #11
0
 public Long count(
     Goods.Type type,
     Member favoriteMember,
     Boolean isMarketable,
     Boolean isList,
     Boolean isTop,
     Boolean isOutOfStock,
     Boolean isStockAlert) {
   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 (favoriteMember != null) {
     restrictions =
         criteriaBuilder.and(
             restrictions, criteriaBuilder.equal(root.join("favoriteMembers"), favoriteMember));
   }
   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));
   }
   criteriaQuery.where(restrictions);
   return super.count(criteriaQuery, null);
 }