/** 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);
}
/** This test demonstrates the use of like in where clauses */
@Test
public void testLike() {
log.info("*** testLike() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like 'M%'
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c).where(cb.like(c.<String>get("firstName"), "M%"));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 2, rows);
}
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like :firstName
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c)
.where(cb.like(c.<String>get("firstName"), cb.parameter(String.class, "firstName")));
TypedQuery<Clerk> query = em.createQuery(qdef).setParameter("firstName", "M%");
List<Clerk> results = query.getResultList();
for (Object o : results) {
log.info("found result:" + o);
}
assertEquals("unexpected number of rows", 2, results.size());
}
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like concat(:firstName,'%')
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c)
.where(
cb.like(
c.<String>get("firstName"),
cb.concat(cb.parameter(String.class, "firstName"), "%")));
TypedQuery<Clerk> query = em.createQuery(qdef).setParameter("firstName", "M");
List<Clerk> results = query.getResultList();
for (Object o : results) {
log.info("found result:" + o);
}
assertEquals("unexpected number of rows", 2, results.size());
}
{
CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
// select c from Clerk c
// where c.firstName like '_anny'
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(c).where(cb.like(c.<String>get("firstName"), "_anny"));
TypedQuery<Clerk> query = em.createQuery(qdef);
List<Clerk> results = query.getResultList();
for (Object o : results) {
log.info("found result:" + o);
}
assertEquals("unexpected number of rows", 1, results.size());
}
}