@Override
protected List<Predicate> doGenerate(
CriteriaBuilder criteriaBuilder,
Root<?> entity,
String field,
Iterable<Expression<Object>> expressions) {
Expression[] expressionArray =
StreamSupport.stream(expressions.spliterator(), false).toArray(Expression[]::new);
return Arrays.asList(criteriaBuilder.not(entity.get(field).in(expressionArray)));
}
/**
* 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);
}
}
/** 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());
}
}
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;
}
