Enterprise Java Development@TOPIC@

Chapter 4. JPAQL Where Clauses

4.1. Equality Test
4.2. Like Test
4.3. Formulas
4.4. Logic Operators
4.5. Equality Tests
4.6. Between
4.7. Testing for Null
4.8. Testing Empty Collection
4.9. Membership Test
4.10. Subqueries
4.11. All
4.12. Any
4.13. Summary

4.1. Equality Test

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

4.2. Like Test

Figure 4.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 4.4. Like Test Literal Parameter

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

Figure 4.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 4.6. Like Test Concatenated String

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

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

4.3. Formulas

Figure 4.9. Example Formula

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

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

4.4. Logic Operators

Figure 4.11. Logic Operator Example

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

Figure 4.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 4.13. Another Logic Operator Example

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

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

4.5. Equality Tests

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

4.6. Between

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

4.7. Testing for Null

Can be used to test for unassigned value or relationship

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

4.8. Testing Empty Collection

Can be used to test for an empty collection

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

4.9. Membership Test

Can be used to determine membership in a collection

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

4.10. Subqueries

Useful when query cannot be expressed through JOINs

Figure 4.26. Example Subquery

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

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

4.11. All

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

4.12. Any

Figure 4.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 4.33. Example ANY Query Runtime Output

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 4.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 4.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

4.13. Summary