/** 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());
}
@Test
public void testQuotientAndMultiply() {
EntityManager em = getOrCreateEntityManager();
em.getTransaction().begin();
CriteriaQuery<Number> criteria = builder.createQuery(Number.class);
criteria.from(Product.class);
criteria.select(
builder.quot(
builder.prod(
builder.literal(BigDecimal.valueOf(10.0)),
builder.literal(BigDecimal.valueOf(5.0))),
BigDecimal.valueOf(2.0)));
Number result = em.createQuery(criteria).getSingleResult();
assertEquals(25.0d, result.doubleValue(), 0.1d);
criteria.select(
builder.prod(
builder.quot(
builder.literal(BigDecimal.valueOf(10.0)),
builder.literal(BigDecimal.valueOf(5.0))),
BigDecimal.valueOf(2.0)));
result = em.createQuery(criteria).getSingleResult();
assertEquals(4.0d, result.doubleValue(), 0.1d);
em.getTransaction().commit();
em.close();
}
@Test
public void testDiff() {
EntityManager em = getOrCreateEntityManager();
em.getTransaction().begin();
CriteriaQuery<Integer> criteria = builder.createQuery(Integer.class);
criteria.from(Product.class);
criteria.select(builder.diff(builder.literal(5), builder.literal(2)));
Integer result = em.createQuery(criteria).getSingleResult();
assertEquals(Integer.valueOf(3), result);
em.getTransaction().commit();
em.close();
}
/** @author Romain Wurtz <*****@*****.**> */
public List<Predicate> createPredicates(
Root<User> from, CriteriaBuilder criteriaBuilder, Map<String, String> filters) {
List<Predicate> predicates = new ArrayList<Predicate>();
for (Iterator<String> it = filters.keySet().iterator(); it.hasNext(); ) {
String filterProperty = it.next();
String filterValue = filters.get(filterProperty);
Expression<String> literal = criteriaBuilder.literal("%" + (String) filterValue + "%");
predicates.add(criteriaBuilder.like(from.<String>get(filterProperty), literal));
}
return predicates;
}
/** This test method demonstrates several date functions */
@Test
@SuppressWarnings({"rawtypes", "unchecked"})
public void testStringFunctions() {
log.info("*** testStringFunctions() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery qdef = cb.createQuery();
Root<Customer> c = qdef.from(Customer.class);
// select c from Customer c
// where c.firstName='CAT'
qdef.select(c).where(cb.equal(c.get("firstName"), "CAT"));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 0, rows);
// select c from Customer c
// where c.firstName=LOWER('CAT')"
qdef.select(c).where(cb.equal(c.get("firstName"), cb.lower(cb.literal("CAT"))));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 1, rows);
// select UPPER(c.firstName) from Customer c
// where c.firstName=LOWER('CAT')
qdef.select(cb.upper(c.<String>get("firstName")))
.where(cb.equal(c.get("firstName"), cb.lower(cb.literal("CAT"))));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 1, rows);
// TODO: determine why SQL generated without quotes here with H2Dialect
/*
Caused by: org.h2.jdbc.JdbcSQLException: Column "C" not found; SQL statement:
select trim(LEADING c from customer0_.FIRST_NAME) as col_0_0_ from JPAQL_CUSTOMER customer0_
where customer0_.FIRST_NAME=?
*/
// select TRIM(LEADING 'c' FROM c.firstName) from Customer c
// where c.firstName='cat')
qdef.select(cb.trim(Trimspec.LEADING, 'c', c.<String>get("firstName")))
.where(cb.equal(c.get("firstName"), "cat"));
// List<String> result = executeQuery(qdef);
// assertEquals("unexpected number of rows", 1, rows);
// assertEquals("unexpected value", "at", result.get(0));
// select c from Customer c
// where CONCAT(CONCAT(c.firstName,' '),c.lastName) ='cat inhat')
qdef.select(c)
.where(
cb.equal(
cb.concat(cb.concat(c.<String>get("firstName"), " "), c.<String>get("lastName")),
"cat inhat"));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows:" + rows, 1, rows);
// select c from Customer c
// where LENGTH(c.firstName) = 3
qdef.select(c).where(cb.equal(cb.length(c.<String>get("firstName")), 3));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows:" + rows, 1, rows);
// select c from Customer c " +
// where LOCATE('cat',c.firstName,2) > 0",
qdef.select(c).where(cb.greaterThan(cb.locate(c.<String>get("firstName"), "cat", 2), 0));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows:" + rows, 0, rows);
// select c from Customer c
// where LOCATE('at',c.firstName,2) > 1
qdef.select(c).where(cb.greaterThan(cb.locate(c.<String>get("firstName"), "at", 2), 1));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows:" + rows, 1, rows);
// select SUBSTRING(c.firstName,2,2) from Customer c " +
// where c.firstName = 'cat'",
qdef.select(cb.substring(c.<String>get("firstName"), 2, 2))
.where(cb.equal(c.get("firstName"), "cat"));
List<String> result = executeQuery(qdef);
assertEquals("unexpected number of rows", 1, rows);
assertEquals("unexpected value", "at", result.get(0));
// select c from Customer c
// where SUBSTRING(c.firstName,2,2) = 'at'
qdef.select(c).where(cb.equal(cb.substring(c.<String>get("firstName"), 2, 2), "at"));
rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows:" + rows, 1, rows);
}
