Using Hibernate Query Language (HQL) with PostgreSQL

In my previous blog, I talked about using Java arrays to talk to PostgreSQL arrays. This blog is going to go one step further in using Java against the database. Hibernate is an ORM implementation available to use with PostgreSQL. Here we discuss its query language, HQL.

The syntax for HQL is very close to that of SQL, so anyone who knows SQL should be able to ramp up very quickly. The major difference is that rather than addressing tables and columns, HQL deals in objects and their properties. Essentially, it is a complete object oriented language to query your database using Java objects and their properties. As opposed to SQL, HQL understands inheritance, polymorphism, & association. Code written in HQL is translated by Hibernate to SQL at runtime and executed against the PostgreSQL database.

An important point to note here is, references to objects and their properties in HQL are case-sensitive; all other constructs are case insensitive.  

Why Use HQL?

The main driver to using HQL would be database portability. Because its implementation is designed to be database agnostic, if your application uses HQL for querying the database, you can interchange the underlying database by making simple changes to the configuration XML file. As opposed to native SQL, the actual code will remain largely unchanged if your application starts talking to a different database.

Prominent Features

A complete list of features implemented by HQL can be found on their website. Here, we present examples of some basic and salient features that will help you get going on HQL. These examples are using a table by the name of ‘largecities’ that lists out the 10 largest metropolitans of the world. The descriptor and data are:

postgres=# \d largecities
 Table "public.largecities"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 rank   | integer                | not null
 name   | character varying(255) | 
Indexes:
 "largecities_pkey" PRIMARY KEY, btree (rank)

postgres=# select * from largecities; 
 rank | name 
------+-------------
    1 | Tokyo
    2 | Seoul
    3 | Shanghai
    4 | Guangzhou
    5 | Karachi
    6 | Delhi
    7 | Mexico City
    8 | Beijing
    9 | Lagos
   10 | Sao Paulo
(10 rows)

HQL works with a class that this table is mapped to in order to create objects in memory with its data. The class is defined as:

@Entity
public class LargeCities {
 @Id
 private int rank;
 private String name;

 public int getRank() {
 return rank;
 }
 public String getName() {
 return name;
 }
 public void setRank(int rank) {
 this.rank = rank;
 }
 public void setName(String name) {
 this.name = name;
 }
}

Notice the @Entity and @Id annotations, which are declare the class ‘LargeCities’ as an entity and the property ‘rank’ as the identifier.

The FROM Clause

The FROM clause is used if you want to load all rows of the table as objects in memory. The sample code given below retrieves all rows from table ‘largecities’ and lists out the data from objects to stdout.

try {

 SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
 Session session = sessionFactory.openSession();
 session.beginTransaction();

 Query query = session.createQuery("FROM LargeCities");
 List<LargeCities> cities = (List<LargeCities>)query.list();

 session.close();

 for (LargeCities c : cities)
     System.out.println(c.getRank() + " " + c.getName());

} catch (Exception e) {
     System.out.println(e.getMessage()); 
}

Note that ‘LargeCities’ referred to in the HQL query is not the ‘largecities’ table but rather the ‘LargeCities’ class. This is the object oriented nature of HQL.

Output from the above program is as follows:

1 Tokyo
2 Seoul
3 Shanghai
4 Guangzhou
5 Karachi
6 Delhi
7 Mexico City
8 Beijing
9 Lagos
10 Sao Paulo

The WHERE Clause

There can be instances where you would want to specify a filter on the objects you want to see. Taking the above example forward, you might want to see just the top 5 largest metropolitans in the world. A WHERE clause can help you achieve that as follows:

try {

 SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
 Session session = sessionFactory.openSession();
 session.beginTransaction();

 Query query = session.createQuery("FROM LargeCities WHERE rank < 6");
 List<LargeCities> cities = (List<LargeCities>)query.list(); 

 session.close(); 

 for (LargeCities c : cities)
     System.out.println(c.getRank() + " " + c.getName());

} catch (Exception e) {
     System.out.println(e.getMessage()); 
}

Output from the above code is:

1 Tokyo
2 Seoul
3 Shanghai
4 Guangzhou
5 Karachi

The SELECT Clause

The default FROM clause retrieve all columns from the table as properties of the object in Java. There are instances where you would want to retrieve only selected properties rather than all of them. In such a case, you can specify a SELECT clause that identifies the precise columns you want to retrieve.

The code below selects just the city name for retrieval. Note that, because it now just one column that is being retrieved, Hibernate loads it as a list of Strings rather than a list of LargeCities objects.

try {

 SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
 Session session = sessionFactory.openSession();
 session.beginTransaction();

 Query query = session.createQuery("SELECT name FROM LargeCities");
 List<String> cities = (List<String>)query.list(); 

 session.close(); 

 for (String c : cities)
     System.out.println(c);

} catch (Exception e) {
     System.out.println(e.getMessage()); 
}

The output of this code is:

Tokyo
Seoul
Shanghai
Guangzhou
Karachi
Delhi
Mexico City
Beijing
Lagos
Sao Paulo

Named Parameters

Much like prepared statements, you can have named parameters through which you can use variables to assign values to HQL queries at runtime. The following example uses a named parameter to find out the rank of ‘Beijing’.

try {

 SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
 Session session = sessionFactory.openSession();
 session.beginTransaction();

 Query query = session.createQuery("SELECT rank FROM LargeCities WHERE name = :city_name");
 query.setParameter("city_name", "Beijing");
 List<Integer> rank = (List<Integer>)query.list(); 

 session.getTransaction().commit();
 session.close(); 

 for (Integer c : rank)
     System.out.println("Rank is: " + c.toString());

} catch (Exception e) {
     System.out.println(e.getMessage()); 
}

Output for this code:

Rank is: 8

Pagination

When programming, numerous scenarios present themselves where code is required to be processed in chunks or pages. The process is called pagination of data and HQL provides a mechanism to handle that with a combination of setFirstResult and setMaxResults, methods of the Query interface. As the names suggest, setFirstResult allows you to specify which record should be the starting point for record retrieval while setMaxResults allows you to specify the maximum number of records to retrieve. This combination is very helpful in Java or in web apps where a large result set is shown split into pages and the user has the ability to specify the page size.

The following code breaks up our ‘largecities’ examples into 2 pages and retrieves data for them.

try {

 SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
 Session session = sessionFactory.openSession();
 session.beginTransaction();

 Query query = session.createQuery("FROM LargeCities");

 query.setFirstResult(0); 
 query.setMaxResults(5);

 List<LargeCities> cities = (List<LargeCities>)query.list(); 

 System.out.println("*** Page 1 ***");
 for (LargeCities c : cities)
     System.out.println(c.getRank() + " " + c.getName());

 query.setFirstResult(5); 

 cities = (List<LargeCities>)query.list(); 

 System.out.println("*** Page 2 ***");
 for (LargeCities c : cities)
     System.out.println(c.getRank() + " " + c.getName());

 session.close(); 

} catch (Exception e) {
     System.out.println(e.getMessage()); 
}

An important point to keep in mind here is that Hibernate usually does pagination in memory rather than at the database query level. This means that for large data sets, it might be more efficient to use cursors, temp tables, or some other construct for pagination.

Other Features

A comprehensive list of features is available on the Hibernate website, but a few more worth mentioning here are:

• UPDATE Clause
• DELETE Clause
• INSERT Clause
• JOINs

Aggregate Methods

• avg
• count
• max
• min
• sum

Drawbacks of Using HQL

HQL gives its users a lot of flexibility and rich set of options to use while talking to a database. The flexibility does come at a price, however. Because HQL is designed to be generic and largely database-agnostic, you should watch out for the following when using HQL.

• At times, you would need to use advanced features & functions that are specific to PostgreSQL. As an example, you might want to harness the power of the newly introduced JSONB data type. Or you might want to use window functions to analyze your data. Because HQL tries to be as generic as possible, in order to use such advanced features, you will need to fallback to native SQL.

• Because of the way left joins are designed, if you are joining an object to another table / object in a one-to-many or many-to-many format, you can potentially get duplicate data. This problem is exacerbated in case of cascading left joins and HQL has to preserve references to these duplicates, essentially ending up transferring a lot of duplicate data. This has the potential to significantly impact performance.

• Because HQL does the object-relational mapping itself, you don’t get full control over how and what data gets fetched. One such infamous issue is the N+1 problem. Although you can find workarounds within HQL, identifying the problem can at time get very tricky.