Monday, September 24

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.

5 Comments

  • Session and SessionFactory??? Really??? It’s not 90’s anymore. Look at JPA and Spring Data JPA. That’s up-to-date approach of using Hibernate.

    As for your last paragraph, that’s complete nonsense. If you log SQL statements and care about them, the you have complete control over the generated SQL. If you don’t care about it, then the problem lies not within Hibernate, but in you.

    • JPQL is not much more than just HQL rebranded. So while your point is valid, the practical difference is minimal. It’s also quite legitimate to use Hibernate directly, even if most people now use it via JPA.

      I’d also like to point readers at JPA Criteria. Though I have to say it’s a pretty baroque API and last time I worked with it (a couple of years ago, admittely) it was so inconsistently implemented across providers that benefits were outweighed by the portability and debugging pain.

      I strongly disagree that you have “complete control” over your SQL via Hibernate/JPA. You can _bypass_ query generation for problem queries using native queries, possibly via named native query mappings etc. fSelectively bypassing it for problem queries is not complete control. Most importantly, even modern JPA still miserably fails to address fetch control on a per-query basis; you’re expected to accept that mapping an entity relationship as lazy or eager declaratively will suit all the accesses in your application. Sure, you can use HQL/JPQL’s “left join fetch” to force an eager load, but AFAIK it’ll then do a giant left join and won’t consider other fetching strategies.

      What I want to be able to do is hint a JPQL or Criteria query to the effect of “when fetching entity A, I want collection A.x fetched eagerly, and collection A.y fetched lazily on-demand. Fetch using the most efficient pattern for the expected data, which might be deduplicated left join or batch/range follow-up SELECTs.” As of JPA2 or the most recent Hibernate I used the closest approximation to this was to declare everything lazily fetched then use left-join fetches with JPQL, avoiding the Criteria API. Otherwise you’d get clobbered by N+1 SELECT cascades, and even then you had to deal with big ugly and inefficient joins with deduplication client-side.

      EclipseLink offers some provider-specific fetch control hints, but last I checked Hibernate doesn’t.

      Hopefully there’s going to be (or is, and I’ve missed it) a new JPA revision that adds per-query declarative fetch control that lets providers do efficient fetch batching. For now, though, you often have to work around the JPA provider or bypass it to get decent performance.

      Sure, that’s complete control, but only in the same way that you have complete control of Java code generation by using JNI to run asm procedures ;) .

  • Hibernate provides a framework to make the relational database system from object oriented domain model and conversion is done by following the practices available to develop the application using the functionality of this accessible library.

  • This interface provide the various method :

    public int executeUpdate() – Execute the update or delete statement.
    public String getNamedParameters() – It return the names of all named parameters of the query.
    public String[] getQueryString() – Get the query string.
    public List list() – It return the query results as a List.
    public Query setMaxResults(int maxResults) – It is used to set the maximum number of rows to retrieve.
    public Object uniqueResult() – It is used to return a single instance that matches the query, or null if the query returns no results.

Leave a Reply

Your email address will not be published. Required fields are marked *