Advanced query options (Hibernate)

 

This topic explains all query options that you may consider optional or advanced. You’ll need the first subject of this topic, the Criteria query interface, whenever you create more complex queries programmatically. This API is much more convenient and elegant than programmatic generation of query strings for HQL and JPA QL. Unfortunately, it’s also only available as a native Hibernate API; Java Persistence doesn’t (yet) standardize a programmatic query interface.

Both Hibernate and Java Persistence support queries written in native SQL. You can embed SQL and stored procedure calls in your Java source code or externalize them to mapping metadata. Hibernate can execute your SQL and convert the resultset into more convenient objects, depending on your mapping.

Filtering of collections is a simple convenience feature of Hibernate—you won’t use it often. It helps you to replace a more elaborate query with a simple API call and a query fragment, for example, if you want to obtain a subset of the objects in a collection.

Finally, we’ll discuss the optional query result cache—we’ve already mentioned that it’s not useful in all situations, so we’ll take a closer look at the benefits of caching results of a query and when you’d ideally enable this feature.

Let’s start with query by criteria and query by example.

Querying with criteria and example

The Criteria and Example APIs are available in Hibernate only; Java Persistence doesn’t standardize these interfaces. As mentioned earlier, it seems likely that other vendors, not only Hibernate, support a similar extension interface and that a future version of the standard will include this functionality.

Querying with programmatically generated criteria and example objects is often the preferred solution when queries get more complex. This is especially true if you have to create a query at runtime. Imagine that you have to implement a search mask in your application, with many check boxes, input fields, and switches the user can enable. You must create a database query from the user’s selection. The traditional way to do this is to create a query string through concatenation, or maybe to write a query builder that can construct the SQL query string for you. You’d run into the same problem if you’d try to use HQL or JPA QL in this scenario.

The Criteria and Example interfaces allow you to build queries programmati-cally by creating and combining objects in the right order. We now show you how to work with these APIs, and how to express selection, restriction, joins, and projection. We assume that you’ve read the previous topic and that you know how these operations are translated into SQL. Even if you decide to use the Criteria and Example APIs as your primary way to write queries, keep in mind that HQL and JPA QL are always more flexible due to their string-based nature. Let’s start with some basic selection and restriction examples.

Basic criteria queries

The simplest criteria query looks like this:

It retrieves all persistent instances of the Item class. This is also called the root entity of the criteria query.

Criteria queries also support polymorphism:

The Criteria interface also supports ordering of results with the addOrder() method and the Order criterion:

This query returns instances of BillingDetails and its subclasses. Likewise, the following criteria query returns all persistent objects:

You don’t need to have an open Session to create a criteria object; a Detached-Criteria can be instantiated and later attached to a Session for execution (or to another Criteria as a subquery):

Usually you want to restrict the result and don’t retrieve all instances of a class. Applying restrictions

For a criteria query, you must construct a Criterion object to express a constraint. The Restrictions class provides factory methods for built-in Criterion types. Let’s search for User objects with a particular email address:

You create a Criterion that represents the restriction for an equality comparison and add it to the Criteria. This eq() method has two arguments: first the name of the property, and then the value that should be compared. The property name is always given as a string; keep in mind that this name may change during a refactoring of your domain model and that you must update any predefined criteria queries manually. Also note that the criteria interfaces don’t support explicit parameter binding, because it’s not needed. In the previous example you bound the string “foo@hibernate.org” to the query; you can bind any java. lang.Object and let Hibernate figure out what to do with it. The unique-Result() method executes the query and returns exactly one object as a result— you have to cast it correctly.

Usually, you write this a bit less verbosely, using method chaining:

you’re able to abbreviate the criteria query restriction code to

You can also name a property of a component with the usual dot notation:

Obviously, criteria queries are more difficult to read if they get more complex—a good reason to prefer them for dynamic and programmatic query generation, but to use externalized HQL and JPA QL for predefined queries. A new feature of JDK 5.0 is static imports’; it helps making criteria queries more readable. For example, by adding

An alternative to obtaining a Criterion is a Property object—this will be more useful later in this section when we discuss projection:

The Criteria API and the org.hibernate.criterion package offer many other operators besides eq() you can use to construct more complex expressions.

Creating comparison expressions

All regular SQL (and HQL, JPA QL) comparison operators are also available via the

Restrictions class:

You also need to be able to find users who do have an email address:

You can also test a collection with isEmpty(), isNotEmpty(), or its actual size:

Or you can compare two properties:

The criteria query interfaces also have special support for string matching.

A ternary logic operator is also available; this query returns all users with no email address:

String matching

For criteria queries, wildcarded searches may use either the same wildcard symbols as HQL and JPA QL (percentage sign and underscore) or specify a MatchMode. The MatchMode is a convenient way to express a substring match without string manipulation. These two queries are equivalent:

The allowed MatchModes are START, END, ANYWHERE, and EXACT.

You often also want to perform case-insensitive string matching. Where you’d resort to a function such as LOWER() in HQL or JPA QL, you can rely on a method of the Criteria API:

Combining expressions with logical operators

If you add multiple Criterion instances to the one Criteria instance, they’re applied conjunctively (using and):

If you need disjunction (or), there are two options. The first is to use Restric-tions.or() together with Restrictions.and():

The second option is to use Restrictions.disjunction() together with Restrictions.conjunction():

We think both these options are ugly, even after spending five minutes trying to format them for maximum readability. JDK 5.0 static imports can help improve readability considerably, but even so, unless you’re constructing a query on the fly, the HQL or JPA QL string is much easier to understand.

You may have noticed that many standard comparison operators (less than, greater than, equals, and so on) are built into the Criteria API, but certain operators are missing. For example, any arithmetic operators such as addition and division aren’t supported directly.

Another issue is function calls. Criteria has built-in functions only for the most common cases such as string case-insensitive matching. HQL, on the other hand, allows you to call arbitrary SQL functions in the WHERE clause.

The Criteria API has a similar facility: You can add an arbitrary SQL expression as a Criterion.

Adding arbitrary SQL expressions

Let’s assume you want to test a string for its length and restrict your query result accordingly. The Criteria API has no equivalent to the LENGTH() function in SQL, HQL, or JPA QL.

You can, however, add a plain SQL function expression to your Criteria:

This query returns all User objects that have a password with less than 5 characters. The {alias} placeholder is needed to prefix any table alias in the final SQL; it always refers to the table the root entity is mapped to (USERS in this case). You also use a position parameter (named parameters aren’t supported by this API) and specify its type as Hibernate.INTEGER. Instead of a single bind argument and type, you can also use an overloaded version of the sqlRestriction() method that supports arrays of arguments and types.

This facility is powerful—for example, you can add an SQL WHERE clause subse-lect with quantification:

This query returns all Item objects which have no bids greater than 100. (The Hibernate criteria query system is extensible: You could also wrap the LENGTH() SQL function in your own implementation of the Criterion interface.) Finally, you can write criteria queries that include subqueries.

Writing subqueries

A subquery in a criteria query is a WHERE clause subselect. Just like in HQL, JPA QL, and SQL, 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 10 items:

This is a correlated subquery. The DetachedCriteria refers to the u alias; this alias is declared in the outer query. Note that the outer query uses a less than operator because the subquery is the right operand. Also note that i.seller.id does not result in a join, because SELLER_ID is a column in the ITEM table, which is the root entity for that detached criteria.

Let’s move on to the next topic about criteria queries: joins and dynamic fetching.

Joins and dynamic fetching

Just like in HQL and JPA QL, you may have different reasons why you want to express a join. First, you may want to use a join to restrict the result by some property of a joined class. For example, you may want to retrieve all Item instances that are sold by a particular User.

Of course, you also want to use joins to dynamically fetch associated objects or collections, as you’d do with the fetch keyword in HQL and JPA QL. In criteria queries you have the same options available, with a FetchMode.

We first look at regular joins and how you can express restrictions that involve associated classes.

Joining associations for restriction

There are two ways to express a join in the Criteria API; hence there are two ways in which you can use aliases for restriction. The first is the createCriteria() method of the Criteria interface. This basically means you can nest calls to cre-ateCriteria():

You usually write the query as follows (method chaining):

The creation of a Criteria for the bids of the Item results in an inner join between the tables of the two classes. Note that you may call list() on either Criteria instance without changing the query result. Nesting criteria works not only for collections (such as bids), but also for single-valued associations (such as seller):

This query returns all items that are sold by users with a particular email address pattern.

The second way to express inner joins with the Criteria API is to assign an alias to the joined entity:

And the same for a restriction on a single-valued association, the seller:

This approach doesn’t use a second instance of Criteria; it’s basically the same alias assignment mechanism you’d write in the FROM clause of an HQL/JPA QL statement. Properties of the joined entity must then be qualified by the alias assigned in createAlias() method, such as s.email. Properties of the root entity of the criteria query (Item) may be referred to without the qualifying alias, or with the alias “this”:

Finally, note that at the time of writing only joining of associated entities or collections that contain references to entities (one-to-many and many-to-many) is supported in Hibernate with the Criteria API. The following example tries to join a collection of components:

Hibernate fails with an exception and tells you that the property you want to alias doesn’t represent an entity association. We think this feature will likely be implemented by the time you read this topic.

Another syntax that is also invalid, but that you may be tempted to try, is an implicit join of a single-valued association with the dot notation:

The “seller.email” string isn’t a property or a component’s property path. Create an alias or a nested Criteria object to join this entity association. Let’s discuss dynamic fetching of associated objects and collections.

Dynamic fetching with criteria queries

In HQL and JPA QL, you use the join fetch operation to eagerly fill a collection or to initialize an object that is mapped as lazy and would otherwise be proxied. You can do the same using the Criteria API:

This query returns all Item instance with a particular collection and eagerly loads the bids collection for each Item.

A FetchMode.JOIN enables eager fetching through an SQL outer join. If you want to use an inner join instead (rare, because it wouldn’t return items that don’t have bids), you can force it:

You can also prefetch many-to-one and one-to-one associations:

Be careful, though. The same caveats as in HQL and JPA QL apply here: Eager fetching more than one collection in parallel (such as bids and images) results in an SQL Cartesian product that is probably slower than two separate queries. Limiting the resultset for pagination, if you use eager fetching for collections, is also done in-memory.

However, dynamic fetching with Criteria and FetchMode is slightly different than in HQL and JPA QL: A Criteria query doesn’t ignore the global fetching strategies as defined in the mapping metadata. For example, if the bids collection is mapped with fetch=”join” or FetchType.EAGER, the following query results in an outer join of the ITEM and BID table:

The returned Item instances have their bids collections initialized and fully loaded. This doesn’t happen with HQL or JPA QL unless you manually query with LEFT JOIN FETCH (or, of course, map the collection as lazy=”false”, which results in a second SQL query).

As a consequence, criteria queries may return duplicate references to distinct instances of the root entity, even if you don’t apply FetchMode.JOIN for a collection in your query. The last query example may return hundreds of Item references, even if you have only a dozen in the database.

You can remove the duplicate references in the result List by wrapping it in a LinkedHashSet (a regular HashSet wouldn’t keep the order or the query result). In HQL and JPA QL, you can also use the DISTINCT keyword; however, there is no direct equivalent of this in Criteria. This is where the ResultTransformer becomes useful.

Applying a result transformer

A result transformer can be applied to a query result so that you can filter or marshal the result with your own procedure instead of the Hibernate default behavior. Hibernate’s default behavior is a set of default transformers that you can replace and/or customize.

All criteria queries return only instances of the root entity, by default:

The Criteria.ROOT_ENTITY is the default implementation of the org.hiber-nate.transform.ResultTransformer interface. The previous query produces the same result, with or without this transformer set. It returns all Item instances and initializes their bids collections. The List probably (depending on the number of Bids for each Item) contains duplicate Item references.

Alternatively, you can apply a different transformer:

Hibernate now filters out duplicate root entity references before returning the result—this is effectively the same filtering that occurs in HQL or JPA QL if you use the DISTINCT keyword.

First, a criteria query is created that joins Item with its bids and seller associations. This is an SQL inner join across three tables. The result of this query, in SQL, is a table where each result row contains item, bid, and user data—almost the same as shown in figure 14.2. With the default transformer, Hibernate returns only Item instances. And, with the DISTINCT_ROOT_ENTITY transformer, it filters out the duplicate Item references. Neither option seems sensible—what you really want is to return all information in a map. The ALIAS_TO_ENTITY_MAP transformer can marshal the SQL result into a collection of Map instances. Each Map has three entries: an Item, a Bid, and a User. All result data is preserved and can be accessed in the application. (The Criteria.ROOT_ALIAS is a shortcut for “this”.)

Good use cases for this last transformer are rare. Note that you can also implement your own org.hibernate.transform.ResultTransformer. Furthermore, HQL and native SQL queries also support a ResultTransformer:

This query now returns a collection of ItemDTO instances, and the attributes of this bean are populated through the setter methods setItemId(), setDesc(), and setPrice().

A much more common way to define what data is to be returned from a query is projection. The Hibernate criteria supports the equivalent of a SELECT clause for simple projection, aggregation, and grouping.

Result transformers are also useful if you want to retrieve aliased entities in a join query:

Projection and report queries

In HQL, JPA QL, and SQL, you write a SELECT clause to define the projection for a particular query. The Criteria API also supports projection, of course programmatically and not string-based. You can select exactly which objects or properties of objects you need in the query result and how you possibly want to aggregate and group results for a report.

Simple projection lists

The following criteria query returns only the identifier values of Item instances which are still on auction:

The setProjection() method on a Criteria accepts either a single projected attribute, as in the previous example, or a list of several properties that are to be included in the result:

This query returns a List of Object[], just like HQL or JPA QL would with an equivalent SELECT clause. An alternative way to specify a property for projection is the Property class:

In HQL and JPA QL, you can use dynamic instantiation with the SELECT NEW operation and return a collection of custom objects instead of Object[]. Hibernate bundles a ResultTransformer for criteria queries that can do almost the same (in fact, it’s more flexible). The following query returns the same result as the previous one, but wrapped in data transfer objects:

 

The ItemPriceSummary is a simple Java bean with setter methods or public fields named itemId, itemDescription, and itemInitialPrice. It doesn’t have to be a mapped persistent class; only the property/field names must match with the aliases assigned to the projected properties in the criteria query. Aliases are assigned with the as() method (which you can think of as the equivalent of the AS keyword in an SQL SELECT). The result transformer calls the setter methods or populates the fields directly and returns a collection of ItemPriceSummary objects.

Let’s do more complex projection with criteria, involving aggregation and grouping.

Aggregation and grouping

The usual aggregation functions and grouping options are also available in criteria queries. A straightforward method counts the number of rows in the result:

TIP Getting the total count for pagination—In real applications, you often must allow users to page through lists and at the same time inform them how many total items are in the list. One way to get the total number is a Criteria query that executes a rowCount(). Instead of writing this additional query, you can execute the same Criteria that retrieves the data for the list with scroll(). Then call last() and getRowNumber() to jump and get the number of the last row. This plus one is the total number of objects you list. Don’t forget to close the cursor. This technique is especially useful if you’re working with an existing DetachedCriteria object and you don’t want to duplicate and manipulate its projection to execute a rowCount(). It also works with HQL or SQL queries.

More complex aggregations use aggregation functions. The following query finds the number of bids and average bid amount each user made:

This query returns a collection of Object[] s with four fields: the user’s identifier, login name, number of bids, and the average bid amount. Remember that you can again use a result transformer for dynamic instantiation and have data transfer objects returned, instead of Object[]s. An alternative version that produces the same result is as follows:

The syntax you prefer is mostly a matter of taste. A more complex example applies aliases to the aggregated and grouped properties, for ordering of the result:

At the time of writing, support for HAVING and restriction on aggregated results isn’t available in Hibernate criteria queries. This will probably be added in the near future.

You can add native SQL expressions to restrictions in a criteria query; the same feature is available for projection.

Using SQL projections

An SQL projection is an arbitrary fragment that is added to the generated SQL SELECT clause. The following query produces the aggregation and grouping as in the previous examples but also adds an additional value to the result (the number of items):

The SQL fragment is embedded in the SELECT clause. It can contain any arbitrary expression and function call supported by the database management system. Any unqualified column name (such as ITEM_ID) refers to the table of the criteria root entity (BID). You must tell Hibernate the returned alias of the SQL projection, numOfItems, and its Hibernate value mapping type, Hibernate.LONG.

The real power of the Criteria API is the possibility to combine arbitrary Cri-terions with example objects. This feature is known as query by example.

The generated SQL is as follows:

Query by example

It’s common for criteria queries to be built programmatically by combining several optional criterions depending on user input. For example, a system administrator may wish to search for users by any combination of first name or last name and retrieve the result ordered by username.

Using HQL or JPA QL, you can build the query using string manipulations:

This code is pretty tedious and noisy, so let’s try a different approach. The Criteria API with what you’ve learned so far looks promising:

This code is much shorter. Note that the ilike() operator performs a case-insensitive match. There seems to be no doubt that this is a better approach. However, for search screens with many optional search criteria, there is an even better way.

As you add new search criteria, the parameter list of findUsers() grows. It would be better to capture the searchable properties as an object. Because all the search properties belong to the User class, why not use an instance of User for that purpose?

Query by example (QBE) relies on this idea. You provide an instance of the queried class with some properties initialized, and the query returns all persistent instances with matching property values. Hibernate implements QBE as part of the Criteria query API:

The call to create() returns a new instance of Example for the given instance of User. The ignoreCase() method puts the example query into a case-insensitive mode for all string-valued properties. The call to enableLike() specifies that the SQL like operator should be used for all string-valued properties, and specifies a MatchMode. Finally, you can exclude particular properties from the search with excludeProperty(). By default, all value-typed properties, excluding the identifier property, are used in the comparison.

You’ve significantly simplified the code again. The nicest thing about Hibernate Example queries is that an Example is just an ordinary Criterion. You can freely mix and match query by example with query by criteria.

Let’s see how this works by further restricting the search results to users with unsold Items. For this purpose, you may add a Criteria to the example user, constraining the result using its items collection of Items:

Even better, you can combine User properties and Item properties in the same search:

At this point, we invite you to take a step back and consider how much code would be required to implement this search screen using hand-coded SQL/JDBC. We won’t reproduce it here; it would stretch for pages. Also note that the client of the findUsersByExample() method doesn’t need to know anything about Hibernate, and it can still create complex criteria for searching.

If HQL, JPA QL, and even Criteria and Example aren’t powerful enough to express a particular query, you must fall back to native SQL.

Using native SQL queries

HQL, JPA QL, or criteria queries should be flexible enough to execute almost any query you like. They refer to the mapped object schema; hence, if your mapping works as expected, Hibernate’s queries should give you the power you need to retrieve data any way you like. There are a few exceptions. If you want to include a native SQL hint to instruct the database management systems query optimizer, for example, you need to write the SQL yourself. HQL, JPA QL, and criteria queries don’t have keywords for this.

On the other hand, instead of falling back to a manual SQL query, you can always try to extend the built-in query mechanisms and include support for your special operation. This is more difficult to do with HQL and JPA QL, because you have to modify the grammar of these string-based languages. It’s easy to extend the Criteria API and add new methods or new Criterion classes. Look at the Hibernate source code in the org.hibernate.criterion package; it’s well designed and documented.

When you can’t extend the built-in query facilities or prevent nonportable manually written SQL, you should first consider using Hibernate’s native SQL query options, which we now present. Keep in mind that you can always fall back to a plain JDBC Connection and prepare any SQL statement yourself. Hibernate’s SQL options allow you to embed SQL statements in a Hibernate API and to benefit from extra services that make your life easier.

Most important, Hibernate can handle the resultset of your SQL query.

Automatic resultset handling

The biggest advantage of executing an SQL statement with the Hibernate API is automatic marshaling of the tabular resultset into business objects. The following SQL query returns a collection of Category objects:

Hibernate reads the resultset of the SQL query and tries to discover the column names and types as defined in your mapping metadata. If the column CATEGORY_ NAME is returned, and it’s mapped to the name property of the Category class, Hibernate knows how to populate that property and finally returns fully loaded business objects.

The * in the SQL query projects all selected columns in the resultset. The automatic discovery mechanism therefore works only for trivial queries; more complex queries need an explicit projection. The next query returns a collection of Item objects:

The SQL SELECT clause includes a placeholder which names the table alias i and projects all columns of this table into the result. Any other table alias, such as the joined USERS table, which is only relevant for the restriction, isn’t included in the resultset. You now tell Hibernate with addEntity() that the placeholder for alias i refers to all columns that are needed to populate the Item entity class. The column names and types are again automatically guessed by Hibernate during query execution and result marshaling.

You can even eagerly fetch associated objects and collections in a native SQL query:

This SQL query projects two sets of columns from two table aliases, and you use two placeholders. The i placeholder again refers to the columns that populate the Item entity objects returned by this query. The addJoin() method tells Hibernate that the u alias refers to columns that can be used to immediately populate the associated seller of each Item.

Automatic marshaling of resultsets into business objects isn’t the only benefit of the native SQL query feature in Hibernate. You can even use it if all you want to retrieve is a simple scalar value.

Retrieving scalar values

A scalar value may be any Hibernate value type. Most common are strings, numbers, or timestamps. The following SQL query returns item data:

The result of this query is a List of Object[] s, effectively a table. Each field in each array is of scalar type—that is, a string, a number, or a timestamp. Except for the wrapping in an Object[], the result is exactly the same as that of a similar plain JDBC query. This is obviously not too useful, but one benefit of the Hibernate API is that it throws unchecked exceptions so you don’t have to wrap the query in try/catch block as you have to if you call the JDBC API.

If you aren’t projecting everything with *, you need to tell Hibernate what scalar values you want to return from your result:

The addScalar() method tells Hibernate that your fname SQL alias should be returned as a scalar value and that the type should be automatically guessed. The query returns a collection of strings. This automatic type discovery works fine in most cases, but you may want to specify the type explicitly sometimes—for example, when you want to convert a value with a UserType:

First, look at the SQL query. It selects the RATING column of the COMMENTS table and restricts the result to comments made by a particular user. Let’s assume that this field in the database contains string values, such as EXCELLENT, OK, or BAD. Hence, the result of the SQL query is string values.

You’d naturally map this not as a simple string in Java but using an enumeration and probably a custom Hibernate UserType.  It must be parameterized with the enumClassname you want it to convert values to—auction.model.Rating in this example. By setting the prepared custom type with the addScalar() method on the query, you enable it as a converter that handles the result, and you get back a collection of Rating objects instead of simple strings.

The result of this query is again a collection of Object[]s. Each array has two fields: an Item instance and a string.

You probably agree that native SQL queries are even harder to read than HQL or JPA QL statements and that it seems much more attractive to isolate and externalize them into mapping metadata. We won’t repeat this here, because the only difference between stored procedure queries and plain SQL queries is the syntax of the call or statement—the marshaling and resultset mapping options are the same.

Java Persistence standardizes JPA QL and also allows the fallback to native SQL.

Native SQL in Java Persistence

Java Persistence supports native SQL queries with the createNativeQuery() method on an EntityManager. A native SQL query may return entity instances, scalar values, or a mix of both. However, unlike Hibernate, the API in Java Persistence utilizes mapping metadata to define the resultset handling. Let’s walk through some examples.

A simple SQL query doesn’t need an explicit resultset mapping:

The resultset is automatically marshaled into a collection of Category instances. Note that the persistence engine expects all columns required to create an instance of Category to be returned by the query, including all property, component, and foreign key columns—otherwise an exception is thrown. Columns are searched in the resultset by name. You may have to use aliases in SQL to return the same column names as defined in your entity mapping metadata.

If your native SQL query returns multiple entity types or scalar types, you need to apply an explicit resultset mapping. For example, a query that returns a

Finally, you can mix scalar results and entity objects in the same native SQL query:

collection of Object[]s, where in each array index 0 is an Item instance and index 1 is a User instance, can be written as follows:

The last argument, ItemSellerResult, is the name of a result mapping you define in metadata (at the class or global JPA XML level):

This resultset mapping likely doesn’t work for the query we’ve shown—remember that for automatic mapping, all columns that are required to instantiate Item and User objects must be returned in the SQL query. It’s unlikely that the four columns you return represent the only persistent properties. For the sake of the example, let’s assume that they are and that your actual problem is the names of the columns in the resultset, which don’t match the names of the mapped columns. First, add aliases to the SQL statement:

Next, use @FieldResult in the resultset mapping to map aliases to fields of the entity instances:

The result of this query is a collection of Object[]s, with two fields, both of some numeric type (most likely long). If you want to mix entities and scalar types as a query result, combine the entities and columns attributes in a @Sql-ResultSetMapping.

Finally, note that the JPA specification doesn’t require that named parameter binding is supported for native SQL queries. Hibernate supports this.

Next, we discuss another more exotic but convenient Hibernate feature (Java Persistence doesn’t have an equivalent): collection filters.

You can also return scalar typed results. The following query returns auction item identifiers and the number of bids for each item:

The resultset mapping doesn’t contain entity result mappings this time, only columns:

Filtering collections

You may wish to execute a query against all elements of a collection. For instance, you may have an Item and wish to retrieve all bids for that particular item, ordered by the time that the bid was created. You can map a sorted or ordered collection for that purpose, but there is an easier choice. You can write a query, and you should already know how:

This query works because the association between bids and items is bidirectional and each Bid knows its Item. There is no join in this query; b.item refers to the ITEM_ID column in the BID table, and you set the value for the comparison directly. Imagine that this association is unidirectional—Item has a collection of Bids, but no inverse association exists from Bid to Item. You can try the following query:

This query is inefficient—it uses an entirely unnecessary join. A better, more elegant solution is to use a collection filter—a special query that can be applied to a persistent collection (or array). It’s commonly used to further restrict or order a result. You apply it on an already loaded Item and its collection of bids:

This filter is equivalent to the first query of this section and results in identical SQL. The createFilter() method on the Session takes two arguments: a persistent collection (it doesn’t have to be initialized) and an HQL query string. Collection filter queries have an implicit FROM clause and an implicit WHERE condition. The alias this refers implicitly to elements of the collection of bids.

Hibernate collection filters aren’t executed in memory. The collection of bids may be uninitialized when the filter is called and, if so, remains uninitialized. Furthermore, filters don’t apply to transient collections or query results. They may be applied only to a persistent collection currently referenced by an entity instance attached to the Hibernate persistence context. The term filter is somewhat misleading, because the result of filtering is a completely new and different collection; the original collection isn’t touched.

The only required clause of a HQL query is the FROM clause. Because a collection filter has an implicit FROM clause, the following is a valid filter:

To the great surprise of everyone, including the designer of this feature, this trivial filter turns out to be useful. You may use it to paginate collection elements:

Usually, you use an ORDER BY with paginated queries, however.

Even though you don’t need a FROM clause in a collection filter, you may have one if you like. A collection filter doesn’t even need to return elements of the collection being filtered. The next query returns any Category with the same name as a category in the given collection:

The following query returns a collection of Users who have bid on the item:

The next query returns all these users’ bids (including those for other items):

Note that the query uses the special HQL elements() function to project all elements of a collection.

All this is a lot of fun, but the most important reason for the existence of collection filters is to allow the application to retrieve some elements of a collection without initializing the whole collection. In the case of large collections, this is important to achieve acceptable performance. The following query retrieves all bids made by a user in the past week:

Again, this doesn’t initialize the bids collection of the User.

Queries, no matter in what language and what API they’re written, should always be tuned to perform as expected before you decide to speed them up with the optional query cache.

Caching query results

You know that the second-level cache is a shared cache of data, and that Hibernate tries to resolve data through a lookup in this cache whenever you access an unloaded proxy or collection or when you load an object by identifier (these are all identifier lookups, from the point of view of the second-level cache). Query results, on the other hand, are by default not cached.

Some queries still use the second-level cache, depending on how you execute a query. For example, if you decide to execute a query with iterate(), as we showed in the previous topic, only the primary keys of entities are retrieved from the database, and entity data is looked up through the first-level and, if enabled for a particular entity, second-level cache. We also concluded that this option makes sense only if the second-level cache is enabled, because an optimization of column reads usually doesn’t influence performance.

Caching query results is a completely different issue. The query result cache is by default disabled, and every HQL, JPA QL, SQL, and Criteria query always hits the database first. We first show you how to enable the query result cache and how it works. We then discuss why it’s disabled and why few queries benefit from result caching.

Enabling the query result cache

The query cache must be enabled using a Hibernate configuration property:

The setCachable() method enables the result cache. It’s also available on the Criteria API. If you want to enable result caching for a javax.persis-tence.Query, use setHint(“org.hibernate.cacheable”, true).

However, this setting alone isn’t enough for Hibernate to cache query results. By default, all queries always ignore the cache. To enable query caching for a particular query (to allow its results to be added to the cache, and to allow it to draw its results from the cache), you use the org.hibernate.Query interface.

Understanding the query cache

When a query is executed for the first time, its results are cached in a cache region—this region is different from any other entity or collection cache region you may already have configured. The name of the region is by default org.hibernate.cache.QueryCache.

You can change the cache region for a particular query with the setCache-Region() method:

This is rarely necessary; you use a different cache region for some queries only if you need a different region configuration—for example, to limit memory consumption of the query cache on a more fine-grained level.

The standard query result cache region holds the SQL statements (including all bound parameters) and the resultset of each SQL statement. This isn’t the complete SQL resultset, however. If the resultset contains entity instances (the previous example queries return Category instances), only the identifier values are held in the resultset cache. The data columns of each entity are discarded from the resultset when it’s put into the cache region. So, hitting the query result cache means that Hibernate will, for the previous queries, find some Category identifier values.

It’s the responsibility of the second-level cache region auction.model.Cate-gory (in conjunction with the persistence context) to cache the state of entities. This is similar to the lookup strategy of iterate(), as explained earlier. In other words, if you query for entities and decide to enable caching, make sure you also enabled regular second-level caching for these entities. If you don’t, you may end up with more database hits after enabling the query cache.

If you cache the result of a query that doesn’t return entity instances, but returns only the same scalar values (e.g., item names and prices), these values are held in the query result cache directly.

If the query result cache is enabled in Hibernate, another always required cache region is also present: org.hibernate.cache.UpdateTimestampsCache. This is a cache region used by Hibernate internally.

Hibernate uses the timestamp region to decide whether a cached query result-set is stale. When you re-execute a query that has caching enabled, Hibernate looks in the timestamp cache for the timestamp of the most recent insert, update, or delete made to the queried table(s). If the found timestamp is later than the timestamp of the cached query results, the cached results are discarded and a new query is issued. This effectively guarantees that Hibernate won’t use the cached query result if any table that may be involved in the query contains updated data; hence, the cached result may be stale. For best results, you should configure the timestamp region so that the update timestamp for a table doesn’t expire from the cache while query results from these tables are still cached in one of the other regions. The easiest way is to turn off expiry for the timestamp cache region in your second-level cache provider’s configuration.

When to use the query cache

The majority of queries don’t benefit from result caching. This may come as a surprise. After all, it sounds like avoiding a database hit is always a good thing. There are two good reasons why this doesn’t always work for arbitrary queries, compared to object navigation or retrieval by identifier.

First, you must ask how often you’re going to execute the same query repeatedly. Granted, you may have a few queries in your application that are executed over and over again, with exactly the same arguments bound to parameters, and the same automatically generated SQL statement. We consider this a rare case, but when you’re certain a query is executed repeatedly, it becomes a good candidate for result caching.

Second, for applications that perform many queries and few inserts, deletes, or updates, caching queries can improve performance and scalability. On the other hand if the application performs many writes, the query cache won’t be utilized efficiently. Hibernate expires a cached query resultset when there is any insert, update, or delete of any row of a table that appeared in the cached query result. This means cached results may have a short lifetime, and even if a query is executed repeatedly, no cached result can be used due to concurrent modifications of the same data (same tables).

For many queries, the benefit of the query result cache is nonexistent or, at least, doesn’t have the impact you’d expect. But one special kind of query can greatly benefit from result caching.

Natural identifier cache lookups

Let’s assume that you have an entity that has a natural key. We aren’t talking about a natural primary key, but about a business key that applies to a single or compound attributes of your entity. For example, the login name of a user can be a unique business key, if it’s immutable. This is the key we already isolated as perfect for the implementation of a good equals() object equality routine. 

Usually, you map the attributes that form your natural key as regular properties in Hibernate. You may enable a unique constraint at the database level to represent this key. For example, if you consider the User class, you may decide that username and emailAddress form the entity’s business key:

Or, to utilize cache lookups by business key, you can map it with <natural-id>:

This mapping enables a unique key constraint at the database level that spans two columns. Let’s also assume that the business key properties are immutable. This is unlikely, because you probably allow users to update their email addresses, but the functionality we’re presenting now makes sense only if you’re dealing with an immutable business key. You map immutability as follows:

This grouping automatically enables the generation of a unique key SQL constraint that spans all grouped properties. If the mutable attribute is set to false, it also prevents updating of the mapped columns. You can now use this business key for cache lookups:

This criteria query finds a particular user object based on the business key. It results in a second-level cache lookup by business key—remember that this is usually a lookup by primary key and is possible only for retrieval by primary identifier. The business key mapping and Criteria API allow you to express this special second-level cache lookup by business key.

At the time of writing, no Hibernate extension annotation for a natural identifier mapping is available, and HQL doesn’t support an equivalent keyword for lookup by business key.

From our point of view, caching at the second-level is an important feature, but it’s not the first option when optimizing performance. Errors in the design of queries or an unnecessarily complex part of your object model can’t be improved with a “cache it all” approach. If an application performs at an acceptable level only with a hot cache—that is, a full cache after several hours or days runtime—it should be checked for serious design mistakes, unperformant queries, and n+1 select problems.

Summary

In this topic, you’ve generated queries programmatically with the Hibernate Criteria and Example APIs. We also looked at embedded and externalized SQL queries and how you can map the resultset of an SQL query to more convenient business objects automatically. Java Persistence also supports native SQL and standardizes how you can map the resultset of externalized SQL queries.

Finally, we covered the query result cache and discussed why it’s useful only in certain situations.

Table 15.1 shows a summary you can use to compare native Hibernate features and Java Persistence.

Table 15.1 Hibernate and JPA comparison chart

Hibernate Core	Java Persistence and EJB 3.0
Hibernate supports a powerful Criteria and Example API for programmatic query generation.	Some QBC and QBE API is expected in an upcoming version of the standard.
Hibernate has flexible mapping options for embedded and externalized SQL queries, with automatic marshaling of resultsets.	Java Persistence standardizes SQL embedding and mapping and supports resultset marshaling.
Hibernate supports a collection filter API.	Java Persistence doesn’t standardize a collection filter API.
Hibernate can cache query results.	A Hibernate-specific query hint can be used to cache query results.

In the next topic, we’ll bring all the pieces together and focus on the design and architecture of applications with Hibernate, Java Persistence, and EJB 3.0 components. We’ll also unit test a Hibernate application.