Hibernate Query Language (HQL)Agenda :
Hibernate Query Language (HQL) :
HQL Command to retrieve complete object :
HQL Command to retrieve partial object :
Executing HQL Command :
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 :
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 :
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 :
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 :
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); } } |