Hibernate Query Language

One really cool thing about using the Spring Boot framework is how easy it is to setup entities for database operations. Hibernate is the ‘persistence layer’ that Spring uses to achieve this and this framework comes with something called ‘HQL‘.

Hibernate uses a powerful query language (HQL) that is similar in appearance to SQL. Compared with SQL, however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association

With HQL, you can construct queries using your database entities instead of SQL. It certainly feels more declarative than constructing SQL queries. For example, here’s an ER diagram of a personal project (cleaning up some things before I make the repo public).

To fetch all Restaurant records which had critical violations (severity of 3), I constructed the following HQL query.

@Query(
    "select r from Violation v inner join v.inspectionReport ir inner join ir.restaurant r where v.severity = 3")

Of course, by doing this, you cede control to Hibernate on exactly how the query is constructed. For more, check this official Spring guide on using JPA (JPA is a spec and Hibernate is a JPA implementation) or this Udemy course on Spring + Hibernate.

Another cool thing you can do with Hibernate is creating a virtual column. This virtual column can then be used in any HQL queries you need. derived properties. Here’s an example of my use case (creating a derived property):

@Formula(
    "(SELECT COUNT(ir_violations.id)\n"
        + "  FROM ir_restaurants INNER JOIN \n"
        + "  ir_inspectionreport ON ir_restaurants.id = ir_inspectionreport.restaurant_id  INNER JOIN\n"
        + "  ir_violations ON ir_inspectionreport.id = ir_violations.inspection_report_id \n"
        + "  WHERE ir_violations.severity = 2 and ir_inspectionreport.restaurant_id = id\n"
        + "  GROUP BY ir_violations.severity)")
private Integer nonCriticalCount;

and using this derived property:

@Query(
  value =
      "select new com.janeullah.healthinspectionrecords.domain.dtos.FlattenedRestaurant"
          + "(r.id,ir.score,r.criticalCount,r.nonCriticalCount,r.establishmentInfo.name,ir.dateReported,r.establishmentInfo.address,r.establishmentInfo.county) "
          + "from InspectionReport ir inner join ir.restaurant r ORDER BY r.establishmentInfo.name ASC"
)

Pretty nifty stuff!