Dec 24, 2009

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.



image



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=”catcolumn=”catIdclass=” 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



image



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=”companycolumn=”COMP_IDclass=”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



image



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=”companycolumn=”COMP_IDclass=”com.plp.dto.Companylazy=”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



0 comments:

Text Widget

Copyright © Vinay's Blog | Powered by Blogger

Design by | Blogger Theme by