Java tutions: hb : Hibernate Query Language (HQL)

Hibernate Query Language (HQL)

Hibernate Query Language (HQL) is same as SQL (Structured Query Language) but it doesn't depends on the table of the database. Instead of table name, we use class name in HQL. So it is database independent query language.

Advantage of HQL

There are many advantages of HQL. They are as follows:

database independent

supports polymorphic queries

easy to learn for Java Programmer

Query Interface

It is an object oriented representation of Hibernate Query. The object of Query can be obtained by calling the createQuery() method of Session interface.

The query interface provides many methods. There is given commonly used methods:

public int executeUpdate() is used to execute the update or delete query.

public List list() returns the result of the relation as a list.

public Query setFirstResult(int rowno) specifies the row number from where record will be retrieved.

public Query setMaxResult(int rowno) specifies the no. of records to be retrieved from the relation (table).

public Query setParameter(int position, Object value) it sets the value to the JDBC style query parameter.

public Query setParameter(String name, Object value) it sets the value to a named query parameter.

hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="connection.driver_class">org.apache.derby.jdbc.ClientDriver</property>

<property name="dialect">org.hibernate.dialect.DerbyDialect</property>

<property name="connection.url">jdbc:derby://localhost:1527/myhb</property>

<property name="hbm2ddl.auto">update</property>

</session-factory>

</hibernate-configuration>

employee.hbm.xml

package mypack;

import javax.persistence.*;

@Entity

@Table(name = "MYEMPLOYEE")

public class Employee {

@Id

private int id;

private String name;

private float salary;

private String address;

public Employee() {

super();

}

public Employee(String name, float salary,String address) {

super();

this.name = name;

this.salary = salary;

this.address = address;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public float getSalary() {

return salary;

}

public void setSalary(float salary) {

this.salary = salary;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

Below Example shows how to insert the data.

SaveData.java

package mypack;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.Transaction;

import org.hibernate.cfg.AnnotationConfiguration;

public class SaveData {

public static void main(String[] args) {

AnnotationConfiguration cfg=new AnnotationConfiguration();

cfg.configure("hibernate.cfg.xml");//populates the data of the configuration file

SessionFactory factory=cfg.buildSessionFactory();

Session session=factory.openSession();

Transaction tx = session.beginTransaction();

Employee emp = new Employee();

emp.setId(111);

emp.setName("Mohan");

emp.setSalary(2100f);

emp.setAddress("BLR");

session.persist(emp);

tx.commit();

session.close();

System.out.println("successfully saved");

}

Example of HQL to get all the records

Query query=session.createQuery("from Employee");//here persistent class name is Employee

List list=query.list();

SelectRecords.java

package mypack.methods;

import mypack.*;

import java.util.*;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class SelectRecords {

public static void main(String[] args) {

SessionFactory sessionFactory = new AnnotationConfiguration().configure("hibernate.cfg.xml").buildSessionFactory();

Session session = sessionFactory.openSession();

Query query = session.createQuery("from mypack.Employee");

List list = query.list();

Iterator<Employee> itr = list.iterator();

while(itr.hasNext()){

Employee emp = itr.next();

System.out.println(emp.getId()+" "+emp.getName()+" "+emp.getSalary()+" "+emp.getAddress());

}

session.close();

}

Example of HQL to get records with pagination

Query query=session.createQuery("from mypack.Employee ");

query.setFirstResult(5);

query.setMaxResult(10);

List list=query.list();//will return the records from 5 to 10th number .

PaginationEx.java

package mypack.methods;

import mypack.*;

import java.util.*;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class PaginationEx {

public static void main(String[] args) {

SessionFactory sessionFactory = new AnnotationConfiguration().configure("hibernate.cfg.xml").buildSessionFactory();

Session session = sessionFactory.openSession();

Query query = session.createQuery("from mypack.Employee");

query.setFirstResult(2);

query.setMaxResults(4);

List list = query.list();

Iterator<Employee> itr = list.iterator();

while(itr.hasNext()){

Employee emp = itr.next();

System.out.println(emp.getId()+" "+emp.getName()+" "+emp.getSalary()+" "+emp.getAddress());

}

session.close();

}

Example of HQL update query

Transaction tx=session.beginTransaction();

Query q=session.createQuery("update mypack.Employee set name=:n where id=:i");

q.setParameter("n","Mohan Rao");

q.setParameter("i",111);

int status=q.executeUpdate();

System.out.println(status);

tx.commit();

UpdateEmployee.java

package mypack.methods;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class UpdateEmployee {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Transaction tx = session.beginTransaction();

Query q=session.createQuery("update mypack.Employee set name=:n where id=:i");

q.setParameter("n", "Surya");

q.setParameter("i", 112);

int status = q.executeUpdate();

System.out.println(status + " Records updated.");

tx.commit();

session.close();

}

Example of HQL delete query

Query query=session.createQuery("delete from mypack.Employee where id=100");

//specifying class name (Employee) not tablename

query.executeUpdate();

DeleteEmployee.java

package mypack.methods;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class DeleteEmployee {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Transaction tx = session.beginTransaction();

Query q=session.createQuery("delete from mypack.Employee where id=:i");

q.setParameter("i", 111);

int status = q.executeUpdate();

System.out.println(status + " Records deleted.");

tx.commit();

session.close();

}

Example of HQL order by query

OrderBy.java

package mypack.methods;

import java.util.Iterator;

import java.util.List;

import mypack.Employee;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class OrderBy {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Query query = session.createQuery("from mypack.Employee as e order by e.salary asc");

List list = query.list();

Iterator itr = list.iterator();

while(itr.hasNext()){

Employee emp = (Employee)itr.next();

System.out.println(emp.getId()+" "+emp.getName()+" "+emp.getSalary());

}

session.close();

}

HQL with Aggregate functions

You may call avg(), min(), max() etc. aggregate functions by HQL. Let's see some common examples:

Example to get total salary of all the employees

Query q=session.createQuery("select sum(salary) from mypack.Employee");

List<Emp> list=q.list();

Iterator<Emp> itr=list.iterator();

while(itr.hasNext()){

System.out.println(itr.next());

}

SumSalary.java

package mypack.aggregations;

import java.util.Iterator;

import java.util.List;

import org.hibernate.*;

import org.hibernate.cfg.*;

import mypack.Employee;

public class SumSalary {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Query q=session.createQuery("select sum(salary) from mypack.Employee");

List<Employee> list=q.list();

Iterator<Employee> itr=list.iterator();

while(itr.hasNext()){

System.out.println(itr.next());

}

session.close();

}

Example to get maximum salary of employee

Query q=session.createQuery("select max(salary) from mypack.Employee");

MaxSalary.java

package mypack.aggregations;

import java.util.Iterator;

import java.util.List;

import mypack.Employee;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class MaxSalary {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Query q=session.createQuery("select max(salary) from mypack.Employee");

List<Employee> list=q.list();

Iterator<Employee> itr=list.iterator();

while(itr.hasNext()){

System.out.println(itr.next());

}

session.close();

}

Example to get minimum salary of employee

Query q=session.createQuery("select min(salary) from mypack.Employee");

MinSalary.java

package mypack.aggregations;

import java.util.Iterator;

import java.util.List;

import mypack.Employee;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class MinSalary {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Transaction tx = session.beginTransaction();

Query q=session.createQuery("select min(salary) from mypack.Employee");

List<Employee> list=q.list();

Iterator<Employee> itr=list.iterator();

while(itr.hasNext()){

System.out.println(itr.next());

}

session.close();

}

Example to count total number of employee ID

Query q=session.createQuery("select count(id) from mypack.Employee");

CountEmployees.java

package mypack.aggregations;

import java.util.Iterator;

import java.util.List;

import org.hibernate.*;

import org.hibernate.cfg.*;

import mypack.Employee;

public class CountEmployees {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Query q=session.createQuery("select count(id) from mypack.Employee");

List<Employee> list=q.list();

Iterator<Employee> itr=list.iterator();

while(itr.hasNext()){

System.out.println(itr.next());

}

session.close();

}

Example to get average salary of each employees

Query q=session.createQuery("select avg(salary) from mypack.Employee");

AverageSalary.java

package mypack.aggregations;

import java.util.Iterator;

import java.util.List;

import org.hibernate.*;

import org.hibernate.cfg.*;

import mypack.Employee;

public class AverageSalary {

public static void main(String[] args) {

SessionFactory factory =new AnnotationConfiguration().configure().buildSessionFactory();

Session session=factory.openSession();

Query q=session.createQuery("select avg(salary) from mypack.Employee");

List<Employee> list=q.list();

Iterator<Employee> itr=list.iterator();

while(itr.hasNext()){

System.out.println(itr.next());

}

session.close();

}

Java tutions

Followers

Amazon Web Services (AWS)

CoreJava

Spring IOC

spring MVC

Spring DAO

springAOP

Servlets

JSP

JBDC

hibernate

Interview Questions

Popular Posts

hb : Hibernate Query Language (HQL)

No comments:

Post a Comment

java materials