The first thing you should know about HQL is that it is exclusively a query language. Unlike SQL, which offers statements of different forms for SELECT, UPDATE, DELETE, and INSERT, HQL is designed exclusively for retrieval. As a result, the syntax is most similar to the SELECT statement. There is no such thing as HQL for updating or inserting data, and the exact same HQL can be used for both SELECT and DELETE operations.

Some of the examples in Chapter 3 and Chapter 4 give an introduction to the use of HQL, and the methods for executing HQL are shown in Chapter 6. Nonetheless, HQL is most easily explored in the context of the Hibern8 IDE, a simple tool for executing HQL statements on the fly in a graphical environment.

Using Hibern8 IDE

The Hibern8 IDE is distributed with the Hibernate Extensions package. To use it, simply launch it using the command java net.sf.hibern8ide.Hibern8IDE (found in the hibern8ide.jar), with the class path set to include the various required Hibernate and Hibernate Extensions libraries as well as the persistent classes used by your application. Launching Hibern8 IDE will display the interface shown in Figure 8.1.

Figure 8.1. Starting Hibern8 IDE

To establish the Hibernate configuration, click on the ... button next to either the Properties field (to select a hibernate.properties file) or the Config File field (to select a hibernate.cfg.xml file).

Click the Add button to add one or more *.hbm.xml mapping files. After adding all of the needed files, click the Apply button.

Using the hibernate.properties and *.hbm.xml files and classes from Chapter 3, Hibern8 IDE will look as shown in Figure 8.2.

Figure 8.2. Hibern8 IDE Configured

Clicking on the HQL Commander tab, you can drag and drop objects or object properties from the left-side object display to the HQL entry field so that Hibern8 IDE can automatically generate HQL for you. Pressing control-enter will cause Hibern8 IDE to execute the HQL and display the results. Figure 8.3 shows an example of retrieving the artifact.id values.

Figure 8.3. Artifact ID Values

Hibern8 IDE allows you to inspect the values of returned results, tunneling in through the returned object hierarchy. Figure 8.4 shows an example of this, allowing you to see in detail the objects returned by a request.

Figure 8.4. Inspecting Hibern8 IDE Results

As described in Chapter 9, you can use Hibern8 in conjunction with a tool like IronTrack SQL to easily monitor the SQL generated by a given HQL statement. This can be very useful as you experiment with HQL using Hibern8.

It is easy to explore the capabilities of HQL using Hibern8, but to truly take advantage of Hibernate you need to understand the structure of HQL in more detail. You are encouraged to work through this reference in conjunction with Hibern8 and the various mapping files in the rest of the book to fully understand HQL.

[select] from [where] [group by] [having] [order by];

This section serves as a road map for understanding the HQL syntax. It can be used to diagram a HQL statement, much as you diagram a sentence in English (circling the noun, the verb, etc.).

A HQL command is composed of several clauses, as shown above. The from clause is the only portion of a query that is mandatory. For example, the simple command from com.cascadetg.ch03.Owner will return all of the owner table records as Owner objects.

The select clause is used to limit the returned data to specific properties or to control the format for the returned data. It will be used to return either an aggregate value (e.g., the number of records returned) or a subset of the data (e.g., just the first name of the student objects).

The from clause is used to widen the returned data to additional classes. The tables used are as determined by the class mappings (defined by your *.hbm.xml files). HQL requires at least one class to form the basis of the query. If you wish to use more than one table, you may have to use join statements (described below) to control how the tables are returned.

The where, group by, and having clauses are used to cull the data returned. You can use a combination of logical expressions and Boolean expressions to compose your where clause. In addition, you can use collection commands to work with collections in your where statements.

The order by clause is used to sort the returned data.

SELECT DISTINCT? selectedPropertiesList | ( NEW className OPEN selectedPropertiesList CLOSE );

The optional distinct keyword can be used to reduce the result set to unique results (no result is returned more than once). This can be useful when you wish to limit the results. For example, the statement select student.id from Student as student, Student as student2 returns the full product of every record in the Student table as matched with every other student record (literally, the records in the student table multiplied by the records in the student table). The distinct keyword can be used to limit these results back to the individual student records. For example, the statement select distinct student.id from Student as student, Student as student2 will return the same results as a from Student.

If one or more properties (selectedProperties) are specified, the query will return them as mapped to objects. For example, the HQL select owner.id, owner.name from com.cascadetg.ch03.Owner as owner will return a set of java.lang.Long and java.lang.String objects.

Instead of specifying that the properties should be returned as a set of different objects, you may wish to use the new className statement instead. This will return an arbitrary class, calling the constructor as the result of a query. For example, the HQL command select new java.lang.StringBuffer (owner.name) from Owner as owner returns a set of java.lang.StringBuffer objects, initialized with the owner.name property.

( path | aggregate ) ( COMMA path | aggregate )*

COUNT OPEN path CLOSE
SUM OPEN path CLOSE
AVG OPEN path CLOSE
MAX OPEN path CLOSE
MIN OPEN path CLOSE
COUNT OPEN STAR CLOSE
COUNT OPEN DISTINCT | ALL path CLOSE

( OPEN query CLOSE ) | ( 'elements'|'indices' OPEN path CLOSE )

FROM className AS? identifier (  ( COMMA className AS? identifier ) | ( joinType classname
 AS? identifier ) )*

The from clause is used to identify the source of the data to be returned. The tables are specified by the classname (com.cascadetg.ch04.Student, or just by Student if the auto-import attribute of the hibernate-mapping tag is set to true as described in Chapter 5.

An identifier must be assigned to the class for queries that need to refer to a class property. For example, com.cascadetg.ch04.Student is typically assigned an identifier such as student. This identifier is required in order to refer to the class in other clauses of the query. For example, select first Name from Student is invalid HQL; the valid statement would be select student.firstName from Student as student.

mysql> select * from student;
+----+-----------+----------+-------------+
| ID | firstName | lastName | idString    |
+----+-----------+----------+-------------+
|  1 | Bob       | Smith    | 123-45-6789 |
|  2 | John      | Stevens  | 456-78-9012 |
|  3 | Betty     | Almara   | 098-76-5432 |
+----+-----------+----------+-------------+
3 rows in set (0.00 sec)

The identifier is particularly useful when you are writing a complex query that needs to refer to the same table with two different meanings; for example, if you wish to join a class against itself.

The AS token is optional, but some prefer it to enhance readability. The statements select owner from Owner as owner and select owner from Owner owner are equivalent.

The values of the joinType token are as shown below.

Join Types

( ( 'left'|'right' 'outer'? ) | 'full' | 'inner' )? JOIN FETCH?

The join syntax is used to control the product of the result of joining two tables together. To consider the results of the various types of join, let's start with a bit of data as shown in Figures 8.5 and 8.6.

Example 8.6. Exam Result Data

mysql> select * from examresult;
+----+-------+-----------+--------+
| ID | score | studentID | examID |
+----+-------+-----------+--------+
|  1 |    85 |         1 |      1 |
|  2 |    89 |         2 |      1 |
|  4 |    75 |         1 |      2 |
|  5 |    83 |         2 |      2 |
+----+-------+-----------+--------+
4 rows in set (0.01 sec)

Looking at the data, we see that there are three students, but only students 1 and 2 have actually taken any exams. Joins allow us to glue together the data items from Figure 8.5 and Figure 8.6 in different ways to obtain different results (the heart of the notion of a relational database).

WARNING

Some databases do not support all of the various forms of join supported by Hibernate. Try to avoid being too clever with your joins if possible. Even if the join works, make sure that it performs well. See Chapter 10 for more information on managing performance.

Inner Join

From basic SQL, we know that the query select result, student from Student as student, Examresult as result will result in a multiplied set of returned data, as shown in Figure 8.7. This is what is referred to as an inner join.

Figure 8.7. Unrestricted Inner Join Results

A where clause is used to cull the data down. The statement select result, student from Student as student, Examresult as result where result.student=student restricts our query to the students who have taken exams, as shown in Figure 8.8.

Figure 8.8. Bound Inner Join Results

Inner joins are the most basic form of join, and the easiest to understand.

Left/Right Joins

Now, imagine that we wish to retrieve all of the students even if they haven't taken a test—in other words, return the records even of a student for whom there are no examresult records. We have to use an outer join to retrieve all of the records. Outer joins come in two flavors, right join and left join. The use of the right or left term depends on the table you wish to use as the “master” table.

The statement select result, student from Student as student, Examresult as result right join result.student joinedstudent where student=joinedstudent returns all the students, including the student with a null examresult as shown in Figure 8.9.

Figure 8.9. Right Outer Join

The join is performed and retrieves all of the records from the student table, even though the record may not be present in the right table (the student table). In effect, you are asking for all records from the right table, in this case student, even if there are no corresponding entries present in the examresult table.

Conversely, using the left join statement instead, as in select result, student from Student as student, Examresult as result left join result.student joinedstudent where student=joinedstudent returns the joined records from the perspective of the examresult table (the left table), as shown in Figure 8.10. In effect, you are asking for all of the records in the examresult table, even if not present in the student table. As there are no records in the examresult table that do not point to a student (that would be a foreign-key violation), you simply get the exam results.

Figure 8.10. Left Outer Join

Full Joins

A full join will return all records from both tables, regardless of any matches between the tables. Note that this command is not supported on all databases (in particular, MySQL doesn't offer support for the full join command).

Outer as Inverse of Inner

It's worth pointing out that you may be specifically seeking the records that do not match a regular join. For example, you may only be interested in the students who haven't taken an exam. To perform this query, you can use the is null modifier, as in select result, student from Student as student, Examresult as result right join result.student joinedstudent where student=joinedstudent and result is null, which produces the results shown in Figure 8.11.

Figure 8.11. Using an Outer Join to Obtain the Inverse of an Inner Join

Fetch Joins

The fetch join is a useful shortcut used to fetch parent and child objects in a single select. For example, the statement select student from Student as student left join fetch student.examresults returns all of the students and their Examresult objects, regardless of whether the student has any associated exam results. If there are any examresults, they are already loaded into Examresult objects, associated with the Student object (accessible via the Student.getExamresults() method. If you know that you will be reading the exam results of the student objects, but have the exam result relationship set to lazy by default, this is an excellent way to retrieve all of the needed data in a single statement.

Similarly, the statement select distinct student from Student as student inner join fetch student.examresults retrieves the student objects with an exam record. As in the left join fetch, the Student and Examresult objects are loaded, but only students with an Examresult are returned.

The fetch statement may only be used as an inner join or a left outer join.

As of the release of Hibernate Extensions-2.1, the Hibern8 IDE does not support the fetch statement. To test fetch joins, you'll need to use the HQL in your own sample code.

WHERE expression

The where clause is used to cull the set of returned rows.

A huge variety of possible expressions, some of them database-dependent, are allowed in a WHERE clause. Regardless of the expression, parentheses ( ) are used to indicate grouping and precedence. Some of the possible elements in a where clause are shown in Table 8.4.

The interpretation of SQL functions is database-dependent. Hibernate will automatically convert the SQL literal ' delimiters to the proper characters for the database.

A where expression may include mathematical operations, logical operations, boolean operations, or a quantified expression (in SQL terms, a subselect).

'exists' | ( expression 'in' ) | ( expression OPERATION 'any' | 'some' ) collection

Query myQuery = hibernateSession.
 createQuery("from Student as student where student.firstName =
  :name");
myQuery.setString("name", "Bob");
Iterator students = myQuery.iterate();

Query myQuery = hibernateSession.
 createQuery("from Student as student where student.firstName =
  ?");
myQuery.setString(0, "Bob");
Iterator students = myQuery.iterate();

GROUP BY path ( COMMA path )*

The command GROUP BY is used to restrict the results to a single result per table. This command is useful in a variety of situations, such as when you are using aggregate functions. For example, the command select student, max(result.score) from Student as student, Examresult as result where student=result.student group by student returns a set of students and the corresponding java.lang.Integer value for each student's high score.

HAVING expression

A HAVING clause is used in the same way as a WHERE clause except for one key difference. WHERE selects rows before the groups and aggregates are calculated, whereas HAVING selects rows after the groups and aggregates have been calculated.

In practice, there is no reason to use a HAVING clause unless the HAVING clause has an aggregate function (otherwise, it's equivalent to a WHERE clause). Unfortunately, certain databases (such as MySQL) don't support an aggregate in the HAVING clause. For those databases, HAVING and WHERE are the same.

ORDER_BY selectedPropertiesList (ASC | DESC)?

The order by command allows you to specify the order in which the data should be returned, as performed by the database. By default, the values are sorted in ascending (ASC) order. This order can be reversed using the DESC token.

As an example, the statement select student from Student as student order by student.firstName returns the students sorted by first name, starting with the letter A, whereas the statement select student from Student as student order by student.firstName desc returns the students starting with the letter Z.

The ordering is performed by the database, and the precise results are dependent on its sorting mechanisms.

Hibernate supports the Criteria API, an object-based query mechanism that constructs a query from a set of objects. The Criteria API is perhaps best understood in the context of an ordinary HQL statement, as described earlier in this chapter.

The Criteria interface, as shown in Figure 8.12, is returned by a simple Session.createCriteria() call.

Figure 8.12. Criteria Interface

Normally, you will use the Expression factory, as shown in Figure 8.13, to obtain a criterion that you wish to apply.

Figure 8.13. Expression Factory

Figure 8.14 shows the hierarchy of built-in criteria. Normally, you won't instantiate these directly, and instead will use the built-in Expression factory.

Figure 8.14. Built-in Criteria Hierarchy

The code shown in Listing 8.3 returns the same result set as the HQL select student.firstName from Student as student where student.firstName like 'B%'.

Criteria myQuery =
       hibernateSession.createCriteria(Student.class);
myQuery.add(Expression.like("firstName", "B%"));

Criteria myQuery =
     hibernateSession.createCriteria(Student.class).add(
          Expression.like("firstName", "B%"));

Criteria myQuery =
                hibernateSession.createCriteria(Student.class);
myQuery.add(Expression.like("firstName", "B%"));
myQuery.add(Expression.like("lastName", "S%"));
myQuery.setFetchMode("examresults", FetchMode.EAGER);
myQuery.setMaxResults(10);
myQuery.setTimeout(1000);

Criteria myQuery =
     hibernateSession
          .createCriteria(Student.class)
          .add(Expression.like("firstName", "B%"))
          .add(Expression.like("lastName", "S%"))
          .setFetchMode("examresults", FetchMode.EAGER)
          .setMaxResults(10)
          .setTimeout(1000);

In addition to HQL and Criteria, Hibernate allows you to retrieve data from the database using native SQL. Describing SQL would go beyond the scope of this text, but it should be noted here that Hibernate requires aliases to be used when working with native SQL. The aliases are used to bind the SQL to the returned objects. Listing 8.7 shows an example of the execution of a native SQL statement binding to the return objects.

session.createSQLQuery("SELECT {student.*} FROM student AS {student}
WHERE ID<10", "student", Student.class).list();

If you simply wish to execute raw SQL against the database (and not retrieve a set of objects bound to a query), you will probably need to query the session for the underlying JDBC connection. An example of the use of raw SQL execution from a Hibernate session can be found in Listing 2.7 (the setInnoDB() method).

Keep in mind that SQL statements mixed with other statements may not execute in the order desired unless you use the Session.flush() method. For more on this, see Chapter 9.

The full scope and theory of SQL is beyond the scope of this book (especially considering the wide variety of proprietary extensions). Consult your database documentation or another text on SQL for more information on native SQL queries.