HQL & Hibernate Mappings

Introduction to HQL

HQL is fully object-oriented, understanding notions like inheritance, polymorphism and association. HQL allows representing SQL queries in object-oriented terms—by using objects and properties of objects. Queries are case-insensitive, except for names of Java classes and properties. The tables are mapped to Java classes in the hbm files. The columns in the tables are mapped to the properties in the hbm file and those properties are set in the Java class.

For example,

<class name="com.plp.dto.Cat" table="CAT">
     <id name="catId" column="ID" type="integer"> 
            <generator class="increment"/>
     </id>
     <property name="name" column=" NAME" type="string"/>
</class>

We can qualify the class name. Since auto-import is default, we can use the class as it is without the complete path.

 

Simple Query Fetch

Consider Cat is the table name and it is mapped to the Java class Cat.java.

A simple query of fetching all the rows in the table:

In HQL, we can write as

from Cat

We can specify the alias for the class like,

From Cat as cat (or) from Cat cat

To fetch from multiple classes, we can write as

from Cat cat, Dog dog

Select clause:

The select clause picks which objects and properties to return in the query result set.

For example,

select cat.name from Cat cat where cat.name = ‘ Browny’

This query returns the names which are ‘Browny’

Various Methods in fetching the results of the queries

• Associations



• Aggregations



• Grouping



• Expressions

Associations

We can associate two tables using Joins. We may also assign aliases to associated entities, or even to elements of a collection of values, using a join. For joins, we have to map the objects in the .hbm files.

Consider two tables CAT and KITTENS.

Cat.hbm.xml

<class name="com.plp.dto.Cat" table="CAT">
     <id name="catId" column="ID" type="integer"> 
            <generator class="increment"/>
     </id>
     <property name="name" column=" NAME" type="string"/>
 </class>

Kittens.hbm.xml

<class name="com.plp.dto.Kittens" table="KITTENS">
     <id name="kittenId" column="ID" type="integer"> 
            <generator class="increment"/>
     </id>
     <many-to-one name=”cat” column=”catId” class=” com.plp.dto.Cat”/>
     <property name="name" column=" NAME" type="string"/>
</class>

Cat.java

class Cat
{
int catId;
String name;

//Getters and Setters for the Class variables
}

Kittens.java

class Kittens
{
int kittenId;
String name;
Cat cat;  //Object for Cat 

//Getters and Setters for the Class variables

Joins in HQL

There are four joins used in HQL. They are

• Inner join



• Left outer join



• Right outer join



• Full outer join (not usually useful)

But these join constructs may be abbreviated as join, left join and right join respectively.

For example, from Cat as cat left join cat.kittens as kittens

Joins can be in two forms.

Explicit form

This form uses the join keyword explicitly for associations.


For example, from Cat as cat left join cat.kittens as kittens

Implicit form

This form does not use the join keyword. Instead, the associations are "dereferenced" using dot-notation. Implicit join result in inner joins of the resulting query.


For example, from Cat as cat where cat.mate.name like '%s%'

Aggregations

Aggregate functions are to be used with Select clause. Some of the aggregate functions are

• avg()
  
  Example -  select avg(cat.weight) from Cat cat



• sum()
  
  Example -   select sum(cat.weight) from Cat cat



• count()
  
  Example -  select count(cat) from Cat cat



• count (distinct ..)
  
  Example -  select count(distinct cat.name) from Cat cat



• count (all ..)
  
  Example -  select count (all cat.name) from Cat cat



• max()
  
  Example -  select max(cat.weight) from Cat cat



• min()
  
  Example -  select min(cat.weight) from Cat cat

Keywords used with Select clause

• distinct

             Example - select distinct cat.name from Cat cat

• all

             Example - select all cat.name from Cat cat

• 3. The arithmetic operators can also be used with the Select clause.

Grouping

In the section regarding aggregation, grouping has been used.  Grouping operates on a set of rows. It groups the dataset according to a specified column/attribute. To group the resultset/dataset/list according to a column, the group by clause is used.

For example,

select count(o) from Order o having o.priceTotal>2000 group by o.id

The behavior of group by in HQL is similar to that of group by in SQL.

Expressions

Expressions allowed in the where clause include most of the kind of things you could write in SQL.

• mathematical operators +, -, *, /



• binary comparison operators =, >=, <=, <>, !=, like



• logical operations and, or, not



• in, not in, between, is null, is not null, is empty, is not empty, member of and not member of



• the HQL index() function, that applies to aliases of a joined indexed collection



• HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex(), along with the special elements() and indices functions which may be quantified using some, all, exists, any, in.



• JDBC-style positional parameters ?



• named parameters :name, :start_date, :x1



• SQL literals 'foo', 69, 6.66E+2, '1970-01-01 10:00:01.0'



• Java public static final constants eg.Color.TABBY

Some examples:

from Cat cat where cat.name between 'A' and 'B'
from Cat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )
from Cat cat where cat.name not between 'A' and 'B'
from Cat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

Keywords in SQL supported in HQL

The SQL functions any, some, all, exists, in are supported when passed the element or index set of a collection (elements and indices functions) or the result of a subquery (see below).

select mother from Cat as mother, Cat as kit
    where kit in elements(foo.kittens)

select p from NameList list, Person p
    where p.name = some elements(list.names)

from Cat cat where exists elements(cat.kittens)

from Player p where 3 > all elements(p.scores)

Named Queries

The HQL queries can be written in the hbm files also. They are called Named Queries.

Example:

<hibernate-mapping>
<class name="com.plp.dto.Cat" table="CAT">
     <id name="catId" column="ID" type="integer"> 
            <generator class="increment"/>
     </id>
     <property name="name" column=" NAME" type="string"/>
</class>

<query name=”example”><[<CDATA
      from Cat
     >]>
</query>
</hibernate-mapping>

Hibernate Mappings

Hibernate allows the mapping of Mapped tables with the domain objects using the persistent collection-valued fields. Object/relational mappings are usually defined in XML document. The mapping language is Java-centric, meaning that mappings are constructed around persistent class declarations, not table declarations. The mappings in Hibernate are,

• one-to-many



• many-to-one



• many-to-many

One-to-many & many-to-one

Let us take two tables, COMPANY and EMPLOYEE . The relationship between COMPANY & EMPLOYEE is one-to-many. ie. One company can have many employees. Likewise, reverse is also possible. Many employees can work in one company. So, here the relation is many-to-one.

For this example, the hbm files are:

Company.hbm.xml

<class name="com.plp.dto.Company" table="COMPANY">
     <id name="compId" column="COMP_ID" type="integer"> 
            <generator class="increment"/>
     </id>
     <property name="name" column=" NAME" type="string"/>

     <set name=”employees”>
       <key column=”EMP_ID”/>
       <one-to-many  class=” com.plp.dto.Employee” />
     </set>  
</class>

Employee.hbm.xml

<class name="com.plp.dto.Employee" table="EMPLOYEE">
     <id name="empId" column="EMP_ID" type="integer"> 
            <generator class="increment"/>
     </id>

    <many-to-one name=”company” column=”COMP_ID” class=”com.plp.dto.Company”/>

     <property name="name" column=" NAME" type="string"/>
  </class>

Company.java

class Company
{
int compId;
String name;
Set<Employee> employees;  //Set of employees 

//Getters and Setters for class variables

}

Employee.java

class Employee
{

int empId;
String name;
Company company;  //Object of Company class

//Getters and Setters for class variables

}

Many-to-many

Now let us take the third table COMPANY_EMPLOYEE. It is the relation table for both the COMPANY and EMPLOYEE tables. The relationship between COMPANY, COMPANY_EMPLOYEE tables and COMPANY_EMPLOYEE, EMPLOYEE tables is one-to-many.

Instead of having these many relationships, we can form many-to-many relationship between the COMPANY and EMPLOYEE tables.

Company.hbm

<set name="employees" table="COMPANY_EMPLOYEE" inverse=”true”>
  <key column="COMP_ID"/>                       
<many-to-many column="EMP_ID" class="com.plp.dto.Employee"/>            
</set>

Employee.hbm

<set name="companies" table="COMPANY_EMPLOYEE">
  <key column="EMP_ID"/>
  <many-to-many column="COMP_ID" class="com.plp.dto.Company"/>
</set>

Lazy Loading

Hibernate provides three forms of lazy fetching - lazy (single-point) associations, lazy collections, and lazy properties. Lazy collections have been around for a while in Hibernate, and they are the most commonly important form of lazy fetching. Lazy (single-point) associations differ from collections by being single references to other objects persisted by Hibernate. Finally, lazy properties could be values like name, that are typically mapped to a single column.

This lazy loading is achieved by the lazy attribute to true. (ie.lazy=”true”). Defaultly, it is true only.

• If lazy=”true”, then the other objects related to parent are not preloaded. The objects are loaded when they are used.



• If lazy=”false”, then the other objects related are preloaded when the program starts.

For example,

<many-to-one name=”company” column=”COMP_ID” class=”com.plp.dto.Company” lazy=”false”/> 

Here, the company and employee objects are preloaded when the program starts. We can use the objects any time. But if there are many relations, then all the objects are fetched which requires lot of space.

Fetch Join

The lazy fetching can be overridden in hql queries using the keyword fetch.

In addition, a "fetch" join allows associations or collections of values to be initialized along with their parent objects, using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections.

For example,

from Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens

Cascading the tables

Let us consider, there is a Parent table and many child tables. If we are inserting the values into the Parent table, the Child table also gets updated. This is applicable for deletion also.

This can be done using the “Cascade” attribute. With this attribute, we save, update and delete the child tables along with the parent table.

Cascade attribute can take many values like,

• all (cascade both save-update and delete)



• save-update (cascade when save() and update())



• delete (cascade when delete())

Conclusion

HQL queries return the query result(s) in the form of object(s)/tuples of object(s) that are ready to be accessed, operated upon, and manipulated programmatically, instead of returning plain data. HQL fully supports polymorphic queries. That is, along with the object to be returned as a query result, all child objects (objects of subclasses) of the given object shall be returned. HQL contains many advance features such as pagination, fetch join with dynamic profiling, and so forth, as compared to SQL. One big advantage of hibernate is we can make Database independent application. Hibernate Mappings will reduce writing of huge code and the HQL queries.

References

• www.hibernate.org/hib_docs/reference/en/html/queryhql.html



• www.javalobby.org/articles/hibernate-query-101/