14.2. Basic HQL and JPA QL queries
Let's start with some simple queries to get familiar with the HQL syntax and semantics. We apply selection to name the data source, restriction to match records to the criteria, and projection to select the data you want returned from a query.
TRY IT
Testing Hibernate queries—The Hibernate Tools for the Eclipse IDE support a Hibernate Console view. You can test your queries in the console window, and see the generated SQL and the result immediately.
You'll also learn JPA QL in this section, because it's a subset of the functionality of HQL—we'll mention the differences when necessary.
When we talk about queries in this section, we usually mean SELECT statements, operations that retrieve data from the database. HQL also supports UPDATE, DELETE, and even INSERT .. SELECT statements, as we discussed in chapter 12, section 12.2.1, "Bulk statements with HQL and JPA QL." JPA QL includes UPDATE and DELETE. We won't repeat these bulk operations here and will focus on SELECT statements. However, keep in mind that some differences between HQL and JPA QL may also apply to bulk operations—for example, whether a particular function is portable.
SELECT statements in HQL work even without a SELECT clause; only FROM is required. This isn't the case in JPA QL, where the SELECT clause isn't optional. This isn't a big difference in practice; almost all queries require a SELECT clause, whether you write JPA QL or HQL. However, we start our exploration of queries with the FROM clause, because in our experience it's easier to understand. Keep in mind that to translate these queries to JPA QL, you must theoretically add a SELECT clause to complete the statement, but Hibernate lets you execute the query anyway if you forget it (assuming SELECT *).
14.2.1. Selection
The simplest query in HQL is a selection (note that we don't mean SELECT clause or statement here, but from where data is selected) of a single persistent class:
from Item
This query generates the following SQL:
select i.ITEM_ID, i.NAME, i.DESCRIPTION, ... from ITEM i
Usually, when you select a class to query from using HQL or JPA QL, you need to assign an alias to the queried class to use as a reference in other parts of the query:
from Item as item
The as keyword is always optional. The following is equivalent:
from Item item
Think of this as being a bit like the temporary variable declaration in the following Java code:
for ( Iterator i = allQueriedItems.iterator(); i.hasNext(); ) {
Item item = (Item) i.next();
...
}
You assign the alias item to queried instances of the Item class, allowing you to refer to their property values later in the code (or query). To remind yourself of the similarity, we recommend that you use the same naming convention for aliases that you use for temporary variables (camelCase, usually). However, we may use shorter aliases in some of the examples in this book, such as i instead of item, to keep the printed code readable.
FAQ
Are HQL and JPA QL case sensitive? We never write HQL and JPA QL keywords in uppercase; we never write SQL keywords in uppercase either. It looks ugly and antiquated—most modern terminals can display both uppercase and lowercase characters. However, HQL and JPA QL aren't case-sensitive for keywords, so you can write FROM Item AS item if you like shouting.
Polymorphic queries
HQL and JPA QL, as object-oriented query languages, support polymorphic queries—queries for instances of a class and all instances of its subclasses, respectively. You already know enough HQL and JPA QL to be able to demonstrate this. Consider the following query:
from BillingDetails
This returns objects of the type BillingDetails, which is an abstract class. In this case, the concrete objects are of the subtypes of BillingDetails: CreditCard and BankAccount. If you want only instances of a particular subclass, you may use
from CreditCard
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:
from java.lang.Object
Of course, this also works for interfaces—this query returns all serializable persistent objects:
from java.io.Serializable
Likewise, the following criteria query returns all persistent objects (yes, you can select all the tables of your database with such a query):
from java.lang.Object
Note that Java Persistence doesn't standardize polymorphic queries that use nonmapped interfaces. However, this works with Hibernate EntityManager.
Polymorphism applies not only to classes named explicitly in the FROM clause, but also to polymorphic associations, as you'll see later in this chapter.
We've discussed the FROM clause, now let's move on to the other parts of HQL and JPA QL.
14.2.2. Restriction
Usually, you don't want to retrieve all instances of a class. You must be able express constraints on the property values of objects returned by the query. This is called restriction. The WHERE clause is used to express a restriction in SQL, HQL, and JPA QL. These expressions may be as complex as you need to narrow down the piece of data you're looking for. Note that restriction doesn't only apply to SELECT statements; you also use a restriction to limit the scope of an UPDATE or DELETE operation.
This is a typical WHERE clause that restricts the results to all User objects with the given email address:
from User u where u.email = '[email protected]'
Notice that the constraint is expressed in terms of a property, email, of the User class, and that you use an object-oriented notion for this.
The SQL generated by this query is
select u.USER_ID, u.FIRSTNAME, u.LASTNAME, u.USERNAME, u.EMAIL from USER u where u.EMAIL = '[email protected]'
You can include literals in your statements and conditions, with single quotes. Other commonly used literals in HQL and JPA QL are TRUE and FALSE:
from Item i where i.isActive = true
A restriction is expressed using ternary logic. The WHERE clause is a logical expression that evaluates to true, false, or null for each tuple of objects. You construct logical expressions by comparing properties of objects to other properties or literal values using the built-in comparison operators.
FAQ
What is ternary logic? A row is included in an SQL resultset if and only if the WHERE clause evaluates to true. In Java, notNullObject==null evaluates to false and null==null evaluates to true. In SQL, NOT_NULL_COLUMN=null and null=null both evaluate to null, not true. Thus, SQL needs a special operator, IS NULL, to test whether a value is null. This ternary logic is a way of handling expressions that may be applied to null column values. Treating null not as a special marker but as a regular value is an SQL extension to the familiar binary logic of the relational model. HQL and JPA QL have to support this ternary logic with ternary operators.
Let's walk through the most common comparison operators.
Comparison expressions
HQL and JPA QL support the same basic comparison operators as SQL. Here are a few examples that should look familiar if you know SQL:
from Bid bid where bid.amount between 1 and 10
from Bid bid where bid.amount > 100
from User u where u.email in ('foo@bar', 'bar@foo')
Because the underlying database implements ternary logic, testing for null values requires some care. Remember that null = null doesn't evaluate to true in SQL, but to null. All comparisons that use a null operand evaluate to null. (That's why you usually don't see the null literal in queries.) HQL and JPA QL provide an SQL-style IS [NOT] NULL operator:
from User u where u.email is null from Item i where i.successfulBid is not null
This query returns all users with no email address and items which are sold.
The LIKE operator allows wildcard searches, where the wildcard symbols are % and _, as in SQL:
from User u where u.firstname like 'G%'
This expression restricts the result to users with a firstname starting with a capital G. You may also negate the LIKE operator, for example, in a substring match expression:
from User u where u.firstname not like '%Foo B%'
The percentage symbol stands for any sequence of characters; the underscore can be used to wildcard a single character. You can define an escape character if you want a literal percentage or underscore:
from User u where u.firstname not like '\%Foo%' escape=''
This query returns all users with a firstname that starts with %Foo.
HQL and JPA QL support arithmetic expressions:
from Bid bid where ( bid.amount / 0.71 ) - 100.0 > 0.0
Logical operators (and parentheses for grouping) are used to combine expressions:
from User user
where user.firstname like 'G%' and user.lastname like 'K%'
from User u
where ( u.firstname like 'G%' and u.lastname like 'K%' )
or u.email in ('[email protected]', '[email protected]' )
You can see the precedence of operators in table 14.1, from top to bottom.
The listed operators and their precedence are the same in HQL and JPA QL. The arithmetic operators, for example multiplication and addition, are self-explanatory. You've already seen how binary comparison expressions have the same semantics as their SQL counterpart and how to group and combine them with logical operators. Let's discuss collection handling.
Expressions with collections
All expressions in the previous sections included only single-valued path expressions: user.email, bid.amount, and so on. You can also use path expressions that end in collections in the WHERE clause of a query, with the right operators.
For example, let's assume you want to restrict your query result by the size of a collection:
from Item i where i.bids is not empty
This query returns all Item instances that have an element in their bids collection. You can also express that you require a particular element to be present in a collection:
from Item i, Category c where i.id = '123' and i member of c.items
This query returns Item and Category instances—usually you add a SELECT clause and project only one of the two entity types. It returns an Item instance with the primary key '123' (a literal in single quotes) and all Category instances this Item instance is associated with. (Another trick you use here is the special .id path; this field always refers to the database identifier of an entity, no matter what the name of the identifier property is.)
There are many other ways to work with collections in HQL and JPA QL. For example, you can use them in function calls.
Calling functions
An extremely powerful feature of HQL is the ability to call SQL functions in the WHERE clause. If your database supports user-defined functions (most do), you can put this to all sorts of uses, good or evil. For the moment, let's consider the usefulness of the standard ANSI SQL functions UPPER() and LOWER(). These can be used for case-insensitive searching:
from User u where lower(u.email) = '[email protected]'
Another common expression is concatenation—although SQL dialects are different here, HQL and JPA QL support a portable concat() function:
from User user
where concat(user.firstname, user.lastname) like 'G% K%'
Also typical is an expression that requires the size of a collection:
from Item i where size(i.bids) > 3
JPA QL standardizes the most common functions, as summarized in table 14.2.
All the standardized JPA QL functions may be used in the WHERE and HAVING clauses of a query (the latter you'll see soon). The native HQL is a bit more flexible. First, it offers additional portable functions, as shown in table 14.3.
| Function | Applicability |
|---|---|
| BIT_LENGTH(s) | Returns the number of bits in s |
| CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP() | Returns the date and/or time of the database management system machine |
| SECOND(d), MINUTE(d), HOUR(d), DAY(d), MONTH(d), YEAR(d) | Extracts the time and date from a temporal argument |
| CAST(t as Type) | Casts a given type t to a Hibernate Type |
| INDEX(joinedCollection) | Returns the index of joined collection element |
| MINELEMENT(c), MAXELEMENT(c), MININDEX(c), MAXINDEX(c), ELEMENTS(c), INDICES(c) | Returns an element or index of indexed collections (maps, lists, arrays) |
| Registered in org.hibernate.Dialect | Extends HQL with other functions in a dialect |
Most of these HQL functions translate into a counterpart in SQL you've probably used before. This translation table is customizable and extendable with an org.hibernate.Dialect. Check the source code of the dialect you're using for your database; you'll probably find many other SQL functions already registered there for immediate use in HQL. Keep in mind that every function that isn't included in the org.hibernate.Dialect superclass may not be portable to other database management systems!
Another recent addition to the Hibernate API is the addSqlFunction() method on the Hibernate Configuration API:
Configuration cfg = new Configuration();
cfg.addSqlFunction(
"lpad",
new StandardSQLFunction("lpad", Hibernate.STRING)
);
... cfg.buildSessionFactory();
This operation adds the SQL function lpad to HQL. See the Javadoc of StandardSQLFunction and its subclasses for more information.
HQL even tries to be smart when you call a function that wasn't registered for your SQL dialect: Any function that is called in the WHERE clause of an HQL statement, and that isn't known to Hibernate, is passed directly to the database, as an SQL function call. This works great if you don't care about database portability, but it requires that you keep your eyes open for nonportable functions if you do care.
Finally, before we move on to the SELECT clause in HQL and JPA QL, let's see how results can be ordered.
Ordering query results
All query languages provide some mechanism for ordering query results. HQL and JPA QL provide an ORDER BY clause, similar to SQL.
This query returns all users, ordered by username:
from User u order by u.username
You specify ascending and descending order using asc or desc:
from User u order by u.username desc
You may order by multiple properties:
from User u order by u.lastname asc, u.firstname asc
You now know how to write a FROM, WHERE, and ORDER BY clause. 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.
14.2.3. Projection
The SELECT clause performs projection in HQL and JPA QL. It allows you to specify exactly which objects or properties of objects you need in the query result.
Simple projection of entities and scalar values
For example, consider the following HQL query:
from Item i, Bid b
This is a valid HQL query, but it's invalid in JPA QL—the standard requires that you use a SELECT clause. Still, the same result that is implicit from this product of Item and Bid can also be produced with an explicit SELECT clause. This query returns ordered pairs of Item and Bid instances:
Query q = session.createQuery("from Item i, Bid b");
// Query q = em.createQuery("select i, b from Item i, Bid b");
Iterator pairs = q.list().iterator();
// Iterator pairs = q.getResultList().iterator();
while ( pairs.hasNext() ) {
Object[] pair = (Object[]) pairs.next();
Item item = (Item) pair[0];
Bid bid = (Bid) pair[1];
}
This 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.
The following explicit SELECT clause also returns a collection of Object[]s:
select i.id, i.description, i.initialPrice
from Item i where i.endDate > current_date()
The Object[]s returned by this query contain a Long at index 0, a String at index 1, and a BigDecimal or MonetaryAmount at index 2. These are scalar values, not entity instances. Therefore, they 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 call this kind of query a scalar query.
When you use a SELECT clause, the elements of the result are no longer guaranteed to be unique. For example, item descriptions aren't unique, so the following query may return the same description more than once:
select item.description from Item item
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 use the DISTINCT keyword:
select distinct item.description from Item item
This eliminates duplicates from the returned list of Item descriptions.
Calling functions
It's also (for some Hibernate SQL dialects) possible to call database specific SQL functions from the SELECT clause. For example, the following query retrieves the current date and time from the database server (Oracle syntax), together with a property of Item:
select item.startDate, current_date() from Item item
The technique of database functions in the SELECT clause isn't limited to database-dependent functions. it works with other more generic (or standardized) SQL functions as well:
select item.startDate, item.endDate, upper(item.name)
from Item item
This query returns Object[]s with the starting and ending date of an item auction, and the name of the item all in uppercase.
In particular, it's possible to call SQL aggregate functions, which we'll cover later in this chapter. Note, however, that the Java Persistence standard and JPA QL don't guarantee that any function that isn't an aggregation function can be called in the SELECT clause. Hibernate and HQL allow more flexibility, and we think other products that support JPA QL will provide the same freedom to a certain extent. Also note that functions that are unknown to Hibernate aren't passed on to the database as an SQL function call, as they are in the WHERE clause. You have to register a function in your org.hibernate.Dialect to enable it for the SELECT clause in HQL.
The previous sections should get you started with basic HQL and JPA QL. It's time to look at the more complex query options, such as joins, dynamic fetching, subselects, and reporting queries.