@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;
}
@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<Product> findByFeatures(final Map<Integer, List<String>> featureValues) {
CriteriaBuilder builder = manager.getCriteriaBuilder();
CriteriaQuery<Product> criteriaQuery = builder.createQuery(Product.class);
Root<Product> product = criteriaQuery.from(Product.class);
Path<ProductFeature> feature = product.join("features");
CriteriaQuery<Product> select = criteriaQuery.select(product);
Predicate featurePredicate = builder.disjunction();
for (final Map.Entry<Integer, List<String>> fValue : featureValues.entrySet()) {
Predicate equalFeatureId = builder.equal(feature.get("featureId"), fValue.getKey());
List<String> values = fValue.getValue();
Predicate equalsValues = builder.disjunction();
for (String value : values) {
Predicate equalFeatureVal = builder.equal(feature.get("value"), value);
equalsValues = builder.or(equalsValues, equalFeatureVal);
}
featurePredicate = builder.or(featurePredicate, builder.and(equalFeatureId, equalsValues));
}
select.where(featurePredicate);
select.groupBy(product.get("id"));
select.having(builder.equal(builder.count(product), featureValues.size()));
TypedQuery<Product> query = manager.createQuery(criteriaQuery);
return query.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 static <T> Long queryEntitiesCount(EntityManager em, Class<T> entity, QueryParameters q) {
if (q == null)
throw new IllegalArgumentException(
"Query parameters can't be null. "
+ "If you don't have any parameters either pass a empty object or "
+ "use the queryEntitiesCount(EntityManager, Class<T>) method.");
log.finest("Querying entity count: '" + entity.getSimpleName() + "' with parameters: " + q);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<T> r = cq.from(entity);
if (!q.getFilters().isEmpty()) {
cq.where(createWhereQuery(cb, r, q));
}
cq.select(cb.count(r));
return em.createQuery(cq).getSingleResult();
}
/** 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);
}
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();
}
@Override
public Long getTotalProducts() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
cq.select(cb.count(cq.from(ProductEntity.class)));
return em.createQuery(cq).getSingleResult();
}
@Override
protected CriteriaQuery<Long> getCountQuery() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<OutgoingDocument> root = cq.from(OutgoingDocument.class);
cq.select(cb.count(root));
cq.where(getPredicates(cb, root));
return cq;
}
@Override
public long countAll() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
cq.select(cb.count(cq.from(clazz)));
return entityManager.createQuery(cq).getSingleResult().intValue();
}
@Override
public Long compter() {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<E> root = cq.from(classeEntite);
cq.select(cb.count(root));
return entityManager.createQuery(cq).getSingleResult();
}
@Override
public Long countPatientFilteredByCdt(ImogJunction criterion, String cdtId) {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> query = builder.createQuery(Long.class);
Root<Patient> root = query.from(Patient.class);
query.select(builder.count(root));
Path<Object> join = root.join("centres", JoinType.LEFT).get("id");
query.where(builder.equal(join, cdtId), DaoUtil.<Patient>toPredicate(criterion, builder, root));
return em.createQuery(query).getSingleResult();
}
/**
* Gets a count of the number of rows that would be returned by the search.
*
* @param criteria
* @param entityManager
* @param type
*/
protected <T> int executeCountQuery(
SearchCriteriaBean criteria, EntityManager entityManager, Class<T> type) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
Root<T> from = countQuery.from(type);
countQuery.select(builder.count(from));
applySearchCriteriaToQuery(criteria, builder, countQuery, from, true);
TypedQuery<Long> query = entityManager.createQuery(countQuery);
return query.getSingleResult().intValue();
}
@Override
public <T extends PersistentObject> long count(Class<T> entityClass) {
long count;
CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = qb.createQuery(Long.class);
Root root = cq.from(entityClass);
cq.select(qb.count(root));
count = em.createQuery(cq).getSingleResult();
return count;
}
/** @author Romain Wurtz <*****@*****.**> */
public int loadRow(
int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, String> filters) {
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
Root<User> from = countQuery.from(User.class);
countQuery.select(criteriaBuilder.count(from));
List<Predicate> predicates = createPredicates(from, criteriaBuilder, filters);
countQuery.where(predicates.toArray(new Predicate[predicates.size()]));
countQuery.where(predicates.toArray(new Predicate[predicates.size()]));
return em.createQuery(countQuery).getSingleResult().intValue();
}
public Long countByFrom(XmppUser from) {
EntityManager entityManager = getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteria = criteriaBuilder.createQuery(Long.class);
Root<ChatPresence> root = criteria.from(ChatPresence.class);
criteria.select(criteriaBuilder.count(root));
criteria.where(criteriaBuilder.equal(root.get(ChatPresence_.from), from));
return entityManager.createQuery(criteria).getSingleResult();
}
@Override
public int size() {
EntityManager em = emf.createEntityManager();
try {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = builder.createQuery(Long.class);
cq.select(builder.count(cq.from(configuration.entityClass())));
return em.createQuery(cq).getSingleResult().intValue();
} finally {
em.close();
}
}
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 boolean hasAdminUser() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<User> root = cq.from(User.class);
cq.select(cb.count(root));
cq.where(cb.isMember(User.Group.Admin, root.get("groups")));
TypedQuery<Long> q = em.createQuery(cq);
q.setParameter("adminRole", User.Group.Admin);
int count = q.getSingleResult().intValue();
return count > 0;
}
public long count(UserEntity owner) {
CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = qb.createQuery(Long.class);
Root<GraphEntity> root = cq.from(GraphEntity.class);
Predicate predicate = qb.equal(root.get(GraphEntity_.owner), owner);
cq.where(predicate);
cq.select(qb.count(root));
return em.createQuery(cq).getSingleResult();
}
/** This test provides an example usage of the HAVING aggregate query function. */
@Test
public void testHaving() {
log.info("*** testHaving() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);
Root<Clerk> c = qdef.from(Clerk.class);
Join<Clerk, Sale> s = c.join("sales", JoinType.LEFT);
// select c, COUNT(s) from Clerk c
// LEFT JOIN c.sales s
// GROUP BY c " +
// HAVING COUNT(S) <= 1
qdef.select(cb.array(c, cb.count(s))).groupBy(c).having(cb.le(cb.count(s), 1));
List<Object[]> results = em.createQuery(qdef).getResultList();
for (Object[] result : results) {
log.info("found=" + Arrays.toString(result));
}
assertEquals("unexpected number of rows", 2, results.size());
}
/** This test provides a demonstration of the COUNT aggregate function */
@Test
public void testCount() {
log.info("*** testCount() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
Root<Sale> s = qdef.from(Sale.class);
// select COUNT(s) from Sale s
qdef.select(cb.count(s));
List<Number> results = executeQuery(qdef);
assertEquals("unexpected number of rows", 1, results.size());
assertEquals("unexpected result", 2, results.get(0).intValue());
}
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;
}
@Override
public Long findByParamsCount(Map<String, Object> params) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
Root<Product> enityRoot = criteriaQuery.from(Product.class);
Predicate criteria = buildCriteria(criteriaBuilder, enityRoot, params);
criteriaQuery.select(criteriaBuilder.count(enityRoot)).where(criteria);
TypedQuery<Long> query = entityManager.createQuery(criteriaQuery);
return query.getSingleResult();
}
@SuppressWarnings({"rawtypes", "unchecked"})
@Override
public int obterQtdRegistros(Map<String, String> filters) throws Exception {
CriteriaBuilder cb = entity.getCriteriaBuilder();
CriteriaQuery q = cb.createQuery(EEATVolume.class);
Root<EEATVolume> c = q.from(EEATVolume.class);
/*
Join<EEATVolume, RegionalProxy> greg = c.join("regionalProxy");
Join<EEATVolume, UnidadeNegocioProxy> uneg = c.join("unidadeNegocioProxy");
Join<EEATVolume, MunicipioProxy> muni = c.join("municipioProxy");
Join<EEATVolume, LocalidadeProxy> loca = c.join("localidadeProxy");
*/
Join<EEATVolume, EEAT> eat = c.join("eeat");
q.select(cb.count(c));
if (filters != null && !filters.isEmpty()) {
Predicate[] predicates = new Predicate[filters.size()];
int i = 0;
for (Map.Entry<String, String> entry : filters.entrySet()) {
String key = entry.getKey();
String val = entry.getValue();
Expression<String> path;
try {
/*
if (key.equals("regionalProxy.nome")) path = greg.get("nome");
else if (key.equals("unidadeNegocioProxy.nome")) path = uneg.get("nome");
else if (key.equals("municipioProxy.nome")) path = muni.get("nome");
else if (key.equals("localidadeProxy.nome")) path = loca.get("nome");
*/
if (key.equals("eeat.descricao")) path = eat.get("descricao");
else path = c.get(key);
if (key.equals("referencia")) {
SimpleDateFormat formataData = new SimpleDateFormat("MM/yyyy");
Date dataConsumo = formataData.parse(val);
predicates[i] = cb.and(cb.equal(path, dataConsumo));
} else {
predicates[i] = cb.and(cb.like(cb.lower(path), "%" + val.toLowerCase() + "%"));
}
} catch (SecurityException ex) {
ex.printStackTrace();
}
i++;
}
q.where(predicates);
}
Query query = entity.createQuery(q);
return ((Long) query.getSingleResult()).intValue();
}
@Override
public long countByContestJidAndUserJidAndProblemJid(
String contestJid, String userJid, String problemJid) {
CriteriaBuilder cb = JPA.em().getCriteriaBuilder();
CriteriaQuery<Long> query = cb.createQuery(Long.class);
Root<M> root = query.from(getModelClass());
query
.select(cb.count(root))
.where(
cb.and(
cb.equal(root.get("contestJid"), contestJid),
cb.equal(root.get("userCreate"), userJid),
cb.equal(root.get("problemJid"), problemJid)));
return JPA.em().createQuery(query).getSingleResult();
}
protected <T> PageRequestId<T> findWithSpecification(
String requestId,
EntityManager entityManager,
Pageable pageable,
Filter<T> filter,
Class<T> type) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
long total;
{
CriteriaQuery<Long> cqTotal = builder.createQuery(Long.class);
Root<T> root = cqTotal.from(type);
if (filter != null) {
Predicate predicate = filter.toPredicate(root, cqTotal, builder);
if (predicate != null) cqTotal.where(predicate);
}
cqTotal.select(builder.count(root));
total = entityManager.createQuery(cqTotal).getSingleResult();
}
CriteriaQuery<T> cq = builder.createQuery(type);
Root<T> root = cq.from(type);
if (filter != null) {
Predicate predicate = filter.toPredicate(root, cq, builder);
if (predicate != null) cq.where(predicate);
}
cq.select(root);
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 = root.get(sx[0]);
for (int i = 1, 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);
}
public void paginate() {
CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();
// Populate this.count
CriteriaQuery<Long> countCriteria = builder.createQuery(Long.class);
Root<Section> root = countCriteria.from(Section.class);
countCriteria = countCriteria.select(builder.count(root)).where(getSearchPredicates(root));
this.count = this.entityManager.createQuery(countCriteria).getSingleResult();
// Populate this.pageItems
CriteriaQuery<Section> criteria = builder.createQuery(Section.class);
root = criteria.from(Section.class);
TypedQuery<Section> query =
this.entityManager.createQuery(criteria.select(root).where(getSearchPredicates(root)));
query.setFirstResult(this.page * getPageSize()).setMaxResults(getPageSize());
this.pageItems = query.getResultList();
}
public void paginate() {
CriteriaBuilder builder = em.getCriteriaBuilder();
// Populate count
CriteriaQuery<Long> countCriteria = builder.createQuery(Long.class);
Root<Author> root = countCriteria.from(Author.class);
countCriteria = countCriteria.select(builder.count(root)).where(getSearchPredicates(root));
count = em.createQuery(countCriteria).getSingleResult();
// Populate pageItems
CriteriaQuery<Author> criteria = builder.createQuery(Author.class);
root = criteria.from(Author.class);
TypedQuery<Author> query =
em.createQuery(criteria.select(root).where(getSearchPredicates(root)));
query.setFirstResult(page * getPageSize()).setMaxResults(getPageSize());
pageItems = query.getResultList();
}
public static CriteriaQuery<Long> buildCountQuery(
String name,
String email,
String phone,
String type,
String region,
String district,
String locality,
String streetToSearch,
EntityManager em) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
Root<Organization> root = countQuery.from(Organization.class);
Predicate predicate =
ArchivalOrganizationsQueryConstructorAdmin.buildPredicate(
name, email, type, phone, region, district, locality, streetToSearch, root, cb);
countQuery.select(cb.count(root));
countQuery.where(predicate);
return countQuery;
}
