Enterprise Java Development@TOPIC@

Chapter 71. JPAQL Where Clauses

71.1. Equality Test
71.2. Like Test
71.3. Formulas
71.4. Logic Operators
71.5. Equality Tests
71.6. Between
71.7. Testing for Null
71.8. Testing Empty Collection
71.9. Membership Test
71.10. Subqueries
71.11. All
71.12. Any
71.13. Summary

71.1. Equality Test

Figure 71.1. Example Equality Test

select c from Customer c 
where c.firstName='cat'
    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='cat'
        
 -found result:firstName=cat, lastName=inhat

  • Return entities where there is an equality match

Figure 71.2. Escaping Special Characters

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

  • Escaped special character is passed through to the database

71.2. Like Test

Figure 71.3. Like Test Literal

select c from Clerk c
where c.firstName like 'M%'
    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 'M%'
...        
 -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, }

Figure 71.4. Like Test Literal Parameter

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

Figure 71.5. Using Like Test Literal Parameter

TypedQuery<T> query = em.createQuery(ejbqlString, resultType);

query.setParameter("firstName", "M%");

List<T> objects = query.getResultList();
    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, }

Figure 71.6. Like Test Concatenated String

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

Figure 71.7. Using Like Test Concatenated String

TypedQuery<T> query = em.createQuery(ejbqlString, resultType);

query.setParameter("firstName", "M");

List<T> objects = query.getResultList();
    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, }

Figure 71.8. Like Test Single Character Wildcard

select c from Clerk c 
where c.firstName like '_anny'
    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 '_anny'

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

71.3. Formulas

Figure 71.9. Example Formula

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

Figure 71.10. Using Formula

String jpaql = "select count(s) from Sale s " +

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

TypedQuery<Number> query = em.createQuery(jpaql, Number.class)

        .setParameter("amount", new BigDecimal(10.00));

        

//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));
    select count(sale0_.SALE_ID) as col_0_0_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount*?>? limit ?
 -tax=5%
    select count(sale0_.SALE_ID) as col_0_0_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount*?>? limit ?
 -tax=6%
    select count(sale0_.SALE_ID) as col_0_0_ 
    from JPAQL_SALE sale0_ 
    where sale0_.amount*?>? limit ?
 -raise taxes to: 7%

71.4. Logic Operators

Figure 71.11. Logic Operator Example

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

Figure 71.12. Logic Operator Example 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='cat' 
        and customer0_.LAST_NAME='inhat' 
        or customer0_.FIRST_NAME='thing'

 -found result:firstName=cat, lastName=inhat
 -found result:firstName=thing, lastName=one
 -found result:firstName=thing, lastName=two

Figure 71.13. Another Logic Operator Example

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

Figure 71.14. Another Logic Operator Example 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<>'cat' 
        or customer0_.LAST_NAME<>'inhat' 
        or customer0_.FIRST_NAME='thing'
        
 -found result:firstName=thing, lastName=one
 -found result:firstName=thing, lastName=two

71.5. Equality Tests

Figure 71.15. Example Entity Equality Query

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

  • Compare entities and not primary/foreign key values

Figure 71.16. Using Entity Equality Query

//get a clerk entity

Clerk clerk = em.createQuery(

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

        Clerk.class)

       .getSingleResult();


//find all sales that involve this clerk

List<Sale> sales = em.createQuery( 

    "select s from Sale s " +

    "JOIN s.clerks c " +

    "where c = :clerk", 

    Sale.class)

        .setParameter("clerk", clerk)

        .getResultList();
    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, }

71.6. Between

Figure 71.17. Example Between Query

select s from Sale s
where s.amount BETWEEN :low AND :high
    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 ? and ?
...
 -found result:date=1998-04-10 10:13:35, amount=$100.00, buyer=1, clerks(1)={1, }

Figure 71.18. Another Example Between Query

select s from Sale s
where s.amount NOT BETWEEN :low AND :high
    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 ? and ?
...
 -found result:date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }

71.7. Testing for Null

Can be used to test for unassigned value or relationship

Figure 71.19. Example Test for Null

select s from Sale s 
where s.store IS NULL
    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 71.20. Example Test for Not Null

select s from Sale s 
where s.store IS NOT NULL
    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, }

71.8. Testing Empty Collection

Can be used to test for an empty collection

Figure 71.21. Example Empty Collection Test

select c from Clerk c
where c.sales IS EMPTY
    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 71.22. Example Non-Empty Test

select c from Clerk c
where c.sales IS NOT EMPTY
    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

71.9. Membership Test

Can be used to determine membership in a collection

Figure 71.23. Example Membership Test

select c from Clerk c 
where c.firstName = 'Manny'
select s from Sale s
where :clerk MEMBER OF s.clerks

  • Defines a shorthand for a subquery

Figure 71.24. Using Membership Test

//get a clerk entity

Clerk clerk = em.createQuery(

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

    Clerk.class)

      .getSingleResult();


//find all sales that involve this clerk

List<Sale> sales = em.createQuery(

    "select s from Sale s " +

    "where :clerk MEMBER OF s.clerks", 

    Sale.class)

        .setParameter("clerk", clerk)

        .getResultList();

Figure 71.25. Using Membership Test 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_ 
    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, }

71.10. Subqueries

Useful when query cannot be expressed through JOINs

Figure 71.26. Example Subquery

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

Figure 71.27. Example Subquery 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

71.11. All

Figure 71.28. Example ALL Query

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

  • List all clerks that have all sales above $125.00 or none at all

  • -or- List all clerks with no sale <= $125.00

Figure 71.29. Example ALL Query 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 sale2_.amount 
            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=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 71.30. Another ALL Query Example

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

  • List all clerks that have all sales below $125.00 or none at all

  • -or- List all clerks with no sale >= $125.00

Figure 71.31. Another ALL Query Example 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 sale2_.amount 
            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)={}

  • 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

71.12. Any

Figure 71.32. Example ANY Query

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

  • List all clerks that have at least one sale above $125.00

Figure 71.33. Example ANY Query Runtime Output

 -executing query:select c from Clerk c where 125 < ANY    (select s.amount from c.sales s)
    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 sale2_.amount 
            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, }

  • 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 71.34. Another Example ANY Query

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

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

Figure 71.35. Another Example ANY Query Runtime Output

 -executing query:select c from Clerk c where 125 > ANY    (select s.amount from c.sales s)
    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 sale2_.amount 
            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, }

  • 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

71.13. Summary