@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;
}
@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();
}
/**
* Gets the all predicates.
*
* @param criteriaBuilder the criteria builder
* @return the all predicates
*/
public Predicate[] getAllPredicates(CriteriaBuilder criteriaBuilder) {
List<Predicate> allPredicates = new ArrayList<Predicate>();
for (PredicateGroup predicateGroup : predicates.values()) {
List<Predicate> andPredicates = predicateGroup.getAndPredicates();
if (!andPredicates.isEmpty()) {
allPredicates.add(
criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()])));
}
List<Predicate> orPredicates = predicateGroup.getOrPredicates();
if (!orPredicates.isEmpty()) {
allPredicates.add(
criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()])));
}
}
if (!andWithOrPredicates.isEmpty()) {
List<Predicate> allAndOrPredicates = new ArrayList<Predicate>();
for (PredicateGroup predicateGroup : andWithOrPredicates.values()) {
List<Predicate> andPredicates = predicateGroup.getAndPredicates();
allAndOrPredicates.add(
criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()])));
}
allPredicates.add(
criteriaBuilder.or(allAndOrPredicates.toArray(new Predicate[allAndOrPredicates.size()])));
}
return allPredicates.toArray(new Predicate[allPredicates.size()]);
}
@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);
}
private Predicate buildSimplePredicate(Path<T> path, String name, String value) {
Predicate predicate;
CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
if (isMultipleOrValue(value)) {
// name=value1,value,...,valueN
// => name=value1 OR name=value OR ... OR name=valueN
List<String> valueList = convertMultipleOrValueToList(value);
List<Predicate> orPredicates = new ArrayList<Predicate>();
for (String currentValue : valueList) {
Predicate orPredicate = buildPredicateWithOperator(path, name, currentValue);
orPredicates.add(orPredicate);
}
predicate = criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
} else if (isMultipleAndValue(value)) {
// name=(subname1=value1&subname2=value&...&subnameN=valueN)
// => name.subname1=value1 AND name.subname2=value AND ... AND name.subnameN=valueN
List<Map.Entry<String, String>> subFieldNameValue = convertMultipleAndValue(value);
List<Predicate> andPredicates = new ArrayList<Predicate>();
Path<T> root = path.get(name);
for (Map.Entry<String, String> entry : subFieldNameValue) {
String currentsubFieldName = entry.getKey();
String currentValue = entry.getValue();
Predicate andPredicate = buildPredicate(root, currentsubFieldName, currentValue);
andPredicates.add(andPredicate);
}
predicate = criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()]));
} else {
// name=value
predicate = buildPredicateWithOperator(path, name, value);
}
return predicate;
}
public List<T> findByCriteria(MultivaluedMap<String, String> queryParameters, Class<T> clazz) {
List<T> resultsList = null;
CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<T> cq = criteriaBuilder.createQuery(clazz);
List<Predicate> andPredicates = new ArrayList<Predicate>();
Root<T> tt = cq.from(clazz);
for (Map.Entry<String, List<String>> entry : queryParameters.entrySet()) {
List<String> valueList = entry.getValue();
Predicate predicate = null;
if (valueList.size() > 1) {
// name=value1&name=value&...&name=valueN
// value of name is list [value1, value, ..., valueN]
// => name=value1 OR name=value OR ... OR name=valueN
List<Predicate> orPredicates = new ArrayList<Predicate>();
for (String currentValue : valueList) {
Predicate orPredicate = buildPredicate(tt, entry.getKey(), currentValue);
orPredicates.add(orPredicate);
}
predicate = criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
} else {
// name=value
// value of name is one element list [value]
// => name=value
predicate = buildPredicate(tt, entry.getKey(), valueList.get(0));
}
andPredicates.add(predicate);
}
cq.where(andPredicates.toArray(new Predicate[andPredicates.size()]));
cq.select(tt);
TypedQuery<T> q = getEntityManager().createQuery(cq);
resultsList = q.getResultList();
return resultsList;
}
/**
* TODO A COMMENTER
*
* @param stepArray TODO A COMMENTER
* @return TODO A COMMENTER
* @throws PersistenceException TODO A COMMENTER
*/
protected CriteriaQuery<SALE> getCriteriaForStep(Step... stepArray) throws PersistenceException {
CriteriaBuilder builder = this.getCriteriaBuilder();
CriteriaQuery<SALE> criteria = this.createCriteria();
Root<SALE> sale_ = criteria.from(this.getEntityClass());
if (stepArray.length != 0) {
Path<SaleStep> saleStep_ = this.getSaleStepPath(sale_);
Bid4WinSet<Step> stepSet = new Bid4WinSet<Step>();
for (Step step : stepArray) {
stepSet.add(step);
stepSet.addAll(step.getRecursiveSubtypeSet());
}
Predicate[] predicates = new Predicate[stepSet.size()];
int i = 0;
try {
for (Step step : stepSet) {
predicates[i++] = builder.equal(saleStep_, new SaleStep(step));
}
} catch (UserException ex) {
throw new PersistenceException(ex);
}
Predicate condition = builder.or(predicates);
criteria.where(condition);
}
return criteria;
}
public <E> Predicate byEntitySelectors(
Root<E> root, CriteriaBuilder builder, SearchParameters sp) {
List<EntitySelector<?, ?, ?>> selectors = sp.getEntities();
List<Predicate> predicates = newArrayList();
for (EntitySelector<?, ?, ?> s : selectors) {
@SuppressWarnings("unchecked")
EntitySelector<? super E, ? extends Identifiable<?>, ?> selector =
(EntitySelector<? super E, ? extends Identifiable<?>, ?>) s;
if (selector.isNotEmpty()) {
List<Predicate> selectorPredicates = newArrayList();
for (Identifiable<?> selection : selector.getSelected()) {
selectorPredicates.add(builder.equal(getExpression(root, selector), selection.getId()));
}
if (TRUE == selector.getIncludeNull()) {
selectorPredicates.add(builder.or(builder.isNull(getExpression(root, selector))));
}
predicates.add(JpaUtil.orPredicate(builder, selectorPredicates));
} else if (selector.isIncludeNullSet()) {
if (selector.getIncludeNull()) {
predicates.add(builder.isNull(getExpression(root, selector)));
} else {
predicates.add(builder.isNotNull(getExpression(root, selector)));
}
}
}
return JpaUtil.concatPredicate(sp, builder, predicates);
}
protected static Predicate getPredicate(
final Class clazz,
final Map<String, String[]> searchTerms,
Root<Persistable> root,
CriteriaBuilder cb) {
LinkedList<Predicate> predicates = new LinkedList<Predicate>();
Predicate predicate;
if (!CollectionUtils.isEmpty(searchTerms)) {
Set<String> propertyNames = searchTerms.keySet();
// put aside nested AND/OR param groups
NestedJunctions junctions = new NestedJunctions();
for (String propertyName : propertyNames) {
String[] values = searchTerms.get(propertyName);
if (!junctions.addIfNestedJunction(propertyName, values)) {
addPredicate(clazz, root, cb, predicates, values, propertyName);
}
}
// add nested AND/OR param groups
Map<String, Map<String, String[]>> andJunctions = junctions.getAndJunctions();
addJunctionedParams(clazz, root, cb, predicates, andJunctions, AND);
Map<String, Map<String, String[]>> orJunctions = junctions.getOrJunctions();
addJunctionedParams(clazz, root, cb, predicates, orJunctions, OR);
}
if (searchTerms.containsKey(SEARCH_MODE)
&& searchTerms.get(SEARCH_MODE)[0].equalsIgnoreCase(OR)) {
predicate = cb.or(predicates.toArray(new Predicate[predicates.size()]));
} else {
predicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
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();
}
protected static Predicate getPredicate(
final Class clazz,
final Restriction searchTerms,
Root<Persistable> root,
CriteriaBuilder cb) {
LinkedList<Predicate> predicates = new LinkedList<Predicate>();
Predicate predicate;
// process child restrictions
if (!CollectionUtils.isEmpty(searchTerms.getRestrictions())) {
for (Restriction restriction : searchTerms.getRestrictions()) {
predicates.add(getPredicate(clazz, restriction, root, cb));
}
}
// process main restriction
if (StringUtils.isNotBlank(searchTerms.getField())) {
String propertyName = searchTerms.getField();
addPredicate(
clazz,
root,
cb,
predicates,
searchTerms.getValues().toArray(new String[searchTerms.getValues().size()]),
propertyName);
}
if (searchTerms.getJunction().equals(Restriction.Junction.OR)) {
predicate = cb.or(predicates.toArray(new Predicate[predicates.size()]));
} else {
predicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
return predicate;
}
@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 static <E> Predicate byRanges(
Root<E> root,
CriteriaQuery<?> query,
CriteriaBuilder builder,
final List<Range<?, ?>> ranges,
final Class<E> type) {
List<Predicate> predicates = newArrayList();
for (Range<?, ?> r : ranges) {
@SuppressWarnings("unchecked")
Range<E, ?> range = (Range<E, ?>) r;
if (range.isSet()) {
Predicate rangePredicate = buildRangePredicate(range, root, builder);
if (rangePredicate != null) {
if (!range.isIncludeNullSet() || range.getIncludeNull() == FALSE) {
predicates.add(rangePredicate);
} else {
predicates.add(builder.or(rangePredicate, builder.isNull(root.get(range.getField()))));
}
}
// no range at all, let's take the opportunity to keep only null...
if (TRUE == range.getIncludeNull()) {
predicates.add(builder.isNull(root.get(range.getField())));
} else if (FALSE == range.getIncludeNull()) {
predicates.add(builder.isNotNull(root.get(range.getField())));
}
}
}
return JpaUtil.andPredicate(builder, predicates);
}
@SuppressWarnings("unchecked")
public Predicate getCanReadOrgPredicate(
User user,
OrgType type,
CriteriaBuilder cb,
CriteriaQuery<Organization> query,
boolean containRoot) {
// 一。获得可管理的顶层机构，不区分机构类型
List<Organization> topCanReadOrgs = getTopCanReadOrgs(user);
Root<Organization> from;
if (query.getRoots().size() > 0) {
from = (Root<Organization>) query.getRoots().iterator().next();
} else {
from = query.from(Organization.class);
}
if (topCanReadOrgs.size() == 0) {
return cb.isNull(from);
}
// 二。应用条件
// 1.机构范围限制(如果有全部数据权限不做限制)
Subquery<Organization> subquery = query.subquery(Organization.class);
Root<Organization> subfrom = subquery.from(Organization.class);
subquery.select(subfrom);
ListJoin<Organization, Organization> join =
subfrom.join(Organization_.ancestors, JoinType.INNER);
In<String> subin = cb.in(join.get(Organization_.id));
for (Organization o : topCanReadOrgs) {
subin = subin.value(o.getId());
}
// 2.应用机构类别
if (type != null) {
Predicate p = cb.equal(subfrom.get(Organization_.orgType), type);
subquery.where(cb.and(subin, p));
} else {
subquery.where(subin);
}
// 3.增加祖先节点
if (containRoot) {
In<String> in = cb.in(from.get(Organization_.id));
boolean hasdata = false;
for (Organization o : topCanReadOrgs) {
Organization parento = o.getParent();
while (parento != null) {
hasdata = true;
in = in.value(parento.getId());
parento = parento.getParent();
}
}
if (hasdata) {
return cb.or(cb.in(from).value(subquery), in);
}
}
return cb.in(from).value(subquery);
}
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);
}
/**
* This test provides a demonstration of using logical AND, OR, and NOT within a query where
* clause
*/
@Test
public void testLogical() {
log.info("*** testLogical() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
// select c from Customer c
// where (c.firstName='cat' AND c.lastName='inhat')
// OR c.firstName='thing'
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c)
.where(
cb.or(
cb.and(cb.equal(c.get("firstName"), "cat"), cb.equal(c.get("lastName"), "inhat")),
cb.equal(c.get("firstName"), "thing")));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 3, rows);
}
{
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
// select c from Customer c
// where (NOT (c.firstName='cat' AND c.lastName='inhat'))
// OR c.firstName='thing'
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c)
.where(
cb.or(
cb.not(
cb.and(
cb.equal(c.get("firstName"), "cat"),
cb.equal(c.get("lastName"), "inhat"))),
cb.equal(c.get("firstName"), "thing")));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 2, rows);
}
}
/*
* (non-Javadoc)
* @see com.lucien.spirit.core.jpa.Criterion#toPredicate(javax.persistence.criteria.Root, javax.persistence.criteria.CriteriaQuery, javax.persistence.criteria.CriteriaBuilder)
*/
public Predicate toPredicate(Root<?> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> predicates = new ArrayList<Predicate>();
for (int i = 0; i < this.criterion.length; i++) {
predicates.add(this.criterion[i].toPredicate(root, query, builder));
}
switch (operator) {
case OR:
return builder.or(predicates.toArray(new Predicate[predicates.size()]));
default:
return null;
}
}
/** @return Retourne tous les objets créés qui correspondent à la recherche effectuée */
@Override
public List<ProductEntity> getProducts(String search) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<ProductEntity> cq = cb.createQuery(ProductEntity.class);
Root<ProductEntity> product = cq.from(ProductEntity.class);
if (search != null && !search.isEmpty()) {
List<Predicate> predicates = getPredicate(cb, product, search);
cq.where(cb.or(predicates.toArray(new Predicate[predicates.size()])));
}
return em.createQuery(cq).getResultList();
}
@Override
protected Predicate buildCondition(
Map.Entry<String, Object> entry, Root<User> root, CriteriaBuilder cb) {
Predicate predicate = null;
switch (entry.getKey()) {
case "username":
predicate =
cb.like(
cb.upper(root.get(entry.getKey())),
"%" + entry.getValue().toString().toUpperCase() + "%");
break;
case "fullname":
predicate =
cb.like(
cb.upper(root.get(entry.getKey())),
"%" + entry.getValue().toString().toUpperCase() + "%");
break;
case "email":
predicate =
cb.like(
cb.upper(root.get(entry.getKey())),
"%" + entry.getValue().toString().toUpperCase() + "%");
break;
case "groups":
if (entry.getValue() != null) {
Object[] groups = (Object[]) entry.getValue();
Predicate[] predicates = new Predicate[groups.length];
for (int i = 0; i < groups.length; i++) {
predicates[i] = cb.isMember((User.Group) groups[i], root.get(entry.getKey()));
}
predicate = cb.or(predicates);
}
break;
case "description":
predicate =
cb.like(
cb.upper(root.get(entry.getKey())),
"%" + entry.getValue().toString().toUpperCase() + "%");
break;
case "code":
predicate =
cb.like(
cb.upper(root.get(entry.getKey())),
"%" + entry.getValue().toString().toUpperCase() + "%");
break;
}
return predicate;
}
@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();
}
/**
* Take a planned operation and check if a real operation exists for this planned operation. An
* operation matches if it's between 2 days before or after the planned date, the planned amount
* matches exactly or the planned label is contained in the real operation.
*
* @param plannedOp Planned operation to check
* @return The operation that match or null if any operation matching is found.
*/
public Operation matchRealOp(Operation plannedOp) {
CriteriaBuilder b = getBuilder();
// creating criteria
CriteriaQuery<Operation> q = b.createQuery(Operation.class);
Root<Operation> op = q.from(Operation.class);
q.select(op);
// start and end date
Calendar startDate = Calendar.getInstance();
startDate.setTime(plannedOp.getOperationDate());
startDate.set(
startDate.get(Calendar.YEAR),
startDate.get(Calendar.MONTH),
startDate.get(Calendar.DAY_OF_MONTH),
0,
0,
0);
startDate.add(Calendar.DAY_OF_MONTH, -2);
Calendar endDate = Calendar.getInstance();
endDate.setTime(plannedOp.getOperationDate());
endDate.set(
endDate.get(Calendar.YEAR),
endDate.get(Calendar.MONTH),
endDate.get(Calendar.DAY_OF_MONTH),
0,
0,
0);
endDate.add(Calendar.DAY_OF_MONTH, 2);
// adding restriction
q.where(
b.and(
b.between(op.get(Operation_.operationDate), startDate.getTime(), endDate.getTime()),
b.isNull(op.get(Operation_.planned)),
b.or(
b.equal(op.get(Operation_.amount), plannedOp.getPlanned()),
b.like(op.get(Operation_.label), "%" + plannedOp.getLabel() + "%"))));
List<Operation> ops = getEm().createQuery(q).getResultList();
if (ops.size() > 0) {
return ops.get(0);
} else {
return null;
}
}
@Override
public ResultSet<Role> getRecords(PagingCriteria criteria) {
Integer displaySize = criteria.getDisplaySize();
Integer displayStart = criteria.getDisplayStart();
Integer pageNumber = criteria.getPageNumber();
String search = criteria.getSearch();
List<SortField> sortFields = criteria.getSortFields();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Role> cq = cb.createQuery(Role.class);
Root<Role> root = cq.from(Role.class);
// Join<Role, Customer> root = cq.from(Role.class).join(Role_.customer);
// Filtering and Searching
List<Predicate> predicateList = new ArrayList<Predicate>();
if ((search != null) && (!(search.isEmpty()))) {
Predicate predicate1 = cb.like(root.get(Role_.name), "%" + search + "%");
Predicate predicate2 = cb.like(root.get(Role_.code), "%" + search + "%");
Predicate predicate = cb.or(predicate1, predicate2);
predicateList.add(predicate);
}
Predicate[] predicates = new Predicate[predicateList.size()];
predicateList.toArray(predicates);
cq.where(predicates);
// Sorting
for (SortField sortField : sortFields) {
String field = sortField.getField();
String direction = sortField.getDirection().getDirection();
if (direction.equalsIgnoreCase("asc")) cq.orderBy(cb.asc(root.get(field)));
else if (direction.equalsIgnoreCase("desc")) cq.orderBy(cb.desc(root.get(field)));
}
Long totalRecords = (long) em.createQuery(cq).getResultList().size();
// Pagination
TypedQuery<Role> typedQuery = em.createQuery(cq);
typedQuery = typedQuery.setFirstResult(displayStart);
typedQuery = typedQuery.setMaxResults(displaySize);
List<Role> resultList = typedQuery.getResultList();
return new ResultSet<Role>(resultList, totalRecords, displaySize);
}
@Override
public List<Pessoa> listByEntity(Pessoa pessoa) {
CriteriaBuilder cb = manager.getCriteriaBuilder();
CriteriaQuery<Pessoa> query = cb.createQuery(Pessoa.class);
Root<Pessoa> root = query.from(Pessoa.class);
EntityType<Pessoa> typePessoa = manager.getMetamodel().entity(Pessoa.class);
Predicate where1 = null, where2 = null, where3 = null, where4 = null;
if (pessoa.getId() != null) {
where1 = cb.equal(root.get("id"), pessoa.getId());
}
if (pessoa.getDtNascimento() != null) {
where2 = cb.equal(root.get("dtNascimento"), pessoa.getDtNascimento());
}
if (pessoa.getTipoPessoa() != null) {
where3 = cb.equal(root.get("tipoPessoa"), pessoa.getTipoPessoa());
}
if (pessoa.getNome() != null) {
where4 =
cb.equal(
root.get(typePessoa.getDeclaredSingularAttribute("nome", String.class)),
pessoa.getNome());
}
List<Predicate> predicados = new ArrayList<Predicate>();
if (where1 != null) {
predicados.add(where1);
}
if (where2 != null) {
predicados.add(where2);
}
if (where3 != null) {
predicados.add(where3);
}
if (where4 != null) {
predicados.add(where4);
}
if (predicados.size() > 0) {
query.where(cb.or(predicados.toArray(new Predicate[] {})));
}
return manager.createQuery(query).getResultList();
}
/**
* @return Retourne les derniers objets créés qui correspondent à la recherche effectuée et à la
* page demandée
*/
@Override
public List<ProductEntity> getLastProducts(int page, String search) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<ProductEntity> cq = cb.createQuery(ProductEntity.class);
Root<ProductEntity> product = cq.from(ProductEntity.class);
if (search != null && !search.isEmpty()) {
List<Predicate> predicates = getPredicate(cb, product, search);
cq.where(cb.or(predicates.toArray(new Predicate[predicates.size()])));
}
cq.orderBy(cb.desc(product.get(ProductEntity_.id)));
return em.createQuery(cq)
.setFirstResult((page - 1) * Configuration.PRODUCTS_PER_PAGE)
.setMaxResults(Configuration.PRODUCTS_PER_PAGE)
.getResultList();
}
/**
* this method get called by primefaces when loading the data to provide a lazy loading
*
* @param first is the index of the first row to display
* @param pageSize how many rows need to be displayed
* @param sortField the sort filed if not it's null
* @param sortOrder always ascending or discending
* @param filters i don't use them hear cuz it's a bit complex
* @return the list of the authors feched from the db
*/
@Override
public List<Author> load(
int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
List<Author> authors;
// the standart way to make creteria builder,query, form
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Author> cq = cb.createQuery(Author.class);
Root<Author> form = cq.from(Author.class);
// make a select operation of authors (form has authors type)
cq.select(form);
// if ther is any sorting we add where conditions
if (sortField != null) {
if (sortOrder.name().equals("ASCENDING")) {
cq.orderBy(cb.asc(form.get(sortField)));
} else {
cq.orderBy(cb.desc(form.get(sortField)));
}
}
// if the user typed in the global filter we add more where conditions
if (globalFilter != null) {
Predicate condition =
cb.like(form.<String>get("familyName"), "%" + globalFilter.trim() + "%");
Predicate c2 =
cb.or(condition, cb.like(form.<String>get("firstName"), "%" + globalFilter.trim() + "%"));
cq.where(c2);
}
// we set the rouw count, primefaces use it to sesplay pages number
this.setRowCount(JpaUtils.countQueryResults(em, cq).intValue());
// we make the query and we set the result nbr
TypedQuery<Author> q = em.createQuery(cq);
q.setFirstResult(first);
q.setMaxResults(pageSize);
authors = q.getResultList();
return authors;
}
/**
* Creates and returns an "in" disjunction (i.e. an "or statement") using the arguments provided.
* The returned disjunction is semantically similar to: "<code>propertyName</code> in <code>
* expressionList</code>", but works around the Oracle expression list size limit. </br></br> See:
* "ORA-01795: maximum number of expressions in a list is 1000"
*
* @param expressionList The expression list for the statement.
* @param property The property to check with the statement.
* @param criteriaBuilder The builder to use to construct the disjunction.
* @return the created disjunction.
*/
@SuppressWarnings({"rawtypes", "unchecked"})
public static Predicate getExpressionDisjunction(
Collection expressionList, Path property, CriteriaBuilder criteriaBuilder) {
List<Predicate> expressionCollections = new ArrayList<Predicate>();
In currentSet = criteriaBuilder.in(property);
int count = MAX_DB_VALUE_LIST;
for (Object expression : expressionList) {
if (count >= MAX_DB_VALUE_LIST) {
currentSet = criteriaBuilder.in(property);
expressionCollections.add(currentSet);
count = 0;
}
currentSet.value(expression);
count++;
}
return criteriaBuilder.or(
expressionCollections.toArray(new Predicate[expressionCollections.size()]));
}
private void queryHotels(final SearchCriteria criteria) {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Hotel> cquery = builder.createQuery(Hotel.class);
Root<Hotel> hotel = cquery.from(Hotel.class);
// QUESTION can like create the pattern for us?
cquery
.select(hotel)
.where(
builder.or(
builder.like(builder.lower(hotel.get(Hotel_.name)), criteria.getSearchPattern()),
builder.like(builder.lower(hotel.get(Hotel_.city)), criteria.getSearchPattern()),
builder.like(builder.lower(hotel.get(Hotel_.zip)), criteria.getSearchPattern()),
builder.like(
builder.lower(hotel.get(Hotel_.address)), criteria.getSearchPattern())));
List<Hotel> results =
em.createQuery(cquery)
.setMaxResults(criteria.getFetchSize())
.setFirstResult(criteria.getFetchOffset())
.getResultList();
nextPageAvailable = results.size() > criteria.getPageSize();
if (nextPageAvailable) {
// NOTE create new ArrayList since subList creates unserializable list
hotels = new ArrayList<Hotel>(results.subList(0, criteria.getPageSize()));
} else {
hotels = results;
}
log.info(
messageBuilder
.get()
.text("Found {0} hotel(s) matching search term [ {1} ] (limit {2})")
.textParams(hotels.size(), criteria.getQuery(), criteria.getPageSize())
.build()
.getText());
/*
* System.out.println(messageBuilder.get().text("Found {0} hotel(s) matching search term [ {1} ] (limit {2})")
* .textParams(hotels.size(), criteria.getQuery(), criteria.getPageSize()).build().getText());
*/
}
/**
* Get a list of filtered projects
*
* @param filter
* @return
*/
public List<Project> getAllProjectsFiltered(TreeFilterStructure filter) {
List<Project> resultList = new ArrayList<>();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Project> query = cb.createQuery(Project.class);
Root<Project> from = query.from(Project.class);
List<Predicate> predicates = new ArrayList<>();
if (filter != null && filter.getQualityStatus() != null) {
predicates.add(cb.equal(from.get(Project_.qualityStatus), filter.getQualityStatus()));
}
if (filter != null && filter.getLCStage() != null) {
predicates.add(cb.equal(from.get(Project_.lifeCycleStage), filter.getLCStage()));
}
if (filter != null && filter.getStartDate() != null) {
predicates.add(cb.greaterThanOrEqualTo(from.get(Project_.startDate), filter.getStartDate()));
}
if (filter != null && filter.getEndDate() != null) {
predicates.add(cb.lessThanOrEqualTo(from.get(Project_.endDate), filter.getEndDate()));
}
Predicate and = null;
if (!predicates.isEmpty()) {
and = cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
if (and != null) {
if (filter != null && filter.getCurrentlyEditedProjectId() != null) {
query.where(
cb.or(cb.equal(from.get(Project_.id), filter.getCurrentlyEditedProjectId()), and));
} else {
query.where(and);
}
// } else {
// if(filter != null && filter.getCurrentlyEditedProjectId() != null) {
// query.where(cb.equal(from.get(Project_.id), filter.getCurrentlyEditedProjectId()));
// }
}
query.orderBy(cb.asc(from.get(Project_.endDate)));
if (!em.createQuery(query).getResultList().isEmpty()) {
resultList = em.createQuery(query).getResultList();
}
return resultList;
}
/**
* Get operations from startDay to endDay.
*
* @param startDay Day from which retrieve operations.
* @param endDay Day to which retrieve operations
* @return Operation list history sorted by operation date and id.
*/
public List<Operation> getHistory(LocalDate startDay, LocalDate endDay) {
CriteriaBuilder b = getBuilder();
// creating criteria
CriteriaQuery<Operation> q = b.createQuery(Operation.class);
Root<Operation> op = q.from(Operation.class);
q.select(op);
// adding restriction
// - every operation between the start and end date
// - every planned operation not sync before start date
q.where(
b.or(
b.between(op.get(Operation_.operationDate), startDay.toDate(), endDay.toDate()),
b.and(
b.lessThan(op.get(Operation_.operationDate), startDay.toDate()),
b.isNull(op.get(Operation_.amount)))));
// ordering
q.orderBy(b.asc(op.get(Operation_.operationDate)), b.asc(op.get(Operation_.operationId)));
return getEm().createQuery(q).getResultList();
}
/**
* Creates an array of JPA predicates for networks list query. Add filter predicates for name and
* principal if required. 1) if name is specified adds 'name = ?' predicate 2) if name pattern is
* specified adds 'name like ?' predicate 3) if principal is user of key without ADMIN role adds
* predicate for filtering not assigned networks 4) if principal is key which has permissions only
* to specific networks adds 'network.id in (allowed_networks)' predicates
*
* @return array of above predicates
* @see {@link com.devicehive.service.NetworkService#list(String, String, String, boolean,
* Integer, Integer, HivePrincipal)}
*/
public static Predicate[] networkListPredicates(
CriteriaBuilder cb,
Root<Network> from,
Optional<String> nameOpt,
Optional<String> namePatternOpt,
Optional<HivePrincipal> principalOpt) {
List<Predicate> predicates = new LinkedList<>();
nameOpt.ifPresent(name -> predicates.add(cb.equal(from.get("name"), name)));
namePatternOpt.ifPresent(pattern -> predicates.add(cb.like(from.get("name"), pattern)));
principalOpt
.flatMap(
principal -> {
User user = principal.getUser();
if (user == null && principal.getKey() != null) {
user = principal.getKey().getUser();
}
return ofNullable(user);
})
.ifPresent(
user -> {
if (!user.isAdmin()) {
predicates.add(from.join("users").in(user));
}
});
principalOpt
.map(HivePrincipal::getKey)
.ifPresent(
key ->
predicates.add(
cb.or(networkPermissionsPredicates(cb, from, key.getPermissions()))));
return predicates.toArray(new Predicate[predicates.size()]);
}