Using HQL in Hibernate

HQL stands for Hibernate Query Language that works on persistent objects and their properties. It is similar to SQL but we use the class and Properties in syntax of query instead of table and columns names. It is case sensitive while we write the name of java classes and properties. You have to user Class name to be mapped to table instead of database table name.
SQL Table

package domain;
import javax.persistence.*;
@Entity
public class Employee{
    private int empId;
    private String name;
    private String email;
    private String designation;
    private long salary;
    private String department;
    @Id @GeneratedValue
    public int getEmpId() {
        return empId;
    }
    public void setEmpId(int empId) {
        this.empId = empId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getDesignation() {
        return designation;
    }
    public void setDesignation(String designation) {
        this.designation = designation;
    }
    public long getSalary() {
        return salary;
    }
    public void setSalary(long salary) {
        this.salary = salary;
    }
    public String getDepartment() {
        return department;
    }
    public void setDepartment(String department) {
        this.department = department;
    }
    @Override
    public String toString() {
        return "Employee [empId=" + empId + ", name=" + name + ", email="
                + email + ", designation=" + designation + ", salary=" + salary
                + ", department=" + department + "]";
    }
    
}


Simple select Query
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.AnnotationConfiguration;
import domain.*;
public class SelectHQL {
    public static void main(String[] args) {
        System.out.println("############## Read ##############");
        Session session = new AnnotationConfiguration().configure()
                .buildSessionFactory().openSession();
        session.beginTransaction();
        Query query = session.createQuery("from Employee where department = 'SEO'");
        List list = query.list();
        for(Object obj:list){
            System.out.println((Employee)obj);
        }
        session.getTransaction().commit();
    }
}


Retrieving Specific Column(s)
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.AnnotationConfiguration;
import domain.*;
public class SelectHQL2 {
    public static void main(String[] args) {
        System.out.println("############## Read ##############");
        Session session = new AnnotationConfiguration().configure()
                .buildSessionFactory().openSession();
        session.beginTransaction();
        Query query = session.createQuery("select e.name from Employee e where department = 'SEO'");
        //The result set for this query will contain a List of Java String objects
        List list = query.list();
        for(Object obj:list){
            System.out.println((String)obj);
        }
        session.getTransaction().commit();
    }
}


Parametrized HQL
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.AnnotationConfiguration;
import domain.*;
public class SelectHQLParams {
    public static void main(String[] args) {
        System.out.println("############## Read ##############");
        Session session = new AnnotationConfiguration().configure()
                .buildSessionFactory().openSession();
        session.beginTransaction();
        Query query = session.createQuery("from Employee where department = :x");
        query.setString("x""SEO");
        List list = query.list();
        for(Object obj:list){
            System.out.println((Employeeobj);
        }
        session.getTransaction().commit();
    }
}

Getting data from selected columns using HQL
 Sometimes it is required that you need to fetch the data from the table using Hobernate from the selected columns you use select query by specifying the properties to which data need to be fetch. If you specify the name of field instead of Java class, hibernate returns the single valued list * if you specify more then one fields in select query , hibernate returns List of array of object to be retrieve using Iterator.

import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.AnnotationConfiguration;
public class SelectHQL3 {
    public static void main(String[] args) {
        System.out.println("############## Read ##############");
        Session session = new AnnotationConfiguration().configure()
                .buildSessionFactory().openSession();
        session.beginTransaction();
        Query query = session.createQuery("select e.name,e.email,e.department from Employee e where department = 'SEO'");
        //This result set contains a List of Object arrays—each array represents one set of properties
        Iterator it=query.iterate();
        while (it.hasNext()) {
            Object[] val = (Object[]) it.next();
            System.out.println(val[0]+"\t"+val[1]);
        }
        session.getTransaction().commit();
    }
}


No comments:

Popular Posts