public static CriteriaQuery<Organization> buildSearchQuery(
String name,
String email,
String phone,
String type,
String region,
String district,
String locality,
String streetToSearch,
String sortCriteria,
String sortOrder,
EntityManager em) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Organization> criteriaQuery = cb.createQuery(Organization.class);
Root<Organization> root = criteriaQuery.from(Organization.class);
Predicate predicate =
ArchivalOrganizationsQueryConstructorAdmin.buildPredicate(
name, email, type, phone, region, district, locality, streetToSearch, root, cb);
if ((sortCriteria != null) && (sortOrder != null)) {
criteriaQuery.orderBy(
SortCriteriaOrganization.valueOf(sortCriteria.toUpperCase())
.getSortOrder(root, cb, sortOrder));
} else {
criteriaQuery.orderBy(cb.desc(root.get("id")));
}
criteriaQuery.select(root);
criteriaQuery.where(predicate);
return criteriaQuery;
}
@Override
public Set<String> getTagsSuggest(String url) {
String hashUrl = MD5Util.md5Hex(url);
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Bookmark> root = query.from(Bookmark.class);
// joins
SetJoin<Bookmark, String> tagsJoin = root.joinSet("tags");
query.select(cb.tuple(tagsJoin.alias("tag"), cb.count(tagsJoin).alias("count_tags")));
query.where(cb.equal(root.get("hashUrl"), hashUrl));
query.groupBy(tagsJoin);
query.orderBy(cb.desc(cb.count(tagsJoin)));
// top five tags
List<Tuple> result = entityManager.createQuery(query).setMaxResults(5).getResultList();
Set<String> topTags = new HashSet<String>();
for (Tuple t : result) {
topTags.add((String) t.get("tag"));
}
return topTags;
}
/**
* 废弃
*
* @param user
* @param pageable
* @return
*/
public Page<User> getUsersByCondition(User user, Pageable pageable) {
System.out.println(user);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);
cq.select(root);
// 使用like的时候可以不用判断是否有该参数传来，因为like %% 就等于没有这个限制条件
Predicate condition1 =
cb.like(root.get("username").as(String.class), cb.parameter(String.class, "username"));
Predicate condition2 =
cb.like(root.get("userAlias").as(String.class), cb.parameter(String.class, "userAlias"));
// Predicate
// condition3=cb.equal(root.get("createTime").as(Date.class),cb.parameter(String.class,
// "createTime"));
cq.where(condition1, condition2);
cb.and(condition1, condition2);
TypedQuery<User> query = em.createQuery(cq);
query.setParameter("username", "%" + user.getUsername() + "%");
query.setParameter("userAlias", "%" + user.getUserAlias() + "%");
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List<User> users = query.getResultList();
Page<User> page = new PageImpl<User>(users, pageable, 10);
return page;
}
@Test
public void testQuotientAndMultiply() {
EntityManager em = getOrCreateEntityManager();
em.getTransaction().begin();
CriteriaQuery<Number> criteria = builder.createQuery(Number.class);
criteria.from(Product.class);
criteria.select(
builder.quot(
builder.prod(
builder.literal(BigDecimal.valueOf(10.0)),
builder.literal(BigDecimal.valueOf(5.0))),
BigDecimal.valueOf(2.0)));
Number result = em.createQuery(criteria).getSingleResult();
assertEquals(25.0d, result.doubleValue(), 0.1d);
criteria.select(
builder.prod(
builder.quot(
builder.literal(BigDecimal.valueOf(10.0)),
builder.literal(BigDecimal.valueOf(5.0))),
BigDecimal.valueOf(2.0)));
result = em.createQuery(criteria).getSingleResult();
assertEquals(4.0d, result.doubleValue(), 0.1d);
em.getTransaction().commit();
em.close();
}
/** This test provides an example of testing whether the collection is empty */
@Test
public void testIsEmpty() {
log.info("*** testIsEmpty() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.sales IS EMPTY
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c).where(cb.isEmpty(c.<List<Sale>>get("sales")));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 1, rows);
}
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.sales IS NOT EMPTY
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c).where(cb.isNotEmpty(c.<List<Sale>>get("sales")));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 2, rows);
}
}
/** This test provides a demonstration of testing membership in a collection. */
@Test
public void testMemberOf() {
log.info("*** testMemberOf() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c where c.firstName = 'Manny'
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c).where(cb.equal(c.get("firstName"), "Manny"));
Clerk clerk = em.createQuery(qdef).getSingleResult();
// find all sales that involve this clerk
CriteriaQuery<Sale> qdef2 = cb.createQuery(Sale.class);
// select s from Sale s
// where :clerk MEMBER OF s.clerks",
Root<Sale> s = qdef2.from(Sale.class);
qdef2.select(s).where(cb.isMember(clerk, s.<List<Clerk>>get("clerks")));
List<Sale> sales = em.createQuery(qdef2).getResultList();
for (Sale result : sales) {
log.info("found=" + result);
}
assertEquals("unexpected number of rows", 2, sales.size());
}
/** This test demonstrates the use of DISTINCT to limit the results to only unique values */
@Test
public void testDISTINCT() {
log.info("*** testDISTINCT() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<String> qdef = cb.createQuery(String.class);
// select DISTINCT c.lastName from Customer c
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c.<String>get("lastName")).distinct(true);
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 3, rows);
}
{
CriteriaQuery<String> qdef = cb.createQuery(String.class);
// select DISTINCT c.firstName from Customer c
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c.<String>get("firstName")).distinct(true);
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows for DISTINCT", 2, rows);
}
}
protected <T, T1, T2> PageRequestId<T> findWithSpecification(
String requestId,
EntityManager entityManager,
Pageable pageable,
Filter3<T, T1, T2> specification,
Class<T> type,
Class<T1> type1,
Class<T2> type2) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
long total;
{
CriteriaQuery<Long> cqTotal = builder.createQuery(Long.class);
Root<T1> root1 = cqTotal.from(type1);
Root<T2> root2 = cqTotal.from(type2);
Predicate predicate = specification.toPredicate(root1, root2, cqTotal, builder);
if (predicate != null) cqTotal.where(predicate);
cqTotal.select(builder.count(root1));
total = entityManager.createQuery(cqTotal).getSingleResult();
}
CriteriaQuery<T> cq = builder.createQuery(type);
Root<T1> root1 = cq.from(type1);
Root<T2> root2 = cq.from(type2);
Predicate predicate = specification.toPredicate(root1, root2, cq, builder);
if (predicate != null) cq.where(predicate);
cq.select(specification.getSelection(root1, root2, cq, builder));
if (pageable.getSort() != null) {
List<Order> orders = new LinkedList<Order>();
for (Iterator<org.springframework.data.domain.Sort.Order> itr = pageable.getSort().iterator();
itr.hasNext(); ) {
org.springframework.data.domain.Sort.Order order = itr.next();
String sx[] = order.getProperty().split("\\.");
Path<Object> p;
if (sx[0].equals("t1")) {
p = root1.get(sx[1]);
} else if (sx[0].equals("t2")) {
p = root2.get(sx[1]);
} else {
throw new RuntimeException("Invalid order " + U.dump(order));
}
for (int i = 2, il = sx.length; i < il; i++) p = p.get(sx[i]);
if (order.isAscending()) {
orders.add(builder.asc(p));
} else {
orders.add(builder.desc(p));
}
}
cq.orderBy(orders);
}
TypedQuery<T> qry = entityManager.createQuery(cq);
qry.setFirstResult(pageable.getOffset()).setMaxResults(pageable.getPageSize());
return new PageRequestIdImpl<T>(qry.getResultList(), pageable, total, requestId);
}
private TypedQuery<Long> getCountQuery(Specification<M> spec) {
CriteriaBuilder builder = this.em.getCriteriaBuilder();
CriteriaQuery query = builder.createQuery(Long.class);
Root root = applySpecificationToCriteria(spec, query);
if (query.isDistinct()) query.select(builder.countDistinct(root));
else {
query.select(builder.count(root));
}
TypedQuery q = this.em.createQuery(query);
this.repositoryHelper.applyEnableQueryCache(q);
return q;
}
/**
* Search by criteria.
*
* @param criteria the criteria
* @return the list< t>
*/
protected List<T> searchByCriteria(E criteria) {
CriteriaQuery<T> query = getCriteriaBuilder().createQuery(getEntityClass());
Root<T> from = query.from(getEntityClass());
query.select(from);
List<Predicate> predicateList = buildConditions(from, criteria);
if (predicateList != null) {
Predicate[] predicates = new Predicate[predicateList.size()];
predicateList.toArray(predicates);
query.where(predicates);
}
if (criteria.getGroupBy() != null) {
query.groupBy(from.get(criteria.getGroupBy()));
}
if (criteria.getOrderField() != null) {
if (criteria.getOrderType() != null && !"".equals(criteria.getOrderType())) {
StringTokenizer outerTokenizer = new StringTokenizer(criteria.getOrderField(), ",");
StringTokenizer typeTokenizer = new StringTokenizer(criteria.getOrderType(), ",");
List<Order> orders = new ArrayList<Order>();
while (outerTokenizer.hasMoreElements()) {
String field = (String) outerTokenizer.nextElement();
String type = (String) typeTokenizer.nextElement();
Path ex = null;
if (field.indexOf(".") > 0) {
StringTokenizer tokenizer = new StringTokenizer(field, ".");
while (tokenizer.hasMoreElements()) {
String key = (String) tokenizer.nextElement();
if (ex == null) {
ex = from.get(key);
} else {
ex = ex.get(key);
}
}
} else {
ex = from.get(field);
}
if ("desc".equals(type)) {
orders.add(getCriteriaBuilder().desc(ex));
} else if ("asc".equals(type)) {
orders.add(getCriteriaBuilder().asc(ex));
}
}
if (orders.size() > 0) {
query.orderBy(orders);
}
} else {
query.orderBy(getCriteriaBuilder().desc(from.get(criteria.getOrderField())));
}
}
TypedQuery<T> typedQuery = getEntityManager().createQuery(query);
if (criteria.getStartIndex() != null) {
typedQuery.setFirstResult(criteria.getStartIndex());
typedQuery.setMaxResults(criteria.getPageSize());
} else {
typedQuery.setFirstResult(0);
typedQuery.setMaxResults(rowLimit);
}
System.out.println("dfjdfjdkfdkfjd" + typedQuery.toString());
return typedQuery.getResultList();
}
@Override
public List<Point> getList(EntityManager em) {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
cq.select(cq.from(Point.class));
Query q = em.createQuery(cq);
return q.getResultList();
}
@Override
public <T extends StoredObject> List<T> getStoredObjects(final Class<T> tClass) {
List<T> list = Collections.emptyList();
emLock.lock();
try {
final Future<List<T>> future =
executorService.submit(
() -> {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<T> cq = cb.createQuery(tClass);
final Root<T> root = cq.from(tClass);
cq.select(root);
final TypedQuery<T> q = em.createQuery(cq);
return new ArrayList<>(q.getResultList());
});
list = future.get();
} catch (InterruptedException | ExecutionException e) {
logger.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
emLock.unlock();
}
return stripMarkedForRemoval(list);
}
/** This test method demonstrates using date functions. */
@Test
public void testDates() {
log.info("*** testDates() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);
Root<Sale> s = qdef.from(Sale.class);
qdef.select(s);
// select s from Sale s
// where s.date < CURRENT_DATE
qdef.where(cb.lessThan(s.<Date>get("date"), cb.currentDate()));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 2, rows);
// select s from Sale s
// where s.date = CURRENT_DATE
qdef.where(cb.equal(s.<Date>get("date"), cb.currentDate()));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 0, rows);
// no bulk query capability in Criteria API
rows = em.createQuery("update Sale s " + "set s.date = CURRENT_DATE").executeUpdate();
assertEquals("unexpected number of rows", 2, rows);
em.getTransaction().commit();
em.clear(); // remove stale objects in cache
// select s from Sale s
// where s.date = CURRENT_DATE
qdef.where(cb.equal(s.<Date>get("date"), cb.currentDate()));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 2, 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());
}
public List<Equipment> findAllOrderedByProductionDate() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Equipment> criteria = cb.createQuery(Equipment.class);
Root<Equipment> equipment = criteria.from(Equipment.class);
criteria.select(equipment).orderBy(cb.desc(equipment.get("productionDate")));
return em.createQuery(criteria).getResultList();
}
public int count(List<Filtro> filtros) {
javax.persistence.criteria.CriteriaQuery cq =
getEntityManager().getCriteriaBuilder().createQuery();
javax.persistence.criteria.Root<BaseEntity> rt = cq.from(entityClass);
CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
if (filtros != null) {
ArrayList<Predicate> predicatesList = new ArrayList<>();
for (Filtro filtro : filtros) {
switch (filtro.getTipoFiltro()) {
case Equals:
predicatesList.add(builder.equal(rt.get(filtro.getDescricao()), filtro.getValor()));
break;
case LessEqual:
Path<Date> date = rt.get(filtro.getDescricao());
predicatesList.add(builder.lessThanOrEqualTo(date, ((Date) filtro.getValor())));
break;
case GreaterEqual:
Path<Date> date2 = rt.get(filtro.getDescricao());
predicatesList.add(builder.greaterThanOrEqualTo(date2, ((Date) filtro.getValor())));
break;
default:
predicatesList.add(builder.equal(rt.get(filtro.getDescricao()), filtro.getValor()));
break;
}
}
cq.where(predicatesList.<Predicate>toArray(new Predicate[predicatesList.size()]));
}
cq.select(builder.count(rt));
javax.persistence.Query q = getEntityManager().createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
}
public int count() {
javax.persistence.criteria.CriteriaQuery<Long> cq =
getEntityManager().getCriteriaBuilder().createQuery(Long.class);
javax.persistence.criteria.Root<E> rt = cq.from(entityClass);
cq.select(getEntityManager().getCriteriaBuilder().count(rt));
return getEntityManager().createQuery(cq).getSingleResult().intValue();
}
/** Load business rules */
private void loadList() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<BusinessRule> c = cb.createQuery(BusinessRule.class);
Root<BusinessRule> obj = c.from(BusinessRule.class);
c.select(obj).where(cb.equal(obj.get("project"), this.project)).orderBy(cb.asc(obj.get("id")));
this.businessRules = em.createQuery(c).getResultList();
}
private List<Vote> findVoteEntitiesByPollAltIds(
Long pollId, Long altId, boolean all, int maxResults, int firstResult) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Vote> q = cb.createQuery(Vote.class);
Root<Vote> vote = q.from(Vote.class);
ParameterExpression<Long> pid = cb.parameter(Long.class);
ParameterExpression<Long> aid = cb.parameter(Long.class);
q.select(vote)
.where(
cb.and(
cb.equal(vote.get(Vote_.alternative).get(Alternative_.poll).get(Poll_.id), pid),
cb.equal(vote.get(Vote_.alternative).get(Alternative_.id), aid)));
TypedQuery<Vote> query = em.createQuery(q);
query.setParameter(pid, pollId).setParameter(aid, altId);
if (!all) {
query.setMaxResults(maxResults);
query.setFirstResult(firstResult);
}
List<Vote> results = query.getResultList();
System.out.println(">>>>>>>>>>>>>>>>>>>> Votes: " + results);
return results;
}
/** This test provides an example collection path using an LEFT OUTER JOIN */
@Test
public void testOuterJoin() {
log.info("*** testOuterJoin() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);
// select c.id, c.firstName, sale.amount
// from Clerk c
// LEFT JOIN c.sales sale
Root<Clerk> c = qdef.from(Clerk.class);
Join<Clerk, Sale> sale = c.join("sales", JoinType.LEFT);
qdef.select(cb.array(c.get("id"), c.get("firstName"), sale.get("amount")));
TypedQuery<Object[]> query = em.createQuery(qdef);
List<Object[]> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for (Object[] result : results) {
assertEquals("unexpected result length", 3, result.length);
Long id = (Long) result[0];
String name = (String) result[1];
BigDecimal amount = (BigDecimal) result[2];
log.info("clerk.id=" + id + ", clerk.firstName=" + name + ", amount=" + amount);
}
}
@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);
}
public List<MyClass138> findByCriteria(MyClass138Criteria myClass138Criteria) {
javax.persistence.criteria.CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery<MyClass138> c = cb.createQuery(MyClass138.class);
javax.persistence.criteria.Root<MyClass138> emp = c.from(MyClass138.class);
c.select(emp);
List<javax.persistence.criteria.Predicate> criteria =
new java.util.ArrayList<javax.persistence.criteria.Predicate>();
if (myClass138Criteria.getId() != null) {
javax.persistence.criteria.Expression<Long> p = emp.get("id");
javax.persistence.criteria.Expression<Long> val = cb.parameter(Long.class, "id");
criteria.add(cb.equal(p, val));
}
if (criteria.size() == 1) {
c.where(criteria.get(0));
} else {
c.where(cb.and(criteria.toArray(new javax.persistence.criteria.Predicate[0])));
}
javax.persistence.TypedQuery<MyClass138> q = this.entityManager.createQuery(c);
if (myClass138Criteria.getId() != null) {
q.setParameter("id", myClass138Criteria.getId());
}
return new java.util.ArrayList<MyClass138>(q.getResultList());
}
@Override
public Map<String, Long> countTagsByUser(String userName) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Bookmark> root = query.from(Bookmark.class);
// joins
SetJoin<Bookmark, String> tagsJoin = root.joinSet("tags");
Join<Bookmark, User> userJoin = root.join("user");
query.select(cb.tuple(tagsJoin.alias("tag"), cb.count(tagsJoin).alias("count_tags")));
query.where(cb.equal(userJoin.get("userName"), userName));
query.groupBy(tagsJoin);
query.orderBy(cb.desc(cb.count(tagsJoin)));
List<Tuple> result = entityManager.createQuery(query).setMaxResults(30).getResultList();
Map<String, Long> tagRanking = new TreeMap<String, Long>();
for (Tuple t : result) {
tagRanking.put((String) t.get("tag"), (Long) t.get("count_tags"));
}
return tagRanking;
}
@Override
public final List<T> findListByProperty(
final Object[] values, final SingularAttribute<T, ? extends Object>... properties) {
final CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(persistentClass);
final Root<T> root = criteriaQuery.from(persistentClass);
criteriaQuery.select(root);
final Object value = values[0];
final SingularAttribute<T, ? extends Object> property = properties[0];
Predicate condition;
condition =
QueryHelper.equalsIgnoreCaseIfStringPredicate(criteriaBuilder, root, value, property);
if (values.length > 1) {
for (int i = 1; i < properties.length; i++) {
final SingularAttribute<T, ? extends Object> property2 = properties[i];
final Object value2 = values[i];
final Predicate condition2 =
QueryHelper.equalsIgnoreCaseIfStringPredicate(criteriaBuilder, root, value2, property2);
condition = criteriaBuilder.and(condition, condition2);
}
}
criteriaQuery.where(condition);
final TypedQuery<T> typedQuery = getEntityManager().createQuery(criteriaQuery);
addCacheHints(typedQuery, "findListByProperty");
return typedQuery.getResultList();
}
@Override
public List<T> getList() {
CriteriaQuery<T> query = em.getCriteriaBuilder().createQuery(getEntityClass());
Root<T> root = query.from(getEntityClass());
query.select(root);
return em.createQuery(query).getResultList();
}
/** 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);
}
/**
* Service method to return all StockCategory from database
*
* @return
*/
public List<StockCategory> findAllStockCategories() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<StockCategory> criteria = criteriaBuilder.createQuery(StockCategory.class);
Root<StockCategory> stockCategory = criteria.from(StockCategory.class);
criteria.select(stockCategory);
return entityManager.createQuery(criteria).getResultList();
}
/**
* Count the objects in the database
*
* @return Number of objects
*/
public int count() {
CriteriaBuilder b = getBuilder();
CriteriaQuery<Long> criteria = b.createQuery(Long.class);
criteria.select(b.count(criteria.from(domainClass)));
return em.createQuery(criteria).getSingleResult().intValue();
}
public int getVoteCount() {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
Root<Vote> rt = cq.from(Vote.class);
cq.select(em.getCriteriaBuilder().count(rt));
Query q = em.createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
}
/** This test provides a demonstration of using a math formual within the where clause. */
@Test
public void testFormulas() {
log.info("*** testFormulas() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
// select count(s) from Sale s
// where (s.amount * :tax) > :amount"
Root<Sale> s = qdef.from(Sale.class);
qdef.select(cb.count(s))
.where(
cb.greaterThan(
cb.prod(s.<BigDecimal>get("amount"), cb.parameter(BigDecimal.class, "tax")),
new BigDecimal(10.0)));
TypedQuery<Number> query = em.createQuery(qdef);
// keep raising taxes until somebody pays $10.00 in tax
double tax = 0.05;
for (;
query.setParameter("tax", new BigDecimal(tax)).getSingleResult().intValue() == 0;
tax += 0.01) {
log.debug("tax=" + NumberFormat.getPercentInstance().format(tax));
}
log.info("raise taxes to: " + NumberFormat.getPercentInstance().format(tax));
assertEquals("unexpected level for tax:" + tax, 0.07, tax, .01);
}