public List<Company> retrieveCompanys(String name, String category) { EntityManager em = createEntityManager(); List<Company> company = null; try { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Company> cq = cb.createQuery(Company.class); Root<Company> c = cq.from(Company.class); List<Predicate> conditions = new ArrayList<Predicate>(); if (name != null) { Predicate nameCondition = cb.like(c.get(Company_.name), "%" + name + "%"); conditions.add(nameCondition); } if (category != null) { Path<CompanyCategory> companyCategoryPath = c.get(Company_.category); Path<String> companyCategoryNamePath = companyCategoryPath.get(CompanyCategory_.categoryName); Predicate categoryCondition = cb.like(companyCategoryNamePath, category + "%"); conditions.add(categoryCondition); } cq.where(conditions.toArray(new Predicate[0])); TypedQuery<Company> query = em.createQuery(cq); company = query.getResultList(); } finally { em.close(); } return company; }
/** * 废弃 * * @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; }
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; }
/** 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); } }
/** 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()); }
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); }
@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(); }
/** * 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(); }
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 <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); }
@Override public List<Category> getCategoryByParameters(Map<String, Object> parameters) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Category> cq = cb.createQuery(Category.class); Root<Category> r = cq.from(Category.class); Join<RssUser, Category> ru = r.join("rssUserList", JoinType.LEFT); Join<RssUser, Rss> rss = ru.join("rss", JoinType.LEFT); Predicate p = cb.conjunction(); for (Map.Entry<String, Object> param : parameters.entrySet()) { if (param.getKey().equals("rssId")) { p = cb.and(p, cb.equal(rss.get("rssId"), param.getValue())); } else if (param.getKey().equals("guid")) { p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue())); } } p = cb.or(p, cb.equal(r.get("categoryId"), DEFAULT_CATEGORY_ID)); cq.distinct(true); cq.multiselect().where(p); TypedQuery typedQuery = em.createQuery(cq); List<Category> resultList = typedQuery.getResultList(); return resultList; }
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; }
public void testMetamodelOnClauseOverCollection() { EntityManager em = createEntityManager(); Query query = em.createQuery("Select e from Employee e join e.phoneNumbers p on p.areaCode = '613'"); List baseResult = query.getResultList(); CriteriaBuilder qb = em.getCriteriaBuilder(); CriteriaQuery<Employee> cq = qb.createQuery(Employee.class); Metamodel metamodel = em.getMetamodel(); EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class); EntityType<PhoneNumber> entityPhone_ = metamodel.entity(PhoneNumber.class); Root<Employee> root = cq.from(entityEmp_); Join phoneNumber = root.join(entityEmp_.getCollection("phoneNumbers")); phoneNumber.on(qb.equal(phoneNumber.get(entityPhone_.getSingularAttribute("areaCode")), "613")); List testResult = em.createQuery(cq).getResultList(); clearCache(); closeEntityManager(em); if (baseResult.size() != testResult.size()) { fail( "Criteria query using ON clause did not match JPQL results; " + baseResult.size() + " were expected, while criteria query returned " + testResult.size()); } }
@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(); }
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()); } }
/** 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); }
/** This test demonstrates creating an explicit JOIN based on adhoc criteria */ @Test public void testExplicitJoin() { log.info("*** testExplicitJoin ***"); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class); // select c from Sale s, Customer c // where c.id = s.buyerId Root<Sale> s = qdef.from(Sale.class); Root<Customer> c = qdef.from(Customer.class); qdef.select(c).where(cb.equal(c.get("id"), s.get("buyerId"))); int rows = executeQuery(qdef).size(); assertTrue("unexpected number of customers:" + rows, rows > 0); }
/** 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); }
@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(); }
public List<Artikel> findArtikelByIds(List<Long> ids) { if (ids == null || ids.isEmpty()) { return Collections.emptyList(); } /* * SELECT a FROM Artikel a WHERE a.id = ? OR a.id = ? OR ... */ final CriteriaBuilder builder = em.getCriteriaBuilder(); final CriteriaQuery<Artikel> criteriaQuery = builder.createQuery(Artikel.class); final Root<Artikel> a = criteriaQuery.from(Artikel.class); final Path<Long> idPath = a.get("id"); // final Path<String> idPath = a.get(Artikel_.id); // Metamodel-Klassen funktionieren nicht mit // Eclipse Predicate pred = null; if (ids.size() == 1) { // Genau 1 id: kein OR notwendig pred = builder.equal(idPath, ids.get(0)); } else { // Mind. 2x id, durch OR verknuepft final Predicate[] equals = new Predicate[ids.size()]; int i = 0; for (Long id : ids) { equals[i++] = builder.equal(idPath, id); } pred = builder.or(equals); } criteriaQuery.where(pred); return em.createQuery(criteriaQuery).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(); }
/** 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); }
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(); }
/** 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()); }
/** * 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(); }
/** 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); }
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(); }
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(); }