/** This test provides an example collection path using an LEFT OUTER JOIN */
@Test
public void testOuterJoin() {
log.info("*** testOuterJoin() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);
// select c.id, c.firstName, sale.amount
// from Clerk c
// LEFT JOIN c.sales sale
Root<Clerk> c = qdef.from(Clerk.class);
Join<Clerk, Sale> sale = c.join("sales", JoinType.LEFT);
qdef.select(cb.array(c.get("id"), c.get("firstName"), sale.get("amount")));
TypedQuery<Object[]> query = em.createQuery(qdef);
List<Object[]> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for (Object[] result : results) {
assertEquals("unexpected result length", 3, result.length);
Long id = (Long) result[0];
String name = (String) result[1];
BigDecimal amount = (BigDecimal) result[2];
log.info("clerk.id=" + id + ", clerk.firstName=" + name + ", amount=" + amount);
}
}
@Test
public void testArray() {
EntityManager em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
Customer c1 = new Customer();
c1.setId("c1");
c1.setAge(18);
c1.setName("Bob");
em.persist(c1);
em.getTransaction().commit();
em.close();
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
final CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> q = cb.createQuery(Object[].class);
Root<Customer> c = q.from(Customer.class);
q.select(cb.array(c.get(Customer_.name), c.get(Customer_.age)));
List<Object[]> result = em.createQuery(q).getResultList();
assertEquals(1, result.size());
assertEquals(c1.getName(), result.get(0)[0]);
assertEquals(c1.getAge(), result.get(0)[1]);
em.getTransaction().commit();
em.close();
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
em.createQuery("delete Customer").executeUpdate();
em.getTransaction().commit();
em.close();
}
/** This test method provides an example of using group by */
@Test
public void testGroupBy() {
log.info("*** testGroupBy() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);
Root<Clerk> c = qdef.from(Clerk.class);
Join<Clerk, Sale> s = c.join("sales", JoinType.LEFT);
// select c, COUNT(s) from Clerk c
// LEFT JOIN c.sales s
// GROUP BY c
qdef.select(cb.array(c, cb.count(s))).groupBy(c);
List<Object[]> results = em.createQuery(qdef).getResultList();
for (Object[] result : results) {
log.info("found=" + Arrays.toString(result));
}
assertEquals("unexpected number of rows", 3, results.size());
}
@Test
public void testIllegalArgumentExceptionBuildingSelectArrayWithSameAliases() {
EntityManager em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
final CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery criteria = builder.createQuery();
Root<Customer> customerRoot = criteria.from(Customer.class);
Path<String> namePath = customerRoot.get(Customer_.name);
Path<Integer> agePath = customerRoot.get(Customer_.age);
try {
CompoundSelection<Object[]> c = builder.array(namePath.alias("SAME"), agePath.alias("SAME"));
criteria.select(c);
fail(
"Attempt to define multi-select with same aliases should have thrown IllegalArgumentException");
} catch (IllegalArgumentException expected) {
}
em.getTransaction().commit();
em.close();
}
/**
* This test provides an example of navigating a path formed by a relationship. In this case the
* path used is a single element.
*/
@Test
public void testPathExpressions() {
log.info("*** testPathExpressions() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);
// select s.id, s.store.name from Sale s
Root<Sale> s = qdef.from(Sale.class);
qdef.select(cb.array(s.get("id"), s.get("store").get("name")));
TypedQuery<Object[]> query = em.createQuery(qdef);
List<Object[]> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for (Object[] result : results) {
assertEquals("unexpected result length", 2, result.length);
Long id = (Long) result[0];
String name = (String) result[1];
log.info("sale.id=" + id + ", sale.store.name=" + name);
}
}
/**
* This test demonstrates a query for multiple properties. In this version we will use a generic
* Object[] for the return type.
*/
@Test
public void testMultiSelectObjectArray() {
log.info("*** testMultiSelectObjectArray() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);
// select c.firstName, c.hireDate from Clerk c
Root<Clerk> c = qdef.from(Clerk.class);
qdef.select(cb.array(c.get("firstName"), c.get("hireDate")));
TypedQuery<Object[]> query = em.createQuery(qdef);
List<Object[]> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for (Object[] result : results) {
assertEquals("unexpected result length", 2, result.length);
String firstName = (String) result[0];
Date hireDate = (Date) result[1];
log.info("firstName=" + firstName + " hireDate=" + hireDate);
}
}