Chapter 8. Criteria Functions

Figure 8.1. Equivalent JPAQL

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

Figure 8.2. Criteria API Definition

CriteriaQuery qdef = cb.createQuery();

Root<Customer> c = qdef.from(Customer.class);


qdef.select(c)

    .where(cb.equal(c.get("firstName"),"CAT"));

Using an untyped CriteriaQuery to be able to switch between different query output types within example

Figure 8.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=?

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

8.1.2. LOWER

Figure 8.4. Equivalent JPAQL

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

Figure 8.5. Criteria API Definition

qdef.select(c)

    .where(cb.equal(c.get("firstName"),cb.lower(cb.literal("CAT"))));

Figure 8.6. 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=lower(?)

 -found result:firstName=cat, lastName=inhat

One customer found because case-sensitive compare now correct

8.1.3. UPPER

Figure 8.7. Equivalent JPAQL

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

Figure 8.8. Criteria API Definition

qdef.select(cb.upper(c.<String>get("firstName")))

    .where(cb.equal(c.get("firstName"),cb.lower(cb.literal("CAT"))));

Figure 8.9. Runtime Output

    select upper(customer0_.FIRST_NAME) as col_0_0_ 
    from JPAQL_CUSTOMER customer0_ 
    where customer0_.FIRST_NAME=lower(?)

 -found result:CAT

First name of customer located returned in upper case

8.1.4. TRIM

Figure 8.10. Equivalent JPAQL

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

Figure 8.11. Criteria API Definition

qdef.select(cb.trim(Trimspec.LEADING, 'c', c.<String>get("firstName")))

    .where(cb.equal(c.get("firstName"),"cat"));

Figure 8.12. Runtime Output

    select trim(LEADING ? 
    from customer0_.FIRST_NAME) as col_0_0_ 
    from JPAQL_CUSTOMER customer0_ 
    where customer0_.FIRST_NAME=?
 -found result:at

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

8.1.5. CONCAT

Figure 8.13. Equivalent JPAQL

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

Figure 8.14. Criteria API Definition

qdef.select(c)

    .where(cb.equal(

            cb.concat(

                    cb.concat(c.<String>get("firstName"), " "),

                    c.<String>get("lastName")),

            "cat inhat"));

Figure 8.15. 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||?||customer0_.LAST_NAME)=?

 -found result:firstName=cat, lastName=inhat

Customer located after concatenation of fields yields match

8.1.6. LENGTH

Figure 8.16. Equivalent JPAQL

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

Figure 8.17. Criteria API Definition

qdef.select(c)

    .where(cb.equal(cb.length(c.<String>get("firstName")),3));

Figure 8.18. 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 length(customer0_.FIRST_NAME)=3

 -found result:firstName=cat, lastName=inhat

Customer found where length of firstName matches specified length criteria

8.1.7. LOCATE

Figure 8.19. Equivalent JPAQL

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

Figure 8.20. Criteria API Definition

qdef.select(c)

    .where(cb.greaterThan(cb.locate(c.<String>get("firstName"), "cat", 2),0));

Figure 8.21. 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 locate(?, customer0_.FIRST_NAME, 2)>0

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

Figure 8.22. Equivalent JPAQL

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

qdef.select(c)

    .where(cb.greaterThan(cb.locate(c.<String>get("firstName"), "at", 2),1));

Figure 8.23. 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 locate(?, customer0_.FIRST_NAME, 2)>1

 -found result:firstName=cat, lastName=inhat

firstName found with 'at' starting at a position 2

8.1.8. SUBSTRING

Figure 8.24. Equivalent JPAQL

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

Figure 8.25. Criteria API Definition

qdef.select(cb.substring(c.<String>get("firstName"),  2, 2))

    .where(cb.equal(c.get("firstName"), "cat"));

Figure 8.26. Runtime Output

    select substring(customer0_.FIRST_NAME, 2, 2) as col_0_0_ 
    from JPAQL_CUSTOMER customer0_ 
    where customer0_.FIRST_NAME=?

 -found result:at

Return the two character substring of firstName starting at position two

Figure 8.27. Equivalent JPAQL

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

Figure 8.28. Criteria API Definition

qdef.select(c)

    .where(cb.equal(

            cb.substring(c.<String>get("firstName"), 2, 2), 

            "at"));

Figure 8.29. 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 substring(customer0_.FIRST_NAME, 2, 2)=?
 -found result:firstName=cat, lastName=inhat

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

Figure 8.30. Equivalent JPAQL

select s from Sale s
where s.date < CURRENT_DATE

Figure 8.31. Criteria API Definition

CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);

Root<Sale> s = qdef.from(Sale.class);

qdef.select(s);


qdef.where(cb.lessThan(s.<Date>get("date"), cb.currentDate()));

Figure 8.32. 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_.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 8.33. Equivalent JPAQL

select s from Sale s
where s.date = CURRENT_DATE

Figure 8.34. Criteria API Definition

qdef.where(cb.equal(s.<Date>get("date"), cb.currentDate()));

Figure 8.35. 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_.date=current_date()

Located no sales on today's date

Figure 8.36. Use JPA-QL to update Rows

update Sale s set s.date = CURRENT_DATE

Figure 8.37. Runtime Output

update JPAQL_SALE set date=CURRENT_DATE

Update all sales to today

JPA Criteria API does not Provide Bulk Updates

JPA provides no mechanism to perform bulk updates with Criteria API. You must use JPAQL or SQL to perform bulk updates.

Figure 8.38. Equivalent JPAQL

select s from Sale s
where s.date = CURRENT_DATE

Figure 8.39. Criteria API Definition

qdef.where(cb.equal(s.<Date>get("date"), cb.currentDate()));

Figure 8.40. 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_.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.

8.3. Order By

ASC - ascending order
DESC - descending order

Figure 8.41. Equivalent JPAQL

select s from Sale s ORDER BY s.amount ASC

Figure 8.42. Criteria API Definition

CriteriaQuery<Sale> qdef = cb.createQuery(Sale.class);

Root<Sale> s = qdef.from(Sale.class);

qdef.select(s);


qdef.orderBy(cb.asc(s.get("amount")));

Figure 8.43. 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_ 
    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 8.44. Equivalent JPAQL

select s from Sale s ORDER BY s.amount DESC

Figure 8.45. Criteria API Definition

qdef.orderBy(cb.desc(s.get("amount")));

Figure 8.46. 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_ 
    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

8.4. Aggregate Functions

8.4.1. COUNT

Figure 8.47. Equivalent JPAQL

select COUNT(s) from Sale s

Figure 8.48. Criteria API Definition

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
Root<Sale> s = qdef.from(Sale.class);

qdef.select(cb.count(s));

Figure 8.49. Runtime Output

select count(*) as col_0_0_ 
from JPAQL_SALE sale0_

8.4.2. MIN/MAX

Figure 8.50. Equivalent JPAQL

select min(s.amount) from Sale s

Figure 8.51. Criteria API Definition

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
Root<Sale> s = qdef.from(Sale.class);

qdef.select(cb.min(s.<BigDecimal>get("amount")));

Figure 8.52. Runtime Output

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

Figure 8.53. Equivalent JPAQL

select max(s.amount) from Sale s

Figure 8.54. Criteria API Definition

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
Root<Sale> s = qdef.from(Sale.class);

qdef.select(cb.max(s.<BigDecimal>get("amount")));

Figure 8.55. Runtime Output

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

8.4.3. SUM/AVE

Figure 8.56. Equivalent JPAQL

select sum(s.amount) from Sale s

Figure 8.57. Criteria API Definition

CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
Root<Sale> s = qdef.from(Sale.class);
//select sum(s.amount) from Sale s
qdef.select(cb.sum(s.<BigDecimal>get("amount")));

Figure 8.58. Runtime Output

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

Figure 8.59. Equivalent JPAQL

select ave(s.amount) from Sale s

Figure 8.60. Criteria API Definition

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
Root<Sale> s = qdef.from(Sale.class);

qdef.select(cb.avg(s.<BigDecimal>get("amount")));

Figure 8.61. Runtime Output

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

8.5. Group By

Figure 8.62. Equivalent JPAQL

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

Figure 8.63. Criteria API Definition

CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);

Root<Clerk> c = qdef.from(Clerk.class);

Join<Clerk,Sale> s = c.join("sales", JoinType.LEFT);


qdef.select(cb.array(c, cb.count(s)))

    .groupBy(c);

Get count of sales for each clerk

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

8.6. Having

Figure 8.65. Equivalent JPAQL

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

Figure 8.66. Criteria API Definition

CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);

Root<Clerk> c = qdef.from(Clerk.class);

Join<Clerk,Sale> s = c.join("sales", JoinType.LEFT);


qdef.select(cb.array(c, cb.count(s)))

    .groupBy(c)

    .having(cb.le(cb.count(s), 1));

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

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

8.7. Summary

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