Chapter 5. JPAQL Functions

Figure 5.1. Example String Compare

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'

No rows found because 'CAT' does not match anything because of case

5.1.2. LOWER

Figure 5.2. Example LOWER Function

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

One customer found because case-sensitive compare now correct

5.1.3. UPPER

Figure 5.3. Example UPPER Function

select UPPER(c.firstName) from Customer c where c.firstName=LOWER('CAT')

    select
        upper(customer0_.FIRST_NAME) as col_0_0_ 
    from JPAQL_CUSTOMER customer0_ 
    where customer0_.FIRST_NAME=lower('CAT')
        
 -found result:CAT

First name of customer located returned in upper case

5.1.4. TRIM

Figure 5.4. Example TRIM Function

select TRIM(LEADING 'c' FROM c.firstName) from Customer c where c.firstName='cat')

    select
        trim(LEADING 'c' FROM customer0_.FIRST_NAME) as col_0_0_ 
    from JPAQL_CUSTOMER customer0_ 
    where customer0_.FIRST_NAME='cat'

 -found result:at

Customer's name, excluding initial 'c' character, returned

5.1.5. CONCAT

Figure 5.5. Example CONCAT Function]

select c from Customer c where CONCAT(CONCAT(c.firstName,' '),c.lastName) ='cat inhat')

    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||' ')||customer0_.LAST_NAME)='cat inhat'

 -found result:firstName=cat, lastName=inhat

Customer located after concatenation of fields yields match

5.1.6. LENGTH

Figure 5.6. Example LENGTH Function

select c from Customer c where LENGTH(c.firstName) = 3

    select
        customer0_.CUSTOMER_ID as CUSTOMER1_3_,
        customer0_.FIRST_NAME as FIRST2_3_,
        customer0_.LAST_NAME as LAST3_3_ 
    from JPAQL_CUSTOMER customer0_ 
    where length(customer0_.FIRST_NAME)=3

 -found result:firstName=cat, lastName=inhat

Customer found where length of firstName matches specified length criteria

5.1.7. LOCATE

Figure 5.7. Example LOCATE Function

select c from Customer c where LOCATE('cat',c.firstName,2) > 0

    select
        customer0_.CUSTOMER_ID as CUSTOMER1_3_,
        customer0_.FIRST_NAME as FIRST2_3_,
        customer0_.LAST_NAME as LAST3_3_ 
    from JPAQL_CUSTOMER customer0_ 
    where locate('cat', customer0_.FIRST_NAME, 2)>0

No firstName found with 'cat' starting at position=2

Figure 5.8. Another Example LOCATE Function

select c from Customer c where LOCATE('at',c.firstName,2) > 1

    select
        customer0_.CUSTOMER_ID as CUSTOMER1_3_,
        customer0_.FIRST_NAME as FIRST2_3_,
        customer0_.LAST_NAME as LAST3_3_ 
    from JPAQL_CUSTOMER customer0_ 
    where locate('at', customer0_.FIRST_NAME, 2)>1

 -found result:firstName=cat, lastName=inhat

firstName found with 'at' starting at a position 2

5.1.8. SUBSTRING

Figure 5.9. Example SUBSTRING Function

select SUBSTRING(c.firstName,2,2) from Customer c where c.firstName = 'cat'

    select
        substring(customer0_.FIRST_NAME, 2, 2) as col_0_0_ 
    from JPAQL_CUSTOMER customer0_ 
    where customer0_.FIRST_NAME='cat'
    -found result:at

Return the two character substring of firstName starting at position two

Figure 5.10. Another Example SUBSTRING Function

select c from Customer c where SUBSTRING(c.firstName,2,2) = 'at'

    select
        customer0_.CUSTOMER_ID as CUSTOMER1_3_,
        customer0_.FIRST_NAME as FIRST2_3_,
        customer0_.LAST_NAME as LAST3_3_ 
    from JPAQL_CUSTOMER customer0_ 
    where substring(customer0_.FIRST_NAME, 2, 2)='at'
        
 -found result:firstName=cat, lastName=inhat

Find the customer with a two characters starting a position two of firstName equaling 'at'

Figure 5.11. CURRENT_DATE Query Example

select s from Sale s
where s.date < CURRENT_DATE

    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_.date<CURRENT_DATE
...
 -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, }

Located two Sales that occurred prior to today's date

Figure 5.12. Another CURRENT_DATE Query Example

select s from Sale s
where s.date = CURRENT_DATE

    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_.date=CURRENT_DATE

Located no sales on today's date

Figure 5.13. Using Bulk Update to Change Date

update Sale s
set s.date = CURRENT_DATE

update JPAQL_SALE 
set date=CURRENT_DATE

Update all sales to today

Figure 5.14. Retrying CURRENT_DATE Query After Bulk Update

select s from Sale s
where s.date = CURRENT_DATE

 -executing query:select s from Sale s where s.date = CURRENT_DATE
    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_.date=CURRENT_DATE
...
 -found result:date=2013-06-05 00:00:00, amount=$100.00, buyer=1, clerks(1)={1, }
...
 -found result:date=2013-06-05 00:00:00, amount=$150.00, buyer=2, clerks(2)={1, 2, }

Now locating sales for today's date

Note

Bulk commands (i.e., update) invalidate cached entities. You must refresh their state with the database or detach/clear them from the persistence context to avoid using out-dated information.

5.3. Order By

ASC - ascending order
DESC - descending order

Figure 5.15. Example Order By

select s from Sale s ORDER BY s.amount ASC

    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_ 
    order by sale0_.amount ASC
...
 -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, }

Note the ASC order on amount

Figure 5.16. Another Example Order By

select s from Sale s ORDER BY s.amount DESC

    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_ 
    order by sale0_.amount DESC
 -found result:date=1999-06-11 14:15:10, amount=$150.00, buyer=2, clerks(2)={1, 2, }
 -found result:date=1998-04-10 10:13:35, amount=$100.00, buyer=1, clerks(1)={1, }

Note the DESC order on amount

5.4. Aggregate Functions

5.4.1. COUNT

Figure 5.17. Example COUNT Aggregate Function

select COUNT(s) from Sale s

    select count(*) as col_0_0_ 
    from JPAQL_SALE sale0_
        
 -found result:2

5.4.2. MIN/MAX

Figure 5.18. Example MIN Aggregate Function

select min(s.amount) from Sale s

    select min(sale0_.amount) as col_0_0_ 
    from JPAQL_SALE sale0_
 -found result:100.00

Figure 5.19. Example MAX Aggregate Function

select max(s.amount) from Sale s

    select max(sale0_.amount) as col_0_0_ 
    from JPAQL_SALE sale0_
 -found result:150.00

5.4.3. SUM/AVE

Figure 5.20. Example SUM Aggregate Function

select sum(s.amount) from Sale s

    select sum(sale0_.amount) as col_0_0_ 
    from JPAQL_SALE sale0_
 -found result:250.00

Figure 5.21. Example AVE Aggregate Function

select ave(s.amount) from Sale s

    select avg(cast(sale0_.amount as double)) as col_0_0_ 
    from JPAQL_SALE sale0_
 -found result:125.0

5.5. Group By

Figure 5.22. Example Group By

select c, COUNT(s) from Clerk c
LEFT JOIN c.sales s
GROUP BY c

Get count of sales for each clerk

Figure 5.23. Example Group By Runtime Output

    select
        clerk0_.CLERK_ID as col_0_0_,
        count(sale2_.SALE_ID) as col_1_0_,
        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_ 
    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 
    group by clerk0_.CLERK_ID
...
 -found=[firstName=Manny, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(2)={1, 2, }, 2]
...
 -found=[firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }, 1]
...
 -found=[firstName=Jack, lastName=Pep, hireDate=1973-03-01, termDate=null, sales(0)={}, 0]

5.6. Having

Figure 5.24. Example Having Aggregate Function

select c, COUNT(s) from Clerk c
LEFT JOIN c.sales s
GROUP BY c
HAVING COUNT(S) <= 1

Provide a list of Clerks and their count of Sales for counts <= 1

Figure 5.25. Example Having Aggregate Function Runtime Output

    select
        clerk0_.CLERK_ID as col_0_0_,
        count(sale2_.SALE_ID) as col_1_0_,
        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_ 
    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 
    group by clerk0_.CLERK_ID 
    having count(sale2_.SALE_ID)<=1
...
 -found=[firstName=Moe, lastName=Pep, hireDate=1970-01-01, termDate=null, sales(1)={2, }, 1]
...
 -found=[firstName=Jack, lastName=Pep, hireDate=1973-03-01, termDate=null, sales(0)={}, 0]

Wed matched on Moe (1 sale) and Jack (0 sales)

5.7. Summary

String and Date Functions
Order By
Group By, Having, and Aggregate Functions