Hibernate and Hibernate Query Language (HQL) Tutorial

(Updated 9/20/11 for WaveMaker 6.4)

This guide provides an introduction to Hibernate query language.
For an online Hibernate tutorial with running examples, go to http://hqltutorial.cloudfoundry.com/

HQL Tutorial Features and Screenshot

This application demonstrates how to write custom HQL queries for use with Hibernate, nHibernate and WaveMaker. Features include:

  • Ability to specify HQL examples in the url
  • In depth introduction to HQL Concepts
  • Running on Cloud Foundry using MySQL
hqltutorial.png

Project source file to download

Download hqltutorial application files here:

  1. WaveMaker HQL Tutorial project: HQL Tutorial WM 6.4 Project Export
  2. MySQL custpurchase database: custpurchase.sql

Running HQL Tutorial Examples

What is HQL?

HQL stands for Hibernate Query Language, and is the data query language that comes with Hibernate. Hibernate is a Java-based library for accessing relational data. You can download Hibernate at www.hibernate.org.

HQL is very similar to SQL, but has two powerful benefits:

  1. HQL provides one language for accessing different SQL databases, each with its own slightly different flavor of SQL; and
  2. HQL has object oriented extensions which can be very powerful.

A Simple Example: SQL vs HQL

A normal SQL query looks something like this:

SELECT name
FROM customer
WHERE state = "CA"

In HQL, this same query looks something like this:

SELECT cust.name
 FROM customer cust
 WHERE cust.state = "CA"

How These Examples Work

This guide provides an easy way to learn how HQL works using a simple data model and a set of working examples. These examples build from simple topics such as HQL SELECT WHERE queries to more complex topics such as HQL JOIN queries

For each example, there is a description of the HQL topic and an example in which you can see the actual HQL and then execute the HQL query to see the result.

The Customer/Purchase Data Model

All examples in this guide use the same simple data model with two tables: a customer table and a purchase table.

There are two relationships: a customer can have zero to many related subsidiaries and a customer can have zero to many related purchases. Here is the DDL for the employee table:

CREATE TABLE CUSTOMER(
 CUSTID INTEGER GENERATED BY DEFAULT 
   AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
 NAME VARCHAR(50),STATE VARCHAR(2),
 PARENTCO INTEGER DEFAULT NULL,
 CONSTRAINT SUBSIDIARIESFKEY FOREIGN KEY(PARENTCO) 
   REFERENCES CUSTOMER(CUSTID))

Here is the DDL for the employee table:

CREATE TABLE PURCHASE(
 ORDERID INTEGER GENERATED BY DEFAULT 
   AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
 ORDERDATE DATE NOT NULL,
 ORDERVALUE INTEGER DEFAULT NULL,
 CUSTOMERNUMBER INTEGER NOT NULL,
 CONSTRAINT ORDERSFKEY FOREIGN KEY(CUSTOMERNUMBER) 
   REFERENCES CUSTOMER(CUSTID))

All of these examples were built using WaveMaker Visual Ajax Studio. If you want to practice creating HQL queries against your own data model, you can download WaveMaker at www.wavemaker.com.

HQL And Case Sensitivity

Keywords are not case sensitive: keywords (e.g., SELECT, FROM and WHERE in the above example) are not case sensitive. In this guide, all keywords will be capitalized
Properties are case sensitive: table and column names (e.g., customer, cust and name in the above example) are case sensitive and must match their corresponding Java declarations.

Basic HQL Query

SELECT is the most common statement in SQL, and is the starting point for most queries:

  • The FROM clause: indicates the source table or tables from which the data is to be retrieved.
  • The AS clause: allows you to create an alias for a table or column name. In general, this is a good practice, particularly for complex queries.
    allows you to define an alias for a table or column.
The following example shows selecting all the customer names from the customer table:

SELECT cust.name AS customer_name
FROM Customer AS cust

Because HQL is object oriented, it assumes by default that you want all the columns returned from each table. Thus to return all rows and columns of the Customer table, simply write:

FROM Customer

HQL WHERE Clause

The where clause allows you to narrow the list of instances returned. The WHERE clause includes a comparison, which is used to restrict the number of rows returned by the query.

The following example uses a where clause to return only Customers who are located in California (e.g., cust.state = 'CA'):

SELECT cust.name AS customer_name
FROM Customer AS cust
WHERE cust.state = 'CA'

Query with Parameters

You can specify input parameters for an Hibernate query using ":" such as :inputVar.

The following example searches for matches to an input string:

FROM Customer cust
WHERE lower(cust.name) LIKE :inputName

Wild Card Query With LIKE

The SQL keyword, LIKE, is used for pattern matching on a string. Usually, the percent character is used to allow pattern matching before or after the keyword.

The following example uses the special pipe symbol "||" to create a string that concatenates the parameter, ":descriptionvar" with the percent symbol to match the occurence of the descriptionvar parameter anywhere in the description column.

FROM Issue WHERE (description LIKE '%' || :descriptionvar || '%')

Optional or Null Parameters

For complex search queries with a number of search parameters, some of the parameters may not always be set. In this case, you can specify directly in HQL to return true if the parameter is null or the parameter matches a specific value.

In the following example, if the parameter ":priorityvar" has the value null the HQL expression evaluates to true, or if :priorityvar is not null, it matches the priority attribute in the table.

FROM Issue WHERE (:priorityvar is null OR priority = :priorityvar)

Case Insensitive Query

You can also perform a case-insensitive query using LOWER function. The wild card character '%' to match any number of characters.

The following example allows the user to enter a string and then performs a case insensitive, wild card search that matches the first part of the name string.

FROM Customer cust
WHERE lower(cust.name) LIKE lower('%'+:inputName+'%')

Inner Join

The JOIN clause allows you to link information from multiple tables, for example associating Customers with their related Purchases. An INNER JOIN only returns results if there are matching records in both tables.

In Hibernate, you can use a relationship name, such as cust.purchases, to specify the purchases for a customer. In standard SQL, this would be handled by a WHERE clause that explicitly compares primary and foreign key values.

NOTE: in Hibernate, joins are different than in normal SQL. Hibernate uses the relationship name defined in your Hibernate mapping file. You must use table name aliases (e.g., Customer AS cust) and then explicitly reference the alias and the relationship in the join (e.g., JOIN cust.purchases).

The following example performs an inner join to return only Customers who have made Purchases.

SELECT 
   cust.name AS name, 
   purch.orderdate AS order_date, 
   purch.ordervalue AS order_value
FROM Customer AS cust
INNER JOIN cust.purchases AS purch

You can also join a table with itself. The following example shows using a self join to get the subsidiaries for each customer:

SELECT 
   cust.name AS name, 
   subsidiary.name AS subsidiary_name
FROM Customer AS cust
INNER JOIN cust.customers AS subsidiary

This example shows calculating the total purchases by customer

SELECT 
   cust.name AS cust_name, 
   SUM(purch.ordervalue) AS total_purchases
FROM Customer as cust 
INNER JOIN cust.purchases as purch

Outer Join

An outer join does not require each record in the two joined tables to have a matching record. In a join, the first table mentioned in the FROM clause is referred to as the left table. The second table mentioned in the FROM clause is referred to as the right table. An outer join can be either a right or left outer join:

  • Left outer join: returns every record in the first table combined with any matching records in the second table
  • Right outer join: returns every record in the second table combined with any matching records in the first table
The following example performs a left outer join to return all Customers along with their related Purchases. Note that it returns even customers who have not made any purchases (e.g., rows in the left table - Customer - that do not have corresponding matches in the right table - purchase). Because the relationship encapsulates the foreign key/primary key mapping, you can dispense with the WHERE cust.pkey = purchase.fkey clause altogether.

The pseudo-code form of an HQL join is:

FROM Table AS tableAlias JOIN tableAlias.relationshipName

SELECT 
   cust.name AS name, 
   purch.orderdate AS order_date, 
   purch.ordervalue AS order_value
FROM Customer AS cust
LEFT OUTER JOIN cust.purchases AS purch

Cross Join

A cross join combines every row in the first table with every row in the second. It is most useful to pass a single value to every row in a table, for example, to pass the total order value to every row in order to calculate what percentage of the total order value that row represents.

HQL Aggregate Functions

HQL supports a number of statistical functions, such as: AVG, SUM, MIN, MAX, COUNT and COUNT(DISTINCT ...). COUNT(DISTINCT) only returns the unique elements of a column.

The following example shows using a variety of statistical functions.

SELECT 
   COUNT(purch) AS purchase_count,
   COUNT(DISTINCT purch.orderdate) AS distinct_dates,
   AVG(purch.ordervalue) AS average_value,
   SUM(purch.ordervalue) AS total_value,
   MIN(purch.ordervalue) AS minimum_value,
   MAX(purch.ordervalue) AS maximum_value
FROM Purchase as purch
JOIN purch.customer as cust
WHERE cust.state = 'CA'

HQL ORDER BY Clause

The ORDER BY clause is used to identify which columns are used to sort the resulting data, and in which order they should be sorted (options are ASC for ascending or DESC for descending).

The following example shows returning a list of customers ordered by state in ascending alphabetical order and then by name in descending alphabetical order

SELECT 
   cust.name AS customer_name, 
   cust.state AS customer_state
FROM Customer AS cust
ORDER BY cust.state ASC, cust.name DESC

HQL GROUP BY Clause

Query results can be grouped by any column value to provide summary information. The GROUP BY clause is used to combine, or group, rows with related values into elements of a smaller set of rows.

The following example shows returning the total value of all orders, grouped by the state in which a customer is located.

SELECT 
   cust.state AS state,
   SUM(purch.ordervalue) AS total_value,
FROM Customer as cust
JOIN cust.purchases as purch
GROUP BY cust.state

HQL Update

Use the UPDATE statement to update a row or rows in the database. The following example updates a row in the Project table with primary key = input parameter :pid and increments the nextIssueNum column by 1.

UPDATE Project p SET p.nextIssueNum = p.nextIssueNum+1 
WHERE p.projectId = :pid

HQL Delete

Use the DELETE statement to delete a row or rows in the database. The following example deletes a row in the Project table with primary key = input parameter :pid.

DELETE FROM Project p WHERE p.projectId = :pid

HQL Insert

HQL does not support inserting rows into a database, but the Hibernate API does have methods for inserting new records.

In WaveMaker, use the LiveForm function, insertData() to insert objects into a database.

HQL Nested Select Statements

This example shows how to return data from two different tables in the same query by using a nested select. The first SELECT queries the lineitem table and returns the total amount of items purchased in a particular order. The second SELECT queries the total payments for that order. Both queries rely on a join to the purchase table to retrieve only lineitem and payments where purch.lineitem = orderid. 

SELECT
(SELECT ROUND(SUM(relitem.quantity * relitem.item.price),2)
FROM purch.lineitems AS relitem) AS totinvoice,
(SELECT SUM(relpayment.amount)
FROM purch.payments AS relpayment) AS totpayment
FROM Purchase AS purch
WHERE purch.orderid = :orderid
GROUP BY purch.orderid

HQL Current Date Function

The HQL variable, current_date, returns the current date.

SELECT current_date AS today
FROM Purchase

HQL Format Date Function

By default, the date is returned as the number of number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT). To format a date into a human-readable format, use the MySQL Date_Format function. The following query produces dates in the format of 'Monday, March 21, 2011':

SELECT DATE_FORMAT(current_date, '%W, %M %d, %Y') AS today
FROM Payment

More information on the MySQL date format function

HQL Date Math - Compare Dates

To compare the difference in days between two dates, use the MySQL datediff function. The following query returns the number of days overdue a purchase invoice is based on comparing today's date to the due date for that invoice.

SELECT purch.customer.company AS name, 
   purch.duedate AS duedate, 
   current_date AS today, 
   DATEDIFF(current_date, purch.duedate) AS overdue, 
FROM Purchase AS purch
WHERE 
   DATEDIFF(current_date, purch.duedate) >= :duevar

More information on the MySQL date format function

HQL Complex Query Examples

This example shows a more complex, multi-level join where billrecord.task.project navigates from the billrecord table to its related project via the task table.

SELECT DATE_FORMAT(billrecord.firstDate, '%b, %y') AS billmonth,
   billproject.projectName AS name, 
   SUM(billrecord.totHours*billresource.billingRate) AS totbill
FROM Billingrecord AS billrecord
JOIN billrecord.task.project AS billproject
JOIN billrecord.resource AS billresource
WHERE billrecord.firstDate >= :startdate AND 
   billrecord.firstDate <= :enddate
GROUP BY DATE_FORMAT(billrecord.firstDate, '%b, %y'), 
   billproject.projectName
ORDER BY DATE_FORMAT(billrecord.firstDate, '%b, %y') ASC, 
   billproject.projectName ASC

The following query returns a set of rows from the Faqitem table along with a count of the related rows in the comments table using the JOIN command and COUNT. Note also that if the :searchstr input parameter is not NULL, the query checks to find a match against two different fields in the Faqitem table.

SELECT f.id AS id, f.title AS title, 
   f.shortdescr AS description, 
   f.createDate AS createDate, f.tags AS topic, 
   COUNT(comm) AS commCount
FROM Faqitem f
LEFT JOIN f.comments AS comm
WHERE :searchstr is NULL 
   OR f.title LIKE '%' || :searchstr || '%' 
   OR f.shortdescr LIKE '%' || :searchstr || '%'
GROUP BY title
ORDER BY f.priority DESC

The following query returns invoice information, including customer data, purchases made by that customer, the total amount purchased and payments made for each purchase and the number of days that purchase is overdue.

SELECT purch.customer.company AS name, 
   purch.orderdate AS ordered, 
   SUM(line.quantity * jitem.price) AS amount, 
   SUM(paid.amount) AS paid, purch.duedate AS duedate, 
   current_date AS today, 
   DATEDIFF(current_date, purch.duedate) AS overdue, 
   purch.status AS status, 
   purch.orderid AS invoicenumber
FROM Purchase AS purch
JOIN purch.lineitems AS line
LEFT JOIN purch.payments AS paid
LEFT JOIN line.item AS jitem
WHERE (:statusvar is null OR status = :statusvar) AND
   (:custvar is null OR lower(purch.customer.company) LIKE lower('%' || :custvar || '%')) AND
   (:duevar is null OR  DATEDIFF(current_date, purch.duedate) >= :duevar)
GROUP by purch.orderid

Other Hibernate Resources

      Share/Bookmark
© VMware, Inc. All Rights Reserved.