Friday, 15 June 2018

Hibernate Query Language(HQL) Examples

             HQL is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. It is a superset of the JPQL, the Java Persistence Query Language; a JPQL query is a valid HQL  query, but not all HQL queries are valid JPQL queries.

             HQL is a language with its own syntax and grammar. It is written as strings and it will convert into conventional SQL queries; Hibernate also provides an API that allows you to directly issue SQL queries as well.

             Keywords like SELECT, FROM, and WHERE, etc., are not case sensitive, but properties like table and column names are case sensitive in HQL.

Advantages:--

  • Instead of returning plain data, HQL queries return the query result(s) in the form of object(s)/tuples of object(s) that are ready to be accessed, operated upon, and manipulated programmatically. This approach does away with the routine task of creating and populating objects from scratch with the "result set" retrieved from database queried.
  • HQL contains many advance features such as pagination, fetch join with dynamic profiling, and so forth, as compared to SQL.

Examples:

I have a given one example student entity, contains fields id, name and age as below,

Student.java,

package com.adnblog;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "student")
public class Student{
    
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = "student_id")
      private Integer id;
     
      @Column(name = "student_name")
      private String name;
     
      @Column(name="student_age")
      private Integer age;
     
      public Integer getId() {
           return id;
      }
 
      public void setId(Integer id) {
           this.id = id;
      }
 
      public String getName() {
           return name;
      }
 
      public void setName(String name) {
           this.name = name;
      }
 
      public Integer getAge() {
           return age;
      }
 
      public void setAge(Integer age) {
            this.age = age;
      }   
}



  • HQL SELECT Query Example:--
The below query is to select the student where id is 121.


Query query = session.createQuery("from Student where id= :id");
query.setParameter("id", "121");
List list = query.list();

Equivalent sql query is,


Select * from student where student_id = 121;

The above query is same as SQL Query but in this we can use entity name as table name and field name as column name.

  • HQL UPDATE Query Example:-
The below query is to update the student name where id is 121.

Query query = session.createQuery("update Student set name= :name" +
        " where id= :id");
query.setParameter("name", "Mahesh");
query.setParameter("id", 121);
int result = query.executeUpdate();

Equivalent sql query is,

UPDATE student SET student_name = 'Mahesh'
          WHERE student_id= 121;


  • HQL DELETE Query Example:-

The below HQL query is to delete the Student from student table where id is 121.

Query query = session.createQuery("delete Student where id= :id");
query.setParameter("id", 121);
int result = query.executeUpdate();

Equivalent SQL query is,

DELETE FROM student where student_id = 121;


  • HQL INSERT Query Example:

          HQL supports only the INSERT INTO .....  SELECT……… ; there is no chance to write
 INSERT INTO ..... VALUES, it means while writing the insert query, we need to select
values from other table, we can’t insert our own values manually.

Query query = session.createQuery("insert into Student(id, name)" +
       "select student_id, student_name from other_student_table");
int result = query.executeUpdate();

Equivalent SQL Query is,


INSERT INTO student(studnet_id, student_name) 
SELECT student_id, student_name FROM other_student_table;

Thank you for visiting blog.....



Related Posts:--
1) What is a Hibernate Caching ? Explain first level and second level cache in Hibernate
2) What are different states of an entity bean in Hibernate?
3) Hibernate One to One Mapping Example - Annotation based
4) Hibernate - JPA Annotations with explanation
5) Advantages of Hibernate over JDBC
6) What are the Core Interfaces of Hibernate framework ?

1 comment: