Linda DeMichiel's Blog

Probably the most significant of the new features introduced in the JPA 2.0 Public Draft is the Criteria API.  This is a non-string-based API for the dynamic construction of object-based queries.

Criteria queries are constructed in terms of query definition objects.  As with JPQL static and dynamic queries, criteria query definition objects are passed to the EntityManager createQuery method to create Query objects—and then executed using the methods of the Query API.

Loosely speaking, a QueryDefinition object can be thought of as a set of nodes corresponding to the semantic constructs of the query:

• domain objects, which correspond to the range variables and other identification variables of the JPQL FROM clause
• where clause predicates, which comprise one or more conditional expression objects
• select clauses, which comprise one or more "select item" objects
• order-by and group-by items
• subqueries
• and so on...

Constructing a Query Definition

The QueryBuilder interface is the factory for QueryDefinition objects.  A QueryBuilder instance is obtained from either the EntityManager or the EntityManagerFactory.  For example,

EntityManager em = ... ; 
QueryBuilder queryBuilder = em.getQueryBuilder();
QueryDefinition qdef = queryBuilder.createQueryDefinition();

Using the query builder, you can create either an "empty" QueryDefinition object or a QueryDefinition object with a root entity class.  The specification of query roots is the first step in constructing a query definition.  Query roots correspond to the range variables of SQL.  They specify the domain objects on which the query is based and which are not reachable by navigation or join. Query root are instances of the DomainObject interface.  In the simple case a query definition has a single root, so the QueryBuilder interface has a convenience method to support this.

DomainObject customer = queryBuilder.createQueryDefinition(Customer.class);

This method returns the DomainObject instance corresponding to the root.  It is semantically equivalent to the following in which the addRoot method is used to add a query root to an empty QueryDefinition object:

QueryDefinition qdef = queryBuilder.createQueryDefinition();
DomainObject customer = qdef.addRoot(Customer.class);

The DomainObject interface extends the QueryDefinition interface, and QueryDefinition operations applied to domain objects operate on the query definition as a whole.  Thus, for example, if you invoke the where method on a domain object, the query definition is modified to include the specified restriction predicate.

A query with a single root entity is assumed to select entities of that type, unless the select method of the QueryDefinition interface is used to specify otherwise.  Thus, the DomainObject customer in the query above represents a complete query definition.  We can pass it to the createQuery method and execute the resulting query, causing all instances of the Customer class to be returned:

Query q = em.createQuery(customer);
List myCustomers = q.getResultList();

The addition of further query roots, like additional range variables in JPQL, has the effect of inducing a cartesian product.

Given one or more query roots, a query domain can be further refined by the addition of other domain objects through join operations.   The argument to the join method is the name of the attribute in the referencing class.  The result is the added DomainObject instance.  This, like the domain objects that are roots, again references the underlying query definition object.

For example, to modify the above query definition to operate over customers and their orders, we would add:

DomainObject order = customer.join("orders");

When executed, this query returns all customers that have orders.  Note further that because the variables order and customer are referencing the same underlying query definition, the following calls to the EntityManager createQuery method are semantically equivalent:

em.createQuery(customer);
em.createQuery(order);


Defining the SELECT and WHERE Clauses

Assume we have the following classes:

@Entity public class Customer {
  @Id int custId;
  String name;
  ...
  @OneToMany(mappedBy="customer") Set<Order> orders;
  ...
}

@Entity public class Order {
  @Id int orderId;
  ...
  @ManyToOne Customer customer;
  @OneToMany(mappedBy="order") Set<LineItem> items;
  ...
}

@Entity public class LineItem {
  @Id int id;
  @ManyToOne Order order;
  @ManyToOne Product product;
  ...
}

@Entity public class Product {
  @Id int productId;
  String name;
  String productType;
  ...
}

Using JPQL, a query to return all customers ordering products of type 'printer' would look something like this:

SELECT c.name
FROM Customer c JOIN c.orders o JOIN o.items i
WHERE i.product.productType = 'printer'

To construct a semantically equivalent query using the Criteria API, we proceed as follows:

1)  Create a query definition object.  This query is rooted in the Customer class, so we again write:

DomainObject customer = queryBuilder.createQueryDefinition(Customer.class);

2)  Join Customer to Order and Order to LineItem.  Since the result of the join method is also a DomainObject, we chain the method invocations:

DomainObject item = customer.join("orders").join("items");

3)  Define the select list of the query and the restrictions over the results.  These modify the underlying query definition object, and can thus be defined in either order.  Let's assume we're defining the restrictions first:

customer.where(item.get("productType").equal("printer"));

The argument to the where method is a conditional predicate.  In this example, the arguments to the condition are a PathExpression instance (derived from invoking get("productType") on the item DomainObject) and the string "printer".  The where method returns this, so it supports method chaining.

The select method of the QueryDefinition interface modifies the query definition to specify the query result.  It also returns this:

customer.select(customer.get("name"));

Putting it all together, and using method chaining we have:

DomainObject customer = queryBuilder.createQueryDefinition(Customer.class);
DomainObject item = customer.join("orders").join("items");
customer.where(item.get("productType").equal("printer"))
        .select(customer.get("name"));

While this example of course just illustrates the basics, the Criteria API provides support for all the functionality of JPQL.

In the following sections we examine how we might rewrite the JPQL examples in part I of this blog.  I'm just going to reproduce the JPQL queries here.  To see the entity classes, please refer to part I.


Navigation

Using JPQL:

SELECT DISTINCT p.billedTo
FROM Employee e JOIN e.contactInfo.phones p
WHERE e.contactInfo.address.zipcode = '95054'
    AND p.phonetype = PhoneType.OFFICE

Or, equivalently:

SELECT DISTINCT p.billedTo
FROM Employee e JOIN e.contactInfo c JOIN c.phones p
WHERE e.contactInfo.address.zipcode = '95054'
    AND p.phonetype = PhoneType.OFFICE

This query navigates through the contactInfo embeddable in the FROM clause and again in the WHERE clause.  Because navigation in the FROM clause has the semantics of inner join, the Criteria API requires the use of the join method to construct the query domain.  The where method uses a compound predicate.

DomainObject e = queryBuilder.createQueryDefinition(Employee.class);
DomainObject p = e.join("contactInfo").join("phones");
e.where(e.get("contactInfo").get("address").get("zipcode").equal("95054")
        .and(p.get("phonetype").equal(PhoneType.OFFICE)))
 .selectDistinct(p.get("billedTo"));


Maps

Here's the first query over maps:

SELECT p
FROM PictureCategory c JOIN c.photos p
WHERE c.name = 'birds' AND KEY(p) LIKE '%egret%'

DomainObject c = queryBuilder.createQueryDefinition(PictureCategory.class);
DomainObject p = c.join("photos");
p.where(c.get("name").equal("birds").and(p.key().like("%egret%")))
 .select(p);

Note that, as in the JPQL query, a variable referring to a map type corresponds to the map value.

Here are the next two queries:

SELECT v.location.street
FROM VideoStore v JOIN v.videoInventory i
WHERE v.location.zipcode = '95054' AND KEY(i).title = 'Vertigo' AND VALUE(i) > 0

and

SELECT v.location.street, ENTRY(i)
FROM VideoStore v JOIN v.videoInventory i
WHERE v.location.zipcode = '95054' AND KEY(i).director = 'Hitchcock' AND VALUE(i) > 0

Using the Criteria API, these queries are written as follows:

DomainObject v = queryBuilder.createQueryDefinition(VideoStore.class);
DomainObject i = v.join("videoInventory");
v.where(v.get("location").get("zipcode").equal("95054")
         .and(i.key().get("title").equal("Vertigo"))
         .and(i.value().greaterThan(0)))
 .select(v.get("location").get("street"));

and

DomainObject v = queryBuilder.createQueryDefinition(VideoStore.class);
DomainObject i = v.join("videoInventory");
v.where(v.get("location").get("zipcode").equal("95054")
         .and(i.key().get("director").equal("Hitchcock"))
         .and(i.value().greaterThan(0)))
  .select(v.get("location").get("street"), i.entry());
 

Ordered Lists

Here's the query over ordered lists:

SELECT e
FROM Employee e JOIN e.dept d
WHERE d.name='Marketing' AND INDEX(e) < 5


DomainObject e = queryBuilder.createQueryDefinition(Employee.class);
DomainObject d = e.join("dept");
e.where(d.get("name").equal("Marketing").and(e.index().lessThan(5)));


Non-polymorphic Queries

The following two examples illustrate non-polymorphic queries:

Example 1:

SELECT e
FROM Employee e JOIN e.dept d
WHERE d.name = 'Marketing' AND TYPE(e) IN (PartTimeEmployee, Contractor)

In the criteria query, entity class objects are used to specify entity type arguments to the in method:

DomainObject e = queryBuilder.createQueryDefinition(Employee.class);
DomainObject d = e.join("dept");
e.where(d.get("name").equal("Marketing")
           .and(e.type().in(PartTimeEmployee.class, Contractor.class)));

Example 2:

SELECT e
FROM Employee e
WHERE TYPE(e) IN :empTypes

In the criteria query below, the param method of the QueryDefinition interface defines the parameter name.  As with JPQL, this parameter must be bound to a collection of entity class objects for the query execution.  Note that in a criteria query the parameter name is not prefixed with a colon (:).

DomainObject e = queryBuilder.createQueryDefinition(Employee.class);
e.where(e.type().in(e.param("empTypes")));

Example 3:

This query uses a TYPE expression to return the entity class:

SELECT TYPE(e), e.name
FROM Employee e JOIN e.dept d
WHERE d.name='Marketing'

As in the JPQL query, the entity class object is returned in the result of the criteria query:

DomainObject e = queryBuilder.createQueryDefinition(Employee.class);
DomainObject d = e.join("dept");
e.where(d.get("name").equal("Marketing"))
 .select(e.type(), e.get("name"));


Case Expressions

These are the case expression examples.  The Criteria API does not currently support update and delete operations.

SELECT c, CASE WHEN c.annualSpending > 10000 THEN 'Premier'
               WHEN c.annualSpending >  5000 THEN 'Gold'
               WHEN c.annualSpending >  2000 THEN 'Silver'
               ELSE 'Bronze'
          END
FROM Customer c


DomainObject c = queryBuilder.createQueryDefinition(Customer.class);
c.select(c, c.case().when(c.get("annualSpending").greaterThan(10000))
                    .then("Premier")
                    .when(c.get("annualSpending").greaterThan(5000))
                    .then("Gold")
                    .when(c.get("annualSpending").greaterThan(2000))
                    .then("Silver")
                    .else("Bronze"));

This one uses a NULLIF expression:

SELECT AVG(NULLIF(e.salary, -99999))
FROM Employee e

DomainObject e = queryBuilder.createQueryDefinition(Employee.class);
e.select(e.avg(e.nullif(e.get("salary"), -999999)));


Subqueries

The criteria API supports the use of both correlated and non-correlated subqueries, as does JPQL.   Like a top-level query, a subquery is constructed through the creation and modification of a QueryDefinition object.

The following JPQL query returns those customers whose unpaid balance is less than half of the average:

SELECT goodCustomer
FROM Customer goodCustomer
WHERE goodCustomer.balanceOwed < (
           SELECT AVG(c.balanceOwed)/2.0 FROM Customer c)

Using the criteria API, the corresponding query can be written as shown below.  There are QueryDefinition objects for both the subquery and the containing query.

DomainObject goodCustomer = queryBuilder.createQueryDefinition(Customer.class);
DomainObject customer = queryBuilder.createQueryDefinition(Customer.class);
goodCustomer.where(goodCustomer.get("balanceOwed)
                     .lessThan(customer.select(customer.get("balancedOwed")
                                                       .dividedBy(2.0)
                                                       .avg())));

In a correlated subquery, the subquery and the containing query share variables.

The following JPQL query selects those employees that make more than all of the managers in their department:

SELECT emp
FROM Employee emp
WHERE emp.salary > ALL (
    SELECT m.salary
    FROM Manager m
    WHERE m.department = emp.department)


Here's the criteria API equivalent.  I'm using QueryDefinition variables to make this very explicit, although like the earlier queries, this query can be written without them.

QueryDefinition qOuter = queryBuilder.createQueryDefinition();
DomainObject emp = qOuter.addRoot(Employee.class);

QueryDefinition qSubq = queryBuilder.createQueryDefinition();
DomainObject mgr = qSubq.addRoot(Manager.class);

qSubq.select(mgr.get("salary"))
     .where(mgr.get("department").equal(emp.get("department")));

qOuter.where(emp.get("salary").greaterThan(qSubq.all()));


Current Status

Our work in the Expert Group in designing the Criteria API involved numerous iterations and considerable debate as to the best approach, and we welcome your feedback on the results.  Not yet reflected in the draft is our intention to provide additional and/or alternative ways to obtain QueryBuilder objects to support the construction of QueryDefinition objects without the need for an entity manager or entity manager factory in order to support disconnected usage scenarios.  Since the provider's implementation of the QueryDefinition object is required to be serializable, this would also allow for QueryDefinition objects to be passed from a remote tier.

When you download the draft and review the spec, you'll notice that it flags a number of open issues and items that are in need of developer feedback.   Please feel free to send me and the expert group your thoughts at  jsr-317-pdr-feedback@sun.com.    Thanks!

The Java Persistence 2.0 Public Draft is scheduled to be released this week, so I thought it would be a good time to review some of the new features that you can expect to find.

If you're familiar with the first draft of the JPA 2.0 specification (the Early Draft), you know that it focused primarily on the area of O/R mapping improvements.  The Public Draft builds on these improvements, and provides ways to leverage them through extensions to the Java Persistence Query Language and through the new Criteria API.

Because there's a lot to cover, this will be a 2-part entry.  In this part, we'll examine some of the new JPQL functionality.  Part 2 will look at the Criteria API.

The functionality added in the Early Draft to support element collections, nested embeddable classes, embeddables having entity relationships, generalized maps, and ordered lists necessitated some changes to the JPQL syntax to make queries over these mapping types easy to write.  Further, we've also added some other requested (and much needed) functionality to the language.

Navigation

As you might expect, we've extended the dot (".") navigation syntax to handle embeddables with relationships and embeddables of embeddables.

Let's suppose we have an Employee entity, with a ContactInfo embeddable class used for contact information, and a set of phones referenced by the ContactInfo embeddable.  The basic structure of the classes might look something like this:

@Entity public class Employee {
  @Id int empId;
  String name;
  @ManyToOne Department dept;
  ContactInfo contactInfo;
  ...
}

@Embeddable public class ContactInfo {
  Address address; 
  @OneToMany Set<Phone> phones;
  ...
}

@Embeddable public class Address {
  String street;
  String city;
  String zipcode;
  ...
}

@Entity public class Phone {
  @Id int phoneId;
  String areaCode;
  String localNumber;
  String internalExtension;
  PhoneType phoneType;
  @ManyToOne Account billedTo;
  ...
}
 
If I want to query for the accounts to which the office phones for employees at Sun's Santa Clara campus (zipcode 95054) are billed, I might write a query like this:

SELECT DISTINCT p.billedTo
FROM Employee e JOIN e.contactInfo.phones p
WHERE e.contactInfo.address.zipcode = '95054' AND p.phonetype = PhoneType.OFFICE

In this query, we're navigating across the ContactInfo embeddable in the FROM clause to the Phones relation, as well as navigating into the ContactInfo embeddable in the WHERE clause to extract the zipcode.

Note that in the FROM clause, navigation behaves like an inner join. Thus, for example, in the following query, if there is no contact information specified for some employees, those employees will not appear in the query result.

SELECT e
FROM Employee e JOIN e.contactInfo.phones p

Identification variables can reference embeddables in the FROM clause, and JOIN can also be used in the FROM clause to navigate over embeddables, so we could also have written this query as follows:

SELECT e
FROM Employee e JOIN e.contactInfo c JOIN c.phones p

   
Maps

To support our generalized map functionality, we've added the KEY and VALUE operators for extracting map keys and map values.  By default, an identification value that refers to an association of type Map denotes the map value, so, strictly speaking, the VALUE operator serves mainly the purpose of documentation.

For example, suppose we have a map from photo name to file name:
 
@Entity public class PictureCategory {
    @Id String name;
    @ElementCollection Map<String, String> photos;
    ...
}

To search for my egret photos,  I can write:

SELECT VALUE(p)
FROM PictureCategory c JOIN c.photos p
WHERE c.name = 'birds' AND KEY(p) LIKE '%egret%'

The following query is equivalent:

SELECT p
FROM PictureCategory c JOIN c.photos p
WHERE c.name = 'birds' AND KEY(p) LIKE '%egret%'

Let's look at another example.  This one keys the map on an entity type:

@Entity public class VideoStore {
  @Id int id;
  String name;
  Address location;
  @ElementCollection Map<Movie, Integer> videoInventory;
  ...
}

@Entity public class Movie {
  @Id int id;
  String title;
  String director;
  @ManyToMany Set<Actor> stars;
  ...
}

In the language of the spec, KEY(p) and VALUE(p) are "general identification variables"—which means that they can be used for further navigation. Thus, to search for the movie Vertigo near Sun's Santa Clara campus, I can write:

SELECT v.location.street
FROM VideoStore v JOIN v.videoInventory i
WHERE v.location.zipcode = '95054' AND KEY(i).title = 'Vertigo' AND VALUE(i) > 0

Map entries don't support navigation, but they can be selected.  The result is returned as an instance of type java.util.Map.Entry.

SELECT v.location.street, ENTRY(i)
FROM VideoStore v JOIN v.videoInventory i
WHERE v.location.zipcode = '95054' AND KEY(i).director = 'Hitchcock' AND VALUE(i) > 0


Ordered Lists

Ordered lists were another frequently-requested feature for which we added O/R mapping support in the Early Draft.

JPA 1.0 supported List as a collection type, but this support did not include support for maintaining a persistent ordering.  In the JPA 2.0 Early Draft, we added the ability to specify the OrderColumn annotation, which means that the persistence provider is required to maintain the list ordering using a separate column when you manipulate the order of the list.  The JPQL INDEX operator allows you to query over the ordering.

For example, suppose a department manager wants to maintain a rank ordering of employees for the purpose of issuing raises.  We might have:

@Entity public class Employee {
  @Id int empId;
  String name;
  @ManyToOne Department dept;
  ...
}

@Entity
public class Department {
  @Id int deptId;
  String name;
  @OneToMany(mappedBy="dept") @OrderColumn List<Employee> members;
  ...
}

The following query retrieves the top employees in the marketing department.

SELECT e
FROM Employee e JOIN e.dept d
WHERE d.name='Marketing' AND INDEX(e) < 5


Non-polymorphic Queries

Another feature that we've added to JPQL is support for non-polymorphic queries.

The TYPE operator allows you to select an entity's type and to restrict a query to one or more entity types.

Thus, if we have an Employee hierarchy with FullTimeEmployee, PartTimeEmployee, and Contractor subtypes, we can write queries such as the following:

SELECT e
FROM Employee e JOIN e.dept d
WHERE d.name = 'Marketing' AND TYPE(e) IN (PartTimeEmployee, Contractor)

PartTimeEmployee and Contractor are entity names.  (Recall that a entity name, by default, corresponds to the unqualified name of the entity class.)

The IN operator has also been extended to accept collection-valued parameters. Thus, you can also write the following:

SELECT e
FROM Employee e
WHERE TYPE(e) IN :empTypes

The argument that should be passed to this query is a collection of class objects.

An entity type can also be returned as the query result, as in the following.

SELECT TYPE(e), e.name
FROM Employee e JOIN e.dept d
WHERE d.name='Marketing'

When the query is executed, the employee type is returned as a class object.


Case Expressions

Another addition to the query language is support for case expressions.  Both simple and general forms of CASE expressions are supported, as with SQL.  We now also support the NULLIF and COALESCE operators.

Here's an example of the CASE operator.  The value of the first CASE clause whose WHEN predicate is satisfied is returned.

SELECT c, CASE WHEN c.annualSpending > 10000 THEN 'Premier'
               WHEN c.annualSpending >  5000 THEN 'Gold'
               WHEN c.annualSpending >  2000 THEN 'Silver'
               ELSE 'Bronze'
          END
FROM Customer c

This example illustrates the simple form of CASE in a bulk UPDATE statement:

UPDATE EMPLOYEE e
SET e.salary = CASE e.rating WHEN 1 THEN e.salary * 1.1
                             WHEN 2 THEN e.salary * 1.05
                             ELSE e.salary * 1.001
               END

The NULLIF operator is useful particularly when the database encoding for missing or inapplicable information is represented in ways other than by nulls.  You may run across this when mapping to legacy databases.  Using NULLIF allows you to easily convert such values to nulls in queries.  If the arguments to NULLIF are equal, NULLIF returns null; otherwise it returns the value of the first argument.

Here's an example.  Let's suppose that salaries are represented as integer values and that missing salaries are encoded by -99999.

SELECT AVG(NULLIF(e.salary, -99999))
FROM Employee e


Scalar Expressions in the SELECT clause

While JPA 1.0 supported scalar expressions in the WHERE clause, it did not support them in the SELECT clause.    As the above queries illustrate, this frequently-requested feature has now also been added.


When you download the draft and review the spec, you'll notice that it flags a number of open issues and items that are in need of developer feedback.   Please feel free to send me and the expert group your thoughts at  jsr-317-pdr-feedback@sun.com.    Thanks!