Chapter 74. Criteria Where Clauses

Figure 74.1. Equivalent JPAQL

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

Figure 74.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 74.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 74.4. Equivalent JPAQL

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

JPAQL requires special characters to be escaped

Figure 74.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 74.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, }

74.2. Like Test

74.2.1. Like Test Literal

Figure 74.7. Equivalent JPAQL

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

Figure 74.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 74.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, }

74.2.2. Like Test Literal Parameter

Figure 74.10. Equivalent JPAQL

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

Figure 74.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 74.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 74.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, }

74.2.3. Like Test Concatenated String

Figure 74.14. Equivalent JPAQL

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

Figure 74.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 74.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 74.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, }

74.2.4. Like Test Single Character Wildcard

Figure 74.18. Equivalent JPAQL

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

Figure 74.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 74.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 74.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, }

74.3. Formulas

Figure 74.22. Equivalent JPAQL

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

Figure 74.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 74.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 74.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%

74.4. Logic Operators

Figure 74.26. Equivalent JPAQL

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

Figure 74.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 74.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 74.29. Equivalent JPAQL

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

Figure 74.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 74.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

74.5. Equality Tests

Must compare values

Figure 74.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 74.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 74.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 74.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, }

74.6. Between

Figure 74.36. Equivalent JPAQL

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

Figure 74.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 74.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 74.39. Equivalent JPAQL

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

Figure 74.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 74.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, }

74.7. Testing for Null

Can be used to test for unassigned value or relationship

Figure 74.42. Equivalent JPAQL

select s from Sale s 
where s.store IS NULL

Figure 74.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 74.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 74.45. Equivalent JPAQL

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

Figure 74.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 74.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, }

74.8. Testing Empty Collection

Can be used to test for an empty collection

Figure 74.48. Equivalent JPAQL

select c from Clerk c
where c.sales IS EMPTY

Figure 74.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 74.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 74.51. Equivalent JPAQL

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

Figure 74.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 74.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

74.9. Membership Test

Can be used to determine membership in a collection

Figure 74.54. Equivalent JPAQL

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

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

Figure 74.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 74.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 74.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, }

74.10. Subqueries

Useful when query cannot be expressed through JOINs

Figure 74.58. Equivalent JPAQL

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

Figure 74.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 74.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

74.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 74.61. Equivalent JPAQL

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

Figure 74.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 74.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 74.64. Equivalent JPAQL

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

Figure 74.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 74.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

74.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 74.67. Equivalent JPAQL

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

Figure 74.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 74.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 74.70. Equivalent JPAQL

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

Figure 74.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 74.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

74.13. Summary

Full featured set of where clause capability