private static <D extends Comparable<? super D>, E> Predicate buildRangePredicate(
Range<E, D> range, Root<E> root, CriteriaBuilder builder) {
if (range.isBetween()) {
return builder.between(root.get(range.getField()), range.getFrom(), range.getTo());
} else if (range.isFromSet()) {
return builder.greaterThanOrEqualTo(root.get(range.getField()), range.getFrom());
} else if (range.isToSet()) {
return builder.lessThanOrEqualTo(root.get(range.getField()), range.getTo());
}
return null;
}
@Test
public void between() {
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.between(builder.length(root.get(Country_.name)), 5, 10));
List<Country> countries = entityManager.createQuery(query).getResultList();
assertAndShow(10, countries);
}
/** This test provides an example of using between condition */
@Test
public void testBetween() {
log.info("*** testBetween() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);
// select s from Sale s
// where s.amount BETWEEN :low AND :high"
Root<Sale> s = qdef.from(Sale.class);
qdef.select(s)
.where(
cb.between(
s.<BigDecimal>get("amount"), new BigDecimal(90.00), new BigDecimal(110.00)));
List<Sale> sales = em.createQuery(qdef).getResultList();
for (Sale result : sales) {
log.info("found=" + result);
}
assertEquals("unexpected number of rows", 1, sales.size());
}
{
CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);
// select s from Sale s
// where s.amount NOT BETWEEN :low AND :high"
Root<Sale> s = qdef.from(Sale.class);
qdef.select(s)
.where(
cb.not(
cb.between(
s.<BigDecimal>get("amount"), new BigDecimal(90.00), new BigDecimal(110.00))));
List<Sale> sales = em.createQuery(qdef).getResultList();
for (Sale result : sales) {
log.info("found=" + result);
}
assertEquals("unexpected number of rows", 1, sales.size());
}
}
@Override
public List<Product> getProductsByConditions(
String categoryContain, String nameContain, Float priceFrom, Float priceBefore) {
openConnection();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> cq = cb.createQuery(Product.class);
Root<Product> productRoot = cq.from(Product.class);
List<Predicate> predicates = new ArrayList<Predicate>();
if (categoryContain != null) {
if (!categoryContain.equals("")) {
Expression<String> catName = productRoot.get("cat").get("name");
Predicate catNameContainPredicate = cb.like(cb.lower(catName), "%" + categoryContain + "%");
predicates.add(catNameContainPredicate);
}
}
if (nameContain != null) {
if (!nameContain.equals("")) {
Expression<String> productName = productRoot.get("name");
Predicate nameContainPredicate = cb.like(cb.lower(productName), "%" + nameContain + "%");
predicates.add(nameContainPredicate);
}
}
Expression<Float> price = productRoot.get("price");
if (priceBefore != null) {
Predicate pricePredicate = cb.between(price, priceFrom, priceBefore);
predicates.add(pricePredicate);
}
if (priceBefore == null) {
Predicate pricePredicate = cb.gt(price, priceFrom);
predicates.add(pricePredicate);
}
cq.select(productRoot);
if (predicates.size() != 0) {
Predicate[] predicatesArray = new Predicate[predicates.size()];
cq.where(predicates.toArray(predicatesArray));
}
TypedQuery<Product> productTypedQuery = entityManager.createQuery(cq);
List<Product> productList = productTypedQuery.getResultList();
closeConnection();
return DbManagerPersistence.filterProducts(productList, nameContain, categoryContain);
}
/**
* 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;
}
}
/**
* 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();
}