The Hibernate Query Language (HQL) is similar to SQL. When compared to SQL, HQL is completely Object Oriented and hence it uses class names in the place of table names and property names in the place of column names. HQL understands inheritance, polymorphism and association. HQL is not case sensitive.
Let us go the package com.demo.HQLexamples and look into HQLexampleExecutor.java
HQL From Clause:
List studentList = session.createQuery(" from Student ").list();
for(Object stud : studentList){
System.out.println("Student Name is "+ ((Student)stud).getStudentName());
Set courses = ((Student)stud).getCourses();
System.out.println("Courses to which this student is associated");
for(Object course: courses)
System.out.println(((Course)course).getCourseName());
}
* The session.createQuery creates an instance of type org.hibernate.Query and the list() method of Query interface is actually the place where the execution of query starts.
* The from clause returns all the instances of a class.
* Objects can be retrieved by iterating the list.
HQL Joins:
Hibernate supports inner join, left outer join, right outer join, full join. In the following example, the query returns an array of objects ( the parent and its associated objects). Pay attention while iterating the list as follows.
List joinList = session.createQuery("from Student as s inner join s.courses").list();
Iterator ite = joinList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
Student student = (Student)objects[0];
Course course = (Course)objects[1];
System.out.println("Student Name " + student.getStudentName());
System.out.println("Course Name "+ course.getCourseName());
}
HQL Select Clause:
The select clause actually decides which objects and properties to return in the query result set. In the following example it returns only the Student objects and not the Course objects even though they are associated with the Student objects.
List selectList = session.createQuery("select distinct s from Student as s inner join s.courses").list(); //now only Student object is returned and not the Course object
for(Object sl : selectList)
System.out.println("Student Name "+ ((Student)sl).getStudentName());
HQL Aggregate functions:
In HQL, the aggregate functions like sum(), max(), min(), count() can be applied the properties and the results can be returned in the queries.
List aggreList = session.createQuery("select count(distinct s.studentName) from Student as s inner join s.courses").list();
System.out.println("Total Number of Students "+ aggreList.toString());
HQL Where Clause:
The where clause is used to filter the list of objects returned. Have a look at the syntax for the place holder in the where clause (:sname).
List whereList = session.createQuery("select distinct s from Student as s inner join s.courses where s.studentName= :sname").setParameter("sname","Prathap Kumar").list();//The where clause allows you to refine the list of instances returned.
for(Object wl: whereList)
System.out.println("Student Id is "+ ((Student)wl).getId());
HQL Order by Clause:
The objects returned in the query can be ordered on any property or component.
List orderList = session.createQuery("select s from Student s order by s.studentName asc").list();
for(Object ol : orderList)
System.out.println("Student name in ascending order "+ ((Student)ol).getStudentName());
HQL Group By Clause:
The aggregate values returned from a query can be grouped on any property or component.
List groupList = session.createQuery("select c.courseName, count(distinct c.courseName) from Student as s, Course c inner join s.courses group by c.courseName").list(); //A query that returns aggregate values can be grouped by any property of a returned class or components
ite = groupList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
System.out.println("Course Name is "+ objects[0]);
System.out.println("Count representation of the above course "+ objects[1]);
}
HQL Subqueries:
Hibernate supports subqueries within queries for the databases that allows subselects.A subquery must be surrounded by parentheses.
List subList = session.createQuery("select s from Student s where s.studentName not in (select distinct s from Student as s inner join s.courses where s.studentName= 'Sekar')").list();
System.out.println("List size "+ subList.size());
for(Object sl: subList)
System.out.println("Student Name from the sub query is "+ ((Student)sl).getStudentName());
Native SQL Queries:
Hibernate also supports native SQL queries. We can directly use the native SQL queries in the place of HQL queries with one minor change. The methodsession.createSQLQuery() should be used instead of session.createQuery() as follows.
List nativeSqlList = session.createSQLQuery("select * from STUDENTS").list(); // STEDENTS refer the actual table name
ite = nativeSqlList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
System.out.println("Student Id "+ objects[0]);
System.out.println("Student name "+ objects[1]);
}
The result set of a native SQL query can be converted into entity or object of a specific class by invoking a method addEntity() as follows.
List nativeRsHandlinglList = session.createSQLQuery("select * from COURSES").addEntity(Course.class).list(); // COURSES refer the actual table name
for(Object nativeList : nativeRsHandlinglList)
System.out.println("Course name is "+ ((Course)nativeList).getCourseName());
Named HQL:
The HQL queries can be named and embedded into the hibernate mapping file and can be retrieved using their respective names. The scope of the names of the HQL queries are globally visible.
Have a look at the namedQueries.hbm.xml file in the package com.demo.HQLexamples.
<query name="namedStudentHql"><![CDATA[ from Student s where s.studentName= :sname]]></query>
List namedHqlList = session.getNamedQuery("namedStudentHql").setString("sname", "Joseph Raj Kumar").list();
for(Object namedhql: whereList)
System.out.println("Student Id is "+ ((Student)namedhql).getId());
Named SQL:
Just as the HQL named queries, the SQL queries can also be named and retrieved by name in the same fashion.
<sql-query name="namedNativeStudentSQL"><![CDATA[select * from STUDENTS]]></sql-query>
List namedSqlList = session.getNamedQuery("namedNativeStudentSQL").list();
ite = nativeSqlList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
System.out.println("Student Id "+ objects[0]);
System.out.println("Student name "+ objects[1]);
Let us go the package com.demo.HQLexamples and look into HQLexampleExecutor.java
HQL From Clause:
List studentList = session.createQuery(" from Student ").list();
for(Object stud : studentList){
System.out.println("Student Name is "+ ((Student)stud).getStudentName());
Set courses = ((Student)stud).getCourses();
System.out.println("Courses to which this student is associated");
for(Object course: courses)
System.out.println(((Course)course).getCourseName());
}
* The session.createQuery creates an instance of type org.hibernate.Query and the list() method of Query interface is actually the place where the execution of query starts.
* The from clause returns all the instances of a class.
* Objects can be retrieved by iterating the list.
HQL Joins:
Hibernate supports inner join, left outer join, right outer join, full join. In the following example, the query returns an array of objects ( the parent and its associated objects). Pay attention while iterating the list as follows.
List joinList = session.createQuery("from Student as s inner join s.courses").list();
Iterator ite = joinList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
Student student = (Student)objects[0];
Course course = (Course)objects[1];
System.out.println("Student Name " + student.getStudentName());
System.out.println("Course Name "+ course.getCourseName());
}
HQL Select Clause:
The select clause actually decides which objects and properties to return in the query result set. In the following example it returns only the Student objects and not the Course objects even though they are associated with the Student objects.
List selectList = session.createQuery("select distinct s from Student as s inner join s.courses").list(); //now only Student object is returned and not the Course object
for(Object sl : selectList)
System.out.println("Student Name "+ ((Student)sl).getStudentName());
HQL Aggregate functions:
In HQL, the aggregate functions like sum(), max(), min(), count() can be applied the properties and the results can be returned in the queries.
List aggreList = session.createQuery("select count(distinct s.studentName) from Student as s inner join s.courses").list();
System.out.println("Total Number of Students "+ aggreList.toString());
HQL Where Clause:
The where clause is used to filter the list of objects returned. Have a look at the syntax for the place holder in the where clause (:sname).
List whereList = session.createQuery("select distinct s from Student as s inner join s.courses where s.studentName= :sname").setParameter("sname","Prathap Kumar").list();//The where clause allows you to refine the list of instances returned.
for(Object wl: whereList)
System.out.println("Student Id is "+ ((Student)wl).getId());
HQL Order by Clause:
The objects returned in the query can be ordered on any property or component.
List orderList = session.createQuery("select s from Student s order by s.studentName asc").list();
for(Object ol : orderList)
System.out.println("Student name in ascending order "+ ((Student)ol).getStudentName());
HQL Group By Clause:
The aggregate values returned from a query can be grouped on any property or component.
List groupList = session.createQuery("select c.courseName, count(distinct c.courseName) from Student as s, Course c inner join s.courses group by c.courseName").list(); //A query that returns aggregate values can be grouped by any property of a returned class or components
ite = groupList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
System.out.println("Course Name is "+ objects[0]);
System.out.println("Count representation of the above course "+ objects[1]);
}
HQL Subqueries:
Hibernate supports subqueries within queries for the databases that allows subselects.A subquery must be surrounded by parentheses.
List subList = session.createQuery("select s from Student s where s.studentName not in (select distinct s from Student as s inner join s.courses where s.studentName= 'Sekar')").list();
System.out.println("List size "+ subList.size());
for(Object sl: subList)
System.out.println("Student Name from the sub query is "+ ((Student)sl).getStudentName());
Native SQL Queries:
Hibernate also supports native SQL queries. We can directly use the native SQL queries in the place of HQL queries with one minor change. The methodsession.createSQLQuery() should be used instead of session.createQuery() as follows.
List nativeSqlList = session.createSQLQuery("select * from STUDENTS").list(); // STEDENTS refer the actual table name
ite = nativeSqlList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
System.out.println("Student Id "+ objects[0]);
System.out.println("Student name "+ objects[1]);
}
The result set of a native SQL query can be converted into entity or object of a specific class by invoking a method addEntity() as follows.
List nativeRsHandlinglList = session.createSQLQuery("select * from COURSES").addEntity(Course.class).list(); // COURSES refer the actual table name
for(Object nativeList : nativeRsHandlinglList)
System.out.println("Course name is "+ ((Course)nativeList).getCourseName());
Named HQL:
The HQL queries can be named and embedded into the hibernate mapping file and can be retrieved using their respective names. The scope of the names of the HQL queries are globally visible.
Have a look at the namedQueries.hbm.xml file in the package com.demo.HQLexamples.
<query name="namedStudentHql"><![CDATA[ from Student s where s.studentName= :sname]]></query>
List namedHqlList = session.getNamedQuery("namedStudentHql").setString("sname", "Joseph Raj Kumar").list();
for(Object namedhql: whereList)
System.out.println("Student Id is "+ ((Student)namedhql).getId());
Named SQL:
Just as the HQL named queries, the SQL queries can also be named and retrieved by name in the same fashion.
<sql-query name="namedNativeStudentSQL"><![CDATA[select * from STUDENTS]]></sql-query>
List namedSqlList = session.getNamedQuery("namedNativeStudentSQL").list();
ite = nativeSqlList.iterator();
while(ite.hasNext()){
Object [] objects = (Object []) ite.next();
System.out.println("Student Id "+ objects[0]);
System.out.println("Student name "+ objects[1]);
