In this Hibernate Query Language example, you learn CRUD operations using HQL, Hibernate CRUD example using session.createQuery(hql).
1.Overview of HQL (Hibernate Query Language)
HQL stands for Hibernate Query language, is a query language in the Hibernate. If your entity relationship is well defined then you can use HQL else you have to use your native SQL query language.
You can write your HQL very easily. In HQL, you have to use POJO class name ( NOT table name). You are able to use HQL if your entity is a well-defined and the relationship between entities should be good enough to use HQL (Hibernate Query Language).
2. What you learn In Hibernate query language Example
In this article, you learn following things, in HQL CRUD example.
- get HQL example
- update HQL example
- delete HQL example
- insert/create HQL example
- Get group by HQL example
- Get order by using HQL exampl
3. mapping (.hbm.xml) files and POJO for HQL
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.
4. Hibernate Configuration file for HQL CRUD operations
com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/hibernate_example root root 2 org.hibernate.dialect.MySQLDialect true
5. Run Many to one relationship
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.mysfotkey.util.HibernateUtil;
public class HQLCRUDExampleClient {
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;
}
}
6. Update HQL example
/**
* 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;
}
7. Delete HQL example
/**
* 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;
}
8. Insert HQL example
In this section you learn how to add records in table using HQL example. Fully qualified POJO name no need to mentioned if you provide auto import in .hbm.xml file.
/**
* This method is used to 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;
}
9. Get HQL example
In this section of HQL CRUD example , you will learn how to get record in HQL Hibernate Java.
/**
* This method is used to get Department object by department 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;
}
10. Get Group by in HQL (Hibernate Query Language) example
In this section of HQL CRUD example , you learn how to get record using group by in HQL Hibernate Java.
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();
}
}
11. Using Order by in HQL (Hibernate Query Language)
In this section of Hibernate HQL example Java you learn how to sort using order by
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
}
12. Output of HQL example
Eclipse console output:
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
13. Reference
Hibernate site
Thanks for visiting this post for learning Hibernate HQL CRUD examples. You can also visit Java Hibernate ORM Tutorial Listing page for more articles on Hibernate ORM framework.
Your comments are welcome to improve this Hibernate Query Language example in Hibernate. Happy Learning 🙂
cool post
I usually visit this blog , like this post
I like the hibernate and post too. good information in this post
I like , its good informative post
good post dear..