In this article, we will see What is HQL in Hibernate in detail along with a simple example. Hibernate framework has provided us with the query language i.e HQL (Hibernate Query language).
What is Hibernate Query Language(HQL)?
As we know, we use SQL query to perform database operation and which works on tables and columns. So, the Hibernate ORM framework provided us with its own query language called Hibernate Query Language.
- In this, it works on the persistent object instead of tables and columns.
- It is fully object-oriented.
- It’s similar to the SQL language.
- HQL follows the case sensitivity for Java classes and their properties.
- Hibernate translate the HQL queries to our SQL queries, which perform the database operation.
Why do we need HQL?
When we have SQL why do we need the other language HQL? Let’s see some of the reason below
- It directly works on the Java classes and not on the tables and columns.
- It is database-independent means we can have any database and don’t need to worry about the syntax for every database.
- When we fetch data from the table the result we don’t need to store in the resultset object instead we can store in the list object and we already know it is good and easy to work with List in Java.
- It is easy to learn if you know SQL you can easily work with HQL.
What are the Methods used in HQL?
In order to execute HQL, we have a Query Interface, and the object is called by calling the createQuery() method with the session interface.
- int executeUpdate(): This method executes the update and deletes query.
- public List list(): This method returns the fetched result as a List object.
- public Query setFirstResult(int row_number): This method specifies the row number from which where we need to retrieve the data.
- public Query setMaxResult(int row_number ): This method specifies the number of records we want to retrieve.
- public Query setParameter(int pos, Object value): This method sets the value to the object.
- public Query setParameter(String name, Object value): This method sets the value to a named query parameter.
Syntax in HQL
FROM Clause
This clause gets the data from the persistent object. Following is the syntax
String str = "FROM Employee"; Query query = session.createQuery(str); List<> results = query.list();
SELECT Clause
This clause is just like the SELECT clause we use in SQL to select particular data only difference here is it works on the persistent object instead of tables and columns.
String str= "SELECT name FROM Employee"; Query query = session.createQuery(str); List results = query.list();
AS Clause
This AS clause is used as an alias for the Object. It is an optional clause. (even if you don’t specify it will work)
String str= "FROM Employee AS e"; Query query = session.createQuery(str); List results = query.list();
WHERE Clause
If we want to retrieve data on the basis of certain conditions we use the WHERE clause in HQL.
String str= "FROM Employee e WHERE e.salary = 10000"; Query query = session.createQuery(str); List results = query.list();
ORDER BY Clause
If we want our data to come in some order like in ascending or descending we use this clause.
String str= "FROM Employee e WHERE e.salary > 1000 ORDER BY e.id DESC"; Query query = session.createQuery(str); List results = query.list();
GROUP BY Clause
This clause fetches the data from the object and group it on the basis of the attribute value. (It works with aggregate function)
String str= "SELECT COUNT(e.salary), e.name FROM Employee e" +
"GROUP BY e.name";
Query query = session.createQuery(str);
List results = query.list();
Named Parameter
Suppose there is a condition when we want to fetch the data on the basis of parameter provided by a user we used named parameter
String str= "FROM Employee e WHERE e.id = :employee_id";
Query query = session.createQuery(str);
query.setParameter("employee_id",45);
List results = query.list();
INSERT Clause
This clause works when we need to insert data from one object to another.
String str= "INSERT INTO Employee(name, salary)" +
"SELECT name, salary FROM previous_employee";
UPDATE & DELETE Clause
The update and delete clause is used to edit and delete certain property from the object. The Query Interface provides us with a method executeUpdate() for executing these statements.
<-------UPDATE---------->
String str= "UPDATE Employee set name= :name" +
"WHERE id = :employee_id";
Query query = session.createQuery(str);
query.setParameter("name", "abc");
query.setParameter("employee_id", 10);
int status= query.executeUpdate();
System.out.println("Rows affected: " + status);
<-------DELETE---------->
String str= "DELETE FROM Employee " +
"WHERE id = :employee_id";
Query query = session.createQuery(str);
query.setParameter("employee_id", 10);
int status= query.executeUpdate();
System.out.println("Rows affected: " + status);
Aggregate Methods in HQL
HQL also supports aggregate methods. following are some aggregate methods
- sum(property_name): This method sums the property value.
- count(property_name): This method counts the property value.
- avg(property_name): This method takes the average property value.
- max(property_name): This method returns the maximum from the property value.
- min(property_name): This method returns the minimum from the property value.
Syntax:
<----SUM------->
Query query=session.createQuery("select sum(salary) from Employee");
<----COUNT------->
Query query=session.createQuery("select count(id) from Employee");
<----MIN------->
Query query=session.createQuery("select min(id) from Employee");
<----MAX------->
Query query=session.createQuery("select max(id) from Employee");
<----AVG------->
Query query=session.createQuery("select avg(salary) from Employee");
Pagination in HQL
If we want to display some data on one page and remaining on the next page, we use Pagination in a web application So, Hibernate provides a method for pagination.
- Query setFisrtResult(int start_pos):This method starts the pagination from the specified start_pos location.
- Query setMaxResult(int max_result): This method retrieves a fixed number of results according to the max_result specified.
Query query=session.createQuery("from Employee");
query.setFirstResult(8);
query.setMaxResult(16);
List<> list=query.list();
Example of HQL in Hibernate
In this example, we will create a table using the HQL query and will perform the operation.
Let us first create a configuration file hibernate.cfg.xml file
When we want to create a table Just write create like this- <property name=”hbm2ddl.auto”>create</property> and then replace it with update(otherwise it will give error).
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 5.3//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-5.3.dtd">
<hibernate-configuration>
<session-factory>
<property name="hbm2ddl.auto">create</property>
<property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>
<property name="connection.url">jdbc:mysql://localhost:3306/device</property>
<property name="connection.username">root</property>
<property name="connection.password">root</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.show_sql">true</property>
<mapping class="Employee"/>
</session-factory>
</hibernate-configuration>
Now Create java Class Employee for setting and getting data. We are creating annotated-based classes so we won’t need a mapping file now. (for annotation you can see the previous article How to use Annotation in Hibernate?)
Employee.java
In this class Just write the @Entity and @Table followed by table name.
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="employees")
public class Employee {
@Id
private int empid;
private String empname;
private int salary;
public int getEmpid() {
return empid;
}
public void setEmpid(int empid) {
this.empid = empid;
}
public String getEmpname() {
return empname;
}
public void setEmpname(String empname) {
this.empname = empname;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
}
Now, create the main class Test.java
In this class, we have used the Query Interface method createQuery() method to execute the query. The query returns the Object in List form so we have to use List Object from java. util package and we have displayed the data using for loop. (Let’s consider we have some data in our created table)
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class Test {
public static void main(String[] args) {
SessionFactory factory = new Configuration().configure().buildSessionFactory();
Session session = factory.openSession();
session.beginTransaction();
/* Let's see the HQL query*/
Query q=session.createQuery("from Employee");
List<Employee> emList=q.list();
for(Employee e:emList)
{
System.out.println(e.getEmpid()+e.getEmpname()+e.getSalary());
}
session.getTransaction().commit();
// System.out.println("table craeted");
}
}
OUTPUT
1 Nicolas 2000 2 Ross 3000 3 abc 4000 4 pqr 5000
Now, we will perform some query operation we just need to Modify our Test.java let’s see
When we want to fetch records whose salary>2000
Query q=session.createQuery("from Employee where salary>2000");
List<Employee> emList=q.list();
for(Employee e:emList)
{
System.out.println(e.getEmpid()+" "+e.getEmpname()+" "+e.getSalary());
}
Output
2 Ross 3000 3 abc 4000 4 pqr 5000
When we want to fetch a particular record with id=1
Query q=session.createQuery("from Employee where id=1");
Employee employee=(Employee)q.uniqueResult();
System.out.println(employee.getEmpid()+" "+employee.getEmpname()+" "+employee.getSalary());
Output
1 Nicolas 2000
Note: in FROM clause it is not the name of the Table it is the name of the Persistent class.
Advantages of Hibernate HQL
- It is easy to understand
- It is database independent.
- It works on Classes instead of directly communicating with the database tables and columns.
Thus this was all about HQL in Hibernate.
In the next article of this tutorial, we will understand the HCQL – Hibernate Criteria Query Language to fetch the records on the basis of certain conditions.
