@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;
}
public List<Programacion> getProgramacionSeguimiento(
boolean distinct, EstadoProgramacion estadoProgramacion) {
System.out.println(
"00---------------------------------------------------------------------------00");
CriteriaQuery<Programacion> cq =
getEntityManager().getCriteriaBuilder().createQuery(Programacion.class);
Root<Programacion> registro = cq.from(Programacion.class);
Join<Programacion, AtencionProgramacion> join =
registro.join(Programacion_.atencionProgramacionList);
if (distinct) cq.distinct(true);
cq.where(
getEntityManager()
.getCriteriaBuilder()
.and(
getEntityManager()
.getCriteriaBuilder()
.equal(registro.get(Programacion_.flgActivo), BigInteger.ONE),
getEntityManager()
.getCriteriaBuilder()
.equal(
join.get(AtencionProgramacion_.estadoProgramacion), estadoProgramacion)));
javax.persistence.Query q = getEntityManager().createQuery(cq);
return q.getResultList();
}
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());
}
}
/** 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);
}
}
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 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());
}
@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 Collection<ProductListItem> findByParams(Map<String, Object> params) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ProductListItem> criteriaQuery =
criteriaBuilder.createQuery(ProductListItem.class);
Root<Product> enityRoot = criteriaQuery.from(Product.class);
Predicate criteria = buildCriteria(criteriaBuilder, enityRoot, params);
Join<Object, Object> categoryJoin = enityRoot.join("category", JoinType.LEFT);
criteriaQuery
.multiselect(
enityRoot.<Long>get("id"),
enityRoot.<String>get("name"),
categoryJoin.<String>get("name"),
enityRoot.<String>get("producer"),
enityRoot.<BigDecimal>get("price"),
enityRoot.<Calendar>get("createDate"))
.where(criteria);
String orderDir = (String) params.get("order_dir");
if (orderDir == null) {
orderDir = "desc";
}
String orderCol = (String) params.get("order_col");
if (orderCol == null) {
orderCol = "id";
}
Path<Object> col = null;
if (orderCol.equals("category_name")) {
col = categoryJoin.get("name");
} else {
col = enityRoot.get(orderCol);
}
if (orderDir.equals("asc")) {
criteriaQuery.orderBy(criteriaBuilder.asc(col));
} else if (orderDir.equals("desc")) {
criteriaQuery.orderBy(criteriaBuilder.desc(col));
}
TypedQuery<ProductListItem> query = entityManager.createQuery(criteriaQuery);
Integer pageSize = (Integer) params.get("page_size");
Integer pageIndex = (Integer) params.get("page_index");
if (pageSize != null && pageIndex != null) {
query.setMaxResults(pageSize);
query.setFirstResult(pageIndex * pageSize);
}
return query.getResultList();
}
/**
* Returns an existing join for the given attribute if one already exists or creates a new one if
* not.
*
* @param from the {@link From} to get the current joins from.
* @param attribute the {@link Attribute} to look for in the current joins.
* @return will never be {@literal null}.
*/
private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute) {
for (Join<?, ?> join : from.getJoins()) {
boolean sameName = join.getAttribute().getName().equals(attribute);
if (sameName && join.getJoinType().equals(JoinType.LEFT)) {
return join;
}
}
return from.join(attribute, JoinType.LEFT);
}
public List<Agent> getAllAgentProfilCreateur() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Agent> cq = cb.createQuery(Agent.class);
Root<Agent> agentRoot = cq.from(Agent.class);
Join<Agent, Utilisateur> utilisateurJoin = agentRoot.join("utilisateur");
Join<Profil, Agent> profilJoin = utilisateurJoin.join("profil");
Predicate pAll = cb.equal(profilJoin.get("droitCreationFiche"), true);
pAll = cb.and(pAll, cb.equal(utilisateurJoin.get("actif"), true));
cq.where(pAll);
cq.orderBy(cb.asc(agentRoot.get("nom")));
TypedQuery<Agent> agentsQuery = em.createQuery(cq.distinct(true));
return agentsQuery.getResultList();
}
@Test
@Transactional
public void testCountCriteria() {
EntityManager em = bookDao.getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Book> criteria = cb.createQuery(Book.class);
Root<Book> root = criteria.from(Book.class);
Join<Book, Author> join = root.join("author");
criteria.where(join.isNotNull());
CriteriaQuery<Long> countCriteria = JpaUtils.countCriteria(em, criteria);
Long result = em.createQuery(countCriteria).getSingleResult();
log.debug("Count: " + result);
}
/** This test provides an example collection path using an INNER JOIN */
@Test
public void testCollectionPathExpressionsInnerJoin() {
log.info("*** testCollectionPathExpressionsInnerJoin ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Date> qdef = cb.createQuery(Date.class);
// select sale.date from Clerk c JOIN c.sales sale
Root<Clerk> c = qdef.from(Clerk.class);
Join<Clerk, Sale> sale = c.join("sales", JoinType.INNER);
qdef.select(sale.<Date>get("date"));
int rows = executeQuery(qdef).size();
assertTrue("unexpected number of sales:" + rows, rows > 0);
}
/**
* Method findByUniqueKey.
*
* @param idTradingday Integer
* @param idContract Integer
* @param startPeriod Date
* @param endPeriod Date
* @return Candle
*/
public Candle findByUniqueKey(
Integer idTradingday, Integer idContract, Date startPeriod, Date endPeriod, Integer barSize) {
try {
EntityManager entityManager = EntityManagerHelper.getEntityManager();
entityManager.getTransaction().begin();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Candle> query = builder.createQuery(Candle.class);
Root<Candle> from = query.from(Candle.class);
query.select(from);
List<Predicate> predicates = new ArrayList<Predicate>();
if (null != idTradingday) {
Join<Candle, Tradingday> tradingday = from.join("tradingday");
Predicate predicate = builder.equal(tradingday.get("idTradingDay"), idTradingday);
predicates.add(predicate);
}
if (null != idContract) {
Join<Candle, Contract> contract = from.join("contract");
Predicate predicate = builder.equal(contract.get("idContract"), idContract);
predicates.add(predicate);
}
if (null != startPeriod) {
Predicate predicate = builder.equal(from.get("startPeriod"), startPeriod);
predicates.add(predicate);
}
if (null != endPeriod) {
Predicate predicate = builder.equal(from.get("endPeriod"), endPeriod);
predicates.add(predicate);
}
if (null != barSize) {
Predicate predicate = builder.equal(from.get("barSize"), barSize);
predicates.add(predicate);
}
query.where(predicates.toArray(new Predicate[] {}));
TypedQuery<Candle> typedQuery = entityManager.createQuery(query);
List<Candle> items = typedQuery.getResultList();
entityManager.getTransaction().commit();
if (items.size() > 0) {
return items.get(0);
}
return null;
} catch (RuntimeException re) {
throw re;
} finally {
EntityManagerHelper.close();
}
}
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();
}
@SuppressWarnings({"unchecked"})
private void renderJoins(
StringBuilder jpaqlQuery, RenderingContext renderingContext, Collection<Join<?, ?>> joins) {
if (joins == null) {
return;
}
for (Join join : joins) {
((FromImplementor) join).prepareAlias(renderingContext);
jpaqlQuery
.append(renderJoinType(join.getJoinType()))
.append(((FromImplementor) join).renderTableExpression(renderingContext));
renderJoins(jpaqlQuery, renderingContext, join.getJoins());
renderFetches(jpaqlQuery, renderingContext, join.getFetches());
}
}
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();
}
@SuppressWarnings("unchecked")
public Collection<Transacao> listarTransacoesPorConta(Long identificador) {
CriteriaBuilder criteria = this.manager.getCriteriaBuilder();
CriteriaQuery<Transacao> cq = criteria.createQuery(Transacao.class);
Root<Transacao> from = cq.from(Transacao.class);
Join<Transacao, Conta> contaDebito = from.join("contaDebito", JoinType.LEFT);
Join<Transacao, Conta> contaCredito = from.join("contaCredito", JoinType.LEFT);
cq.where(
criteria.or(
criteria.equal(contaDebito.get("identificador"), identificador),
criteria.equal(contaCredito.get("identificador"), identificador)));
return manager.createQuery(cq).getResultList();
}
@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();
}
/**
* Method findCandlesByContractDateRangeBarSize.
*
* @param idContract Integer
* @param startDate Date
* @param endDate Date
* @param barSize Integer
* @return List<Candle>
*/
public List<Candle> findCandlesByContractDateRangeBarSize(
Integer idContract, Date startOpenDate, Date endOpenDate, Integer barSize) {
try {
EntityManager entityManager = EntityManagerHelper.getEntityManager();
entityManager.getTransaction().begin();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Candle> query = builder.createQuery(Candle.class);
Root<Candle> from = query.from(Candle.class);
query.select(from);
query.orderBy(builder.asc(from.get("startPeriod")));
List<Predicate> predicates = new ArrayList<Predicate>();
if (null != idContract) {
Join<Candle, Contract> contract = from.join("contract");
Predicate predicateContract = builder.equal(contract.get("idContract"), idContract);
predicates.add(predicateContract);
}
if (null != startOpenDate) {
Join<Candle, Tradingday> tradingdayOpenDate = from.join("tradingday");
Predicate predicateStartDate =
builder.greaterThanOrEqualTo(
tradingdayOpenDate.get("open").as(Date.class), startOpenDate);
predicates.add(predicateStartDate);
Predicate predicateEndDate =
builder.lessThanOrEqualTo(tradingdayOpenDate.get("open").as(Date.class), endOpenDate);
predicates.add(predicateEndDate);
}
if (null != barSize) {
Predicate predicate = builder.equal(from.get("barSize"), barSize);
predicates.add(predicate);
}
query.where(predicates.toArray(new Predicate[] {}));
TypedQuery<Candle> typedQuery = entityManager.createQuery(query);
List<Candle> items = typedQuery.getResultList();
entityManager.getTransaction().commit();
return items;
} catch (RuntimeException re) {
EntityManagerHelper.rollback();
throw re;
} finally {
EntityManagerHelper.close();
}
}
@SuppressWarnings("unchecked")
private <X, T, V extends T, K extends JoinImplementor> K treat(
Join<X, T> join, Class<V> type, BiFunction<Join<X, T>, Class<V>, K> f) {
final Set<Join<X, ?>> joins = join.getParent().getJoins();
final K treatAs = f.apply(join, type);
joins.add(treatAs);
return treatAs;
}
@SuppressWarnings("unchecked")
public static Predicate[] deviceListPredicates(
CriteriaBuilder cb,
Root<Device> from,
Optional<String> name,
Optional<String> namePattern,
Optional<String> status,
Optional<Long> networkId,
Optional<String> networkName,
Optional<Long> deviceClassId,
Optional<String> deviceClassName,
Optional<String> deviceClassVersion,
Optional<HivePrincipal> principal) {
final List<Predicate> predicates = new LinkedList<>();
name.ifPresent(n -> predicates.add(cb.equal(from.<String>get("name"), n)));
namePattern.ifPresent(np -> predicates.add(cb.like(from.<String>get("name"), np)));
status.ifPresent(s -> predicates.add(cb.equal(from.<String>get("status"), s)));
final Join<Device, Network> networkJoin = (Join) from.fetch("network", JoinType.LEFT);
networkId.ifPresent(nId -> predicates.add(cb.equal(networkJoin.<Long>get("id"), nId)));
networkName.ifPresent(
nName -> predicates.add(cb.equal(networkJoin.<String>get("name"), nName)));
final Join<Device, DeviceClass> dcJoin = (Join) from.fetch("deviceClass", JoinType.LEFT);
deviceClassId.ifPresent(dcId -> predicates.add(cb.equal(dcJoin.<Long>get("id"), dcId)));
deviceClassName.ifPresent(
dcName -> predicates.add(cb.equal(dcJoin.<String>get("name"), dcName)));
deviceClassVersion.ifPresent(
dcVersion -> predicates.add(cb.equal(dcJoin.<String>get("version"), dcVersion)));
predicates.addAll(deviceSpecificPrincipalPredicates(cb, from, principal));
return predicates.toArray(new Predicate[predicates.size()]);
}
/**
* Method findByContractAndDateRange.
*
* @param idContract Integer
* @param startPeriod Date
* @param endPeriod Date
* @return List<Candle>
*/
public List<Candle> findByContractAndDateRange(
Integer idContract, Date startPeriod, Date endPeriod, Integer barSize) {
try {
EntityManager entityManager = EntityManagerHelper.getEntityManager();
entityManager.getTransaction().begin();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Candle> query = builder.createQuery(Candle.class);
Root<Candle> from = query.from(Candle.class);
query.select(from);
List<Predicate> predicates = new ArrayList<Predicate>();
if (null != idContract) {
Join<Candle, Contract> contract = from.join("contract");
Predicate predicate = builder.equal(contract.get("idContract"), idContract);
predicates.add(predicate);
}
if (null != startPeriod) {
Expression<Date> start = from.get("startPeriod");
Predicate predicate = builder.greaterThanOrEqualTo(start, startPeriod);
predicates.add(predicate);
}
if (null != endPeriod) {
Expression<Date> end = from.get("endPeriod");
Predicate predicate = builder.lessThanOrEqualTo(end, endPeriod);
predicates.add(predicate);
}
if (null != barSize) {
Predicate predicate = builder.equal(from.get("barSize"), barSize);
predicates.add(predicate);
}
query.where(predicates.toArray(new Predicate[] {}));
TypedQuery<Candle> typedQuery = entityManager.createQuery(query);
List<Candle> items = typedQuery.getResultList();
entityManager.getTransaction().commit();
return items;
} catch (RuntimeException re) {
EntityManagerHelper.rollback();
throw re;
} finally {
EntityManagerHelper.close();
}
}
public List<Historico> buscaTodosHistoricos(
String keyArduino, int initResult, int maxResult, String atributo, int ordem) {
CriteriaBuilder builder = entityManager.getCriteriaBuilder(); // builder
CriteriaQuery<Historico> query = builder.createQuery(Historico.class); // retorno da query
Root<Historico> historico = query.from(Historico.class); // classe base onde será feito o select
/** Alias */
Join<Historico, Dispositivo> dispo = historico.join("dispositivo");
Join<Dispositivo, Casa> casa = dispo.join("casa");
Join<Casa, Arduino> ard = casa.join("arduino");
Order order = null;
if (ordem > 0) {
order = builder.asc(historico.get(atributo)); // ordem crescente
} else {
order = builder.desc(historico.get(atributo)); // ordem decrescente
}
TypedQuery<Historico> typedQuery =
entityManager.createQuery(
query
.select(historico)
.where(builder.equal(ard.get("key"), keyArduino))
.orderBy(order));
typedQuery.setFirstResult(initResult);
typedQuery.setMaxResults(maxResult);
return typedQuery.getResultList();
}
/**
* Method findCandleCount.
*
* @param idTradingday Integer
* @param idContract Integer
* @return Long
*/
public Long findCandleCount(Integer idTradingday, Integer idContract) {
try {
EntityManager entityManager = EntityManagerHelper.getEntityManager();
entityManager.getTransaction().begin();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object> query = builder.createQuery();
Root<Candle> from = query.from(Candle.class);
Expression<Long> id = from.get("idCandle");
Expression<Long> minExpression = builder.count(id);
List<Predicate> predicates = new ArrayList<Predicate>();
if (null != idTradingday) {
Join<Candle, Tradingday> tradingday = from.join("tradingday");
Predicate predicate = builder.equal(tradingday.get("idTradingDay"), idTradingday);
predicates.add(predicate);
}
if (null != idContract) {
Join<Candle, Contract> contract = from.join("contract");
Predicate predicate = builder.equal(contract.get("idContract"), idContract);
predicates.add(predicate);
}
query.where(predicates.toArray(new Predicate[] {}));
CriteriaQuery<Object> select = query.select(minExpression);
TypedQuery<Object> typedQuery = entityManager.createQuery(select);
Object item = typedQuery.getSingleResult();
entityManager.getTransaction().commit();
if (null == item) item = new Long(0);
return (Long) item;
} catch (RuntimeException re) {
EntityManagerHelper.rollback();
throw re;
} finally {
EntityManagerHelper.close();
}
}
public static Predicate[] accessKeyListPredicates(
CriteriaBuilder cb,
Root<AccessKey> from,
Long userId,
Optional<String> labelOpt,
Optional<String> labelPatten,
Optional<Integer> typeOpt) {
List<Predicate> predicates = new LinkedList<>();
Join user = (Join) from.fetch("user", JoinType.LEFT);
predicates.add(cb.equal(user.get("id"), userId));
if (labelPatten.isPresent()) {
labelPatten.ifPresent(pattern -> predicates.add(cb.like(from.get("label"), pattern)));
} else {
labelOpt.ifPresent(label -> predicates.add(cb.equal(from.get("label"), label)));
}
typeOpt.ifPresent(type -> predicates.add(cb.equal(from.get("type"), type)));
return predicates.toArray(new Predicate[predicates.size()]);
}
public void consultarPessoaComCriteria(
String pCPF, String pEndereco, String pNome, String pDtNascimento) {
EntityManager manager = UtilJPA.getEntityManager();
CriteriaBuilder builderCriteria = manager.getCriteriaBuilder();
CriteriaQuery<Pessoa> queryCriteria = builderCriteria.createQuery(Pessoa.class);
Root<Pessoa> pessoa = queryCriteria.from(Pessoa.class);
List<Predicate> condicoesWhere = new ArrayList<>();
if (BibliotecaFuncoes.isStringValida(pCPF)) {
Path<String> atributoCPF = pessoa.get("cpf");
queryCriteria.where(builderCriteria.equal(atributoCPF, pCPF));
}
if (BibliotecaFuncoes.isStringValida(pEndereco)) {
Path<String> atributoEndereco = pessoa.get("endereco");
queryCriteria.where(builderCriteria.equal(atributoEndereco, pEndereco));
}
if (BibliotecaFuncoes.isStringValida(pNome)) {
Join<Pessoa, DadosPessoais> dadosPessoa = pessoa.join("dadosPessoais");
queryCriteria.where(
builderCriteria.and(builderCriteria.equal(dadosPessoa.get("nome"), pNome)));
}
Predicate[] arrayCondicionais = condicoesWhere.toArray(new Predicate[condicoesWhere.size()]);
Predicate todosOsCondicionais = builderCriteria.and(arrayCondicionais);
queryCriteria.where(todosOsCondicionais);
TypedQuery<Pessoa> queryPessoa = manager.createQuery(queryCriteria);
Collection<Pessoa> pessoas = queryPessoa.getResultList();
manager.close();
}
@SuppressWarnings("unchecked")
protected SetJoin<UserEntity, ExtensionFieldValueEntity> createOrGetJoin(
String alias,
Root<UserEntity> root,
SetAttribute<UserEntity, ExtensionFieldValueEntity> attribute) {
for (Join<UserEntity, ?> currentJoin : root.getJoins()) {
if (currentJoin.getAlias() == null) {
// if alias is null, it is not an alias for an extension join, so we ignore it
continue;
}
if (currentJoin.getAlias().equals(alias)) {
return (SetJoin<UserEntity, ExtensionFieldValueEntity>) currentJoin;
}
}
final SetJoin<UserEntity, ExtensionFieldValueEntity> join = root.join(attribute, JoinType.LEFT);
join.alias(alias);
return join;
}
@SuppressWarnings("unchecked")
private static List<Predicate> deviceSpecificPrincipalPredicates(
CriteriaBuilder cb, Root<Device> from, Optional<HivePrincipal> principal) {
final List<Predicate> predicates = new LinkedList<>();
final Join<Device, Network> networkJoin = (Join) from.fetch("network", JoinType.LEFT);
final Join<Device, Network> usersJoin = (Join) networkJoin.fetch("users", JoinType.LEFT);
from.fetch("deviceClass", JoinType.LEFT); // need this fetch to populate deviceClass
principal.ifPresent(
p -> {
User user = p.getUser();
if (user == null && p.getKey() != null) {
user = p.getKey().getUser();
}
if (user != null && !user.isAdmin()) {
predicates.add(cb.equal(usersJoin.<Long>get("id"), user.getId()));
}
if (p.getDevice() != null) {
predicates.add(cb.equal(from.<Long>get("id"), p.getDevice().getId()));
}
if (p.getKey() != null) {
for (AccessKeyBasedFilterForDevices extraFilter :
AccessKeyBasedFilterForDevices.createExtraFilters(p.getKey().getPermissions())) {
if (extraFilter.getDeviceGuids() != null) {
predicates.add(from.<String>get("guid").in(extraFilter.getDeviceGuids()));
}
if (extraFilter.getNetworkIds() != null) {
predicates.add(networkJoin.<Long>get("id").in(extraFilter.getNetworkIds()));
}
}
}
});
return predicates;
}
public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<Member> member = criteriaQuery.from(Member.class);
Join<Product, Order> orders = member.join("orders");
criteriaQuery.multiselect(
member.get("id"),
member.get("username"),
member.get("email"),
member.get("point"),
member.get("amount"),
member.get("balance"),
criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid")));
Predicate restrictions = criteriaBuilder.conjunction();
if (beginDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.greaterThanOrEqualTo(orders.<Date>get("createDate"), beginDate));
}
if (endDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.lessThanOrEqualTo(orders.<Date>get("createDate"), endDate));
}
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.equal(orders.get("orderStatus"), Order.OrderStatus.completed),
criteriaBuilder.equal(orders.get("paymentStatus"), Order.PaymentStatus.paid));
criteriaQuery.where(restrictions);
criteriaQuery.groupBy(
member.get("id"),
member.get("username"),
member.get("email"),
member.get("point"),
member.get("amount"),
member.get("balance"));
criteriaQuery.orderBy(
criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid"))));
TypedQuery<Object[]> query =
entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
if (count != null && count >= 0) {
query.setMaxResults(count);
}
return query.getResultList();
}
public static void main(String[] args) {
conex = new Conexao();
CriteriaBuilder builder = conex.getEm().getCriteriaBuilder();
// CONSULTA 1
// Todos os cursos com ch > 3600
CriteriaQuery<Curso> query = builder.createQuery(Curso.class);
Root<Curso> curso = query.from(Curso.class);
Predicate pred = builder.gt(curso.get("cargaHoraria"), 3600);
query.select(curso);
query.where(pred);
TypedQuery<Curso> tpQuery = conex.getEm().createQuery(query);
List<Curso> CursoList = tpQuery.getResultList();
System.out.println();
System.out.println("***************************************");
System.out.println("Todos os cursos com carga horaria maior que 3600:");
System.out.println();
for (Curso c : CursoList) {
System.out.println(c.getNomeCurso() + " = Carga horaria: " + c.getCargaHoraria());
}
System.out.println("---------------------------------------");
// CONSULTA 2
// Todos as turmas do periodo da tarde e noite
CriteriaQuery<Turma> criteriaQuery2 = builder.createQuery(Turma.class);
Root<Turma> turma = criteriaQuery2.from(Turma.class);
Predicate pred1 = builder.equal(turma.get("periodo"), "Noturno");
Predicate pred2 = builder.equal(turma.get("periodo"), "Vespertino");
Predicate p1orP2 = builder.or(pred1, pred2);
criteriaQuery2.select(turma);
criteriaQuery2.where(p1orP2);
TypedQuery<Turma> query2 = conex.getEm().createQuery(criteriaQuery2);
List<Turma> turmasConsulta2 = query2.getResultList();
System.out.println();
System.out.println("***************************************");
System.out.println("Todos as turmas do periodo vespertino e noturno:");
System.out.println();
for (Turma turm : turmasConsulta2) {
System.out.println(
"Turma: "
+ turm.getIdTurma()
+ " - data de inicio: "
+ turm.getDataInicio()
+ " - data de termino: "
+ turm.getDataTermino());
}
System.out.println("---------------------------------------");
// CONSULTA 3
// todas as turmas com situação = "Aberta" e curso = "Ciência da Computação“;
CriteriaQuery<Turma> criteriaQuery3 = builder.createQuery(Turma.class);
Root<Turma> turma2 = criteriaQuery3.from(Turma.class);
Join<Turma, Curso> cursoJoin = turma2.join("curso");
Predicate pred3 = builder.equal(turma2.get("situacao"), true);
Predicate pred4 = builder.equal(cursoJoin.get("nomeCurso"), "Ciencias da Computacao");
Predicate p3andP4 = builder.and(pred3, pred4);
criteriaQuery3.select(turma2);
criteriaQuery3.where(p3andP4);
TypedQuery<Turma> query3 = conex.getEm().createQuery(criteriaQuery3);
List<Turma> turmasConsulta3 = query3.getResultList();
System.out.println();
System.out.println("***************************************");
System.out.println("Todos as turmas abertas do curso Ciencias da Computacao:");
System.out.println();
for (Turma turm : turmasConsulta3) {
System.out.println(
"Turma: "
+ turm.getIdTurma()
+ ", Curso: "
+ turm.getCurso().getNomeCurso()
+ " - data de inicio: "
+ turm.getDataInicio()
+ " - data de termino: "
+ turm.getDataTermino());
}
System.out.println("---------------------------------------");
// CONSULTA 4
// todos os professores que lecionam no curso de "Bacharelado em Tecnologia da Informação“;
CriteriaQuery<Professor> c4 = builder.createQuery(Professor.class);
Root<Professor> prof = c4.from(Professor.class);
Join<Professor, Turma> profTurma = prof.join("turmas");
Join<Turma, Curso> turmaCurso = profTurma.join("curso");
Predicate p1 =
builder.equal(turmaCurso.get("nomeCurso"), "Bacharelado em Tecnologia da Informacao");
c4.select(prof);
c4.where(p1);
TypedQuery<Professor> q4 = conex.getEm().createQuery(c4);
List<Professor> profsC4 = q4.getResultList();
System.out.println();
System.out.println("***************************************");
System.out.println("Todos os professores do Bacharelado em Tecnologia da Informação");
System.out.println();
for (Professor prof4 : profsC4) {
System.out.println("Professor: " + prof4.getNome() + ", matricula: " + prof4.getMatricula());
}
System.out.println("---------------------------------------");
// CONSULTA 5
// todos os professores de turmas com periodo = Tarde e com cursos de carga horária > 4000
CriteriaQuery<Professor> c5 = builder.createQuery(Professor.class);
Root<Professor> prof5 = c5.from(Professor.class);
Join<Professor, Turma> profTurma5 = prof5.join("turmas");
Join<Turma, Curso> turmaCurso5 = profTurma5.join("curso");
Predicate p1c5 = builder.equal(profTurma5.get("periodo"), "Vespertino");
Predicate p2c5 = builder.gt(turmaCurso5.get("cargaHoraria"), 4000);
Predicate pgeral = builder.and(p1c5, p2c5);
c5.select(prof5);
c5.where(pgeral);
c5.distinct(true);
TypedQuery<Professor> q5 = conex.getEm().createQuery(c5);
List<Professor> profsC5 = q5.getResultList();
System.out.println();
System.out.println("***************************************");
System.out.println(
"Todos os professores de turmas com periodo = Tarde e com cursos de carga horária > 4000");
System.out.println();
for (Professor p5 : profsC5) {
System.out.println("Professor: " + p5.getNome() + ", matricula: " + p5.getMatricula());
}
System.out.println("---------------------------------------");
/*
http://www.objectdb.com/java/jpa/query/jpql/logical
*/
}
