1625 words

FROM clause (JPQL / Criteria API)

The FROM clause declares query identification variables that represent iteration over objects in the database. A query identification variable is similar to a variable of a Java enhanced for loop in a program, since both are used for iteration over objects.

Range Variables

Range variables are query identification variables that iterate over all the database objects of a specific entity class hierarchy (i.e. an entity class and all its descendant entity classes). Identification variables are always polymorphic. JPQL does not provide a way to exclude descendant classes from iteration at the FROM clause level. JPA 2, however, adds support for filtering instances of specific types at the WHERE clause level by using a type expression.

For example, in the following query, c iterates over all the Country objects in the database:

SELECT c FROM Country AS c

The AS keyword is optional, and the same query can also be written as follows:

SELECT c FROM Country c

By default, the name of an entity class in a JPQL query is the unqualified name of the class (e.g. just Country with no package name). The default name can be overridden by specifying another name explicitly in the @Entityjavax.persistence.EntityJPA annotationSpecifies that the class is an entity.
See JavaDoc Reference Page...
's nameEntity.nameannotation element(Optional) The entity name.
See JavaDoc Reference Page...
annotation element.

Multiple range variables are allowed. For example, the following query returns all the pairs of countries that share a common border:

SELECT c1, c2 FROM Country c1, Country c2
WHERE c2 MEMBER OF c1.neighbors

Multiple variables are equivalent to nested loops in a program. The FROM clause above defines two loops. The outer loop uses c1 to iterate over all the Country objects. The inner loop uses c2 to also iterate over all the Country objects. A similar query with no WHERE clause would return all the possible combinations of two countries. The WHERE clause filters any pair of countries that do not share a border, returning as results only neighbor countries.

Caution is required when using multiple range variables. Iteration over about 1,000,000 database objects with a single range variable might be acceptable. But iteration over the same objects with two range variables forming nested loops (outer and inner) might prevent query execution within a reasonable response time.

Database Management Systems (DBMS), including ObjectDB, try to optimize execution of multi-variable queries. Whenever possible, full nested iteration over the entire Cartesian product is avoided. The above query, for example, can be executed as follows. An outer loop iterates with c1 over all the Country objects in the database. An inner loop iterates with c2 only over the neighbors collection of the outer c1. In this case, by propagation of a WHERE constraint to the FROM phase, a full iteration over the Cartesian product is avoided.

[INNER] JOIN

As discussed above, range variables represent iteration over all the database objects of a specified entity type. JPQL provides an additional type of identification variable, a join variable, which represent a more limited iteration over specified collections of objects.

The following query uses one range variable and one join variable:

SELECT c1, c2 FROM Country c1 INNER JOIN c1.neighbors c2

In JPQL, JOIN can only appear in a FROM clause. The INNER keyword is optional (i.e. INNER JOIN is equivalent to JOIN). c1 is declared as a range variable that iterates over all the Country objects in the database. c2 is declared as a join variable that is bound to the c1.neighbors path and iterates only over objects in that collection.

You might have noticed that this query is equivalent to the previous neighbors query, which has two range variables and a WHERE clause. However, this second query form that uses a join variable is preferred. Besides being shorter and cleaner, the second query describes the right and efficient way for executing the query (which is using a full range outer loop and a collection limited inner loop) without relying on DBMS optimizations.

It is quite common for JPQL queries to have a single range variable that serves as a root and additional join variables that are bound to path expressions. Join variables can also be bound to path expressions that are based on other join variables that appear earlier in the FROM clause.

Join variables can also be bound to a single value path expression. For example:

SELECT c, p.name FROM Country c JOIN c.capital p

In this case, the inner loop iterates over a single object because every Country c has only one Capital p. Join variables that are bound to a single value expression are less commonly used because usually they can be replaced by a simpler long path expression (which is not an option for a collection). For example:

SELECT c, c.capital.name FROM Country c

One exception is when OUTER JOIN is required because path expressions function as implicit INNER JOIN variables.

LEFT [OUTER] JOIN

To understand the purpose of OUTER JOIN, consider the following INNER JOIN query that retrieves pairs of (country name, capital name):

SELECT c.name, p.name FROM Country c JOIN c.capital p

The FROM clause defines iteration over (country, capital) pairs. A country with no capital city (e.g. Nauru, which does not have an official capital) is not part of any iterated pair and is therefore excluded from the query results. INNER JOIN simply skips any outer variable value (e.g. any Country) that has no matching inner variable (e.g. a Capital).

The behavior of OUTER JOIN is different, as demonstrated by the following query variant:

SELECT c, p.name FROM Country c LEFT OUTER JOIN c.capital p

The OUTER keyword is optional (LEFT OUTER JOIN is equivalent to LEFT JOIN). When using OUTER JOIN, if a specific outer variable does not have any matching inner value it gets at least a NULL value as a matching value in the FROM iteration. Therefore, a Country c with no Capital city has a minimum representation of (c, NULL) in the FROM iteration.

For example, unlike the INNER JOIN variant of this query that skips Nauru completely, the OUTER JOIN variant returns Nauru with a NULL value as its capital.

[LEFT [OUTER] | INNER] JOIN FETCH

JPA support of transparent navigation and fetch makes it very easy to use, since it provides the illusion that all the database objects are available in memory for navigation. But this feature could also cause performance problems.

For example, let's look at the following query execution and result iteration:

The query returns only Country instances.  Consequently, the loop that iterates over the results is inefficient, since retrieval of the referenced Capital objects is performed one at a time, i.e. the number of round trips to the database is much larger than necessary.

A simple solution is to use the following query, which returnes exactly the same result objects (Country instances):

SELECT c FROM Country c JOIN FETCH c.capital

The JOIN FETCH expression is not a regular JOIN and it does not define a JOIN variable. Its only purpose is specifying related objects that should be fetched from the database with the query results on the same round trip. Using this query improves the efficiency of iteration over the result Country objects because it eliminates the need for retrieving the associated Capital objects separately.

Notice, that if the Coutry and Capital objects are needed only for their names - the following report query could be even more efficient: 

SELECT c.name, c.capital.name FROM Country c

Reserved Identifiers

The name of a JPQL query variable must a valid Java identifier but cannot be one of the following reserved words:

ABS, ALL, AND, ANY, AS, ASC, AVG, BETWEEN, BIT_LENGTH, BOTH, BY, CASE, CHAR_LENGTH, CHARACTER_LENGTH, CLASS, COALESCE, CONCAT, COUNT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP,DELETE, DESC, DISTINCT, ELSE, EMPTY, END, ENTRY, ESCAPE, EXISTS, FALSE, FETCH,FROM, GROUP, HAVING, IN, INDEX, INNER, IS, JOIN, KEY, LEADING, LEFT, LENGTH, LIKE,LOCATE, LOWER, MAX, MEMBER, MIN, MOD, NEW, NOT, NULL, NULLIF, OBJECT, OF, OR, ORDER, OUTER, POSITION, SELECT, SET, SIZE, SOME, SQRT, SUBSTRING, SUM, THEN,TRAILING, TRIM, TRUE, TYPE, UNKNOWN, UPDATE, UPPER, VALUE, WHEN, WHERE.

JPQL variables as well as all the reserved identifiers in the list above are case insensitive. Therefore, ABS, abs, Abs and aBs are all invalid variable names.

FROM and JOIN in Criteria Queries

FROM query identification variables are represented in criteria queries by sub interfaces of Fromjavax.persistence.criteria.FromJPA interfaceRepresents a bound type, usually an entity that appears in the from clause, but may also be an embeddable belonging to an entity in the from clause.
See JavaDoc Reference Page...
:

Criteria Query Roots

The CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries.
See JavaDoc Reference Page...
's fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.
See JavaDoc Reference Page...
method serves as a factory of Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.
See JavaDoc Reference Page...
 instances.

For example, the following JPQL query, which defines two uncorrelated range variables - c1, c2:

SELECT c1, c2 FROM Country c1, Country c2

can be built as a criteria query using the following code:

Unlike other CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries.
See JavaDoc Reference Page...
 methods - invocation of the fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.
See JavaDoc Reference Page...
method does not override a previous invocation of that method. Every time the fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.
See JavaDoc Reference Page...
method is invoked - a new variable is added to the query.

Criteria Query Joins

JOIN variables are represented in criteria queries by the Joinjavax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.
See JavaDoc Reference Page...
interface (and its sub interfaces).

For example, the following JPQL query:

SELECT c, p.name FROM Country c LEFT OUTER JOIN c.capital p

can be built as a criteria query using the following code:

The Fromjavax.persistence.criteria.FromJPA interfaceRepresents a bound type, usually an entity that appears in the from clause, but may also be an embeddable belonging to an entity in the from clause.
See JavaDoc Reference Page...
interface provides various forms of the join method. Every invocation of join adds a new JOIN variable to the query. Since Fromjavax.persistence.criteria.FromJPA interfaceRepresents a bound type, usually an entity that appears in the from clause, but may also be an embeddable belonging to an entity in the from clause.
See JavaDoc Reference Page...
is the super interface of both Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.
See JavaDoc Reference Page...
and Joinjavax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.
See JavaDoc Reference Page...
 - join methods can be invoked on Root instances (as demonstrated above) as well as on previously built Join instances.

Criteria Query Fetch Joins

The following JPQL query:

SELECT c FROM Country c JOIN FETCH c.capital

can be built as a criteria query using the following code:

Several forms of the fetch method are defined in the Pathjavax.persistence.criteria.PathJPA interfaceRepresents a simple or compound attribute path from a bound type or collection, and is a "primitive" expression.
See JavaDoc Reference Page...
interface, which represents path expressions and is also the super interface of the Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.
See JavaDoc Reference Page...
and Joinjavax.persistence.criteria.JoinJPA interfaceA join to an entity, embeddable, or basic type.
See JavaDoc Reference Page...
interfaces.


This documentation explains how to use JPA in the context of the ObjectDB Object Database but mostly relevant
also for ORM JPA implementations, such as Hibernate (and HQL), EclipseLink, TopLink, OpenJPA and DataNucleus.
ObjectDB is not an ORM JPA implementation but an Object Database (ODBMS) for Java with built in JPA 2 support.