/** 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);
}
/** 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;
}
@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);
}
@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);
}
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;
}
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);
}
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);
}
