10 Criteria API

Criteria API is used to build dynamic queries at runtime. CriteriaQuery is a select query, and entities fetched will be put into persistence context. However, CriteriaUpdate and CriteriaDelete are batch DML and not synchronized with persistence context.

CriteriaBuilder is the starting point to build a Criteria. See JPA javadoc for complete API references.

Criteria Query

Example: select all full-time employees hired after 02/18/2018.

    EntityManagerFactory emf = Persistence.createEntityManageFactory("pu");
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class);
    Root<Employee> root = criteriaQuery.from(Employee.class);

    Date date = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US).parse("02/18/2018");
    Predicate restriction = criteriaBuilder.greaterThan(root.get(Employee_.hiredDate), 
            criteriaBuilder.literal(date));
    criteriaQuery.where(restriction);
    criteriaQuery.distinct(true);
    
    TypedQuery<Employee> query = em.createQuery(criteriaQuery);
    List<Employee> employees = query.getResultList();

Equivalent JPQL:

    TypedQuery<Employee> query = em.createQuery(
        "select distinct e from Employee e where e.hiredDate>?1", Employee.class);
    query.setParameter(1, date);
    List<Employee> employees = query.getResultList();

It is expensive to create an EntityManagerFactory since it will build metamodel for the persistence unit. So it should be cached for creating EntityManager(s).

Joins And Fetches

Example: select all full-time employees of the employer, fetching employee's phone numbers.

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<FullTimeEmployee> criteriaQuery = criteriaBuilder.createQuery(FullTimeEmployee.class);
    Root<FullTimeEmployee> root = criteriaQuery.from(FullTimeEmployee.class);
    Join<Employee, Employer> employer = root.join(Employee_.employer, JoinType.INNER);
    root.fetch(FullTimeEmployee_.phoneNumbers, JoinType.LEFT);
    criteriaQuery.where(criteriaBuilder.equal(employer.get(Employer_.id), 1L);
    criteriaQuery.distinct(true);

    TypedQuery<FullTimeEmployee> query = em.createQuery(criteriaQuery); 
    List<FullTimeEmployee> employees = query.getResultList();

Equivalent JPQL:

    TypedQuery<FullTimeEmployee> query = em.createQuery("select e from FullTimeEmployee e " + 
        "inner join e.employer em left join fetch e.phoneNumbers where em.id=1", FullTimeEmployee.class); 
    List<FullTimeEmployee> employees = query.getResultList();

Compound Selection

A compound selection can be tuple, construct or array. For example,

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<Employee> root = criteriaQuery.from(Employee.class);

    // Tuple
    CompoundSelection<Tuple> idNameTuple = criteriaBuilder.tuple( 
        root.get(Employee_.id), root.get(Employee_.name));

    // Construct
    CompoundSelection<ZipCode> zipCodeConstruct = criteriaBuilder.construct(ZipCode.class,
        root.get(Employee_.address).get(Address_.zipCode).get(ZipCode_.majorCode),
        root.get(Employee_.address).get(Address_.zipCode).get(ZipCode_.minorCode));

    // Array
    CompoundSelection<Object[]> employerDeptArray = criteriaBuilder.array(
        root.join(Employee_.employer).get(Employer_.name),
        root.join(Employee_.department).get(Department_.id).get(DepartmentId_.name));

    criteriaQuery.multiselect(idNameTuple, zipCodeConstruct, employerDeptArray);
    
    DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US);
    criteriaQuery.where(criteriaBuilder.between(root.get(Employee_.birthday),
        df.parse("1/1/1980"), df.parse("12/31/1990")));

    criteriaQuery.orderBy(criteriaBuilder.asc(root.get(Employee_.id)));
    criteriaQuery.distinct(true);

    TypedQuery<Object[]> query = em.createQuery(criteriaQuery);
    List<Object[]> results = query.getResultList();
    for (Object[] row : results) {
        Tuple tuple = (Tuple)row[0];
        ZipCode zipCode = (ZipCode)row[1];
        Object[] array = (Object[])row[2];
        ...
    }

The class ZipCode must have the corresponding constructor defined.

public class ZipCode {
    public ZipCode(String majorCode, String minorCode) {
        ...
    }
}

Equivalent JPQL:

    TypedQuery<Object[]> query = em.createQuery("select distinct " +
        "e.id, e.name, " +
        "new com.cmobilecom.jpa.test.managed_classes.ZipCode(e.address.zipCode.majorCode, e.address.zipCode.minorCode), " +
        "e.employer.name, e.department.id.name " +
        "from Employee e " +
        "where e.birthday between {d '1980-01-01'} and {d '1990-12-31'} " +
        "order by e.id asc", Object[].class);
    List<Object[]> results = query.getResultList();

JPQL supports construct selection, but does not support tuple or array compound selection.

Type Expressions

Example: list all full-time and part-time employees with small projects assigned.

    // Employee entity hierarchy: single table with String discriminator.
    // Project entity hierarchy: joined with character discriminator.
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class);
    Root<Employee> root = criteriaQuery.from(Employee.class);

    CollectionJoin<Employee, Project> projects = root.join(Employee_.projects);

    criteriaQuery.where(root.type().in(FullTimeEmployee.class, PartTimeEmployee.class),
        criteriaBuilder.equal(projects.type(), SmallProject.class));
    criteriaQuery.distinct(true);

    TypedQuery<Employee> query = em.createQuery(criteriaQuery);
    List<Employee> employees = query.getResultList();

Equivalent JPQL:

    TypedQuery<Employee> query = em.createQuery("select distinct e from Employee e inner join e.projects p " +
        "where type(e) in (FullTimeEmployee, PartTimeEmployee) and type(p)=SmallProject", Employee.class);
    List<Employee> employees = query.getResultList();

Parameters and Alias

Parameters can be positional or named. Aliases are defined in selection clause, and used in orderBy clause. Some databases support using aliases in groupBy and having clauses.

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<FullTimeEmployee> root = criteriaQuery.from(FullTimeEmployee.class);
    
    // abs(2 + ((salary mod 9)- 6) * 3 ),
    Expression<Integer> salary = criteriaBuilder.toInteger(root.get(FullTimeEmployee_.salary));
    Expression<Integer> prod = criteriaBuilder.prod(
        criteriaBuilder.sum(criteriaBuilder.mod(salary, 9),
        criteriaBuilder.neg(criteriaBuilder.literal(6))), 3);
    Expression<Integer> computedSalary = criteriaBuilder.abs(criteriaBuilder.sum(2, prod)).alias("computedSalary");

    criteriaQuery.multiselect(root.get(FullTimeEmployee_.id), computedSalary);
    criteriaQuery.where(criteriaBuilder.ge(root.get(FullTimeEmployee_.salary), 
        criteriaBuilder.parameter("minSalary", BigDecimal.class)));
    criteriaQuery.orderBy(criteriaBuilder.asc(computedSalary));
    
    TypedQuery<Object[]> query = em.createQuery(criteriaQuery);
    query.setParameter("minSalary", 100000);
    List<Object[]> results = query.getResultList();

Equivalent JPQL:

    // abs(2 + ((salary mod 9)- 6) * 3 )
    TypedQuery<Object[]> query = em.createQuery("select e.id, abs(2 + (mod(cast(e.salary as integer), 9) - 6) * 3) as computedSalary " +
        "from FullTimeEmployee e where e.salary >= :minSalary " + 
        "order by computedSalary asc", Object[].class);
    query.setParameter("minSalary", 100000);
    List<Object[]> results = query.getResultList();

Group By, Having

Example: list sum(salary) for each department whose sum(salary)>=3000000.

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
    Root<FullTimeEmployee> root = criteriaQuery.from(FullTimeEmployee.class);
    
    // select: department, sum(salary)
    Path<Department> department = root.get(FullTimeEmployee_.department);
    Path<BigDecimal> salary = root.get(FullTimeEmployee_.salary);
    
    Expression<BigDecimal> sum = (Expression<BigDecimal>) criteriaBuilder.sum(salary).
        alias("sumOfSalary");
    
    criteriaQuery.select(criteriaBuilder.tuple(department.get(Department_.id), sum));
    
    // group by, having
    criteriaQuery.groupBy(department);
    criteriaQuery.having(criteriaBuilder.ge(sum, BigDecimal.valueOf(3000000.00)));
    
    // order by
    criteriaQuery.orderBy(criteriaBuilder.desc(sum));
    
    TypedQuery<Tuple> query = em.createQuery(criteriaQuery);
    List<Tuple> results = query.getResultList();

Equivalent JPQL:

    TypedQuery<Object[]> query = em.createQuery(
        "select e.department.id, sum(e.salary) sumOfSalary " +
        "from FullTimeEmployee e " +
        "group by e.department " +
        "having sum(e.salary) >= ?1 " +
        "order by sumOfSalary desc", Object[].class);
    query.setParameter(1, 3000000.00);
    List<Object[]> results = query.getResultList();

JPQL does not support aliases in groupBy or having clause.

Pagination

Query.setFirstResult(offset) and Query.setMaxResults(pageSize) are used to achieve pagination. For example,

Retrieve 1st page (page size: 100):

    TypedQuery<Employee> query = em.createQuery(...);
    query.setFirstResult(0);
    query.setMaxResults(100);
    List<Employee> employeesOnPageOne = query.getResultList();

Retrieve Nth page:

    query.setFirstResult(100 * (N-1));
    query.setMaxResults(100);
    List<Employee> employeesOnPageOne = query.getResultList();

Subquery

see Subquery.

Criteria Update

Example: update offices (size: M, not private) to be (size: L, private).

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaUpdate<Office> criteriaUpdate = criteriaBuilder.createCriteriaUpdate(Office.class);
    Root<Office> root = criteriaUpdate.from(Office.class);
    Predicate sizeRestriction = criteriaBuilder.equal(root.get("size"), Office.Size.M);
    Predicate privateRestriction = criteriaBuilder.equal(root.get("privateOffice"), false);
    criteriaUpdate.where(sizeRestriction, privateRestriction);

    criteriaUpdate.set("size", Office.Size.L);
    criteriaUpdate.set("privateOffice", true);

    Query query = em.createQuery(criteriaUpdate);
    int updated = query.executeUpdate();

Equivalent JPQL:

    Query query = em.createQuery("update Office o " +
        "set size=com.cmobilecom.jpa.example.managed_classes.Office$Size.L, privateOffice=true " +
        "where o.size=com.cmobilecom.jpa.example.managed_classes.Office$Size.M and o.privateOffice=false");
    int updated = query.executeUpdate();

Enum value literals: use fully qualified name.

Criteria Delete

Example: delete offices with specified office numbers.

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaDelete<Office> criteriaDelete = criteriaBuilder.createCriteriaDelete(Office.class); 
    Root<Office> root = criteriaDelete.from(Office.class);
    Path<String> officeNumberPath = root.get(Office_.number);
    Predicate restriction = officeNumberPath.in("100A", "100B");
    criteriaDelete.where(restriction);

    Query query = em.createQuery(criteriaDelete);
    int deleted = query.executeUpdate();

Equivalent JPQL:

    Query query = em.createQuery("delete from Office o where o.number in (?1, ?2)");
    query.setParameter(1, "100A");
    query.setParameter(2, "100B");
    int deleted = query.executeUpdate();

← JPQL→ ConverterFrames / No Frames