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);
}
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);
}
private List<Organization> getTopOrgs() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Organization> query = cb.createQuery(Organization.class);
Root<Organization> root = query.from(Organization.class);
query.where(cb.isNull(root.get(Organization_.parent)));
return em.createQuery(query).getResultList();
}
@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);
}
@Test
public void isNull() {
EntityManager entityManager = factory.createEntityManager();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Country> query = builder.createQuery(Country.class);
Root<Country> root = query.from(Country.class);
query.select(root);
query.where(builder.isNull(root.get(Country_.name)));
List<Country> countries = entityManager.createQuery(query).getResultList();
assertAndShow(0, countries);
}
/**
* Retrieve all planned operations with no amount before a date (included).
*
* @param day Date from which retrieve the old operations.
* @return List of old operations
*/
public List<Operation> getOldPlannedOps(LocalDate day) {
CriteriaBuilder b = getBuilder();
// creating criteria
CriteriaQuery<Operation> q = b.createQuery(Operation.class);
Root<Operation> op = q.from(Operation.class);
q.select(op);
// adding restriction
q.where(
b.and(
b.isNull(op.get(Operation_.amount)),
b.lessThanOrEqualTo(op.get(Operation_.operationDate), day.toDate())));
return getEm().createQuery(q).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;
}
}
public List<NsiV2Message> findUnansweredRequestsSentBefore(DateTime sentBefore) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<NsiV2Message> query = cb.createQuery(NsiV2Message.class);
Root<NsiV2Message> root = query.from(NsiV2Message.class);
query
.where(
cb.and(
cb.lessThan(root.get(NsiV2Message_.lastSentAt), sentBefore),
cb.equal(root.get(NsiV2Message_.role), Role.REQUESTER),
cb.equal(root.get(NsiV2Message_.type), Type.REQUEST),
cb.isNull(root.get(NsiV2Message_.timedOutAt)),
cb.isNull(root.get(NsiV2Message_.asyncReply))))
.orderBy(cb.asc(root.get(NsiV2Message_.lastSentAt)), cb.asc(root.get(NsiV2Message_.id)));
return entityManager.createQuery(query).getResultList();
}
static Application findApplication(String name, String version) {
EntityManager em = Stripersist.getEntityManager();
if (name != null) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery q = cb.createQuery(Application.class);
Root<Application> root = q.from(Application.class);
Predicate namePredicate = cb.equal(root.get("name"), name);
Predicate versionPredicate =
version != null ? cb.equal(root.get("version"), version) : cb.isNull(root.get("version"));
q.where(cb.and(namePredicate, versionPredicate));
try {
return (Application) em.createQuery(q).getSingleResult();
} catch (NoResultException nre) {
}
}
return null;
}
public List<Subject> listByEducationType(EducationType educationType) {
EntityManager entityManager = getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Subject> criteria = criteriaBuilder.createQuery(Subject.class);
Root<Subject> root = criteria.from(Subject.class);
criteria.select(root);
Predicate educationTypePredicate;
if (educationType != null)
educationTypePredicate =
criteriaBuilder.equal(root.get(Subject_.educationType), educationType);
else educationTypePredicate = criteriaBuilder.isNull(root.get(Subject_.educationType));
criteria.where(
criteriaBuilder.and(
criteriaBuilder.equal(root.get(Subject_.archived), Boolean.FALSE),
educationTypePredicate));
return entityManager.createQuery(criteria).getResultList();
}
/**
* 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();
}
/** This test provides a demonstration of testing for a null value. */
@Test
public void testIsNull() {
log.info("*** testIsNull() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);
// select s from Sale s
// where s.store IS NULL
Root<Sale> s = qdef.from(Sale.class);
qdef.select(s).where(cb.isNull(s.get("store")));
// .where(cb.equal(s.get("store"), cb.nullLiteral(Store.class)));
List<Sale> sales = em.createQuery(qdef).getResultList();
for (Sale result : sales) {
log.info("found=" + result);
}
assertEquals("unexpected number of rows", 0, sales.size());
}
{
CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);
// select s from Sale s
// where s.store IS NOT NULL
Root<Sale> s = qdef.from(Sale.class);
qdef.select(s).where(cb.isNotNull(s.get("store")));
// .where(cb.not(cb.equal(s.get("store"), cb.nullLiteral(Store.class))));
List<Sale> sales = em.createQuery(qdef).getResultList();
for (Sale result : sales) {
log.info("found=" + result);
}
assertEquals("unexpected number of rows", 2, sales.size());
}
}
public static Predicate createWhereQuery(CriteriaBuilder cb, Root<?> r, QueryParameters q) {
Predicate predicate = cb.conjunction();
for (QueryFilter f : q.getFilters()) {
Predicate np = null;
try {
Path<String> stringField = getCriteraField(f.getField(), r);
Path<Date> dateField = getCriteraField(f.getField(), r);
switch (f.getOperation()) {
case EQ:
if (f.getDateValue() != null) {
np = cb.equal(stringField, f.getDateValue());
} else {
np = cb.equal(stringField, getValueForPath(stringField, f.getValue()));
}
break;
case EQIC:
if (f.getDateValue() != null) {
np = cb.equal(stringField, f.getDateValue());
} else if (f.getValue() != null) {
np = cb.equal(cb.lower(stringField), f.getValue().toLowerCase());
}
break;
case NEQ:
if (f.getDateValue() != null) {
np = cb.notEqual(stringField, f.getDateValue());
} else {
np = cb.notEqual(stringField, getValueForPath(stringField, f.getValue()));
}
break;
case NEQIC:
if (f.getDateValue() != null) {
np = cb.notEqual(stringField, f.getDateValue());
} else if (f.getValue() != null) {
np = cb.notEqual(cb.lower(stringField), f.getValue().toLowerCase());
}
break;
case LIKE:
np = cb.like(stringField, f.getValue());
break;
case LIKEIC:
np = cb.like(cb.lower(stringField), f.getValue().toLowerCase());
break;
case GT:
if (f.getDateValue() != null) {
np = cb.greaterThan(dateField, f.getDateValue());
} else {
np = cb.greaterThan(stringField, f.getValue());
}
break;
case GTE:
if (f.getDateValue() != null) {
np = cb.greaterThanOrEqualTo(dateField, f.getDateValue());
} else {
np = cb.greaterThanOrEqualTo(stringField, f.getValue());
}
break;
case LT:
if (f.getDateValue() != null) {
np = cb.lessThan(dateField, f.getDateValue());
} else {
np = cb.lessThan(stringField, f.getValue());
}
break;
case LTE:
if (f.getDateValue() != null) {
np = cb.lessThanOrEqualTo(dateField, f.getDateValue());
} else {
np = cb.lessThanOrEqualTo(stringField, f.getValue());
}
break;
case IN:
np =
stringField.in(
f.getValues()
.stream()
.map(s -> getValueForPath(stringField, s))
.collect(Collectors.toList()));
break;
case INIC:
np =
cb.lower(stringField)
.in(
f.getValues()
.stream()
.map(String::toLowerCase)
.collect(Collectors.toList()));
break;
case NIN:
np =
cb.not(
stringField.in(
f.getValues()
.stream()
.map(s -> getValueForPath(stringField, s))
.collect(Collectors.toList())));
break;
case NINIC:
np =
cb.not(
cb.lower(stringField)
.in(
f.getValues()
.stream()
.map(String::toLowerCase)
.collect(Collectors.toList())));
break;
case ISNULL:
np = cb.isNull(stringField);
break;
case ISNOTNULL:
np = cb.isNotNull(stringField);
break;
}
} catch (IllegalArgumentException e) {
throw new NoSuchEntityFieldException(
e.getMessage(), f.getField(), r.getJavaType().getSimpleName());
}
predicate = cb.and(predicate, np);
}
return predicate;
}