Wednesday, December 30, 2015

Hibernate - Introducing HQL(Hibernate Query Language) and the Query Object

Ref:- http://www.dineshonjava.com/p/introducing-hqlhibernate-query-language.html#.VnIhn_krLIU

Hibernate created a new language named Hibernate Query Language (HQL), the syntax is quite similar to database SQL language. The main difference between is HQL uses class name instead of table name, and property names instead of column name.

Hibernate uses the following ways to retrieve objects from the database:

Hibernate Query Language (HQL)
Query By Criteria (QBC) and Query BY Example (QBE) using Criteria API
Native SQL queries

The most preferred way is using the Hibernate Query Language (HQL), which is an easy-to-learn and powerful query language, designed as a minimal object-oriented extension to SQL. HQL has syntax and keywords/clauses very similar to SQL. It also supports many other SQL-like features, such as aggregate functions (for example: sum(), max()) and clauses such as group byand order by clause.

Why WE USE HQL?

Although it is possible to use native SQL queries directly with a Hibernate-based persistence layer, it is more efficient to use HQL instead. The reasons of choosing HQL over the other two methods are given below.

HQL allows representing SQL queries in object-oriented terms—by using objects and properties of objects.
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 "resultset" retrieved from database queried.
HQL fully supports polymorphic queries. That is, along with the object to be returned as a query result, all child objects (objects of subclasses) of the given object shall be returned.
HQL is easy to learn and implement, as its syntax and features are very similar to SQL.
HQL contains many advance features such as pagination, fetch join with dynamic profiling, and so forth, as compared to SQL.
HQL facilitates writing database-type independent queries that are converted to the native SQL dialect of the underlying database at runtime. This approach helps tap the extra features the native SQL query provides, without using a non-standard native SQL query.

HQL Syntax>>

As described earlier, most of HQL's syntax and features are very similar to SQL. An HQL query may consist of following elements:

Clauses
Aggregate functions
Subqueries

Clauses in the HQL are:

Aggregate functions are:

avg(...), sum(...), min(...), max(...)
count(*)
count(...), count(distinct ...), count(all...)

Subqueries
Subqueries are nothing but its a query within another query. Hibernate supports Subqueries if the underlying database supports it.

Table A: HQL Clauses with their description, syntax, and examples.

Clause	Description	Syntax	Example
from	The simplest form of an HQL query. Specifies the object whose instances are to be returned as the query result. Commonly used with the selectclause.	from object [as object_alias]* object_alias simply means another name given to refer to an object for convenience.	from UserDetails as user Will return all instances of object UserDetails.
select	Specifies objects and properties to be returned in the query result set. Used in conjunction with the fromclause.	select [object.]property	select user.userName from UserDetails as user Will return all values of userName in all instances ofUserDetails.
where	Specifies the conditions that should be satisfied by the instances returned as the query result. Used with select and/or from clause.	where condition Here, condition is a combination of logical, relational operators i.e. =, >, AND, NOT etc.	from UserDetails as user where user.userId > 2 Will return all instances of user in UserDetails whose correspondinguser.userId values are greater than 2.
order by	Specifies the order (ascending/descending) in which the properties of objects returned as query results should be listed. Used with the select and fromclauses.	order by object0.property0[asc\|desc][, object1.property0]... By default, order is ascending unless specified otherwise.	from UserDetails as user order by userId asc Will return a list of all instances of user in ascending order of corresponding userId values.
group by	Specifies the grouping criteria using objects properties, by which the list of objects returned as a query result should be grouped together. Used with the select and/or from clause.	group by object0.property0[,object1.property0]...	select userId from UserDetails as user group by user.userId Will return list of all userId instances from user grouped by corresponding values of user.

Hibernate - Select and Pagination in HQL

Ref:- http://www.dineshonjava.com/p/select-and-pagination-in-hql.html#.VnIiBfkrLIU

Pagination of search results is a common requirement for any application.

Out of performance reasons it is recommended to restrict the number of returned objects per query. In fact is a very common use case anyway that the user navigates from one page to an other. The way to define pagination is exactly the way you would define pagination in a plain HQL or Criteria query.

Using the createQuery() method of a Session object that returns a Query object.

First, instantiate the Session object using the openSession() method of SessionFactory.

Then, invoke the createQuery() method on the resulting object.

Query q = session.createQuery("...");
q.setFirstResult(start);
q.setMaxResults(length);

Student.java

view plainprint?
package com.sdnext.hibernate.tutorial.dto;  
  
import java.io.Serializable;  
  
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 implements Serializable   
{  
 /** 
  * serialVersionUID 
  */  
 private static final long serialVersionUID = 8633415090390966715L;  
 @Id  
 @Column(name="ID")  
 @GeneratedValue(strategy=GenerationType.AUTO)  
 private int id;  
 @Column(name="STUDENT_NAME")  
 private String studentName;  
 @Column(name="ROLL_NUMBER")  
 private int rollNumber;  
 @Column(name="COURSE")  
 private String course;  
 public int getId() {  
  return id;  
 }  
 public void setId(int id) {  
  this.id = id;  
 }  
 public String getStudentName() {  
  return studentName;  
 }  
 public void setStudentName(String studentName) {  
  this.studentName = studentName;  
 }  
 public int getRollNumber() {  
  return rollNumber;  
 }  
 public void setRollNumber(int rollNumber) {  
  this.rollNumber = rollNumber;  
 }  
 public String getCourse() {  
  return course;  
 }  
 public void setCourse(String course) {  
  this.course = course;  
 }  
 public String toString()  
 {  
  return "ROLL Number: "+rollNumber+"| Name: "+studentName+"| Course: "+course;  
 }  
}  

hibernate.cfg.xml

view plainprint?
<hibernate-configuration>   
 <session-factory>   
  <!-- Database connection settings -->  
   <property name="connection.driver_class">com.mysql.jdbc.Driver</property>   
   <property name="connection.url">jdbc:mysql://localhost:3306/hibernateDB2</property>   
   <property name="connection.username">root</property>   
   <property name="connection.password">root</property>   
  
  <!-- JDBC connection pool (use the built-in) -->  
   <property name="connection.pool_size">1</property>   
  
  <!-- SQL dialect -->  
   <property name="dialect">org.hibernate.dialect.MySQLDialect</property>   
  
  <!-- Enable Hibernate's automatic session context management -->  
    <property name="current_session_context_class">thread</property>   
    
  <!-- Disable the second-level cache -->  
   <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>   
  
  <!-- Echo all executed SQL to stdout -->  
   <property name="show_sql">true</property>   
    
  <!-- Drop and re-create the database schema on startup -->  
   <property name="hbm2ddl.auto">update</property>   
     
     
   <mapping class="com.sdnext.hibernate.tutorial.dto.Student">  
        
  </mapping></session-factory>   
 </hibernate-configuration>  

HibernateTestDemo.java

view plainprint?
package com.sdnext.hibernate.tutorial;  
  
import java.util.List;  
  
import org.hibernate.Query;  
import org.hibernate.Session;  
import org.hibernate.SessionFactory;  
import org.hibernate.cfg.AnnotationConfiguration;  
  
import com.sdnext.hibernate.tutorial.dto.Student;  
  
public class HibernateTestDemo {  
 /** 
  * @param args 
  */  
 public static void main(String[] args)   
 {  
  SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();  
  Session session = sessionFactory.openSession();  
  session.beginTransaction();  
    
  String SQL_QUERY = "FROM Student student";  
  Query query = session.createQuery(SQL_QUERY);  
  query.setFirstResult(1);//set first result start value  
  query.setMaxResults(5);//number of result to be display  
    
  List<student> students = query.list();  
  for(Student student : students)  
  {  
   System.out.println(student);  
  }  
  session.getTransaction().commit();  
  session.close();  
 }  
}  
</student>  

Output:

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.annotations.Version).

log4j:WARN Please initialize the log4j system properly.