Hibernate Query Language (HQL) with CRUD example

hibernate_logo
Hibernate Query Language (HQL) is a query language in the Hibernate. If your relationship is well defined then you can use HQL else you have to you native query language which I mention in another post.

1.Overview of HQL (Hibernate Query Language)

You can write your own query in HQL very easily. Keep in your mind , when I say HQL, you have to use POJO class name ( NOT table name). You are able to use HQL if you entity is well defined meaning that relationship between entities should be good enought to use HQL (Hibernat Query Language).

2. mapping (.hbm.xml) files and POJO (domain model) classes

For mapping and POJO (Plain Object Java Object) or domain model class refer another post: one-to-many relationship example and many-to-one relationship. example of another post. This example tested in the basis of previous post of one-to-many and many-to-one examples.

3. Hibernate Configuration

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?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="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
   <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/hibernate_example</property>
   <property name="hibernate.connection.username">root</property>
   <property name="connection.password">root</property>
   <property name="connection.pool_size">2</property>
   <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
   <property name="show_sql">true</property>
   <!-- <property name="hbm2ddl.auto">create</property> -->
 
    <!-- One2Many set example and for HQL and SQL and Criteria Query as well -->
    <mapping resource="com/mysoftkey/relationship/one2many/Employee.hbm.xml"/>
    <mapping resource="com/mysoftkey/relationship/one2many/Department.hbm.xml"/>
 
    <!-- ManyToOne relationship , and increment Generator class as well -->
    <mapping resource="com/mysoftkey/relationship/many2one/Student.hbm.xml"/>
    <mapping resource="com/mysoftkey/relationship/many2one/Address.hbm.xml"/>
 
  </session-factory>
</hibernate-configuration>

4. Run Many to one relationship

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
package com.mysoftkey.relationship.one2many;
 
import java.util.Iterator;
import java.util.List;
 
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
 
import com.delhiguru.util.HibernateUtil;
 
public class HQLEmployeeClient {
 
  public static void main(String[] args) {
    Employee emp = getEmployeeByFirstName("Ranjeet");
    if (emp != null) {
      System.out.println(emp.getFirstname());
    }
 
    // Get employee by department name;
    Employee employee = getEmployeeByDepartmentName("Sales");
    System.out.println("employee by department name: Sales ");
    System.out.println("id: " + employee.getEmployeeId() + " , firstname: " + employee.getFirstname());
 
    // update addres by using HQL for addressId = 1
    updateAddressByHQLQuery(1);
 
    // update addres by using HQL for addressId = 1
    //deleteAddressByHQLQuery(1); 
 
     // update addres by using HQL for addressId = 1
    insertByHQLQuery();
 
    // 
    Department dept =  getDepartmentByIdHQLQuery(1);
    System.out.println("id: "+ dept.getDepartmentId() + " , department name: " + dept.getDepartmentName());
 
  }
 
  /**
   * This method is used get Employee  by firstname by using HQL (Hibernate Query Language).
   * 
   * @param firstName
   * @return
   */
  public static Employee getEmployeeByFirstName(String firstName) {
    Employee emp = null;
 
    try {
      SessionFactory sf = HibernateUtil.getSessionFactory();
      Session session = sf.openSession();
      session.beginTransaction();
      Query query = session.createQuery("from com.mysoftkey.relationship.one2many.Employee as e where e=:fName");
      query.setString("fName", firstName);
      List list = query.list();
      if (list != null && list.size() > 0) {
        emp = (Employee) list.get(0);
      }
 
      session.getTransaction().commit();
      session.close();
    } catch (Exception e2) {
      e2.printStackTrace();
    }
 
    return emp;
 
  }
 
  /**
   * This method is used get Employee  Address object by using HQL (Hibernate Query Language).
   * 
   * @param firstName
   * @return
   */
  public static Employee getEmployeeByDepartmentName(String departmentName) {
    Employee emp = null;
 
    try {
      SessionFactory sf = HibernateUtil.getSessionFactory();
      Session session = sf.openSession();
      session.beginTransaction();
      Query query = session.createQuery("from com.mysoftkey.relationship.one2many.Employee as e where e.department.departmentName=:dpttName");
      query.setString("dpttName", departmentName);
      List list = query.list();
      if (list != null && list.size() > 0) {
        emp = (Employee) list.get(0);
      }
 
      session.getTransaction().commit();
      session.close();
    } catch (Exception e2) {
      // TODO: handle exception
    }
 
    return emp;
 
  }
 
}

5. Update HQL example method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  /**
   * This method is used insert Address object by using HQL (Hibernate Query Language).
   * 
   * @param addressId
   * @return
   */
  public static int updateAddressByHQLQuery(long addressId) {
    int result = -1;
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
      session.beginTransaction();
      Query query = session.createQuery("update com.mysoftkey.relationship.many2one.Address set street=:streetName where addressId=:addressId");
      query.setParameter("streetName", "R K puram, New Delhi");
      query.setParameter("addressId", addressId);
      result = query.executeUpdate();
      session.getTransaction().commit();
    } catch (HibernateException e) {
      e.printStackTrace();
      return result;
    } finally {
      session.close();
    }
    return result;
  }

6. Delete HQL example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  /**
   * This method is used get Address object by addressId, 
   * using HQL (Hibernate Query Language).
   * 
   * @param addressId
   * @return
   */
  public static int deleteAddressByHQLQuery(long addressId) {
    int result = -1;
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
      session.beginTransaction();
      Query query = session.createQuery("Delete from com.mysoftkey.relationship.many2one.Address where addressId=:addressID");
      query.setParameter("addressID", addressId);
      result = query.executeUpdate();
      session.getTransaction().commit();
    } catch (HibernateException e) {
      e.printStackTrace();
      return result;
    } finally {
      session.close();
    }
    return result;
  }

7. Insert HQL method example

fully qualified POJO name no need to mentioned if you provide auto import in .hbm.xml file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 /**
   * This method is used insert Address object by using HQL (Hibernate Query Language).
   * 
   * @param departmentId 
   */
  public static int insertByHQLQuery() {
    int result = -1;
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
      session.beginTransaction();
      Query query = session.createQuery(
          "insert into com.mysoftkey.relationship.many2one.Address(street, city, state, zipcode) values ('Darka city','New Delhi','Delhi',110059)");
      result = query.executeUpdate();
      session.getTransaction().commit();
    } catch (HibernateException e) {
      e.printStackTrace();
      return result;
    } finally {
      session.close();
    }
    return result;
  }

8. Get HQL example method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 /**
   * This method is used to get Department object by deppartment id 
   * by using HQL (Hibernate Query Language).
   * 
   * @param departmentId
   */
  public static Department getDepartmentByIdHQLQuery(long departmentId) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Department dept = null;
    try {
      session.beginTransaction();
      Query query = session.createQuery("from com.mysoftkey.relationship.one2many.Department where departmentId=:id");
      query.setParameter("id", departmentId);
 
      for (Iterator it = query.iterate(); it.hasNext();) {
 
        dept = (Department) it.next();
        System.out.println("Name: " + dept.getDepartmentName());
      }
 
    } catch (HibernateException e) {
      e.printStackTrace();
    } finally {
      session.close();
    }
    return dept;
 
  }

8. Get Group by in HQL (Hibernate Query Language) method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
   public static void getInfoGroupBy() {
    Employee emp = null;
 
    try {
      SessionFactory sf = HibernateUtil.getSessionFactory();
      Session session = sf.openSession();
     String hql = "SELECT MAX(e.salary), e.firstName FROM com.mysoftkey.misc.Employee e GROUP BY e.firstName";
      // avg return double, sum return long, MIN and MAX return integer
      Query query = session.createQuery(hql);
      List list = query.list();
 
      if (list != null && list.size() > 0) {
        Object[] objArray = (Object[]) list.get(0);
        // Long salSum = (Long)objArray[0];
        // Double salSum = (Double)objArray[0];
        Integer salSum = (Integer) objArray[0];
        String firstName = (String) objArray[1];
        System.out.println("firstName " + firstName + " , sal : " + salSum);
 
      }
      session.close();
    } catch (Exception e2) {
      e2.printStackTrace();
    }
 
  }

8. Using Order by in HQL (Hibernate Query Language)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  public static Employee getEmployee() {
    Employee emp = null;
    String firstName = "Anila";
 
    try {
      SessionFactory sf = HibernateUtil.getSessionFactory();
      Session session = sf.openSession();
      session.beginTransaction();
 
      String hql = "FROM com.mysoftkey.misc.Employee e WHERE e.id > 1  ORDER BY e.firstName DESC, e.salary DESC ";
 
      Query query = session.createQuery(hql);
      // query.setString("fName", firstName);
      // query.setFirstResult(1);
      // query.setMaxResults(2);
 
      List list = query.list();
 
      if (list != null && list.size() > 0) {
        emp = (Employee) list.get(0);
        System.out.println("reuturned Size : " + list.size());
        System.out.println(
            "firstName " + emp.getFirstName() + ", lastName " + emp.getLastName() + " , sal : " + emp.getSalary());
 
        System.out.println("----------------------------------------------------------");
        for (int i = 0; i < list.size(); i++) {
          Employee e = (Employee) list.get(i);
          System.out.println("id: " + e.getId() + " , firstName : " + e.getFirstName() + ", sal : " + e.getSalary());
 
        }
        System.out.println("----------------------------------------------------------");
      }
 
      session.getTransaction().commit();
      session.close();
    } catch (Exception e2) {
      // TODO: handle exception
    }

9. Output of many to one hibernate relationship

Eclipse console output:

1
2
3
4
5
6
7
8
9
10
Hibernate: select employee0_.EMPLOYEE_ID as EMPLOYEE1_0_, employee0_.firstname as firstname0_, employee0_.lastname as lastname0_, employee0_.birth_date as birth4_0_, employee0_.cell_phone as cell5_0_, employee0_.department_id as department6_0_ from EMPLOYEE_one2many employee0_ where employee0_.EMPLOYEE_ID=?
Hibernate: select employee0_.EMPLOYEE_ID as EMPLOYEE1_0_, employee0_.firstname as firstname0_, employee0_.lastname as lastname0_, employee0_.birth_date as birth4_0_, employee0_.cell_phone as cell5_0_, employee0_.department_id as department6_0_ from EMPLOYEE_one2many employee0_, DEPARTMENT_one2many department1_ where employee0_.department_id=department1_.DEPARTMENT_ID and department1_.DEPT_NAME=?
employee by department name: Sales 
id: 1 , firstname: Ranjeet
Hibernate: update address_many2one set ADDRESS_STREET=? where ADDRESS_ID=?
 
Hibernate: select department0_.DEPARTMENT_ID as col_0_0_ from DEPARTMENT_one2many department0_ where department0_.DEPARTMENT_ID=?
Hibernate: select department0_.DEPARTMENT_ID as DEPARTMENT1_1_0_, department0_.DEPT_NAME as DEPT2_1_0_ from DEPARTMENT_one2many department0_ where department0_.DEPARTMENT_ID=?
Name: Sales
id: 1 , department name: Sales

Your comments are welcome to improve this Hibernate Query Language example in Hibernate. Happy Learning :)

Please follow and like us:
RSS
Follow by Email
Facebook
LinkedIn
Google+

Leave a Reply

Your email address will not be published. Required fields are marked *