Skip to main content

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();
    }
}


Comments

Popular posts from this blog

Using HyperSQL (HSQLDB)

HSQLDB is a portable RDBMS implemented in pure java. It can be embedded with your application as well as can be used separately. It is very a small database that supports almost all features of the standard database system. It comes with small jar file that can be found in lib folder. The HSQLDB jar package is located in the /lib directory of the ZIP package and contains several components and programs. Core components of jar file are : HyperSQL RDBMS Engine (HSQLDB), HyperSQL JDBC Driver, Database Manager, and Sql Tool. Installing and Using Download: download latest release of HyperSQL database from http://hsqldb.org website and extract it. You will see following contents. Here "bin" directory contains some batch files those can be used to run a swing based GUI tool. You can use runManagerSwing.bat to connect to database, but database must be on before running it. Directory lib contains File hsqldb.jar . It is the database to be used by you. Running database First

How to handle values from dynamically generated elements in web page using struts2

Some time you will see the form containing the button " Add More " . This facility is provided for the user to get the values for unknown number of repeating for some information. for example when you are asking to get the projects details from user, you need to put the option to add the more project for the user since you don't known how many projects user have. In the HTML form, you repeat the particular section to get the multiple values for those elements. In Html page , you can put the option to add new row of elements or text fields by writing the java script or using JQuery API. Now, the question is that how to capture the values of dynamically generated text fields on the server. Using the servlet programming you can get the values by using getParameters() method that resultants the array of the parameter having the same name. But this limit you to naming the text fields in the HTML form. To ally this approach, you have to take the same name for t

In Process Mode of HSQLDB in web application.

If you want to use the database into your web application, you can use the HSQLDB in In_Process mode. In this mode, you can embed the HSQLDB into your web application and it runs as a part of your web application programm in the same JVM. In this mode, the database does not open any port to connect to the application on the hosing machine and you don't need to configure anything to access it. Database is not expposed to other application and can not be accessed from any dabase tools like dbVisualizer etc. In this mode ,database will be unknown from any other person except you. But in the 1.8.0 version, you can use Server intance for external as well as in process access.  To close the databse, you can issue SHUTDOWN command as an SQL query.   In the in-process mode, database starts from JDBC with the associated databse file provided through  connection URL. for example   DriverManager.getConnection("jdbc:hsqldb:mydatabase","SA","");   Here myd