Chapter 75. Criteria Functions

Figure 75.1. Equivalent JPAQL

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

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

75.1.2. LOWER

Figure 75.4. Equivalent JPAQL

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

Figure 75.5. Criteria API Definition

qdef.select(c)

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

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

75.1.3. UPPER

Figure 75.7. Equivalent JPAQL

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

Figure 75.8. Criteria API Definition

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

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

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

75.1.4. TRIM

Figure 75.10. Equivalent JPAQL

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

Figure 75.11. Criteria API Definition

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

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

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

75.1.5. CONCAT

Figure 75.13. Equivalent JPAQL

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

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

75.1.6. LENGTH

Figure 75.16. Equivalent JPAQL

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

Figure 75.17. Criteria API Definition

qdef.select(c)

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

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

75.1.7. LOCATE

Figure 75.19. Equivalent JPAQL

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

Figure 75.20. Criteria API Definition

qdef.select(c)

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

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

75.1.8. SUBSTRING

Figure 75.24. Equivalent JPAQL

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

Figure 75.25. Criteria API Definition

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

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

Figure 75.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 75.27. Equivalent JPAQL

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

Figure 75.28. Criteria API Definition

qdef.select(c)

    .where(cb.equal(

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

            "at"));

Figure 75.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 75.30. Equivalent JPAQL

select s from Sale s
where s.date < CURRENT_DATE

Figure 75.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 75.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 75.33. Equivalent JPAQL

select s from Sale s
where s.date = CURRENT_DATE

Figure 75.34. Criteria API Definition

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

Figure 75.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
Update with a bulk query

Note

Criteria API added Bulk Updates in JPA 2.1

Figure 75.36. Equivalent JPAQL

update Sale s set s.date = CURRENT_DATE

Figure 75.37. Criteria API Definition

CriteriaUpdate<Sale> qupdate = cb.createCriteriaUpdate(Sale.class);

Root<Sale> s2 = qupdate.from(Sale.class);

qupdate.set(s2.<Date>get("date"), cb.currentDate());

int rows = em.createQuery(qupdate).executeUpdate();

Figure 75.38. Runtime Output

update JPAQL_SALE set date=CURRENT_DATE

Update all sales to today

Figure 75.39. Equivalent JPAQL

select s from Sale s
where s.date = CURRENT_DATE

Figure 75.40. Criteria API Definition

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

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

75.3. Order By

ASC - ascending order
DESC - descending order

Figure 75.42. Equivalent JPAQL

select s from Sale s ORDER BY s.amount ASC

Figure 75.43. 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 75.44. 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 75.45. Equivalent JPAQL

select s from Sale s ORDER BY s.amount DESC

Figure 75.46. Criteria API Definition

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

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

75.4. Aggregate Functions

75.4.1. COUNT

Figure 75.48. Equivalent JPAQL

select COUNT(s) from Sale s

Figure 75.49. 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 75.50. Runtime Output

select count(*) as col_0_0_ 
from JPAQL_SALE sale0_

75.4.2. MIN/MAX

Figure 75.51. Equivalent JPAQL

select min(s.amount) from Sale s

Figure 75.52. 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 75.53. Runtime Output

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

Figure 75.54. Equivalent JPAQL

select max(s.amount) from Sale s

Figure 75.55. 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 75.56. Runtime Output

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

75.4.3. SUM/AVE

Figure 75.57. Equivalent JPAQL

select sum(s.amount) from Sale s

Figure 75.58. 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 75.59. Runtime Output

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

Figure 75.60. Equivalent JPAQL

select ave(s.amount) from Sale s

Figure 75.61. 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 75.62. Runtime Output

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

75.5. Group By

Figure 75.63. Equivalent JPAQL

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

Figure 75.64. 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 75.65. 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]

75.6. Having

Figure 75.66. Equivalent JPAQL

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

Figure 75.67. 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 75.68. 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)

75.7. Summary

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