/** 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());
}
/** 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);
}
/**
* 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);
}
/**
* Get balance of the account for previous operations.
*
* @param day Get the balance for operation before this day to the opening of the account
* @return Balance of the account
*/
public BigDecimal getBalanceHistory(LocalDate day) {
CriteriaBuilder b = getBuilder();
// creating criteria
CriteriaQuery<BigDecimal> q = b.createQuery(BigDecimal.class);
Root<Operation> op = q.from(Operation.class);
q.select(b.sum(op.get(Operation_.amount)));
// adding restriction
q.where(b.lessThan(op.get(Operation_.operationDate), day.toDate()));
return getEm().createQuery(q).getSingleResult();
}
@Test
public void diff() {
EntityManager entityManager = factory.createEntityManager();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> query = builder.createQuery(Employee.class);
Root<Employee> root = query.from(Employee.class);
query.select(root);
query.where(builder.lessThan(builder.diff(root.get(Employee_.age), 10), 10));
List<Employee> employees = entityManager.createQuery(query).getResultList();
assertAndShow(4, employees);
}
public List<Movimentacao> pesquisarPorPeriodo(Calendar dataDe, Calendar dataAte) {
if (naoExiste(dataDe)) {
// Primeiro verificamos os campos que podemos usar
return new ArrayList<Movimentacao>();
}
if (naoExiste(dataAte)) {
// Primeiro verificamos os campos que podemos usar
return new ArrayList<Movimentacao>();
}
CriteriaBuilder cb = getEm().getCriteriaBuilder();
CriteriaQuery<Movimentacao> c = cb.createQuery(Movimentacao.class);
Root<Movimentacao> tabelaDeMovimentacao = c.from(Movimentacao.class);
c.select(tabelaDeMovimentacao);
List<Predicate> criteria = new ArrayList<Predicate>();
if (existe(dataDe)) {
ParameterExpression<String> p = cb.parameter(String.class, "data");
criteria.add(cb.greaterThan(tabelaDeMovimentacao.<String>get("data"), p));
}
if (existe(dataAte)) {
ParameterExpression<String> p = cb.parameter(String.class, "data");
criteria.add(cb.lessThan(tabelaDeMovimentacao.<String>get("data"), p));
}
if (criteria.size() == 0) {
throw new RuntimeException("no criteria");
} else if (criteria.size() == 1) {
c.where(criteria.get(0));
} else {
c.where(cb.and(criteria.toArray(new Predicate[0])));
}
TypedQuery<Movimentacao> q = getEm().createQuery(c);
if (existe(dataDe)) {
q.setParameter("data", dataDe);
}
if (existe(dataAte)) {
q.setParameter("data", dataAte);
}
return q.getResultList();
}
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();
}
protected Predicate buildPredicateWithOperator(Path<T> path, String name, String value) {
CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
Operator operator = Operator.fromString(name);
// perform operation, default operation is equal
if (operator == null) {
Path<T> attribute = path.get(name);
Object valueObject = convertStringValueToObject(attribute, value);
return criteriaBuilder.equal(attribute, valueObject);
} else {
switch (operator) {
case GT:
return criteriaBuilder.greaterThan((Expression) path, value);
case GTE:
return criteriaBuilder.greaterThanOrEqualTo((Expression) path, value);
case LT:
return criteriaBuilder.lessThan((Expression) path, value);
case LTE:
return criteriaBuilder.lessThanOrEqualTo((Expression) path, value);
case NE:
{
Object valueObject = convertStringValueToObject(path, value);
return criteriaBuilder.notEqual(path, valueObject);
}
case EQ:
{
Object valueObject = convertStringValueToObject(path, value);
return criteriaBuilder.equal(path, valueObject);
}
case EX:
return criteriaBuilder.like((Expression) path, value.replace('*', '%'));
default:
{
Path<T> attribute = path.get(name);
Object valueObject = convertStringValueToObject(attribute, value);
return criteriaBuilder.equal(attribute, valueObject);
}
}
}
}
/**
* 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();
}
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;
}
/*
* (non-Javadoc)
* @see com.github.dactiv.orm.core.spring.data.jpa.PredicateBuilder#build(javax.persistence.criteria.Path, java.lang.Object, javax.persistence.criteria.CriteriaBuilder)
*/
@SuppressWarnings({"unchecked", "rawtypes"})
public Predicate build(Path expression, Object value, CriteriaBuilder builder) {
return builder.lessThan(expression, (Comparable) value);
}
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()]);
}
