2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Umair's PlanetPostgreSQL3 / Using Hibernate Query Language (HQL) with PostgreSQL
Umair Shahid

Using Hibernate Query Language (HQL) with PostgreSQL

May 25, 2016/5 Comments/in Umair's PlanetPostgreSQL /by Umair Shahid

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.
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
5 replies
  1. Jirka Pinkas
    Jirka Pinkas says:
    May 28, 2016 at 9:51 am

    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.

    Reply
    • craig.ringer
      craig.ringer says:
      July 19, 2016 at 4:27 am

      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 😉 .

      Reply
  2. devops online training
    devops online training says:
    September 11, 2017 at 6:15 am

    I hadn’t thought of using containers but that’s a great idea. Thanks so much for sharing!

    Reply
  3. priya
    priya says:
    June 25, 2018 at 7:41 am

    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.

    Reply
  4. Natasha Verma
    Natasha Verma says:
    July 13, 2018 at 6:06 am

    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.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

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

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
PostgreSQL-based application performance: latency and hidden delays JSONB and PostgreSQL 9.5: with even more powerful tools!
Scroll to top
×