@Override
public Predicate toPredicate(Root<Role> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Predicate predicate = cb.conjunction();
List<Expression<Boolean>> expressions = predicate.getExpressions();
if (isNotBlank(criteria.getFilter())) {
expressions.add(
cb.or(
cb.like(
cb.lower(root.<String>get(Role_.roleName)),
wildcardsAndLower(criteria.getFilter())),
cb.like(
cb.lower(root.<String>get(Role_.roleDesc)),
wildcardsAndLower(criteria.getFilter()))));
}
if (isNotBlank(criteria.getRoleName())) {
expressions.add(
cb.like(
cb.lower(root.<String>get(Role_.roleName)),
wildcardsAndLower(criteria.getRoleName())));
}
if (isNotBlank(criteria.getRoleDesc())) {
expressions.add(
cb.like(
cb.lower(root.<String>get(Role_.roleDesc)),
wildcardsAndLower(criteria.getRoleDesc())));
}
if (null != criteria.getIsSys()) {
expressions.add(cb.equal(root.<Boolean>get(Role_.isSys), criteria.getIsSys()));
}
return predicate;
}
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();
}
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;
}
@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;
}
@Override
public List<Train> find(TrainFilter trainFilter) {
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Train> cq = cb.createQuery(Train.class);
Root<Train> from = cq.from(Train.class);
cq.select(from);
if (trainFilter.getId() != null) {
Predicate idEqual = cb.equal(from.get(Train_.id), trainFilter.getId());
cq.where(cb.and(idEqual));
}
if (trainFilter.getName() != null) {
Predicate idEqual = cb.equal(from.get(Train_.id), trainFilter.getName());
cq.where(cb.and(idEqual));
}
if (trainFilter.getSortProperty() != null) {
cq.orderBy(new OrderImpl(from.get(trainFilter.getSortProperty()), trainFilter.isSortOrder()));
}
TypedQuery<Train> q = em.createQuery(cq);
if (trainFilter.getOffset() != null && trainFilter.getLimit() != null) {
q.setFirstResult(trainFilter.getOffset());
q.setMaxResults(trainFilter.getLimit());
}
List<Train> allitems = q.getResultList();
return allitems;
}
/** This test provides a demonstration for comparing two entities within a query */
@Test
public void testEquality() {
log.info("*** testEquality() ***");
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
// select s from Sale s
// JOIN s.clerks c
// where c = :clerk
CriteriaQuery<Sale> qdef2 = cb.createQuery(Sale.class);
Root<Sale> s = qdef2.from(Sale.class);
Join<Sale, Clerk> c2 = s.join("clerks");
qdef2.select(s).where(cb.equal(c2, clerk));
List<Sale> sales = em.createQuery(qdef2).getResultList();
for (Sale result : sales) {
log.info("found=" + result);
}
assertEquals("unexpected number of rows", 2, sales.size());
}
private Predicate[] getSearchPredicates(Root<Section> root) {
CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();
List<Predicate> predicatesList = new ArrayList<Predicate>();
String name = this.search.getName();
if (name != null && !"".equals(name)) {
predicatesList.add(builder.like(root.<String>get("name"), '%' + name + '%'));
}
String description = this.search.getDescription();
if (description != null && !"".equals(description)) {
predicatesList.add(builder.like(root.<String>get("description"), '%' + description + '%'));
}
int numberOfRows = this.search.getNumberOfRows();
if (numberOfRows != 0) {
predicatesList.add(builder.equal(root.get("numberOfRows"), numberOfRows));
}
int rowCapacity = this.search.getRowCapacity();
if (rowCapacity != 0) {
predicatesList.add(builder.equal(root.get("rowCapacity"), rowCapacity));
}
Venue venue = this.search.getVenue();
if (venue != null) {
predicatesList.add(builder.equal(root.get("venue"), venue));
}
return predicatesList.toArray(new Predicate[predicatesList.size()]);
}
private int getNumberOfInstancesEnded(final ProcessIdentifier identifier) {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Long> cq = cb.createQuery(Long.class);
final Root<MeasuredProcessInstance> processInstanceRoot =
cq.from(MeasuredProcessInstance.class);
cq.where(
cb.equal(
processInstanceRoot
.get(MeasuredProcessInstance_.identifier)
.get(ProcessInstanceIdentifier_.metricsId),
identifier.getMetricsId()),
cb.equal(
processInstanceRoot
.get(MeasuredProcessInstance_.identifier)
.get(ProcessInstanceIdentifier_.packageName),
identifier.getPackageName()),
cb.equal(
processInstanceRoot
.get(MeasuredProcessInstance_.identifier)
.get(ProcessInstanceIdentifier_.processId),
identifier.getProcessId()),
cb.isNotNull(processInstanceRoot.get(MeasuredProcessInstance_.endingTime)));
cq.select(cb.countDistinct(processInstanceRoot));
return em.createQuery(cq).getSingleResult().intValue();
}
@Override
public List<ClassTeacherMap> findClassesBySchoolAndMember(
DictSchool school, Member member, ClassStatus classStatus) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ClassTeacherMap> criteriaQuery =
criteriaBuilder.createQuery(ClassTeacherMap.class);
Root<ClassTeacherMap> root = criteriaQuery.from(ClassTeacherMap.class);
criteriaQuery.select(root);
Predicate restrictions = criteriaBuilder.conjunction();
if (school != null) {
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.equal(root.get("dictClass").get("dictSchool"), school));
}
if (member != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
}
if (classStatus != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.equal(root.get("dictClass").get("classStatus"), classStatus));
}
criteriaQuery.where(restrictions);
return entityManager
.createQuery(criteriaQuery)
.setFlushMode(FlushModeType.COMMIT)
.getResultList();
}
public Page<Review> findPage(
Member member, Product product, Type type, Boolean isShow, Pageable pageable) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Review> criteriaQuery = criteriaBuilder.createQuery(Review.class);
Root<Review> root = criteriaQuery.from(Review.class);
criteriaQuery.select(root);
Predicate restrictions = criteriaBuilder.conjunction();
if (member != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
}
if (product != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("product"), product));
}
if (type == Type.positive) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.ge(root.<Number>get("score"), 4));
} else if (type == Type.moderate) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.<Number>get("score"), 3));
} else if (type == Type.negative) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.le(root.<Number>get("score"), 2));
}
if (isShow != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isShow"), isShow));
}
criteriaQuery.where(restrictions);
return super.findPage(criteriaQuery, pageable);
}
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 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);
}
@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();
}
public Optional<NsiV2Message> findOneForUpdate(
NsiV2Message.Role role,
NsiV2Message.Type type,
String requesterNsa,
String providerNsa,
String correlationId) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<NsiV2Message> query = cb.createQuery(NsiV2Message.class);
Root<NsiV2Message> root = query.from(NsiV2Message.class);
query.where(
cb.and(
cb.equal(root.get(NsiV2Message_.role), role),
cb.equal(root.get(NsiV2Message_.type), type),
cb.equal(root.get(NsiV2Message_.requesterNsa), requesterNsa),
cb.equal(root.get(NsiV2Message_.providerNsa), providerNsa),
cb.equal(root.get(NsiV2Message_.correlationId), correlationId)));
return entityManager
.createQuery(query)
.setLockMode(LockModeType.PESSIMISTIC_WRITE)
.setMaxResults(1)
.getResultList()
.stream()
.findFirst();
}
/**
* Applies the criteria found in the {@link SearchCriteriaBean} to the JPA query.
*
* @param criteria
* @param builder
* @param query
* @param from
*/
@SuppressWarnings({"unchecked", "rawtypes"})
protected <T> void applySearchCriteriaToQuery(
SearchCriteriaBean criteria,
CriteriaBuilder builder,
CriteriaQuery<?> query,
Root<T> from,
boolean countOnly) {
List<SearchCriteriaFilterBean> filters = criteria.getFilters();
if (filters != null && !filters.isEmpty()) {
List<Predicate> predicates = new ArrayList<>();
for (SearchCriteriaFilterBean filter : filters) {
if (filter.getOperator() == SearchCriteriaFilterOperator.eq) {
Path<Object> path = from.get(filter.getName());
Class<?> pathc = path.getJavaType();
if (pathc.isAssignableFrom(String.class)) {
predicates.add(builder.equal(path, filter.getValue()));
} else if (pathc.isEnum()) {
predicates.add(builder.equal(path, Enum.valueOf((Class) pathc, filter.getValue())));
}
} else if (filter.getOperator() == SearchCriteriaFilterOperator.bool_eq) {
predicates.add(
builder.equal(
from.<Boolean>get(filter.getName()), Boolean.valueOf(filter.getValue())));
} else if (filter.getOperator() == SearchCriteriaFilterOperator.gt) {
predicates.add(
builder.greaterThan(from.<Long>get(filter.getName()), new Long(filter.getValue())));
} else if (filter.getOperator() == SearchCriteriaFilterOperator.gte) {
predicates.add(
builder.greaterThanOrEqualTo(
from.<Long>get(filter.getName()), new Long(filter.getValue())));
} else if (filter.getOperator() == SearchCriteriaFilterOperator.lt) {
predicates.add(
builder.lessThan(from.<Long>get(filter.getName()), new Long(filter.getValue())));
} else if (filter.getOperator() == SearchCriteriaFilterOperator.lte) {
predicates.add(
builder.lessThanOrEqualTo(
from.<Long>get(filter.getName()), new Long(filter.getValue())));
} else if (filter.getOperator() == SearchCriteriaFilterOperator.neq) {
predicates.add(builder.notEqual(from.get(filter.getName()), filter.getValue()));
} else if (filter.getOperator() == SearchCriteriaFilterOperator.like) {
predicates.add(
builder.like(
builder.upper(from.<String>get(filter.getName())),
filter.getValue().toUpperCase().replace('*', '%')));
}
}
query.where(predicates.toArray(new Predicate[predicates.size()]));
}
OrderByBean orderBy = criteria.getOrderBy();
if (orderBy != null && !countOnly) {
if (orderBy.isAscending()) {
query.orderBy(builder.asc(from.get(orderBy.getName())));
} else {
query.orderBy(builder.desc(from.get(orderBy.getName())));
}
}
}
@Override
public Long count(
Coupon coupon, Member member, Boolean hasBegun, Boolean hasExpired, Boolean isUsed) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<CouponCode> criteriaQuery = criteriaBuilder.createQuery(CouponCode.class);
Root<CouponCode> root = criteriaQuery.from(CouponCode.class);
criteriaQuery.select(root);
Predicate restrictions = criteriaBuilder.conjunction();
Path<Coupon> couponPath = root.get("coupon");
if (coupon != null) {
restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(couponPath, coupon));
}
if (member != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
}
if (hasBegun != null) {
if (hasBegun) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.or(
couponPath.get("beginDate").isNull(),
criteriaBuilder.lessThanOrEqualTo(
couponPath.<Date>get("beginDate"), new Date())));
} else {
restrictions =
criteriaBuilder.and(
restrictions,
couponPath.get("beginDate").isNotNull(),
criteriaBuilder.greaterThan(couponPath.<Date>get("beginDate"), new Date()));
}
}
if (hasExpired != null) {
if (hasExpired) {
restrictions =
criteriaBuilder.and(
restrictions,
couponPath.get("endDate").isNotNull(),
criteriaBuilder.lessThan(couponPath.<Date>get("endDate"), new Date()));
} else {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.or(
couponPath.get("endDate").isNull(),
criteriaBuilder.greaterThanOrEqualTo(
couponPath.<Date>get("endDate"), new Date())));
}
}
if (isUsed != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUsed"), isUsed));
}
criteriaQuery.where(restrictions);
return super.count(criteriaQuery, null);
}
public List<Goods> findList(
ProductCategory productCategory,
Boolean isMarketable,
Goods.GenerateMethod generateMethod,
Date beginDate,
Date endDate,
Integer first,
Integer count) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Goods> criteriaQuery = criteriaBuilder.createQuery(Goods.class);
Root<Goods> root = criteriaQuery.from(Goods.class);
criteriaQuery.select(root);
Predicate restrictions = criteriaBuilder.conjunction();
if (productCategory != null) {
Subquery<ProductCategory> subquery = criteriaQuery.subquery(ProductCategory.class);
Root<ProductCategory> subqueryRoot = subquery.from(ProductCategory.class);
subquery.select(subqueryRoot);
subquery.where(
criteriaBuilder.or(
criteriaBuilder.equal(subqueryRoot, productCategory),
criteriaBuilder.like(
subqueryRoot.<String>get("treePath"),
"%"
+ ProductCategory.TREE_PATH_SEPARATOR
+ productCategory.getId()
+ ProductCategory.TREE_PATH_SEPARATOR
+ "%")));
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.in(root.get("productCategory")).value(subquery));
}
if (isMarketable != null) {
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
}
if (generateMethod != null) {
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.equal(root.get("generateMethod"), generateMethod));
}
if (beginDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
}
if (endDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
}
criteriaQuery.where(restrictions);
return super.findList(criteriaQuery, first, count, null, null);
}
public List<T> load(
int first,
int count,
String sortField,
SortOrder sortOrder,
Map<String, String> filters,
List<Filtro> filtros) {
javax.persistence.criteria.CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery cq = builder.createQuery();
Root root = cq.from(entityClass);
cq.select(root);
if (sortField != null) {
if (sortOrder == SortOrder.ASCENDING) {
cq.orderBy(builder.asc(root.get(sortField)));
} else if (sortOrder == SortOrder.DESCENDING) {
cq.orderBy(builder.desc(root.get(sortField)));
}
}
/*
if (filtros != null) {
Set<Entry<String, Object>> entrySet = filtros.entrySet();
ArrayList<Predicate> predicatesList = new ArrayList<>(entrySet.size());
for (Entry<String, Object> entry : entrySet) {
predicatesList.add(builder.equal(root.get(entry.getKey()), entry.getValue()));
}
cq.where(predicatesList.<Predicate>toArray(new Predicate[predicatesList.size()]));
}
*/
if (filtros != null) {
ArrayList<Predicate> predicatesList = new ArrayList<>();
for (Filtro filtro : filtros) {
switch (filtro.getTipoFiltro()) {
case Equals:
predicatesList.add(builder.equal(root.get(filtro.getDescricao()), filtro.getValor()));
break;
case LessEqual:
Path<Date> date = root.get(filtro.getDescricao());
predicatesList.add(builder.lessThanOrEqualTo(date, ((Date) filtro.getValor())));
break;
case GreaterEqual:
Path<Date> date2 = root.get(filtro.getDescricao());
predicatesList.add(builder.greaterThanOrEqualTo(date2, ((Date) filtro.getValor())));
break;
default:
predicatesList.add(builder.equal(root.get(filtro.getDescricao()), filtro.getValor()));
break;
}
}
cq.where(predicatesList.<Predicate>toArray(new Predicate[predicatesList.size()]));
}
javax.persistence.Query q = getEntityManager().createQuery(cq);
q.setFirstResult(first);
q.setMaxResults(count);
return q.getResultList();
}
@SuppressWarnings("CPD-START")
public List<Scenario> getScenariosByName(final long projectId, final String scenarioName) {
CriteriaQuery<Scenario> q = criteriaBuilder.createQuery(Scenario.class);
Root<Scenario> s = q.from(Scenario.class);
q.where(
criteriaBuilder.and(
criteriaBuilder.equal(s.get(Scenario_.name), scenarioName),
criteriaBuilder.equal(s.get(Scenario_.project).get(Project_.id), projectId)));
return crudService.createQuery(q).getResultList();
}
@Override
public PadHardVersion getVersion(Pad pad, long versionId) {
CriteriaBuilder builder = getCriteriaBuilder();
CriteriaQuery<PadHardVersion> query = builder.createQuery(PadHardVersion.class);
Root<PadHardVersion> version = query.from(PadHardVersion.class);
query.where(
builder.and(
builder.equal(version.get(PadHardVersion_.pad), pad),
builder.equal(version.get(PadHardVersion_.version), versionId)));
return getSingleResult(query);
}
/**
* @param project
* @param date
* @return
*/
public List<JenkinsMetricMeasurement> getMeasurementsForProjectByDate(String project, Date date) {
logger.info("Obtaining measurements for the project: " + project);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<JenkinsMetricMeasurement> query = cb.createQuery(JenkinsMetricMeasurement.class);
Root<JenkinsMetricMeasurement> root = query.from(JenkinsMetricMeasurement.class);
Predicate condition = cb.equal(root.get(JenkinsMetricMeasurement_.name), project);
Predicate condition2 = cb.equal(root.get(JenkinsMetricMeasurement_.timeStamp), date);
Predicate condition3 = cb.and(condition, condition2);
query.where(condition3);
query.orderBy(cb.desc(root.get(JenkinsMetricMeasurement_.timeStamp)));
return em.createQuery(query).getResultList();
}
private UserVariable findByUserAndVariableKey(User user, UserVariableKey key) {
EntityManager entityManager = getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<UserVariable> criteria = criteriaBuilder.createQuery(UserVariable.class);
Root<UserVariable> root = criteria.from(UserVariable.class);
criteria.select(root);
criteria.where(
criteriaBuilder.and(
criteriaBuilder.equal(root.get(UserVariable_.user), user),
criteriaBuilder.equal(root.get(UserVariable_.key), key)));
return getSingleResult(entityManager.createQuery(criteria));
}
@Override
public List<Semestre> findByNiveau(Niveau n) throws DataAccessException {
CriteriaBuilder cb = getManager().getCriteriaBuilder();
CriteriaQuery<Semestre> cq = cb.createQuery(Semestre.class);
Root<Semestre> semestreRoot = cq.from(Semestre.class);
Path<Niveau> niveauPath = semestreRoot.get(Semestre_.niveau);
cq.where(
cb.and(
cb.equal(semestreRoot.get(Semestre_.active), 1),
cb.equal(niveauPath, n),
cb.equal(niveauPath.get(Niveau_.active), 1)));
cq.select(semestreRoot);
return getManager().createQuery(cq).getResultList();
}
public HelpPageContent findByPageAndLocale(HelpPage page, Locale locale) {
EntityManager entityManager = getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<HelpPageContent> criteria = criteriaBuilder.createQuery(HelpPageContent.class);
Root<HelpPageContent> root = criteria.from(HelpPageContent.class);
criteria.select(root);
criteria.where(
criteriaBuilder.and(
criteriaBuilder.equal(root.get(HelpPageContent_.page), page),
criteriaBuilder.equal(root.get(HelpPageContent_.locale), locale)));
return getSingleResult(entityManager.createQuery(criteria));
}
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 ejbqlString = "Update Employee e set e.lastName = case when e.firstName = 'Bob' then
// 'Jones' when e.firstName = 'Jill' then 'Jones' else '' end";
public void testMetamodelComplexConditionCaseInCriteriaUpdate() {
if ((getPersistenceUnitServerSession()).getPlatform().isSymfoware()) {
getPersistenceUnitServerSession()
.logMessage(
"Test complexConditionCaseInUpdateTest skipped for this platform, "
+ "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
return;
}
EntityManager em = createEntityManager();
List<Employee> results = null;
Metamodel metamodel = em.getMetamodel();
EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class);
CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaUpdate<Employee> cq = qb.createCriteriaUpdate(Employee.class);
Root<Employee> root = cq.from(Employee.class);
Case caseExp = qb.selectCase();
caseExp.when(
qb.equal(root.get(entityEmp_.getSingularAttribute("firstName", String.class)), "Bob"),
"Jones");
caseExp.when(
qb.equal(root.get(entityEmp_.getSingularAttribute("firstName", String.class)), "Jill"),
"Jones");
caseExp.otherwise("");
cq.set(root.get(entityEmp_.getSingularAttribute("lastName")), caseExp);
beginTransaction(em);
try {
clearCache();
em.createQuery(cq).executeUpdate();
String verificationString = "select e from Employee e where e.lastName = 'Jones'";
results = em.createQuery(verificationString).getResultList();
} finally {
if (isTransactionActive(em)) {
rollbackTransaction(em);
}
closeEntityManager(em);
}
assertTrue("complexConditionCaseInUpdateTest - wrong number of results", results.size() == 2);
for (Employee e : results) {
assertTrue(
"complexConditionCaseInUpdateTest wrong last name for - " + e.getFirstName(),
e.getLastName().equals("Jones"));
}
}
/**
* 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<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();
}
/** 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 static Predicate[] oAuthGrantsListPredicates(
CriteriaBuilder cb,
Root<OAuthGrant> from,
User user,
Optional<Date> startOpt,
Optional<Date> endOpt,
Optional<String> oAuthIdOpt,
Optional<Integer> typeOpt,
Optional<String> scopeOpt,
Optional<String> redirectUri,
Optional<Integer> accessType) {
List<Predicate> predicates = new LinkedList<>();
if (!user.isAdmin()) {
predicates.add(from.join("user").in(user));
}
startOpt.ifPresent(start -> predicates.add(cb.greaterThan(from.get("timestamp"), start)));
endOpt.ifPresent(end -> predicates.add(cb.lessThan(from.get("timestamp"), end)));
oAuthIdOpt.ifPresent(id -> predicates.add(cb.equal(from.join("client").get("oauthId"), id)));
typeOpt.ifPresent(type -> predicates.add(cb.equal(from.get("type"), type)));
scopeOpt.ifPresent(scope -> predicates.add(cb.equal(from.get("scope"), scope)));
redirectUri.ifPresent(uri -> predicates.add(cb.equal(from.get("redirectUri"), uri)));
accessType.ifPresent(at -> predicates.add(cb.equal(from.get("accessType"), at)));
return predicates.toArray(new Predicate[predicates.size()]);
}
