Chapter 73. JPA Criteria API

Figure 73.1. Boiler-plate code

CriteriaBuilder cb = em.getCriteriaBuilder();


//example-specific criteria API definition goes here

CriteriaQuery<T> qdef = ...


//repeated display loop eliminated

TypedQuery<T> query = em.createQuery(qdef);

List<T> objects = query.getResultList();

for(T o: objects) {

   log.info("found result:" + o);

}

Get CriteriaBuilder from EntityManager
Build example-specific query definition in CriteriaQuery using CriteriaBuilder
Execute Query/Print results

73.2. Simple Entity Query

Figure 73.2. Equivalent JPAQL

select c from Customer c

Figure 73.3. Criteria API Definition

CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);


Root<Customer> c = qdef.from(Customer.class);        

qdef.select(c);

"from"
- defines source of root query terms
- returns object leveraged in query body
"select" defines root query objects -- all path references must start from this set
no "where" clause indicates all entities are selected

Figure 73.4. In Programming Context

CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);


Root<Customer> c = qdef.from(Customer.class);        

qdef.select(c);


TypedQuery<Customer> query = em.createQuery(qdef);

List<Customer> results = query.getResultList();

Figure 73.5. Runtime Output

    select
        customer0_.CUSTOMER_ID as CUSTOMER1_3_,
        customer0_.FIRST_NAME as FIRST2_3_,
        customer0_.LAST_NAME as LAST3_3_ 
    from JPAQL_CUSTOMER customer0_
 -found result:firstName=cat, lastName=inhat
 -found result:firstName=thing, lastName=one
 -found result:firstName=thing, lastName=two

73.3. Non-Entity Query

Figure 73.6. Equivalent JPAQL

select c.lastName from Customer c

Figure 73.7. Criteria API Definition

CriteriaQuery<String> qdef = cb.createQuery(String.class);


Root<Customer> c = qdef.from(Customer.class);

qdef.select(c.<String>get("lastName"));

Allows return of simple property
c.get("lastName") is called a "path"
All paths based from root query terms (thus requirement for Root<Customer> c object)
Single path selects return typed list of values

Figure 73.8. In Programming Context

CriteriaQuery<String> qdef = cb.createQuery(String.class);


Root<Customer> c = qdef.from(Customer.class);

qdef.select(c.<String>get("lastName"));


TypedQuery<String> query = em.createQuery(qdef);

List<String> results = query.getResultList();

Query result is a List<String> because "c.lastName" is a String

Figure 73.9. Runtime Output

    select customer0_.LAST_NAME as col_0_0_ 
    from JPAQL_CUSTOMER customer0_
 -lastName=inhat
 -lastName=one
 -lastName=two

73.4. Multi-select Query

73.4.1. Multi-select Query with Object[]

Figure 73.10. Equivalent JPAQL

select c.firstName, c.hireDate from Clerk c

Figure 73.11. Criteria API Definition

CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);


Root<Clerk> c = qdef.from(Clerk.class);

qdef.select(cb.array(c.get("firstName"), c.get("hireDate")));

Select specifies multiple terms within array()
Terms are expressed thru a path expression
Terms must be based off paths from root terms in the FROM (or JOIN) clause -- thus why Root<Clerk> c was retained from cb.from() call

Figure 73.12. In Programming Context

CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);


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);

}

Query defined to return elements of select in Object[]

Figure 73.13. Runtime Output

    select
        clerk0_.FIRST_NAME as col_0_0_,
        clerk0_.HIRE_DATE as col_1_0_ 
    from JPAQL_CLERK clerk0_
 -firstName=Manny hireDate=1970-01-01
 -firstName=Moe hireDate=1970-01-01
 -firstName=Jack hireDate=1973-03-01

73.4.2. Multi-select Query with Tuple

Figure 73.14. Equivalent JPAQL

select c.firstName as firstName, c.hireDate as hireDate from Clerk c

Figure 73.15. Criteria API Definition

CriteriaQuery<Tuple> qdef = cb.createTupleQuery();


Root<Clerk> c = qdef.from(Clerk.class);

qdef.select(cb.tuple(

        c.get("firstName").alias("firstName"), 

        c.get("hireDate").alias("hireDate")));

Aliases may be assigned to select terms for named-access to results

Figure 73.16. In Programming Context

CriteriaQuery<Tuple> qdef = cb.createTupleQuery();


Root<Clerk> c = qdef.from(Clerk.class);

qdef.select(cb.tuple(

        c.get("firstName").alias("firstName"), 

        c.get("hireDate").alias("hireDate")));


TypedQuery<Tuple> query = em.createQuery(qdef);

List<Tuple> results = query.getResultList();

assertTrue("no results", results.size() > 0);

for(Tuple result : results) {

    assertEquals("unexpected result length", 2, result.getElements().size());

    String firstName = result.get("firstName", String.class);

    Date hireDate = result.get("hireDate", Date.class);

    log.info("firstName=" + firstName + " hireDate=" + hireDate);

}

Query defined to return instances of Tuple class
Tuples provide access using
- get(index) - simular to Object[]
- get(index, Class<T> resultType) - typed access by index
- get(alias) - access by alias
- get(alias, Class<T> resultType) - typed access by alias
- getElements() - access thru collection interface

Figure 73.17. Runtime Output

    select
        clerk0_.FIRST_NAME as col_0_0_,
        clerk0_.HIRE_DATE as col_1_0_ 
    from JPAQL_CLERK clerk0_
 -firstName=Manny hireDate=1970-01-01
 -firstName=Moe hireDate=1970-01-01
 -firstName=Jack hireDate=1973-03-01

73.4.3. Multi-select Query with Constructor

Figure 73.18. Equivalent JPAQL

select new ejava.jpa.examples.query.Receipt(s.id,s.buyerId,s.date, s.amount)
from Sale s

Figure 73.19. Criteria API Definition

CriteriaQuery<Receipt> qdef = cb.createQuery(Receipt.class);


Root<Sale> s = qdef.from(Sale.class);

qdef.select(cb.construct(

                Receipt.class, 

                s.get("id"), 

                s.get("buyerId"),

                s.get("date"),

                s.get("amount")));

Individual elements of select() are matched up against class constructor

Figure 73.20. In Programming Context

CriteriaQuery<Receipt> qdef = cb.createQuery(Receipt.class);


Root<Sale> s = qdef.from(Sale.class);

qdef.select(cb.construct(

                Receipt.class, 

                s.get("id"), 

                s.get("buyerId"),

                s.get("date"),

                s.get("amount")));


TypedQuery<Receipt> query = em.createQuery(qdef);

List<Receipt> results = query.getResultList();

assertTrue("no results", results.size() > 0);

for(Receipt receipt : results) {

    assertNotNull("no receipt", receipt);

    log.info("receipt=" + receipt);

}

Constructed class may be simple POJO -- no need to be an entity
Instances are not managed
Suitable for use as Data Transfer Objects (DTOs)

Figure 73.21. Runtime Output

    select
        sale0_.SALE_ID as col_0_0_,
        sale0_.BUYER_ID as col_1_0_,
        sale0_.date as col_2_0_,
        sale0_.amount as col_3_0_ 
    from JPAQL_SALE sale0_
 -receipt=sale=1, customer=1, date=1998-04-10 10:13:35, amount=$100.00
 -receipt=sale=2, customer=2, date=1999-06-11 14:15:10, amount=$150.00

73.5. Path Expressions

73.5.1. Single Element Path Expressions

Figure 73.22. Equivalent JPAQL

select s.id, s.store.name from Sale s

Figure 73.23. Criteria API Definition

CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);


Root<Sale> s = qdef.from(Sale.class);

qdef.select(cb.array(s.get("id"),

                     s.get("store").get("name")));

All paths based off root-level FROM (or JOIN) terms
Paths use call chaining to change contexts
Paths -- used this way -- must always express a single element. Must use JOINs for paths involving collections
All paths based off root-level FROM (or JOIN) terms

Figure 73.24. In Programming Context

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);


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);

}

Figure 73.25. Runtime Output

    select
        sale0_.SALE_ID as col_0_0_,
        store1_.name as col_1_0_ 
    from JPAQL_SALE sale0_, ORMQL_STORE store1_ 
    where sale0_.SALE_STORE=store1_.STORE_ID
 -sale.id=1, sale.store.name=Big Al's
 -sale.id=2, sale.store.name=Big Al's

Automatic INNER JOIN formed between Sale and Store because of the cross-entity path

73.5.2. Collection Element Path Expressions

73.5.2.1. INNER JOIN Collection Path Expressions

Figure 73.26. Equivalent JPAQL

select sale.date from Clerk c JOIN c.sales sale

Figure 73.27. Criteria API Definition

CriteriaQuery<Date> qdef = cb.createQuery(Date.class);


Root<Clerk> c = qdef.from(Clerk.class);

Join<Clerk, Sale> sale = c.join("sales", JoinType.INNER);

qdef.select(sale.<Date>get("date"));

Collection is brought in as a root term of the query through a JOIN expression
JOINs will match entities by their defined primary/foreign keys
INNER JOIN will return only those entities where there is a match
INNER JOIN is default JoinType when none specified

Figure 73.28. Runtime Output

    select sale2_.date as col_0_0_ 
    from JPAQL_CLERK clerk0_ 
    inner join JPAQL_SALE_CLERK_LINK sales1_ on clerk0_.CLERK_ID=sales1_.CLERK_ID 
    inner join JPAQL_SALE sale2_ on sales1_.SALE_ID=sale2_.SALE_ID

 -found result:1998-04-10 10:13:35.0
 -found result:1999-06-11 14:15:10.0
 -found result:1999-06-11 14:15:10.0

(Many-to-Many) Link table used during JOIN
Tables automatically joined on primary keys
Only Sales sold by our Clerks are returned

73.5.2.2. LEFT OUTER JOIN Collection Path Expressions

Figure 73.29. Equivalent JPAQL

select c.id, c.firstName, sale.amount 
from Clerk c 
LEFT JOIN c.sales sale

Figure 73.30. Criteria API Definition

CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);


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")));

LEFT OUTER JOIN will return root with or without related entities

Figure 73.31. Runtime Output

    select
        clerk0_.CLERK_ID as col_0_0_,
        clerk0_.FIRST_NAME as col_1_0_,
        sale2_.amount as col_2_0_ 
    from JPAQL_CLERK clerk0_ 
    left outer join JPAQL_SALE_CLERK_LINK sales1_ on clerk0_.CLERK_ID=sales1_.CLERK_ID 
    left outer join JPAQL_SALE sale2_ on sales1_.SALE_ID=sale2_.SALE_ID
 -clerk.id=1, clerk.firstName=Manny, amount=100.00
 -clerk.id=1, clerk.firstName=Manny, amount=150.00
 -clerk.id=2, clerk.firstName=Moe, amount=150.00
 -clerk.id=3, clerk.firstName=Jack, amount=null

(Many-to-Many) Link table used during JOIN
Tables automatically joined on primary keys
All clerks, with or without a Sale, are returned

73.5.2.3. Explicit Collection Path Expressions

Figure 73.32. Equivalent JPAQL

select c from Sale s, Customer c 
where c.id = s.buyerId

Figure 73.33. Criteria API Definition

CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);


Root<Sale> s = qdef.from(Sale.class);

Root<Customer> c = qdef.from(Customer.class);

qdef.select(c)

    .where(cb.equal(c.get("id"), s.get("buyerId")));

Permits JOINs without relationship in entity model

Figure 73.34. Runtime Output

    select
        customer1_.CUSTOMER_ID as CUSTOMER1_3_,
        customer1_.FIRST_NAME as FIRST2_3_,
        customer1_.LAST_NAME as LAST3_3_ 
    from JPAQL_SALE sale0_ cross 
    join JPAQL_CUSTOMER customer1_ 
    where customer1_.CUSTOMER_ID=sale0_.BUYER_ID
        
 -found result:firstName=cat, lastName=inhat
 -found result:firstName=thing, lastName=one

Returns all Customers that are identified by a Sale

73.6. Eager Fetching through JOINs

73.6.1. Lazy Fetch Problem

Figure 73.35. Equivalent JPAQL

select s from Store s JOIN s.sales
where s.name='Big Al''s'

Figure 73.36. Criteria API Definition

CriteriaQuery<Store> qdef = cb.createQuery(Store.class);


Root<Store> s = qdef.from(Store.class);

s.join("sales");

qdef.select(s)

    .where(cb.equal(s.get("name"), "Big Al's"));

A normal JOIN (implicit or explicit) may honor the fetch=LAZY property setting of the relation
Can be exactly what is desired
Can also cause problems or extra work if not desired

Figure 73.37. In Programming Context

@Entity @Table(name="ORMQL_STORE")

public class Store {

...

    @OneToMany(mappedBy="store", 

            cascade={CascadeType.REMOVE}, 

            fetch=FetchType.LAZY)

    private List<Sale> sales = new ArrayList<Sale>();

Sales are lazily fetched when obtaining Store

Figure 73.38. In Programming Context (con.t)

CriteriaBuilder cb = em2.getCriteriaBuilder();

CriteriaQuery<Store> qdef = cb.createQuery(Store.class);


Root<Store> s = qdef.from(Store.class);

s.join("sales");

qdef.select(s)

    .where(cb.equal(s.get("name"), "Big Al's"));


Store store = em2.createQuery(qdef).getSingleResult();

em2.close();

try {

    store.getSales().get(0).getAmount();

    fail("did not trigger lazy initialization exception");

} catch (LazyInitializationException expected) {

    log.info("caught expected exception:" + expected);

}

Accessing the Sale properties causes a LazyInitializationException when persistence context no longer active or accessible

Figure 73.39. Runtime Output

    select
        store0_.STORE_ID as STORE1_0_,
        store0_.name as name0_ 
    from ORMQL_STORE store0_ 
    inner join JPAQL_SALE sales1_ on store0_.STORE_ID=sales1_.SALE_STORE 
    where store0_.name=? limit ?
 -caught expected exception:org.hibernate.LazyInitializationException: 
     failed to lazily initialize a collection of role: 
     ejava.jpa.examples.query.Store.sales, no session or session was closed

One Row per Parent is Returned for fetch=LAZY

Note that only a single row is required to be returned from the database for a fetch=LAZY relation. Although it requires more queries to the database, it eliminates duplicate parent information for each child row and can eliminate the follow-on query all together when not accessed.

73.6.2. Adding Eager Fetch during Query

Figure 73.40. Equivalent JPAQL

select s from Store s JOIN FETCH s.sales
where s.name='Big Al''s'

Figure 73.41. Criteria API Definition

CriteriaQuery<Store> qdef = cb.createQuery(Store.class);


Root<Store> s = qdef.from(Store.class);

s.fetch("sales");

qdef.select(s)

    .where(cb.equal(s.get("name"), "Big Al's"));

A JOIN FETCH used to eager load related entities as side-effect of query
Can be used as substitute for fetch=EAGER specification on relation

Figure 73.42. Runtime Output

    select
        store0_.STORE_ID as STORE1_0_0_,
        sales1_.SALE_ID as SALE1_1_1_,
        store0_.name as name0_0_,
        sales1_.amount as amount1_1_,
        sales1_.BUYER_ID as BUYER3_1_1_,
        sales1_.date as date1_1_,
        sales1_.SALE_STORE as SALE5_1_1_,
        sales1_.SALE_STORE as SALE5_0_0__,
        sales1_.SALE_ID as SALE1_0__ 
    from ORMQL_STORE store0_ 
    inner join JPAQL_SALE sales1_ on store0_.STORE_ID=sales1_.SALE_STORE 
    where store0_.name=?

Sales are eagerly fetched when obtaining Store

Parent Rows Repeated for each Child for fetch=EAGER

Note that adding JOIN FETCH to parent query causes the parent rows to be repeated for each eagerly loaded child row and eliminated by the provider. This requires fewer database queries but results in more (and redundant) data to be returned from the query.

73.7. Distinct Results

Figure 73.43. Equivalent JPAQL

select DISTINCT c.lastName from Customer c

Figure 73.44. Criteria API Definition

CriteriaQuery<String> qdef = cb.createQuery(String.class);


Root<Customer> c = qdef.from(Customer.class);

qdef.select(c.<String>get("lastName"))

    .distinct(true);

Limits output to unique value combinations

Figure 73.45. Runtime Output

    select distinct customer0_.LAST_NAME as col_0_0_ 
    from JPAQL_CUSTOMER customer0_
 -found result:two
 -found result:inhat
 -found result:one

Figure 73.46. Equivalent JPAQL

select DISTINCT c.firstName from Customer c

Figure 73.47. Criteria API Definition

CriteriaQuery<String> qdef = cb.createQuery(String.class);


Root<Customer> c = qdef.from(Customer.class);

qdef.select(c.<String>get("firstName"))

    .distinct(true);

Figure 73.48. Runtime Output

    select distinct customer0_.FIRST_NAME as col_0_0_ 
    from JPAQL_CUSTOMER customer0_
 -found result:cat
 -found result:thing

73.8. Summary

Entity Queries
Non-Entity (value) and Multi-select Queries
Path Expressions (Root and Join)
Join Fetch
Distinct Results