Chapter 7. Criteria Where Clauses

Figure 7.1. Equivalent JPAQL

select c from Customer c 
where c.firstName='cat'

Figure 7.2. Criteria API Definition

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


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

qdef.select(c)

    .where(cb.equal(c.get("firstName"), "cat"));

Return entities where there is an equality match

Figure 7.3. 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_ 
    where customer0_.FIRST_NAME=?
 -found result:firstName=cat, lastName=inhat

Figure 7.4. Equivalent JPAQL

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

JPAQL requires special characters to be escaped

Figure 7.5. Criteria API Definition

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


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

qdef.select(s)

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

Literal values automatically escaped

Figure 7.6. Runtime Output

    select
        store0_.STORE_ID as STORE1_0_,
        store0_.name as name0_ 
    from ORMQL_STORE store0_ 
    where store0_.name=?
...
 -found result:name=Big Al's, sales(2)={1, 2, }

7.2. Like Test

7.2.1. Like Test Literal

Figure 7.7. Equivalent JPAQL

select c from Clerk c 
where c.firstName like 'M%'

Figure 7.8. Criteria API Definition

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


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

qdef.select(c)

    .where(cb.like(c.<String>get("firstName"), "M%"));

Figure 7.9. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where clerk0_.FIRST_NAME like ?
...
 -found result:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }
 -found result:firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }

7.2.2. Like Test Literal Parameter

Figure 7.10. Equivalent JPAQL

select c from Clerk c
where c.firstName like :firstName

Figure 7.11. Criteria API Definition

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


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

qdef.select(c)

    .where(cb.like(c.<String>get("firstName"), 

                   cb.parameter(String.class, "firstName")));

Figure 7.12. In Programming Context

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


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

Figure 7.13. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where clerk0_.FIRST_NAME like ?
 -found result:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }
 -found result:firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }

7.2.3. Like Test Concatenated String

Figure 7.14. Equivalent JPAQL

select c from Clerk c
where c.firstName like concat(:firstName,'%')

Figure 7.15. Criteria API Definition

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


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

qdef.select(c)

    .where(cb.like(c.<String>get("firstName"),

                   cb.concat(cb.parameter(String.class, "firstName"), "%")));

Figure 7.16. In Programming Context

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


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

Figure 7.17. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where clerk0_.FIRST_NAME like (?||?)
 -found result:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }
 -found result:firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }

7.2.4. Like Test Single Character Wildcard

Figure 7.18. Equivalent JPAQL

select c from Clerk c
where c.firstName like '_anny'

Figure 7.19. Criteria API Definition

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


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

qdef.select(c)

    .where(cb.like(c.<String>get("firstName"),"_anny"));

Figure 7.20. In Programming Context

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


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

Figure 7.21. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where clerk0_.FIRST_NAME like ?

 -found result:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }

7.3. Formulas

Figure 7.22. Equivalent JPAQL

select s from Sale s 
where (s.amount * :tax) > :amount

Figure 7.23. Criteria API Definition

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


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

qdef.select(cb.count(s))

    .where(cb.greaterThan(

        cb.prod(s.<BigDecimal>get("amount"), cb.parameter(BigDecimal.class, "tax")), 

        new BigDecimal(10.0)));

Figure 7.24. In Programming Context

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


//select count(s) from Sale s 

//where (s.amount * :tax) > :amount"

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

qdef.select(cb.count(s))

    .where(cb.greaterThan(

        cb.prod(s.<BigDecimal>get("amount"), cb.parameter(BigDecimal.class, "tax")), 

        new BigDecimal(10.0)));

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

        

//keep raising taxes until somebody pays $10.00 in tax

double tax = 0.05;

for (;query.setParameter("tax", new BigDecimal(tax))

           .getSingleResult().intValue()==0;

      tax += 0.01) {

    log.debug("tax=" + NumberFormat.getPercentInstance().format(tax));

}

log.info("raise taxes to: " + NumberFormat.getPercentInstance().format(tax));

Figure 7.25. Runtime Output

    select count(*) as col_0_0_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount*?>10 limit ?
 -tax=5%
    select count(*) as col_0_0_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount*?>10 limit ?
 -tax=6%
    select count(*) as col_0_0_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount*?>10 limit ?
 -raise taxes to: 7%

7.4. Logic Operators

Figure 7.26. Equivalent JPAQL

select c from Customer c
where (c.firstName='cat' AND c.lastName='inhat')
    OR c.firstName='thing'

Figure 7.27. Criteria API Definition

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


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

Figure 7.28. 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_ 
    where customer0_.FIRST_NAME=? and customer0_.LAST_NAME=? or customer0_.FIRST_NAME=?
 -found result:firstName=cat, lastName=inhat
 -found result:firstName=thing, lastName=one
 -found result:firstName=thing, lastName=two

Figure 7.29. Equivalent JPAQL

select c from Customer c 
where (NOT (c.firstName='cat' AND c.lastName='inhat')) 
    OR c.firstName='thing'

Figure 7.30. Criteria API Definition

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


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

        );

Figure 7.31. 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_ 
    where customer0_.FIRST_NAME<>? or customer0_.LAST_NAME<>? or customer0_.FIRST_NAME=?
 -found result:firstName=thing, lastName=one
 -found result:firstName=thing, lastName=two

7.5. Equality Tests

Must compare values

Figure 7.32. Equivalent JPAQL

select c from Clerk c 
where c.firstName = 'Manny'

select s from Sale s JOIN s.clerks c 
where c = :clerk

Figure 7.33. Criteria API Definition

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

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

qdef.select(c)

    .where(cb.equal(c.get("firstName"), "Manny"));



CriteriaQuery<Sale> qdef2 = cb.createQuery(Sale.class);

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

Join<Sale, Clerk> c2 = s.join("clerks");

qdef2.select(s)

     .where(cb.equal(c2, clerk));

Compare entities and not primary/foreign key values

Figure 7.34. In Programming Context

//find clerk of interest 

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

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

qdef.select(c)

    .where(cb.equal(c.get("firstName"), "Manny"));

Clerk clerk = em.createQuery(qdef).getSingleResult();


//find all sales that involve this clerk

CriteriaQuery<Sale> qdef2 = cb.createQuery(Sale.class);

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

Join<Sale, Clerk> c2 = s.join("clerks");

qdef2.select(s)

     .where(cb.equal(c2, clerk));


List<Sale> sales = em.createQuery(qdef2).getResultList();

Figure 7.35. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where clerk0_.FIRST_NAME='Manny' limit ?

    select
        sale0_.SALE_ID as SALE1_1_,
        sale0_.amount as amount1_,
        sale0_.BUYER_ID as BUYER3_1_,
        sale0_.date as date1_,
        sale0_.SALE_STORE as SALE5_1_ 
    from JPAQL_SALE sale0_ 
    inner join JPAQL_SALE_CLERK_LINK clerks1_ on sale0_.SALE_ID=clerks1_.SALE_ID 
    inner join JPAQL_CLERK clerk2_ on clerks1_.CLERK_ID=clerk2_.CLERK_ID 
    where clerk2_.CLERK_ID=?
...
 -found=date=1998-04-10 10:13:35, amount=$100.00, buyer=1, clerks(1)={1, }
...
 -found=date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }

7.6. Between

Figure 7.36. Equivalent JPAQL

select s from Sale s
where s.amount BETWEEN :low AND :high

Figure 7.37. Criteria API Definition

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

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

Figure 7.38. Runtime Output

    select
        sale0_.SALE_ID as SALE1_1_,
        sale0_.amount as amount1_,
        sale0_.BUYER_ID as BUYER3_1_,
        sale0_.date as date1_,
        sale0_.SALE_STORE as SALE5_1_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount between 90 and 110
...
 -found=date=1998-04-10 10:13:35, amount=$100.00, buyer=1, clerks(1)={1, }

Figure 7.39. Equivalent JPAQL

select s from Sale s
where s.amount NOT BETWEEN :low AND :high

Figure 7.40. Criteria API Definition

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

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

Figure 7.41. Runtime Output

    select
        sale0_.SALE_ID as SALE1_1_,
        sale0_.amount as amount1_,
        sale0_.BUYER_ID as BUYER3_1_,
        sale0_.date as date1_,
        sale0_.SALE_STORE as SALE5_1_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount not between 90 and 110
...
 -found=date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }

7.7. Testing for Null

Can be used to test for unassigned value or relationship

Figure 7.42. Equivalent JPAQL

select s from Sale s 
where s.store IS NULL

Figure 7.43. Criteria API Definition

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

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

qdef.select(s)

    .where(cb.isNull(s.get("store")));

    //.where(cb.equal(s.get("store"), cb.nullLiteral(Store.class)));

Figure 7.44. Runtime Output

    select
        sale0_.SALE_ID as SALE1_1_,
        sale0_.amount as amount1_,
        sale0_.BUYER_ID as BUYER3_1_,
        sale0_.date as date1_,
        sale0_.SALE_STORE as SALE5_1_ 
    from JPAQL_SALE sale0_ 
    where sale0_.SALE_STORE is null

Figure 7.45. Equivalent JPAQL

select s from Sale s 
where s.store IS NOT NULL

Figure 7.46. Criteria API Definition

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

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

qdef.select(s)

    .where(cb.isNotNull(s.get("store")));

    //.where(cb.not(cb.equal(s.get("store"), cb.nullLiteral(Store.class))));

Figure 7.47. Runtime Output

    select
        sale0_.SALE_ID as SALE1_1_,
        sale0_.amount as amount1_,
        sale0_.BUYER_ID as BUYER3_1_,
        sale0_.date as date1_,
        sale0_.SALE_STORE as SALE5_1_ 
    from JPAQL_SALE sale0_ 
    where sale0_.SALE_STORE is not null
...
 -found result:date=1998-04-10 10:13:35, amount=$100.00, buyer=1, clerks(1)={1, }
...
 -found result:date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }

7.8. Testing Empty Collection

Can be used to test for an empty collection

Figure 7.48. Equivalent JPAQL

select c from Clerk c
where c.sales IS EMPTY

Figure 7.49. Criteria API Definition

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

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

qdef.select(c)

    .where(cb.isEmpty(c.<List<Sale>>get("sales")));

Figure 7.50. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where not (exists (
        select sale2_.SALE_ID 
        from JPAQL_SALE_CLERK_LINK sales1_, JPAQL_SALE sale2_ 
        where clerk0_.CLERK_ID=sales1_.CLERK_ID and sales1_.SALE_ID=sale2_.SALE_ID)
    )
...
 -found result:firstName=Jack, lastName=Pep, hireDate=1973-03-01, termDate=null, sales(0)={}

Sub-select returns values from collection under test
Outer query tests for no existing (EMPTY)values

Figure 7.51. Equivalent JPAQL

select c from Clerk c
where c.sales IS NOT EMPTY

Figure 7.52. Criteria API Definition

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


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

qdef.select(c)

    .where(cb.isNotEmpty(c.<List<Sale>>get("sales")));

Figure 7.53. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where exists (
            select sale2_.SALE_ID 
            from JPAQL_SALE_CLERK_LINK sales1_, JPAQL_SALE sale2_ 
            where clerk0_.CLERK_ID=sales1_.CLERK_ID and sales1_.SALE_ID=sale2_.SALE_ID
        )
...
 -found result:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }
...
 -found result:firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }

Sub-select returns values from collection under test
Outer query tests for existing (NOT EMPTY)values

7.9. Membership Test

Can be used to determine membership in a collection

Figure 7.54. Equivalent JPAQL

select c from Clerk c 
where c.firstName = 'Manny'

select s from Sale s
where :clerk MEMBER OF s.clerks

Figure 7.55. Criteria API Definition

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

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

qdef.select(c)

    .where(cb.equal(c.get("firstName"), "Manny"));

CriteriaQuery<Sale> qdef2 = cb.createQuery(Sale.class);

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

qdef2.select(s)

     .where(cb.isMember(clerk, s.<List<Clerk>>get("clerks")));

Defines a shorthand for a subquery

Figure 7.56. In Programming Context

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

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

qdef.select(c)

    .where(cb.equal(c.get("firstName"), "Manny"));

Clerk clerk = em.createQuery(qdef).getSingleResult();


//find all sales that involve this clerk

CriteriaQuery<Sale> qdef2 = cb.createQuery(Sale.class);

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

qdef2.select(s)

     .where(cb.isMember(clerk, s.<List<Clerk>>get("clerks")));

List<Sale> sales = em.createQuery(qdef2).getResultList();

Figure 7.57. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where clerk0_.FIRST_NAME=? limit ?

    select
        sale0_.SALE_ID as SALE1_1_,
        sale0_.amount as amount1_,
        sale0_.BUYER_ID as BUYER3_1_,
        sale0_.date as date1_,
        sale0_.SALE_STORE as SALE5_1_ 
    from JPAQL_SALE sale0_ 
    where ? in (
            select clerk2_.CLERK_ID 
            from JPAQL_SALE_CLERK_LINK clerks1_, JPAQL_CLERK clerk2_ 
            where sale0_.SALE_ID=clerks1_.SALE_ID and clerks1_.CLERK_ID=clerk2_.CLERK_ID
        )
...
 -found=date=1998-04-10 10:13:35, amount=$100.00, buyer=1, clerks(1)={1, }
...
 -found=date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }

7.10. Subqueries

Useful when query cannot be expressed through JOINs

Figure 7.58. Equivalent JPAQL

select c from Customer c
where c.id IN
    (select s.buyerId from Sale s
     where s.amount > 100)

Figure 7.59. Criteria API Definition

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


    //form subquery

Subquery<Long> sqdef = qdef.subquery(Long.class);

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

sqdef.select(s.<Long>get("buyerId"))

    .where(cb.greaterThan(s.<BigDecimal>get("amount"), new BigDecimal(100)));


    //form outer query

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

qdef.select(c)

   .where(cb.in(c.get("id")).value(sqdef));

Figure 7.60. 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_ 
    where customer0_.CUSTOMER_ID in (
            select sale1_.BUYER_ID 
            from JPAQL_SALE sale1_ 
            where sale1_.amount>100
        )
        
 -found result:firstName=thing, lastName=one

7.11. All

All existing values must meet criteria (i.e., no value may fail criteria)
Zero values is the lack of failure (i.e., meets criteria)

Figure 7.61. Equivalent JPAQL

select c from Clerk c
where 125 < ALL
(select s.amount from c.sales s)

Figure 7.62. Criteria API Definition

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

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

qdef.select(c);


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.all(sqdef));

qdef.where(p1);

List all clerks that have all sales above $125.00 or none at all
-or- List all clerks with no sale <= $125.00

Figure 7.63. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where 125<all (
            select sale3_.amount 
            from JPAQL_CLERK clerk1_ 
            inner join JPAQL_SALE_CLERK_LINK sales2_ on clerk1_.CLERK_ID=sales2_.CLERK_ID 
            inner join JPAQL_SALE sale3_ on sales2_.SALE_ID=sale3_.SALE_ID 
            where clerk0_.CLERK_ID=clerk1_.CLERK_ID
        )
...
 -found result:firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }
...
 -found result:firstName=Jack, lastName=Pep, hireDate=1973-03-01, termDate=null, sales(0)={}

Manny excluded because has 1 sale below $125.00
Moe included because has only $150.00 sale
Jack included because has no sales that fail criteria

Figure 7.64. Equivalent JPAQL

select c from Clerk c
where 125 > ALL
(select s.amount from c.sales s)

Figure 7.65. Criteria API Definition

Predicate p2 = cb.greaterThan(

        cb.literal(new BigDecimal(125)), 

        cb.all(sqdef));


qdef.where(p2);

List all clerks that have all sales below $125.00 or none at all
-or- List all clerks with no sale >= $125.00

Figure 7.66. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where 125>all (
            select sale3_.amount 
            from JPAQL_CLERK clerk1_ 
            inner join JPAQL_SALE_CLERK_LINK sales2_ on clerk1_.CLERK_ID=sales2_.CLERK_ID 
            inner join JPAQL_SALE sale3_ on sales2_.SALE_ID=sale3_.SALE_ID 
            where clerk0_.CLERK_ID=clerk1_.CLERK_ID
        )

 -found result:firstName=Jack, lastName=Pep, hireDate=1973-03-01, termDate=null, sales(0)={}

Manny excluded because has 1 sale above $125.00
Moe excluded because has only $150.00 sale
Jack included because has no sales that fail criteria

7.12. Any

Any matching value meets criteria (i.e., one match and you are in)
Zero values fails to meet the criteria (i.e., must have at least one matching value)

Figure 7.67. Equivalent JPAQL

select c from Clerk c
where 125 < ANY
(select s.amount from c.sales s)

Figure 7.68. Criteria API Definition

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

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

qdef.select(c);


//select c from Clerk c

//where 125 < ALL " +

//(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 all clerks that have at least one sale above $125.00

Figure 7.69. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where 125<any (
            select sale3_.amount 
            from JPAQL_CLERK clerk1_ 
            inner join JPAQL_SALE_CLERK_LINK sales2_ on clerk1_.CLERK_ID=sales2_.CLERK_ID 
            inner join JPAQL_SALE sale3_ on sales2_.SALE_ID=sale3_.SALE_ID 
            where clerk0_.CLERK_ID=clerk1_.CLERK_ID
        )
...
 -found result:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }
...
 -found result:firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }

Manny included because has 1 sale above $125.00
Moe included because $150.00 sale qualifies him as well
Jack excluded because has no sales that meet criteria

Figure 7.70. Equivalent JPAQL

select c from Clerk c
where 125 > ANY
(select s.amount from c.sales s)

Figure 7.71. Criteria API Definition

Predicate p2 = cb.greaterThan(

        cb.literal(new BigDecimal(125)), 

        cb.any(sqdef));


qdef.where(p2);

List all clerks that have at least one sale below $125.00

Figure 7.72. Runtime Output

    select
        clerk0_.CLERK_ID as CLERK1_2_,
        clerk0_.FIRST_NAME as FIRST2_2_,
        clerk0_.HIRE_DATE as HIRE3_2_,
        clerk0_.LAST_NAME as LAST4_2_,
        clerk0_.TERM_DATE as TERM5_2_ 
    from JPAQL_CLERK clerk0_ 
    where 125>any (
            select sale3_.amount 
            from JPAQL_CLERK clerk1_ 
            inner join JPAQL_SALE_CLERK_LINK sales2_ on clerk1_.CLERK_ID=sales2_.CLERK_ID 
            inner join JPAQL_SALE sale3_ on sales2_.SALE_ID=sale3_.SALE_ID 
            where clerk0_.CLERK_ID=clerk1_.CLERK_ID
        )

 -found result:firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }

Manny included because has 1 sale below $125.00
Moe excluded because his only $150.00 sale above criteria
Jack excluded because has no sales that meet criteria

7.13. Summary

Full featured set of where clause capability