Enterprise Java Development@TOPIC@

Chapter 72. JPAQL Functions

72.1. String Functions
72.1.1. Base Query
72.1.2. LOWER
72.1.3. UPPER
72.1.4. TRIM
72.1.5. CONCAT
72.1.6. LENGTH
72.1.7. LOCATE
72.1.8. SUBSTRING
72.2. Date Functions
72.3. Order By
72.4. Aggregate Functions
72.4.1. COUNT
72.4.2. MIN/MAX
72.4.3. SUM/AVE
72.5. Group By
72.6. Having
72.7. Summary

72.1. String Functions

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

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

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

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

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

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

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

Figure 72.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 72.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'

72.2. Date Functions

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

72.3. Order By

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

72.4. Aggregate Functions

Figure 72.17. Example COUNT Aggregate Function

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

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

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

72.5. Group By

Figure 72.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 72.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]

72.6. Having

Figure 72.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 72.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)

72.7. Summary