Chapter 15. The query languages

In this chapter

• Writing JPQL and criteria queries
• Retrieving data efficiently with joins
• Reporting queries and subselects

Queries are the most interesting part of writing good data access code. A complex query may require a long time to get right, and its impact on the performance of the application can be tremendous. On the other hand, writing queries becomes much easier with more experience, and what seemed difficult at first is only a matter of knowing the available query languages.

This chapter covers the query languages available in JPA: JPQL and the criteria query API. We always show the same query example with both languages/API, where the result of the queries is equivalent.

We expect that you won’t read this chapter just once but will rely on it as a reference to look up the correct syntax for a particular query when coding your application. Hence, our writing style is less verbose, with many small code examples for different use cases. We also sometimes simplify parts of the CaveatEmptor application for better readability. For example, instead of referring to MonetaryAmount, we use a simple BigDecimal amount in comparisons.

Let’s start with some query terminology. You apply selection to define where the data should be retrieved from, restriction to match records to a given criteria, and projection to select the data you want returned from a query. You’ll find this chapter organized in this manner.

When we talk about queries in this chapter, we usually mean SELECT statements: operations that retrieve data from the database. JPA also supports UPDATE, DELETE, and even INSERT ... SELECT statements in JPQL, criteria, and SQL flavors, which we’ll discuss in section 20.1. We won’t repeat those bulk operations here and will focus on SELECT statements. We start with some basic selection examples.

15.1. Selection

First, when we say selection, we don’t mean the SELECT clause of a query. We aren’t talking about the SELECT statement as such, either. We are referring to selecting a relation variable—or, in SQL terms, the FROM clause. It declares where data for your query should come from: simplifying, which tables you “select” for a query. Alternatively, with classes instead of table names in JPQL:

from Item

The following query (just a FROM clause) retrieves all Item entity instances. Hibernate generates the following SQL:

select i.ID, i.NAME, ... from ITEM i

The equivalent criteria query can be built with the from() method, passing in the entity name:

CriteriaQuery criteria = cb.createQuery(Item.class);
criteria.from(Item.class);

Hibernate understands queries with only a FROM clause or criterion. Unfortunately, the JPQL and criteria queries we’ve just shown aren’t portable; they aren’t JPA-compliant. The JPA specification requires that a JPQL query have a SELECT clause and that portable criteria queries call the select() method.

This requires assignment of aliases and query roots, our next topic.

15.1.1. Assigning aliases and query roots

select i from Item as i

select i from Item i

for(Iterator i = result.iterator(); i.hasNext();) {
    Item item = (Item) i.next();
    // ...
}

CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
criteria.select(i);

criteria.select(criteria.from(Item.class));

EntityType entityType = getEntityType(
    em.getMetamodel(), "Item"
);
<enter/>
criteria.select(criteria.from(entityType));

15.1.2. Polymorphic queries

JPQL, as an object-oriented query language, supports polymorphic queries—queries for instances of a class and all instances of its subclasses, respectively. Consider the following queries:

select bd from BillingDetails bd
criteria.select(criteria.from(BillingDetails.class));

These queries return all instances of the type BillingDetails, which is an abstract class. In this case, each instance is of a subtype of BillingDetails: CreditCard or BankAccount. If you want only instances of a particular subclass, you may use this:

select cc from CreditCard cc
criteria.select(criteria.from(CreditCard.class));

The class named in the FROM clause doesn’t even need to be a mapped persistent class; any class will do. The following query returns all persistent objects:

select o from java.lang.Object o

select s from java.io.Serializable s

select bd from BillingDetails bd where type(bd) = CreditCard
Root<BillingDetails> bd = criteria.from(BillingDetails.class);
criteria.select(bd).where(
    cb.equal(bd.type(), CreditCard.class)
);

select bd from BillingDetails bd where type(bd) in :types
Root<BillingDetails> bd = criteria.from(BillingDetails.class);
criteria.select(bd).where(
    bd.type().in(cb.parameter(List.class, "types"))
);

Query query = // ...
query.setParameter("types", Arrays.asList(CreditCard.class, BankAccount.class));

select bd from BillingDetails bd where not type(bd) = BankAccount
Root<BillingDetails> bd = criteria.from(BillingDetails.class);
criteria.select(bd).where(
    cb.not(cb.equal(bd.type(), BankAccount.class))
);

15.2. Restriction

Usually, you don’t want to retrieve all instances of a class from the database. You must be able to express constraints on the data returned by the query. We call this restriction. The WHERE clause declares restriction conditions in SQL and JPQL, and the where() method is the equivalent in the criteria query API.

This is a typical WHERE clause that restricts the results to all Item instances with a given name:

select i from Item i where i.name = 'Foo'
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.equal(i.get("name"), "Foo")
);

The query expresses the constraint in terms of a property, name, of the Item class.

The SQL generated by these queries is

select i.ID, i.NAME, ... from ITEM i where i.NAME = 'Foo'

You can include string literals in your statements and conditions, with single quotes. For date, time, and timestamp literals, use the JDBC escape syntax: ... where i.auction-End = {d '2013-26-06'}. Note that your JDBC driver and DBMS define how to parse this literal and what other variations they support. Remember our advice from the previous chapter: don’t concatenate unfiltered user input into your query string—use parameter binding. Other common literals in JPQL are true and false:

select u from User u where u.activated = true
Root<User> u = criteria.from(User.class);
criteria.select(u).where(
    cb.equal(u.get("activated"), true)
);

SQL (JPQL and criteria queries) expresses restrictions with ternary logic. The WHERE clause is a logical expression that evaluates to true, false, or null.

Let’s walk through the most common comparison operators in logical expressions, including ternary operators.

15.2.1. Comparison expressions

JPQL and the criteria API support the same basic comparison operators as SQL. Here are a few examples that should look familiar if you know SQL.

The following query returns all bids with amounts in a given range:

The criteria query may look a bit strange; you probably haven’t seen generics in the middle of expressions often in Java. The Root#get() method produces a Path<X> of an entity attribute. To preserve type safety, you must specify the attribute type of that Path, as in <BigDecimal>get("amount"). The other two arguments of the between() method then must be of the same type, or the comparison wouldn’t make sense or compile.

The following query returns all bids with amounts greater than the given value:

The gt() method only accepts arguments of Number type, such as BigDecimal or -Integer. If you need to compare values of other types, for example a Date, use greater-Than() instead:

Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.greaterThan(
        i.<Date>get("auctionEnd"),
        tomorrowDate
    )
);

The following query returns all users with the user names “johndoe” and “janeroe”:

select u from User u where u.username in ('johndoe', 'janeroe')
Root<User> u = criteria.from(User.class);
criteria.select(u).where(
    cb.<String>in(u.<String>get("username"))
        .value("johndoe")
        .value("janeroe")
);

For restrictions with enums, use the fully qualified literal:

select i from Item i
    where i.auctionType = org.jpwh.model.querying.AuctionType.HIGHEST_BID
<enter/>
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.equal(
        i.<AuctionType>get("auctionType"),
        AuctionType.HIGHEST_BID
    )
);

Because SQL relies on ternary logic, testing for null values requires special care. You use the IS [NOT] NULL operators in JPQL and isNull() and isNotNull() in the criteria query API.

Here are IS NULL and isNull() in action, getting items without a buy-now price:

select i from Item i where i.buyNowPrice is null
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.isNull(i.get("buyNowPrice"))
);

Using IS NOT NULL and isNotNull(), you return items with a buy-now price:

select i from Item i where i.buyNowPrice is not null
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.isNotNull(i.get("buyNowPrice"))
);

The LIKE operator allows wildcard searches, where the wildcard symbols are % and _, as in SQL:

select u from User u where u.username like 'john%'
Root<User> u = criteria.from(User.class);
criteria.select(u).where(
    cb.like(u.<String>get("username"), "john%")
);

The expression john% restricts the result to users with a username starting with “john”. You may also negate the LIKE operator, for example, in a substring match expression:

select u from User u where u.username not like 'john%'
Root<User> u = criteria.from(User.class);
criteria.select(u).where(
    cb.like(u.<String>get("username"), "john%").not()
);

You can match any substring by surrounding the search string with percentage -characters:

select u from User u where u.username like '%oe%'
Root<User> u = criteria.from(User.class);
criteria.select(u).where(
    cb.like(u.<String>get("username"), "%oe%")
);

The percentage symbol stands for any sequence of characters; the underscore can be used to wildcard a single character. You can escape with a character of your choice if you want a literal percentage or underscore:

select i from Item i
    where i.name like 'Name\_with\_underscores' escape :escapeChar
query.setParameter("escapeChar", "\");
<enter/>
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.like(i.<String>get("name"), "Name\_with\_underscores", '')
);

These queries return all items with Name_with_underscores. In Java strings, the character is the escape character, so you must escape it, which explains the double-backslash in the example.

JPA also supports arithmetic expressions:

select b from Bid b where (b.amount / 2) - 0.5 > 49
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(
    cb.gt(
        cb.diff(
            cb.quot(b.<BigDecimal>get("amount"), 2),
            0.5
        ),
        49
    )
);

Logical operators (and parentheses for grouping) combine expressions:

select i from Item i
    where (i.name like 'Fo%' and i.buyNowPrice is not null)
          or i.name = 'Bar'/
<enter/>
Root<Item> i = criteria.from(Item.class);
<enter/>
Predicate predicate = cb.and(
    cb.like(i.<String>get("name"), "Fo%"),
    cb.isNotNull(i.get("buyNowPrice"))
);
<enter/>
predicate = cb.or(
    predicate,
    cb.equal(i.<String>get("name"), "Bar")
);
<enter/>
criteria.select(i).where(predicate);

Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.like(i.<String>get("name"), "Fo%"),
    // AND
    cb.isNotNull(i.get("buyNowPrice"))
    // AND ...
);

15.2.2. Expressions with collections

All expressions in the previous sections had only single-valued path expressions: user.username, item.buyNowPrice, and so on. You can also write path expressions that end in collections, and apply some operators and functions.

For example, let’s assume you want to restrict your query result to Category instances that have an element in their items collection:

select c from Category c
    where c.items is not empty
<enter/>
Root<Category> c = criteria.from(Category.class);
criteria.select(c).where(
    cb.isNotEmpty(c.<Collection>get("items"))
);

The c.items path expression in the JPQL query terminates in a collection property: the items of a Category. Note that it’s always illegal to continue a path expression after a collection-valued property: you can’t write c.items.buyNowPrice.

You can restrict the result depending on the size of the collection, with the size() function:

select c from Category c
    where size(c.items) > 1
<enter/>
Root<Category> c = criteria.from(Category.class);
criteria.select(c).where(
    cb.gt(
        cb.size(c.<Collection>get("items")),
        1
    )
);

You can also express that you require a particular element to be present in a collection:

select c from Category c
    where :item member of c.items
<enter/>
Root<Category> c = criteria.from(Category.class);
criteria.select(c).where(
    cb.isMember(
        cb.parameter(Item.class, "item"),
        c.<Collection<Item>>get("items")
    )
);

For persistent maps, the special operators key(), value(), and entry() are available. Let’s say you have a persistent map of Image embeddables for each Item, as shown in section 7.2.4. The filename of each Image is the map key. The following query retrieves all Image instances with a.jpg suffix in the filename:

select value(img)
    from Item i join i.images img
    where key(img) like '%.jpg'

The value() operator returns the values of the Map, and the key() operator returns the key set of the Map. If you want to return Map.Entry instances, use the entry() operator.

Let’s look at other available functions next, not limited to collections.

15.2.3. Calling functions

An extremely powerful feature of the query languages is the ability to call functions in the WHERE clause. The following queries call the lower() function for case-insensitive searching:

select i from Item i where lower(i.name) like 'ba%'
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.like(cb.lower(i.<String>get("name")), "ba%")
);

Look at the summary of all available functions in table 15.2. For criteria queries, the equivalent methods are in CriteriaBuilder, with slightly different name formatting (using camelCase and no underscores).

Table 15.2. JPA query functions (overloaded methods not listed)

select i from Item i
    where
        datediff('DAY', i.createdOn, i.auctionEnd)
    > 1

select i from Item i
    where
        function('DATEDIFF', 'DAY', i.createdOn, i.auctionEnd)
    > 1

Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
    cb.gt(
        cb.function(
            "DATEDIFF",
            Integer.class,
            cb.literal("DAY"),
            i.get("createdOn"),
            i.get("auctionEnd")
        ),
        1
    )
);

15.2.4. Ordering query results

All query languages provide some mechanism for ordering query results. JPQL provides an ORDER BY clause, similar to SQL.

The following query returns all users, ordered by username, ascending by default:

select u from User u order by u.username

You specify ascending or descending order with the asc and desc keywords:

select u from User u order by u.username desc

With the criteria query API, you must specify ascending and descending order with asc() or desc():

Root<User> u = criteria.from(User.class);
criteria.select(u).orderBy(
    cb.desc(u.get("username"))
);

You may order by multiple properties:

select u from User u order by u.activated desc, u.username asc
Root<User> u = criteria.from(User.class);
criteria.select(u).orderBy(
    cb.desc(u.get("activated")),
    cb.asc(u.get("username"))
);

select i.name from Item i order by i.buyNowPrice asc
select i from Item i order by i.seller.username desc

You now know how to write the FROM, WHERE, and ORDER BY clauses. You know how to select the entities you want to retrieve instances of and the necessary expressions and operations to restrict and order the result. All you need now is the ability to project the data of this result to what you need in your application.

15.3. Projection

In simple terms, selection and restriction in a query is the process of declaring which tables and rows you want to query. Projection is defining the “columns” you want returned to the application: the data you need. The SELECT clause in JPQL performs projections.

15.3.1. Projection of entities and scalar values

For example, consider the following queries:

select i, b from Item i, Bid b
Root<Item> i = criteria.from(Item.class);
Root<Bid> b = criteria.from(Bid.class);
criteria.select(cb.tuple(i, b));
<enter/>
/* Convenient alternative:
criteria.multiselect(
    criteria.from(Item.class),
    criteria.from(Bid.class)
);
*/

As promised earlier, this criteria query shows how you can add several Roots by calling the from() method several times. To add several elements to your projection, either call the tuple() method of CriteriaBuilder, or the shortcut multiselect().

You’re creating a Cartesian product of all Item and Bid instances. The queries return ordered pairs of Item and Bid entity instances:

The query returns a List of Object[] . At index 0 is the Item , and at index 1 is the Bid .

Because this is a product, the result contains every possible combination of Item and Bid rows found in the two underlying tables. Obviously, this query isn’t useful, but you shouldn’t be surprised to receive a collection of Object[] as a query result. Hibernate manages all Item and Bid entity instances in persistent state, in the persistence context. Note how the HashSets filter out duplicate Item and Bid instances.

Alternatively, with the Tuple API, in criteria queries you get typed access to the result list. Start by calling createTupleQuery() to create a CriteriaQuery<Tuple>. Then, refine the query definition by adding aliases for the entity classes:

The Tuple API offers several ways to access the result, by index, by alias, or untyped meta access:

The following projection also returns a collection of Object[]s:

The Object[]s returned by this query contain a Long at index 0, a String at index 1, and an Address at index 2. The first two are scalar values; the third is an embedded class instance. None are managed entity instances! Therefore, these values aren’t in any persistent state, like an entity instance would be. They aren’t transactional and obviously aren’t checked automatically for dirty state. We say that all of these values are transient. This is the kind of query you need to write for a simple reporting screen, showing all user names and their home addresses.

You have now seen path expressions several times: using dot-notation, you can reference properties of an entity, such as User#username with u.username. For a nested embedded property, for example, you can write the path u.homeAddress.city.zipcode. These are single-valued path expressions, because they don’t terminate in a mapped collection property.

A more convenient alternative than Object[] or Tuple, especially for report queries, is dynamic instantiation in projections, which is next.

15.3.2. Using dynamic instantiation

Let’s say you have a reporting screen in your application where you need to show some data in a list. You want to show all auction items and when each auction ends. You don’t want to load managed Item entity instances, because no data will be modified: you only read data.

First, write a class called ItemSummary with a constructor that takes a Long for the item’s identifier, a String for the item’s name, and a Date for the item’s auction end timestamp:

public class ItemSummary {
    public ItemSummary(Long itemId, String name, Date auctionEnd) {
        // ...
    }
<enter/>
    // ...
}

We sometimes call these kinds of classes data transfer objects (DTOs), because their main purpose is to shuttle data around in the application. The ItemSummary class isn’t mapped to the database, and you can add arbitrary methods (getter, setter, printing of values) as needed by your reporting user interface.

Hibernate can directly return instances of ItemSummary from a query with the new keyword in JPQL and the construct() method in criteria:

In the result list of this query, each element is an instance of ItemSummary. Note that in JPQL, you must use a fully qualified class name, which means including the package name. Also note that nesting constructor calls isn’t supported: you can’t write new ItemSummary(..., new UserSummary(...)).

Dynamic instantiation isn’t limited to non-persistent data transfer classes like ItemSummary. You can construct a new Item or a User in a query, which is a mapped entity class. The only important rule is that the class must have a matching constructor for your projection. But if you construct entity instances dynamically, they won’t be in persistent state when returned from the query! They will be in transient or detached state, depending on whether you set the identifier value. One use case for this feature is simple data duplication: Retrieve a “new” transient Item with some values copied into the constructor from the database, set some other values in the application, and then store it in the database with persist().

If your DTO class doesn’t have the right constructor, and you want to populate it from a query result through setter methods or fields, apply a ResultTransformer, as shown in in section 16.1.3. Later, we have more examples of aggregation and grouping.

Next, we’re going to look at an issue with projection that is frequently confusing for many engineers: handling duplicates.

15.3.3. Getting distinct results

When you create a projection in a query, the elements of the result aren’t guaranteed to be unique. For example, item names aren’t unique, so the following query may return the same name more than once:

select i.name from Item i
CriteriaQuery<String> criteria = cb.createQuery(String.class);
<enter/>
criteria.select(
    criteria.from(Item.class).<String>get("name")
);

It’s difficult to see how it could be meaningful to have two identical rows in a query result, so if you think duplicates are likely, you normally apply the DISTINCT keyword or distinct() method:

select distinct i.name from Item i
CriteriaQuery<String> criteria = cb.createQuery(String.class);
<enter/>
criteria.select(
    criteria.from(Item.class).<String>get("name")
);
criteria.distinct(true);

This eliminates duplicates from the returned list of Item descriptions and translates directly into the SQL DISTINCT operator. The filtering occurs at the database level. Later in this chapter, we show you that this isn’t always the case.

Earlier, you saw function calls in restrictions, in the WHERE clause. You can also call functions in projections, to modify the returned data within the query.

15.3.4. Calling functions in projections

The following queries return a custom String with the concat() function in the projection:

This query returns a List of Strings, each with the form “[Item name]:[Auction end date]”. This example also shows that you can write nested function calls.

Next, the coalesce() function returns null if all its arguments evaluate to null; otherwise it returns the value of the first non-null argument:

select i.name, coalesce(i.buyNowPrice, 0) from Item i
Root<Item> i = criteria.from(Item.class);
criteria.multiselect(
    i.get("name"),
    cb.coalesce(i.<BigDecimal>get("buyNowPrice"), 0)
);

If an Item doesn’t have a buyNowPrice, a BigDecimal for the value zero is returned instead of null.

Similar to coalesce() but more powerful are case/when expressions. The following query returns the username of each User and an additional String with either “Germany”, “Switzerland”, or “Other”, depending on the length of the user’s address zipcode:

select
    u.username,
    case when length(u.homeAddress.zipcode) = 5 then 'Germany'
         when length(u.homeAddress.zipcode) = 4 then 'Switzerland'
         else 'Other'
    end
from User u
<enter/>
// Check String literal support; see Hibernate bug HHH-8124
Root<User> u = criteria.from(User.class);
criteria.multiselect(
    u.get("username"),
    cb.selectCase()
        .when(
            cb.equal(
                cb.length(u.get("homeAddress").<String>get("zipcode")), 5
            ), "Germany"
        )
        .when(
            cb.equal(
                cb.length(u.get("homeAddress").<String>get("zipcode")), 4
            ), "Switzerland"
        )
        .otherwise("Other")
);

For the built-in standard functions, refer to the tables in the previous section. Unlike function calls in restrictions, Hibernate won’t pass on an unknown function call in a projection to the database as a plain direct SQL function call. Any function you’d like to call in a projection must be known to Hibernate and/or invoked with the special function() operation of JPQL.

This projection returns the name of each auction Item and the number of days between item creation and auction end, calling the SQL datediff() function of the H2 database:

select
    i.name,
    function('DATEDIFF', 'DAY', i.createdOn, i.auctionEnd)
from Item i

Root<Item> i = criteria.from(Item.class);
criteria.multiselect(
    i.get("name"),
    cb.function(
        "DATEDIFF",
        Integer.class,
        cb.literal("DAY"),
        i.get("createdOn"),
        i.get("auctionEnd")
    )
);

If instead you want to call a function directly, you give Hibernate the function’s return type, so it can parse the query. You add functions for invocation in projections by extending your configured org.hibernate.Dialect. The datediff() function is already registered for you in the H2 dialect. Then, you can either call it as shown with function(), which works in other JPA providers when accessing H2, or directly as date-diff(), which most likely only works in Hibernate. Check the source code of the dialect for your database; you’ll probably find many other proprietary SQL functions already registered there.

Furthermore, you can add SQL functions programmatically on boot to Hibernate by calling the method applySqlFunction() on a Hibernate MetadataBuilder. The following example adds the SQL function lpad() to Hibernate before it’s started:

...
MetadataBuilder metadataBuilder = metadataSources.getMetadataBuilder();
metadataBuilder.applySqlFunction(
    "lpad",
    new org.hibernate.dialect.function.StandardSQLFunction(
        "lpad", org.hibernate.type.StringType.INSTANCE
    )
);

See the Javadoc of SQLFunction and its subclasses for more information.

Next, we look at aggregation functions, which are the most useful functions in reporting queries.

15.3.5. Aggregation functions

Reporting queries take advantage of the database’s ability to perform efficient grouping and aggregation of data. For example, a typical report query would retrieve the highest initial item price in a given category. This calculation can occur in the database, and you don’t have to load many Item entity instances into memory.

The aggregation functions standardized in JPA are count(), min(), max(), sum(), and avg().

The following query counts all Items:

select count(i) from Item i
criteria.select(
    cb.count(criteria.from(Item.class))
);

The query returns the result as a Long:

Long count = (Long)query.getSingleResult();

The special count(distinct) JPQL function and countDistinct() method ignore duplicates:

select count(distinct i.name) from Item i
criteria.select(
    cb.countDistinct(
        criteria.from(Item.class).get("name")
    )
);

The following query calculates the total value of all Bids:

select sum(b.amount) from Bid b
CriteriaQuery<Number> criteria = cb.createQuery(Number.class);
criteria.select(
    cb.sum(
        criteria.from(Bid.class).<BigDecimal>get("amount")
    )
);

This query returns a BigDecimal, because the amount property is of type BigDecimal. The sum() function also recognizes the BigInteger property type and returns Long for all other numeric property types.

The next query returns the minimum and maximum bid amounts for a particular Item:

select min(b.amount), max(b.amount) from Bid b
    where b.item.id = :itemId
<enter/>
Root<Bid> b = criteria.from(Bid.class);
criteria.multiselect(
    cb.min(b.<BigDecimal>get("amount")),
    cb.max(b.<BigDecimal>get("amount"))
);
criteria.where(
    cb.equal(
        b.get("item").<Long>get("id"),
        cb.parameter(Long.class, "itemId")
    )
);

The result is an ordered pair of BigDecimals (two instances of BigDecimals, in an Object[] array).

When you call an aggregation function in the SELECT clause, without specifying any grouping in a GROUP BY clause, you collapse the results down to a single row, containing the aggregated value(s). This means (in the absence of a GROUP BY clause) any SELECT clause that contains an aggregation function must contain only aggregation functions.

For more advanced statistics and for reporting, you need to be able to perform grouping, which is up next.

15.3.6. Grouping

JPA standardizes several features of SQL that are most commonly used for reporting—although they’re also used for other things. In reporting queries, you write the SELECT clause for projection and the GROUP BY and HAVING clauses for aggregation.

Just like in SQL, any property or alias that appears outside of an aggregate function in the SELECT clause must also appear in the GROUP BY clause. Consider the next query, which counts the number of users with each last name:

select u.lastname, count(u) from User u
    group by u.lastname
<enter/>
Root<User> u = criteria.from(User.class);
criteria.multiselect(
    u.get("lastname"),
    cb.count(u)
);
criteria.groupBy(u.get("lastname"));

In this example, the u.lastname property isn’t inside an aggregation function, so projected data has to be “grouped by” u.lastname. You also don’t need to specify the property you want to count; the count(u) expression is automatically translated into count(u.id).

The next query finds the average Bid#amount for each Item:

select i.name, avg(b.amount)
    from Bid b join b.item i
    group by i.name
<enter/>
Root<Bid> b = criteria.from(Bid.class);
criteria.multiselect(
    b.get("item").get("name"),
    cb.avg(b.<BigDecimal>get("amount"))
);
criteria.groupBy(b.get("item").get("name"));

select i, avg(b.amount)
    from Bid b join b.item i
    group by i

select i, avg(b.amount)
    from Bid b join b.item i
    group by i.id, i.name, i.createdOn, i.auctionEnd,
             i.auctionType, i.approved, i.buyNowPrice,
             i.seller
<enter/>
Root<Bid> b = criteria.from(Bid.class);
Join<Bid, Item> i = b.join("item");
criteria.multiselect(
    i,
    cb.avg(b.<BigDecimal>get("amount"))
);
criteria.groupBy(
    i.get("id"), i.get("name"), i.get("createdOn"), i.get("auctionEnd"),
    i.get("auctionType"), i.get("approved"), i.get("buyNowPrice"),
    i.get("seller")
);

select u.lastname, count(u) from User u
    group by u.lastname
    having u.lastname like 'D%'
<enter/>
Root<User> u = criteria.from(User.class);
criteria.multiselect(
    u.get("lastname"),
    cb.count(u)
);
criteria.groupBy(u.get("lastname"));
criteria.having(cb.like(u.<String>get("lastname"), "D%"));

The previous sections should get you started with basic queries. It’s time to look at some more complex options. For many engineers, the most difficult to understand but also one of most powerful benefits of the relational model is the ability to join arbitrary data.

15.4. Joins

Join operations combine data in two (or more) relations. Joining data in a query also enables you to fetch several associated instances and collections in a single query: for example, to load an Item and all its bids in one round trip to the database. We now show you how basic join operations work and how to use them to write such dynamic fetching strategies. Let’s first look at how joins work in SQL queries, without JPA.

15.4.1. Joins with SQL

Let’s start with the example we already mentioned: joining the data in the ITEM and BID tables, as shown in figure 15.1. The database contains three items: the first has three bids, the second has one bid, and the third has no bids. Note that we don’t show all columns; hence the dotted lines.

Figure 15.1. The ITEM and BID tables are obvious candidates for a join operation.

ITEM

BID

What most people think of when they hear the word join in the context of SQL databases is an inner join. An inner join is the most important of several types of joins and the easiest to understand. Consider the SQL statement and result in figure 15.2. This SQL statement contains an ANSI-style inner join in the FROM clause.

Figure 15.2. The result of an ANSI-style inner join of two tables

If you join the ITEM and BID tables with an inner join, with the condition that the ID of an ITEM row must match the ITEM_ID value of a BID row, you get items combined with their bids in the result. Note that the result of this operation contains only items that have bids.

You can think of a join as working as follows: first you take a product of the two tables, by taking all possible combinations of ITEM rows with BID rows. Second, you filter these combined rows with a join condition: the expression in the ON clause. (Any good database engine has much more sophisticated algorithms to evaluate a join; it usually doesn’t build a memory-consuming product and then filter out rows.) The join condition is a Boolean expression that evaluates to true if the combined row is to be included in the result.

It’s crucial to understand that the join condition can be any expression that evaluates to true. You can join data in arbitrary ways; you aren’t limited to comparisons of identifier values. For example, the join condition on i.ID = b.ITEM_ID and b.AMOUNT > 100 would only include rows from the BID table that also have an AMOUNT greater than 100. The ITEM_ID column in the BID table has a foreign key constraint, ensuring that a BID has a reference to an ITEM row. This doesn’t mean you can only join by comparing primary and foreign key columns. Key columns are of course the most common operands in a join condition, because you often want to retrieve related information together.

If you want all items, not just the ones which have related bids, and NULL instead of bid data when there is no corresponding bid, then you write a (left) outer join, as shown in figure 15.3.

Figure 15.3. The result of an ANSI-style left outer join of two tables

In case of the left outer join, each row in the (left) ITEM table that never satisfies the join condition is also included in the result, with NULL returned for all columns of BID. Right outer joins are rarely used; developers always think from left to right and put the “driving” table of a join operation first. In figure 15.4, you can see the same result with BID instead of ITEM as the driving table, and a right outer join.

Figure 15.4. The result of an ANSI-style right outer join of two tables

In SQL, you usually specify the join condition explicitly. Unfortunately, it isn’t possible to use the name of a foreign key constraint to specify how two tables are to be joined: select * from ITEM join BID on FK_BID_ITEM_ID doesn’t work.

You specify the join condition in the ON clause for an ANSI-style join or in the WHERE clause for a so-called theta-style join: select * from ITEM i, BID b where i.ID = b.ITEM_ID. This is an inner join; here you see that a product is created first in the FROM clause.

We now discuss JPA join options. Remember that Hibernate eventually translates all queries into SQL, so even if the syntax is slightly different, you should always refer to the illustrations shown in this section and verify that you understand what the resulting SQL and result set looks like.

15.4.2. Join options in JPA

JPA provides four ways of expressing (inner and outer) joins in queries:

• An implicit association join with path expressions
• An ordinary join in the FROM clause with the join operator
• A fetch join in the FROM clause with the join operator and the fetch keyword for eager fetching
• A theta-style join in the WHERE clause

Let’s start with implicit association joins.

15.4.3. Implicit association joins

In JPA queries, you don’t have to specify a join condition explicitly. Rather, you specify the name of a mapped Java class association. This is the same feature we’d prefer to have in SQL: a join condition expressed with a foreign key constraint name. Because you’ve mapped most, if not all, foreign key relationships of your database schema, you can use the names of these mapped associations in the query language. This is syntactical sugar, but it’s convenient.

For example, the Bid entity class has a mapped many-to-one association named item, with the Item entity class. If you refer to this association in a query, Hibernate has enough information to deduce the join expression with a key column comparison. This helps make queries less verbose and more readable.

Earlier in this chapter, we showed you property path expressions, using dot-notation: single-valued path expressions such as user.homeAddress.zipcode and collection--valued path expressions such as item.bids. You can create a path expression in an implicit inner join query:

select b from Bid b where b.item.name like 'Fo%'
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(
    cb.like(
        b.get("item").<String>get("name"),
        "Fo%"
    )
);

The path b.item.name creates an implicit join on the many-to-one associations from Bid to Item—the name of this association is item. Hibernate knows that you mapped this association with the ITEM_ID foreign key in the BID table and generates the SQL join condition accordingly. Implicit joins are always directed along many-to-one or one-to-one associations, never through a collection-valued association (you can’t write item.bids.amount).

Multiple joins are possible in a single path expression:

select b from Bid b where b.item.seller.username = 'johndoe'
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(
    cb.equal(
        b.get("item").get("seller").get("username"),
        "johndoe"
    )
);

This query joins rows from the BID, the ITEM, and the USER tables.

We frown on the use of this syntactic sugar for more complex queries. SQL joins are important, and especially when optimizing queries, you need to be able to see at a glance exactly how many of them there are. Consider the following query:

select b from Bid b where b.item.seller.username = 'johndoe'
   and b.item.buyNowPrice is not null
<enter/>
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(
    cb.and(
        cb.equal(
            b.get("item").get("seller").get("username"),
            "johndoe"
        ),
        cb.isNotNull(b.get("item").get("buyNowPrice"))
    )
);

How many joins are required to express such a query in SQL? Even if you get the answer right, it takes more than a few seconds to figure out. The answer is two. The generated SQL looks something like this:

select b.*
    from BID b
        inner join ITEM i on b.ITEM_ID = i.ID
        inner join USER u on i.SELLER_ID = u.ID
        where u.USERNAME = 'johndoe'
            and i.BUYNOWPRICE is not null;

Alternatively, instead of joins with such complex path expressions, you can write ordinary joins explicitly in the FROM clause.

15.4.4. Explicit joins

JPA differentiates between purposes you may have for joining. Suppose you’re querying items; there are two possible reasons you may be interested in joining them with bids.

You may want to limit the items returned by the query based on some criterion to apply to their bids. For example, you may want all items that have a bid of more than 100, which requires an inner join. Here, you aren’t interested in items that have no bids.

On the other hand, you may be primarily interested in the items but may want to execute an outer join just because you want to retrieve all bids for the queried items in a single SQL statement, something we called eager join fetching earlier. Remember that you prefer to map all associations lazily by default, so an eager fetch query will override the default fetching strategy at runtime for a particular use case.

Let’s first write some queries that use joins for the purpose of restriction. If you want to retrieve Item instances and restrict the result to items that have bids with a certain amount, you have to assign an alias to a joined association. Then you refer to the alias in a WHERE clause to restrict the data you want:

select i from Item i
    join i.bids b
    where b.amount > 100
<enter/>
Root<Item> i = criteria.from(Item.class);
Join<Item, Bid> b = i.join("bids");
criteria.select(i).where(
    cb.gt(b.<BigDecimal>get("amount"), new BigDecimal(100))
);

This query assigns the alias b to the collection bids and limits the returned Item instances to those with Bid#amount greater than 100.

So far, you’ve only written inner joins. Outer joins are mostly used for dynamic fetching, which we discuss soon. Sometimes, you want to write a simple query with an outer join without applying a dynamic fetching strategy. For example, the following query and retrieves items that have no bids, and items with bids of a minimum bid amount:

select i, b from Item i
    left join i.bids b on b.amount > 100
<enter/>
Root<Item> i = criteria.from(Item.class);
Join<Item, Bid> b = i.join("bids", JoinType.LEFT);
b.on(
    cb.gt(b.<BigDecimal>get("amount"), new BigDecimal(100))
);
criteria.multiselect(i, b);

This query returns ordered pairs of Item and Bid, in a List<Object[]>.

The first thing that is new in this query is the LEFT keyword and JoinType.LEFT in the criteria query. Optionally you can write LEFT OUTER JOIN and RIGHT OUTER JOIN in JPQL, but we usually prefer the short form.

The second change is the additional join condition following the ON keyword. If instead you place the b.amount > 100 expression into the WHERE clause, you restrict the result to Item instances that have bids. This isn’t what you want here: you want to retrieve items and bids, and even items that don’t have bids. If an item has bids, the bid amount must be greater than 100. By adding an additional join condition in the FROM clause, you can restrict the Bid instances and still retrieve all Item instances, whether they have bids or not.

This is how the additional join condition translates into SQL:

... from ITEM i
    left outer join BID b
        on i.ID = b.ITEM_ID and (b.AMOUNT > 100)

The SQL query will always contain the implied join condition of the mapped association, i.ID = b.ITEM_ID. You can only append additional expressions to the join condition. JPA and Hibernate don’t support arbitrary outer joins without a mapped entity association or collection.

Hibernate has a proprietary WITH keyword, it’s the same as the ON keyword in JPQL. You may see it in older code examples, because JPA only recently standardized ON.

You can write a query returning the same data with a right outer join, switching the driving table:

select b, i from Bid b
    right outer join b.item i
    where b is null or b.amount > 100
<enter/>
Root<Bid> b = criteria.from(Bid.class);
Join<Bid, Item> i = b.join("item", JoinType.RIGHT);
criteria.multiselect(b, i).where(
   cb.or(
      cb.isNull(b),
      cb.gt(b.<BigDecimal>get("amount"), new BigDecimal(100)))
);

This right outer join query is more important than you may think. Earlier in this book, we told you to avoid mapping a persistent collection whenever possible. If you don’t have a one-to-many Item#bids collection, you need a right outer join to retrieve all Items and their Bid instances. You drive the query from the “other” side: the many-to-one Bid#item.

Left outer joins also play an important role with eager dynamic fetching.

15.4.5. Dynamic fetching with joins

All the queries you saw in the previous sections have one thing in common: the returned Item instances have a collection named bids. This @OneToMany collection, if mapped as FetchType.LAZY (the default for collections), isn’t initialized, and an additional SQL statement is triggered as soon as you access it. The same is true for all single-valued associations, like the @ManyToOne association seller of each Item. By default, Hibernate generates a proxy and loads the associated User instance lazily and only on demand.

What options do you have to change this behavior? First, you can change the fetch plan in your mapping metadata and declare a collection or single-valued association as FetchType.EAGER. Hibernate then executes the necessary SQL to guarantee that the desired network of instances is loaded at all times. This also means a single JPA query may result in several SQL operations! As an example, the simple query select i from Item i may trigger additional SQL statements to load the bids of each Item, the seller of each Item, and so on.

In chapter 12, we made the case for a lazy global fetch plan in mapping metadata, where you shouldn’t have FetchType.EAGER on association and collection mappings. Then, for a particular use case in your application, you dynamically override the lazy fetch plan and write a query that fetches the data you need as efficiently as possible. For example, there is no reason you need several SQL statements to fetch all Item instances and to initialize their bids collections, or to retrieve the seller for each Item. You can do this at the same time, in a single SQL statement, with a join operation.

Eager fetching of associated data is possible with the FETCH keyword in JPQL and the fetch() method in the criteria query API:

select i from Item i
    left join fetch i.bids
<enter/>
Root<Item> i = criteria.from(Item.class);
i.fetch("bids", JoinType.LEFT);
criteria.select(i);

You’ve already seen the SQL query this produces and the result set in figure 15.3.

This query returns a List<Item>; each Item instance has its bids collection fully initialized. This is different than the ordered pairs returned by the queries in the previous section!

Be careful—you may not expect the duplicate results from the previous query:

Make sure you understand why these duplicates appear in the result List. Verify the number of Item “rows” in the result set, as shown in figure 15.3. Hibernate preserves the rows as list elements; you may need the correct row count to make rendering a report table in the user interface easier.

You can filter out duplicate Item instances by passing the result List through a LinkedHashSet, which doesn’t allow duplicate elements but preserves the order of elements. Alternatively, Hibernate can remove the duplicate elements with the -DISTINCT operation and distinct() criteria method:

select distinct i from Item i
    left join fetch i.bids
<enter/>
Root<Item> i = criteria.from(Item.class);
i.fetch("bids", JoinType.LEFT);
criteria.select(i).distinct(true);

Understand that in this case the DISTINCT operation does not execute in the database. There will be no DISTINCT keyword in the SQL statement. Conceptually, you can’t remove the duplicate rows at the SQL ResultSet level. Hibernate performs deduplication in memory, just as you would manually with a LinkedHashSet.

You can also prefetch many-to-one or one-to-one associations with the same syntax:

This query returns a List<Item>, and each Item has its bids collection initialized. The seller of each Item is loaded as well. Finally, the bidder of each Bid instance is loaded. You can do this in one SQL query by joining rows of the ITEM, BID, and USERS tables.

If you write JOIN FETCH without LEFT, you get eager loading with an inner join (also if you use INNER JOIN FETCH). An eager inner join fetch makes sense if there must be a fetched value: an Item must have a seller, and a Bid must have a bidder.

There are limits to how many associations you should eagerly load in one query and how much data you should fetch in one round trip. Consider the following query, which initializes the Item#bids and Item#images collections:

This is a bad query, because it creates a Cartesian product of bids and images, with a potentially extremely large result set. We covered this issue in section 12.2.2.

To summarize, eager dynamic fetching in queries has the following caveats:

• Never assign an alias to any fetch-joined association or collection for further restriction or projection. The query left join fetch i.bids b where b.amount ... is invalid. You can’t say, “Load the Item instances and initialize their bids collections, but only with Bid instances that have a certain amount.” You can assign an alias to a fetch-joined association for further fetching: for example, retrieving the bidder of each Bid: left join fetch i.bids b join fetch b.bidder.
• You shouldn’t fetch more than one collection; otherwise, you create a Cartesian product. You can fetch as many single-valued associations as you like without creating a product.
• Queries ignore any fetching strategy you’ve defined in mapping metadata with @org.hibernate.annotations.Fetch. For example, mapping the bids collection with org.hibernate.annotations.FetchMode.JOIN has no effect on the queries you write. The dynamic fetching strategy of your query ignores the global fetching strategy. On the other hand, Hibernate doesn’t ignore the mapped fetch plan: Hibernate always considers a FetchType.EAGER, and you may see several additional SQL statements when you execute your query.
• If you eager-fetch a collection, the List returned by Hibernate preserves the number of rows in the SQL result as duplicate references. You can filter out the duplicates in-memory either manually with a LinkedHashSet or with the special DISTINCT operation in the query.

There is one more issue to be aware of, and it deserves some special attention. You can’t paginate a result set at the database level if you eagerly fetch a collection. For example, for the query select i from Item i fetch i.bids, how should Query#setFirstResult(21) and Query#setMaxResults(10) be handled?

Clearly, you expect to get only 10 items, starting with item 21. But you also want to load all bids of each Item eagerly. Therefore, the database can’t do the paging operation; you can’t limit the SQL result to 10 arbitrary rows. Hibernate will execute paging in-memory if a collection is eagerly fetched in a query. This means all Item instances will be loaded into memory, each with the bids collection fully initialized. Hibernate then gives you the requested page of items: for example, only items 21 to 30.

Not all items might fit into memory, and you probably expected the paging to occur in the database before it transmitted the result to the application! Therefore, Hibernate will log a warning message if your query contains fetch [collectionPath] and you call setFirstResult() or setMaxResults().

We don’t recommend the use of fetch [collectionPath] with setMaxResults() or setFirstResult() options. Usually there is an easier query you can write to get the data you want to render—and we don’t expect that you load data page by page to modify it. For example, if you want to show several pages of items and for each item the number of bids, write a report query:

select i.id, i.name, count(b)
    from Item i left join i.bids b
        group by i.id, i.name

The result of this query can be paged by the database with setFirstResult() and setMaxResults(). It’s much more efficient than retrieving any Item or Bid instances into memory, so let the database create the report for you.

The last JPA join option on the list is the theta-style join.

15.4.6. Theta-style joins

In traditional SQL, a theta-style join is a Cartesian product together with a join condition in the WHERE clause, which is applied on the product to restrict the result. In JPA queries, the theta-style syntax is useful when your join condition isn’t a foreign key relationship mapped to a class association.

For example, suppose you store the User’s name in log records instead of mapping an association from LogRecord to User. The classes don’t know anything about each other, because they aren’t associated. You can then find all the Users and their Log-Records with the following theta-style join:

select u, log from User u, LogRecord log
    where u.username = log.username
<enter/>
Root<User> u = criteria.from(User.class);
Root<LogRecord> log = criteria.from(LogRecord.class);
criteria.where(
    cb.equal(u.get("username"), log.get("username")));
criteria.multiselect(u, log);

The join condition here is a comparison of username, present as an attribute in both classes. If both rows have the same username, they’re joined (with an inner join) in the result. The query result consists of ordered pairs:

List<Object[]> result = query.getResultList();
for (Object[] row : result) {
    assertTrue(row[0] instanceof User);
    assertTrue(row[1] instanceof LogRecord);
}

You probably won’t need to use the theta-style joins often. Note that it’s currently not possible in JPA to outer join two tables that don’t have a mapped association—theta-style joins are inner joins.

Another more common case for theta-style joins is comparisons of primary key or foreign key values to either query parameters or other primary or foreign key values in the WHERE clause:

select i, b from Item i, Bid b
    where b.item = i and i.seller = b.bidder
<enter/>
Root<Item> i = criteria.from(Item.class);
Root<Bid> b = criteria.from(Bid.class);
criteria.where(
    cb.equal(b.get("item"), i),
    cb.equal(i.get("seller"), b.get("bidder"))
);
criteria.multiselect(i, b);

This query returns pairs of Item and Bid instances, where the bidder is also the seller. This is an important query in CaveatEmptor because it lets you detect people who bid on their own items. You probably should translate this query into a database constraint and not allow such a Bid instance to be stored.

The previous query also has an interesting comparison expression: i.seller = b.bidder. This is an identifier comparison, our next topic.

15.4.7. Comparing identifiers

JPA supports the following implicit identifier comparison syntax in queries:

select i, u from Item i, User u
    where i.seller = u and u.username like 'j%'
<enter/>
Root<Item> i = criteria.from(Item.class);
Root<User> u = criteria.from(User.class);
criteria.where(
    cb.equal(i.get("seller"), u),
    cb.like(u.<String>get("username"), "j%")
);
criteria.multiselect(i, u);

In this query, i.seller refers to the SELLER_ID foreign key column of the ITEM table, referencing the USERS table. The alias u refers to the primary key of the USERS table (on the ID column). Hence, this query has a theta-style join and is equivalent to the easier, readable alternative:

select i, u from Item i, User u
    where i.seller.id = u.id and u.username like 'j%'
<enter/>
Root<Item> i = criteria.from(Item.class);
Root<User> u = criteria.from(User.class);
criteria.where(
    cb.equal(i.get("seller").get("id"), u.get("id")),
    cb.like(u.<String>get("username"), "j%")
);
criteria.multiselect(i, u);

select i from Item i where i.seller = :seller
Root<Item> i = criteria.from(Item.class);
criteria.where(
    cb.equal(
        i.get("seller"),
        cb.parameter(User.class, "seller")
    )
);
criteria.select(i);
query.setParameter("seller", someUser);
List<Item> result = query.getResultList();

select i from Item i where i.seller.id = :sellerId
Root<Item> i = criteria.from(Item.class);
criteria.where(
    cb.equal(
        i.get("seller").get("id"),
        cb.parameter(Long.class, "sellerId")
    )
);
criteria.select(i);
query.setParameter("sellerId", USER_ID);
List<Item> result = query.getResultList();

select b from Bid b where b.item.name like 'Fo%'
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(
    cb.like(
        b.get("item").<String>get("name"),
        "Fo%"
    )
);

select b from Bid b where b.item.id = :itemId
CriteriaQuery<Bid> criteria = cb.createQuery(Bid.class);
Root<Bid> b = criteria.from(Bid.class);
criteria.where(
    cb.equal(
        b.get("item").get("id"),
        cb.parameter(Long.class, "itemId")
    )
);
criteria.select(b);

This completes our discussion of queries that involve joins. Our final topic is nesting selects within selects: subselects.

15.5. Subselects

Subselects are an important and powerful feature of SQL. A subselect is a select query embedded in another query, usually in the SELECT, FROM, or WHERE clause.

JPA supports subqueries in the WHERE clause. Subselects in the FROM clause aren’t supported because the query languages doesn’t have transitive closure. The result of a query may not be usable for further selection in a FROM clause. The query language also doesn’t support subselects in the SELECT clause, but you map can subselects to derived properties with @org.hibernate.annotations.Formula, as shown in section 5.1.3.

Subselects can be either correlated with the rest of the query or uncorrelated.

15.5.1. Correlated and uncorrelated nesting

The result of a subquery may contain either a single row or multiple rows. Typically, subqueries that return single rows perform aggregation. The following subquery returns the total number of items sold by a user; the outer query returns all users who have sold more than one item:

select u from User u
    where (
        select count(i) from Item i where i.seller = u
    ) > 1
<enter/>
Root<User> u = criteria.from(User.class);
<enter/>
Subquery<Long> sq = criteria.subquery(Long.class);
Root<Item> i = sq.from(Item.class);
sq.select(cb.count(i))
    .where(cb.equal(i.get("seller"), u)
    );
<enter/>
criteria.select(u);
criteria.where(cb.greaterThan(sq, 1L));

The inner query is a correlated subquery—it refers to an alias (u) from the outer query.

The next query contains an uncorrelated subquery:

select b from Bid b
    where b.amount + 1 >= (
        select max(b2.amount) from Bid b2
    )
<enter/>
Root<Bid> b = criteria.from(Bid.class);
<enter/>
Subquery<BigDecimal> sq = criteria.subquery(BigDecimal.class);
Root<Bid> b2 = sq.from(Bid.class);
sq.select(cb.max(b2.<BigDecimal>get("amount")));
<enter/>
criteria.select(b);
criteria.where(
    cb.greaterThanOrEqualTo(
        cb.sum(b.<BigDecimal>get("amount"), new BigDecimal(1)),
        sq
    )
);

The subquery in this example returns the maximum bid amount in the entire system; the outer query returns all bids whose amount is within one (U.S. dollar, Euro, and so on) of that amount. Note that in both cases, parentheses enclose the subquery in JPQL. This is always required.

Uncorrelated subqueries are harmless, and there is no reason not to use them when convenient. You can always rewrite them as two queries, because they don’t -reference each other. You should think more carefully about the performance impact of correlated subqueries. On a mature database, the performance cost of a simple correlated subquery is similar to the cost of a join. But it isn’t necessarily possible to rewrite a correlated subquery using several separate queries.

If a subquery returns multiple rows, you combine it with quantification.

15.5.2. Quantification

The following quantifiers are standardized:

• ALL—The expression evaluates to true if the comparison is true for all values in the result of the subquery. It evaluates to false if a single value of the subquery result fails the comparison test.
• ANY—The expression evaluates to true if the comparison is true for some (any) value in the result of the subquery. If the subquery result is empty or no value satisfies the comparison, it evaluates to false. The keyword SOME is a synonym for ANY.
• EXISTS—Evaluates to true if the result of the subquery consists of one or more values.

For example, the following query returns items where all bids are less or equal than 10:

select i from Item i
    where 10 >= all (
        select b.amount from i.bids b
    )
<enter/>
Root<Item> i = criteria.from(Item.class);
<enter/>
Subquery<BigDecimal> sq = criteria.subquery(BigDecimal.class);
Root<Bid> b = sq.from(Bid.class);
sq.select(b.<BigDecimal>get("amount"));
sq.where(cb.equal(b.get("item"), i));
<enter/>
criteria.select(i);
criteria.where(
    cb.greaterThanOrEqualTo(
        cb.literal(new BigDecimal(10)),
        cb.all(sq)
    )
);

The following query returns items with a bid of exactly 101:

select i from Item i
    where 101.00 = any (
        select b.amount from i.bids b
    )
<enter/>
Root<Item> i = criteria.from(Item.class);
<enter/>
Subquery<BigDecimal> sq = criteria.subquery(BigDecimal.class);
Root<Bid> b = sq.from(Bid.class);
sq.select(b.<BigDecimal>get("amount"));
sq.where(cb.equal(b.get("item"), i));
<enter/>
criteria.select(i);
criteria.where(
    cb.equal(
        cb.literal(new BigDecimal("101.00")),
        cb.any(sq)
    )
);

To retrieve all items that have bids, check the result of the subquery with EXISTS:

select i from Item i
    where exists (
        select b from Bid b where b.item = i
    )
<enter/>
Root<Item> i = criteria.from(Item.class);
<enter/>
Subquery<Bid> sq = criteria.subquery(Bid.class);
Root<Bid> b = sq.from(Bid.class);
sq.select(b).where(cb.equal(b.get("item"), i));
<enter/>
criteria.select(i);
criteria.where(cb.exists(sq));

This query is more important than it looks. You can find all items that have bids with the following query: select i from Item i where i.bids is not empty. This, however, requires a mapped one-to-many collection Item#bids. If you follow our recommendations, you probably only have the “other” side mapped: the many-to-one Bid#item. With an exists() and a subquery, you can get the same result.

Subqueries are an advanced technique; you should question frequent use of subqueries, because queries with subqueries can often be rewritten using only joins and aggregation. But they’re powerful and useful from time to time.

15.6. Summary

• If you knew SQL coming into this chapter, you’re now able to write a wide variety of queries in JPQL and with the criteria query API. If you aren’t comfortable with SQL, consult our reference section.
• With selection, you pick the source(s) of your query: the “tables” you want to query. Then you apply restriction expressions to limit the queried “rows” to the relevant subset. The projection of your query defines the returned “columns”: the data retrieved by your query. You can also direct the database to aggregate and group data efficiently, before returning it.
• We discussed joins: how you select, restrict, and combine data from several tables. A JPA application relies on joins to fetch associated entity instances and collections in a single database round-trip. This is a critical feature when you’re trying to reduce database load, and we recommend you repeat these examples to get a firm grasp of joining data and eager fetching.
• You can nest queries inside each other as subselects.