Hibernate Query Language (HQL)

Agenda :

HQL Command to retrieve complete object
HQL Command to retrieve partial object
Executing HQL Command
Example on selecting complete object
Example on selecting partial object with single column
Example on selecting partial object with more than one column
Passing runtime values
Positional parameter example
Named parameter example
Example for deleting the object using HQL
Example for updating the object using HQL

Hibernate Query Language (HQL) :

HQL is used to perform bulk operations. HQL is database independent. An Object oriented from of SQL is nothing but HQL.
In HQL we will replace table names with POJO class names, and column names with POJO class variable names.

HQL Command to retrieve complete object :

SQL command for retrieving complete record from the database is

select * from employees
  // employees is table name in database

Using HQL if we want to retrieve complete object from the database then we need to replace class name in the place of table name, and reference variable name in the place of * symbol.
```
select e from Employee e

//here employee is a pojo class name, not a table name ,
// and 'e' is a reference  variable for Employee class. 
```
We can start the command directly with from keyword also.
```
from Employee
```

HQL Command to retrieve partial object :

SQL command for retrieving partial object is

select empname, sal from employees

//empname and sal are column names
// and employees is table name in database

Using HQL, if we want to load partial object then we need to replace column names with pojo class variable names.

select e.employeeName, e.salary from Employee e

//Here employeeName and salary are pojo class variable names 
// and Employee is a pojo class

Executing HQL Command :

In order to execute HQL command first we need to create Query object. Query is an interface and QueryImpl is its implemented class.
To get Query object, we need to call createQuery() method on Session interface.
On the query object call list() method, list() method will return java.util.List.

We need to iterate that list to get the required objects

Ex :

Query query=session.createQuery("HQL Command");
List list=query.list();
Iterator iterator=list.iterator();

while(iterator.hasNext()){
 Employee e=(Employee)iterator.next();
}

Example on selecting complete object :

While retrieving the complete object, hibernate internally converts each row of table into an object of pojo class type and hibernate stores all these pojo classes into List.
So while iterating the object inside while loop, we need to typecast with pojo class type only.

Employee.java

package com.beans;

public class Employee {
private long employeeId;
private String employeeName;
private double salary;


public long getEmployeeId() {
	return employeeId;
}
public void setEmployeeId(long employeeId){
	this.employeeId = employeeId;
}
public String getEmployeeName(){
	return employeeName;
}
public void setEmployeeName(String employeeName){
	this.employeeName = employeeName;
}
public double getSalary() {
	return salary;
}
public void setSalary(double salary) {
	this.salary = salary;
}

}

Employee.hbm.xml

<hibernate-mapping>

<class name="com.beans.Employee" table="EMPLOYEEDB">
  <id name="employeeId" column="EID"/>
  <property name="employeeName" column="ENAME" length="30"/>
  <property name="salary" column="ESAL"/>      
</class>

</hibernate-mapping>

hibernate.cfg.xml

<hibernate-configuration>

<session-factory>

<!-- Related to the connection properties -->

<property name="myeclipse.connection.profile">myJdbcDriver</property>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
<property name="connection.username">lms</property>
<property name="connection.password">scott</property>


<!-- Related to the hibernate properties -->
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
<property name="show_sql">true</property>
<property name="hbm2ddl.auto">update</property>

<!-- Related to hibernate mapping -->
<mapping resource="com/hiber/Employee.hbm.xml" />

</session-factory>

</hibernate-configuration>

Client Application 1 :

package com.client;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.beans.Employee;

public class ClientAppOne {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();

String hqlQuery="from Employee";

Query query=session.createQuery(hqlQuery);
List list=query.list();
Iterator iterator=list.iterator();

while(iterator.hasNext()){
Employee e=(Employee)iterator.next();
System.out.println(e.getEmployeeId());
System.out.println(e.getEmployeeName());
System.out.println(e.getSalary());
System.out.println("----------");
}

}
}

Example on selecting partial object with single column :

While retrieving the object with single column, hibernate internally creates an object of that particular value column value type and stores all these objects into List.
So while iterating the list, we need to type cast with that particular column value type only.

Note :Employee class, mapping file and configuration files are same as above application.

Client Application 2 :

package com.client;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ClientApp {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();

String hqlQuery="select e.salary from Employee e";

Query query=session.createQuery(hqlQuery);
List list=query.list();
Iterator iterator=list.iterator();

while(iterator.hasNext()){
Double sal=(Double)iterator.next();
System.out.println(sal);
System.out.println("success");
}

}
}

Example on selecting partial object with more than one column :

While retrieving the object with more than one column, hibernate internally stores these multiple column values into an object array and stores that object array into List.
So while iterating the object, we need to type cast with object array.

Ex : Employee class, mapping file and configuration files are same as above application.

Client Application 3 :

package com.client;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ClientApp {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();

String hqlQuery="select e.employeeName,e.salary from Employee e";

Query query=session.createQuery(hqlQuery);
List list=query.list();
Iterator iterator=list.iterator();

while(iterator.hasNext()){
Object[] o=(Object[])iterator.next();
System.out.println(o[0]);
System.out.println(o[1]);
System.out.println("success");
}

}
}

Passing runtime values :

If we want to pass runtime values to the query then we have to use either '?'(positional parameters) or label (named parameters) in HQL command.
To set the run time values to the query we need to call setParameter() method on Query object.

Note :

index number of positional parameter will start from zero.
But in JDBC index number for positional parameter will start from 1.
Any name prefixed with : symbol is said to be named parameter.

Positional parameter example :

Ex : Employee class, mapping file and configuration files are same as above application.

package com.client;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ClientApp {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();

String hqlQuery="select e.employeeName,e.salary" +
          "from Employee e where e.employeeId=?";

Query query=session.createQuery(hqlQuery);
query.setParameter(0, 10L);

List list=query.list();
Iterator iterator=list.iterator();

while(iterator.hasNext()){
Object[] o=(Object[])iterator.next();
System.out.println(o[0]);
System.out.println(o[1]);
System.out.println("success");
}

}
}

Named parameter example :

Ex : Employee class, mapping file and configuration files are same as above application.

package com.client;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ClientApp {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();

String hqlQuery="select e.employeeName,e.salary" +
          " from Employee e where e.employeeId=:id";

Query query=session.createQuery(hqlQuery);
query.setParameter("id", 10L);

List list=query.list();
Iterator iterator=list.iterator();

while(iterator.hasNext()){
Object[] values=(Object[])iterator.next();
System.out.println(values[0]);
System.out.println(values[1]);
System.out.println("success");
}

}
}

package com.client;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ClientApp {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();

String hqlQuery="select e.employeeName,e.salary" +
          " from Employee e where e.salary=:id";

Query query=session.createQuery(hqlQuery);
query.setParameter("id", new Double(4000));

List list=query.list();
Iterator iterator=list.iterator();

while(iterator.hasNext()){
Object[] values=(Object[])iterator.next();
System.out.println(values[0]);
System.out.println(values[1]);
System.out.println("success");
}

}
}

Example for deleting the object using HQL :

Ex : Employee class, mapping file and configuration files are same as above application.

package com.client;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class ClientAppDelete {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();
Transaction tx=session.beginTransaction();

String hqlQuery="delete from Employee e where e.salary=:sal";

Query query=session.createQuery(hqlQuery);
query.setParameter("sal",5000D);

int i=query.executeUpdate();
tx.commit();

System.out.println("delete records :"+ i);
}
}

Example for updating the object using HQL :

Ex : Employee class, mapping file and configuration files are same as above application.

package com.client;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class ClientAppUpdate {

public static void main(String ar[]){

Configuration cfg=new Configuration();
cfg.configure();

SessionFactory sf=cfg.buildSessionFactory();
Session session=sf.openSession();
Transaction tx=session.beginTransaction();

String hqlQuery="update Employee e set e.employeeName=? where e.employeeId=?";

Query query=session.createQuery(hqlQuery);
query.setParameter(0, "arun");
query.setParameter(1, 1l);

int i=query.executeUpdate();
tx.commit();

System.out.println("update records :"+ i);
}
}