Hibernate Query Language (HQL)

While most ORM tools and object databases offer an object query language, Hibernate’s HQL stands out as complete and easy to use. Although you can use SQL statements directly with Hibernate (which is covered in detail in the “Using Native SQL” section of this chapter), we recommend that you use HQL (or criteria) whenever possible to avoid database portability hassles, as well as to take advantage of Hibernate’s SQL-generation and caching strategies. In addition to its technical advantages over traditional SQL, HQL is a more compact query language than SQL because it can make use of the relationship information defined in the Hibernate mappings.

We realize that not every developer trusts Hibernate’s generated SQL to be perfectly optimized. If you do encounter a performance bottleneck in your queries, we recommend that you use SQL tracing on your database during performance testing of your critical components. If you see an area that needs optimization, first try to optimize using HQL, and only later drop into native SQL. Hibernate provides statistics information through a JMX MBean, which you can use for analyzing Hibernate’s performance. Hibernate’s statistics also give you insight into how caching is performing.

Syntax Basics

HQL was inspired by SQL and is a major inspiration for the Java Persistence Query Language (JPQL) . The JPQL specification is included in the standard for JPA available from the Java Community Process web site ( www.jcp.org/en/jsr/detail?id=220 ). HQL’s syntax is defined as an ANTLR grammar; the grammar files are included in the grammar directory of the Hibernate core download. (ANTLR is a tool for building language parsers.)

As the ANTLR grammar files are somewhat cryptic, and as not every statement that is permissible according to the ANTLR grammar’s rules can be used in Hibernate, we outline the syntax for the four fundamental HQL operations in this section. Note that the following descriptions of syntax are not comprehensive; there are some deprecated or more obscure usages (particularly for SELECT statements) that are not covered here.

UPDATE [VERSIONED]
   [FROM] path [[AS] alias] [, ...]
   SET property = value [, ...]
   [WHERE logicalExpression]

Query query=session.createQuery("update Person set creditscore=:creditscore where name=:name");
query.setInteger("creditscore", 612);
query.setString("name", "John Q. Public");
int modifications=query.executeUpdate();

DELETE
   [FROM] path [[AS] alias]
   [WHERE logicalExpression]

Query query=session.createQuery("delete from Person where accountstatus=:status");
query.setString("status", "purged");
int rowsDeleted=query.executeUpdate();

INSERT
   INTO path ( property [, ...])
   select

Query query=session.createQuery("insert into purged_users(id, name, status) "+
    "select id, name, status from users where status=:status");
query.setString("status", "purged");
int rowsCopied=query.executeUpdate();

[SELECT [DISTINCT] property [, ...]]
   FROM path [[AS] alias] [, ...] [FETCH ALL PROPERTIES]
   WHERE logicalExpression
   GROUP BY property [, ...]
   HAVING logicalExpression
   ORDER BY property [ASC | DESC] [, ...]

Named Queries

Hibernate (and JPA) provide named queries. Named queries are created via class-level annotations on entities; normally, the queries apply to the entity in whose source file they occur, but there’s no absolute requirement for this to be true.

Named queries are created with the @NamedQueries annotation, which contains an array of @NamedQuery sets; each has a query and a name.

First, let’s create an object model we can use as an example. Our object model will contain products and suppliers; it will also contain a specialized product (“Software”) that adds an attribute to Product. One effect of the hierarchy we use here is that Lombok is no longer as usable as it has been,¹ so we’re going to eliminate some of the boilerplate from the source code — namely, constructors, mutators, accessors, equals(), hashCode(), and toString(). The source code download for the book will have all of these methods, of course. This object model structure is shown in Listings 9-1, 9-2, and 9-3.

Listing 9-1. The Supplier Entity, with Boilerplate Removed

@Entity
public class Supplier implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer id;
    @Column(unique = true)
    @NotNull
    String name;
    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true,
            mappedBy = "supplier", targetEntity = Product.class)
    List<Product> products = new ArrayList<>();
}

Listing 9-2. The Basic Product Entity, with Boilerplate Removed

@Entity
public class Product implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Integer id;
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    Supplier supplier;
    @Column
    @NotNull
    String name;
    @Column
    @NotNull
    String description;
    @Column
    @NotNull
    Double price;
}

Listing 9-3. The Software Entity, with Boilerplate Removed

@Entity
public class Software extends Product implements Serializable {
    @Column
    @NotNull
    String version;
}

Adding a named query is as simple as adding an annotation to one of the entities. For example, if we wanted to add a named query to retrieve all Supplier entities, we could do so by adding a @NamedQuery annotation to any of the entities, although it makes the most sense to put the query in Supplier’s source code.

@NamedQuery(name = "supplier.findAll", query = "from Supplier s")

You can group queries together by adding a @NamedQueries annotation, and then embedding the named queries in an array as part of that annotation. This would look like this:

@NamedQueries({
        @NamedQuery(name = "supplier.findAll", query = "from Supplier s"),
        @NamedQuery(name = "supplier.findByName",
                query = "from Supplier s where s.name=:name"),
})

Using the named queries is very simple. Let’s create a test that populates data before every test run, and clears it out after each test run, along with a test that uses our supplier.findAll query.² This test is shown in Listing 9-4.

Listing 9-4. A Test that Works with a Known Dataset

public class QueryTest {
    Session session;
    Transaction tx;

    @BeforeMethod
    public void populateData() {
        try(Session session = SessionUtil.getSession()) {
            Transaction tx = session.beginTransaction();

            Supplier supplier = new Supplier("Hardware, Inc.");
            supplier.getProducts().add(
                    new Product(supplier, "Optical Wheel Mouse", "Mouse", 5.00));
            supplier.getProducts().add(
                    new Product(supplier, "Trackball Mouse", "Mouse", 22.00));
            session.save(supplier);

            supplier = new Supplier("Supplier 2");
            supplier.getProducts().add(
                    new Software(supplier, "SuperDetect", "Antivirus", 14.95, "1.0"));
            supplier.getProducts().add(
                    new Software(supplier, "Wildcat", "Browser", 19.95, "2.2"));
            supplier.getProducts().add(
                    new Product(supplier, "AxeGrinder", "Gaming Mouse", 42.00));

            session.save(supplier);
            tx.commit();
        }
        this.session = SessionUtil.getSession();
        this.tx = this.session.beginTransaction();
    }

    @AfterMethod
    public void closeSession() {
        session.createQuery("delete from Product").executeUpdate();
        session.createQuery("delete from Supplier").executeUpdate();
        if (tx.isActive()) {
            tx.commit();
        }
        if (session.isOpen()) {
            session.close();
        }
    }

    @Test
    public void testNamedQuery() {
        Query query = session.getNamedQuery("supplier.findAll");
        List<Supplier> suppliers = query.list();
        assertEquals(suppliers.size(), 2);
    }
}

The methods annotated with @BeforeMethod and @AfterMethod will run before and after each @Test method; this means that each test will have a consistent dataset upon entry, as well as an active transaction and session.

The actual test itself is very simple: it acquires a named query (“supplier.findAll”) and executes it, making sure that we’ve retrieved all of the Supplier instances we expect to find.

We can, of course, create queries on the fly, as we’ve done many times through the book so far. Listing 9-5 is another example (for the sake of completeness) for querying all Products:

Listing 9-5. Creating a Query

@Test
public void testSimpleQuery() {
    Query<Product> query = session.createQuery("from Product", Product.class);
    query.setComment("This is only a query for product");
    List<Product> products = query.list();
    assertEquals(products.size(), 5);
}

The createQuery() method takes a valid HQL statement (and, if desired, a Java type reference) and returns an org.hibernate.query.Query object. The Query class provides methods for returning the query results as a Java List, as an Iterator, or as a unique result. Other functionality includes named parameters, results scrolling, JDBC fetch sizes, and JDBC timeouts. You can also add a comment to the SQL that Hibernate creates, which is useful for tracing which HQL statements correspond to which SQL statements.

Logging and Commenting the Underlying SQL

Hibernate can output the underlying SQL behind your HQL queries into your application’s log file. This is especially useful if the HQL query does not give the results you expect, or if the query takes longer than you wanted. You can run the SQL that Hibernate generates directly against your database in the database’s query analyzer at a later date to determine the causes of the problem. This is not a feature you will have to use frequently, but it is useful should you have to turn to your database administrators for help in tuning your Hibernate application.

Hibernate: /* This is only a query for product */ select product0_.id as id1_0_, product0_.description as descript2_0_, product0_.name as name3_0_, product0_.price as price4_0_, product0_.supplier_id as supplier5_0_, product0_1_.version as version1_1_, case when product0_1_.id is not null then 1 when product0_.id is not null then 0 end as clazz_ from Product product0_ left outer join Software product0_1_ on product0_.id=product0_1_.id

select supplier0_.id as id1_2_, supplier0_.name as name2_2_ from Supplier supplier0_

Hibernate: /* named HQL query supplier.findAll */ select supplier0_.id as id1_2_, supplier0_.name as name2_2_ from Supplier supplier0_

public Query<R> setComment(String comment)

String hql = "from Supplier";
Query<Supplier> query = session.createQuery(hql, Supplier.class);
query.setComment("My HQL: " + hql);
List<Supplier> results = query.list();

Hibernate: /*My HQL: from Supplier*/ select supplier0_.id as id, supplier0_.name
as name2_ from Supplier supplier0_

The from Clause and Aliases

We have already discussed the basics of the from clause in HQL in the earlier section, “The First Example with HQL.” The most important feature to note is the alias. Hibernate allows you to assign aliases to the classes in your query with the as clause. Use the aliases to refer back to the class inside the query. For instance, our previous simple example would be the following:

from Product as p

or the following:

from Product as product

You’ll see either alias-naming convention in applications, although it’s generally used to shorten long queries (and thus you’ll see “from Product as p” more often than other such forms). The as keyword is optional — you can also specify the alias directly after the class name, as follows:

from Product product

If you need to fully qualify a class name in HQL, just specify the package and class name. Hibernate will take care of most of this behind the scenes, so you really need this only if you have classes with duplicate names in your application. If you have to do this in Hibernate, use syntax such as the following:

from chapter09.model.Product

The from clause is very basic and useful for working directly with objects. However, if you want to work with the object’s properties without loading the full objects into memory, you must use the select clause.

The select Clause and Projection

The select clause provides more control over the result set than the from clause. If you want to obtain the properties of objects in the result set, use the select clause. For instance, we could run a projection query on the products in the database that only returned the names, instead of loading the full object into memory, as follows:

select product.name from Product product

The result set for this query will contain a List of Java String objects. Additionally, we can retrieve the prices and the names for each product in the database, like so:

select product.name, product.price from Product product

This result set contains a List of Object arrays (therefore, List<Object[]>)—each array represents one set of properties (in this case, a name and price pair).

If you’re only interested in a few properties, this approach can allow you to reduce network traffic to the database server and save memory on the application’s machine.

Using Restrictions with HQL

As with SQL, you use the where clause to select results that match your query’s expressions. HQL provides many different expressions that you can use to construct a query. In the HQL language grammar, there are many possible expressions,⁶ including these:

• Logic operators: OR, AND, NOT

• Equality operators: =, <>, !=, ^=

• Comparison operators: <, >, <=, >=, like, not like, between, not between

• Math operators: +, -, *, /

• Concatenation operator: ||

• Cases: Case when <logical expression> then <unary expression> else _<unary expression> end

• Collection expressions: some, exists, all, any

In addition, you may also use the following expressions in the where clause:

• HQL named parameters:, such as :date, :quantity

• JDBC query parameter: ?

• Date and time SQL-92 functional operators: current_time(), current_date(), current_timestamp()

• SQL functions (supported by the database): length(), upper(), lower(), ltrim(), rtrim(), etc.

Using Named Parameters

Hibernate supports named parameters in its HQL queries. This makes writing queries that accept input from the user easy — and you do not have to defend against SQL injection attacks.

String sql = "select p from products where name = '" + name + "'";

You could escape the user’s input yourself for every query, but it is much less of a security risk⁸ if you let Hibernate manage all of your input with named parameters. Hibernate’s named parameters are similar to the JDBC query parameters (?) you may already be familiar with, but Hibernate’s parameters are less confusing. It is also more straightforward to use Hibernate’s named parameters if you have a query that uses the same parameter in multiple places.

When using JDBC query parameters, any time you add, change, or delete parts of the SQL statement, you need to update your Java code that sets its parameters, because the parameters are indexed based on the order in which they appear in the statement. Hibernate lets you provide names for the parameters in the HQL query, so you do not have to worry about accidentally moving parameters around in the query.

The simplest example of named parameters uses regular SQL types for the parameters:

String hql = "from Product where price > :price";
Query query = session.createQuery(hql);
query.setDouble("price",25.0);
List results = query.list();

Normally, you do not know the values that are to be substituted for the named parameters; if you did, you would probably encode them directly into the query string. When the value to be provided will be known only at run time, you can use some of HQL’s object-oriented features to provide objects as values for named parameters. The Query interface has a setEntity() method that takes the name of a parameter and an object.

Using this functionality, we could retrieve all the products that have a supplier whose object we already have:

String supplierHQL = "from Supplier where name='MegaInc'";
Query supplierQuery = session.createQuery(supplierHQL);
Supplier supplier = (Supplier) supplierQuery.list().get(0);

String hql = "from Product as product where product.supplier=:supplier";
Query query = session.createQuery(hql);
query.setEntity("supplier",supplier);
List results = query.list();

You can also use regular JDBC query parameters in your HQL queries. We do not particularly see any reason why you would want to, but they do work.

Paging Through the Result Set

Pagination through the result set of a database query is a very common application pattern. Typically, you would use pagination for a web application that returned a large set of data for a query. The web application would page through the database query result set to build the appropriate page for the user. The application would be very slow if the web application loaded all of the data into memory for each user. Instead, you can page through the result set and retrieve the results you are going to display one chunk at a time.

There are two methods on the Query interface for paging: setFirstResult() and setMaxResults(), just as with the Criteria interface. The setFirstResult() method takes an integer that represents the first row in your result set, starting with row 0. You can tell Hibernate to only retrieve a fixed number of objects with the setMaxResults() method. Your HQL is unchanged — you need only to modify the Java code that executes the query. Here’s a test that shows pagination in action to get the sixth through the tenth names of Suppliers:

@Test
public void testPagination() {
    try (Session session = SessionUtil.getSession()) {
        Transaction tx = session.beginTransaction();
        session.createQuery("delete from Product").executeUpdate();
        session.createQuery("delete from Supplier").executeUpdate();

        for (int i = 0; i < 30; i++) {
            Supplier supplier = new Supplier();
            supplier.setName(String.format("supplier %02d", i));
            session.save(supplier);
        }

        tx.commit();
    }

    try (Session session = SessionUtil.getSession()) {
        Query<String> query = session.createQuery(
            "select s.name from Supplier s order by s.name", String.class);
        query.setFirstResult(5);
        query.setMaxResults(5);
        List<String> suppliers = query.list();
        String list = suppliers
                .stream()
                .collect(Collectors.joining(","));
        assertEquals(list,
            "supplier 05,supplier 06,supplier 07,supplier 08,supplier 09");
    }
}

You can change the numbers around and play with the pagination. If you turn on SQL logging, you can see which SQL commands Hibernate uses for pagination. For the open source H2 database, Hibernate uses offset and limit. For other databases, Hibernate uses the appropriate commands for pagination. For instance, Microsoft SQL Server does not support the limit command, so Hibernate uses only the top command. If your application is having performance problems with pagination, this can be very helpful for debugging.

If you only have one result in your HQL result set, Hibernate has a shortcut method for obtaining just that object.

Obtaining a Unique Result

HQL’s Query interface provides a uniqueResult() method for obtaining just one object from a HQL query. Although your query may yield only one object, you may also use the uniqueResult() method with other result sets if you limit the results to just the first result. You could use the setMaxResults() method discussed in the previous section. The uniqueResult() method on the Query object returns a single object, or null if there are zero results. If there is more than one result, then the uniqueResult() method throws a NonUniqueResultException.

The following short example demonstrates having a result set that would have included more than one result, except that it was limited with the setMaxResults() method:

String hql = "from Product where price>25.0";
Query<Product> query = session.createQuery(hql, Product.class);
query.setMaxResults(1);
Product product = query.uniqueResult();
//test for null here if needed

Unless your query returns one or zero results, the uniqueResult() method will throw a NonUniqueResultException exception. Do not expect Hibernate just to pick off the first result and return it — either set the maximum results of the HQL query to 1, or obtain the first object from the result list.

Sorting Results with the order by Clause

To sort your HQL query’s results, you will need to use the order by clause. You can order the results by any property on the objects in the result set: either ascending (asc) or descending (desc). You can use ordering on more than one property in the query, if you need to. A typical HQL query for sorting results looks like this:

from Product p where p.price>25.0 order by p.price desc

If you wanted to sort by more than one property, you would just add the additional properties to the end of the order by clause, separated by commas. For instance, you could sort by product price and the supplier’s name, as follows:

from Product p order by p.supplier.name asc, p.price asc

HQL is more straightforward for ordering than the equivalent approach using the Criteria Query API .

Associations

Associations allow you to use more than one class in n HQL query, just as SQL allows you to use joins between tables in a relational database. You add an association to a HQL query with the join clause. Hibernate supports five different types of joins: inner join, cross join, left outer join, right outer join, and full outer join. If you use cross join, just specify both classes in the from clause (from Product p, Supplier s). For the other joins, use a join clause after the from clause. Specify the type of join, the object property to join on, and an alias for the other class.

You can use inner join to obtain the supplier for each product, and then retrieve the supplier name, product name, and product price, as so:

select s.name, p.name, p.price from Product p inner join p.supplier as s

You can retrieve the objects using similar syntax:

from Product p inner join p.supplier as s

We used aliases in these HQL statements to refer to the entities in our query expressions. These are particularly important in queries with associations that refer to two different entities with the same class — for instance, if we are doing a join from a table back to itself. Commonly, these types of joins are used to organize tree data structures.

Notice that Hibernate does not return Object objects in the result set; instead, Hibernate returns Object arrays in the results.⁹ You will have to access the contents of the Object arrays to get the Supplier and the Product objects.

If you would like to start optimizing performance, you can use one query to ask Hibernate to fetch the associated objects and collections for an object. If you were using lazy loading with Hibernate, the objects in the collection would not be initialized until you accessed them. If you use fetch on a join in your query, you can ask Hibernate to retrieve the objects in the collection at the time the query executes. Add the fetch keyword after the join in the query, like so:

from Supplier s inner join fetch s.products as p

When you use fetch for a query like this, Hibernate will return only the Supplier objects, not the Product objects. This is because you are specifying the join, so Hibernate knows which objects to fetch (instead of using lazy loading). If you need to get the Product objects, you can access them through the associated Supplier object. You cannot use the properties of the Product objects in expressions contained in the where clause. Use of the fetch keyword overrides any settings you have in the mapping file for object initialization.

Aggregate Methods

HQL supports a range of aggregate methods, similar to SQL. They work the same way in HQL as in SQL, so you do not have to learn any specific Hibernate terminology. The difference is that in HQL, aggregate methods apply to the properties of persistent objects. The count(¼) method returns the number of times the given column name appears in the result set. You may use the count(*) syntax to count all the objects in the result set, or count(product.name) to count the number of objects in the result set with a name property. Here is an example using the count(*) method to count all products:

select count(*) from Product product

The distinct keyword only counts the unique values in the row set — for instance, if there are 100 products, but 10 have the same price as another product in the results, then a select count(distinct product.price) from Product query would return 90. In our database, the following query will return 2, one for each supplier:

select count(distinct product.supplier.name) from Product product

If we removed the distinct keyword, it would return 5, one for each product.

All of these queries return a Long object in the list. You could use the uniqueResult() method here to obtain the result.

The aggregate functions available through HQL include the following:

• avg( property name ): The average of a property’s value

• count( property name or *): The number of times a property occurs in the results

• max( property name ): The maximum value of the property values

• min( property name ): The minimum value of the property values

• sum( property name ): The sum total of the property values

If you have more than one aggregate method, the result set List will contain an Object array with each of the aggregates you requested. Adding another aggregate to the select clause is straightforward:

select min(product.price), max(product.price) from Product product

You can also combine these with other projection properties in the result set.

Bulk Updates and Deletes with HQL

The Query interface contains a method called executeUpdate() for executing HQL UPDATE or DELETE statements.¹⁰ The executeUpdate() method returns an int that contains the number of rows affected by the update or delete, as follows:

public int executeUpdate() throws HibernateException

HQL updates look as you would expect them to, being based on SQL UPDATE statements. Do not include an alias with the update; instead, put the set keyword right after the class name, as follows:

String hql = "update Supplier set name = :newName where name = :name";

Query query = session.createQuery(hql);
query.setString("name","SuperCorp");
query.setString("newName","MegaCorp");
int rowCount = query.executeUpdate();
System.out.println("Rows affected: " + rowCount);

//See the results of the update
Query q = session.createQuery("from Supplier", Supplier.class);
List<Supplier> results = q.list();

After carrying out this query, any supplier previously named SuperCorp will be named MegaCorp. You may use a where clause with updates to control which rows get updated, or you may leave it off to update all rows. Notice that we printed out the number of rows affected by the query. We also used named parameters in our HQL for this bulk update.

Bulk deletes work in a similar way. Use the delete from clause with the class name you would like to delete from. Then use the where clause to narrow down which entries in the table you would like to delete. Use the executeUpdate() method to execute deletes against the database as well.

Be careful when you use bulk delete with objects that are in relationships. Hibernate will not know that you removed the underlying data in the database, and you can get foreign key integrity errors. To get around this, you could set the not-found attribute to ignore on your one-to-many and many-to-one mappings, which will make IDs that are not in the database resolve to null references. The default value for the not-found attribute is exception. Setting the not-found attribute to ignore also has the side effect of causing your associations to load eagerly and fetch all related records instead of using lazy loading. This can cause serious performance problems.

Our code surrounding the HQL DELETE statement is basically the same — we use named parameters, and we print out the number of rows affected by the delete:

String hql = "delete from Product where name = :name";
Query query = session.createQuery(hql);
query.setString("name","Mouse");
int rowCount = query.executeUpdate();
System.out.println("Rows affected: " + rowCount);

//See the results of the update
query = session.createQuery("from Product");
List results = query.list();

Using Native SQL

Although you should probably use HQL whenever possible, Hibernate does provide a way to use native SQL statements directly through Hibernate. One reason to use native SQL is that your database supports some special features through its dialect of SQL that are not supported in HQL. Another reason is that you may want to call stored procedures from your Hibernate application. We discuss stored procedures and other database-specific integration solutions in Appendix A. Rather than just providing an interface to the underlying JDBC connection, like other Java ORM tools, Hibernate provides a way to define the entity (or join) that the query uses. This makes integration with the rest of your ORM-oriented application easy.

You can modify your SQL statements to make them work with Hibernate’s ORM layer. You do need to modify your SQL to include Hibernate aliases that correspond to objects or object properties. You can specify all properties on an object with {objectname.*}, or you can specify the aliases directly with {objectname.property}. Hibernate uses the mappings to translate your object property names into their underlying SQL columns. This may not be the exact way you expect Hibernate to work, so be aware that you do need to modify your SQL statements for full ORM support. You will especially run into problems with native SQL on classes with subclasses — be sure you understand how you mapped the inheritance across either a single table or multiple tables, so that you select the right properties off the table.

Underlying Hibernate’s native SQL support is the org.hibernate.query.NativeQuery interface, which extends the org.hibernate.query.Query interface already discussed. Your application will create a native SQL query from the session with the createSQLQuery() method on the Session interface (inherited from the QueryProducer interface, but that’s probably more detail than we need).

public NativeQuery createSQLQuery(String queryString)

After you pass a string containing the SQL query to the createSQLQuery() method, you should associate the SQL result with an existing Hibernate entity, a join, or a scalar result. The NativeQuery interface has addEntity(), addJoin(), and addScalar() methods. For the entities and joins, you can specify a lock mode, which we discussed in Chapter 8. The addEntity() methods take an alias argument and either a class name or an entity name. The addJoin() methods take an alias argument and a path to join.

Using native SQL with scalar results is the simplest way to get started with native SQL. Our Java code looks like this:

String sql = "select avg(product.price) as avgPrice from Product product";
SQLQuery query = session.createSQLQuery(sql);
query.addScalar("avgPrice",Hibernate.DOUBLE);
List results = query.list();

Because we did not specify any entity aliases, Hibernate executes exactly the same SQL that we passed through:

select avg(product.price) as avgPrice from Product product

The SQL is regular SQL (we did not have to do any aliasing here). We created an SQLQuery object, and then added a scalar mapping with the built-in double type (from the org.hibernate._Hibernate class). We needed to map the avgPrice SQL alias to the object type. The results are a List with one object — a Double.

A bit more complicated than the previous example is the native SQL that returns a result set of objects. In this case, we will need to map an entity to the SQL query. The entity consists of the alias we used for the object in the SQL query and its class. For this example, we used our Supplier class:

String sql = "select {supplier.*} from Supplier supplier";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity("supplier", Supplier.class);
List results = query.list();

Hibernate modifies the SQL and executes the following command against the database:

select Supplier.id as id0_, Supplier.name as name2_0_ from Supplier supplier

The special aliases allow Hibernate to map the database columns back to the object properties.

Summary

HQL is a powerful object-oriented query language that provides the power of SQL while taking advantage of Hibernate’s object-relational mapping and caching. If you are porting an existing application to Hibernate, you can use Hibernate’s native SQL facilities to execute SQL against the database. The SQL functionality is also useful for executing SQL statements that are specific to a given database and have no equivalents in HQL.

You can turn on SQL logging for Hibernate, and Hibernate will log the generated SQL that it executes against the database. If you add a comment to your HQL query object, Hibernate will display a comment in the log next to the SQL statement; this helps with tracing SQL statements back to HQL in your application.

Our next chapter explores the Criteria API, a more fluent style of query that also allows the compiler to check the validity of the query. In our code so far, we’ve expressed almost everything as tests, but the Criteria API provides this at compilation rather than as part of a build process.