Hibernate : Pagination of the Data using HQL

Most common way of fetching the database record using HQL is
Query query = session.createQuery(from Employee)

but if your query result in some large data then its going to consume a lot of Memory
and can possibly going to slow your program as it would require to process lots of data
but hibernate provide a convenient approach using HQL pagination through which you can limit the no of records return by the database.

Lets walk through the fallowing code which will give you overview to use HQL as well as the Hibernate pagination.

lets create your Model class as fallows and annotate with it with the javax.persistence

package com.rajkrrsinghblogspot.model;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="EMP_TABLE")
public class Employee {

 @Id
 private int empid;
 private String empName;

 public Employee(){

 }

 public Employee(int empid,String empName){
  this.empid = empid;
  this.empName = empName;
 }


 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 String toString(){
  return "::EMPID::"+getEmpid()+"::EMPNAME::"+getEmpName()+"::";
 }


}

Create Hibernate.cfg.xml as fallows and keep it in the classpath

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 
    <session-factory>
 
        <!-- Database connection settings -->
        <property name="connection.driver_class">org.apache.derby.jdbc.ClientDriver</property>
        <property name="connection.url">jdbc:derby://localhost:1527/MyHibDB;create=true</property>
        <property name="connection.username">rajkumar</property>
        <property name="connection.password">rajkumar</property>
 
         
        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.DerbyDialect</property>
 
        <!-- Enable Hibernate's current session context -->
        <property name="current_session_context_class">org.hibernate.context.ManagedSessionContext</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">create</property>
 
        <mapping class="com.rajkrrsinghblogspot.model.Employee"/>
         
    </session-factory>
 
</hibernate-configuration>

Now Create your main class to persist the database into the database and also retrive the result form database using HQL as fallows

package com.rajkrrsingh;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.rajkrrsinghblogspot.model.Employee;


public class App 
{
 public static void main( String[] args )
 {
  System.out.println("Testing my hibernate configuration");

  Employee emp = null;
  List<Employee> list = new ArrayList<Employee>();

  for(int i=0;i<10;i++){
   emp = new Employee(i, "Emp"+i);
   list.add(emp);
  }  

  SessionFactory sf = new Configuration().configure().buildSessionFactory();
  Session session = sf.openSession();
  session.beginTransaction();
  for(Employee e : list){
   session.save(e);  
  }

  session.getTransaction().commit();
  session.close();
  
  session = sf.openSession();
  session.beginTransaction();
  Query query = session.createQuery("from Employee");
  query.setFirstResult(1);
  query.setMaxResults(3);
  List<Employee> empList = query.list();
  
  System.out.println("\n");
  System.out.println("Printing 3 results from HQL using Pagination");
  System.out.println("\n");
  for(Employee em : empList){
   System.out.println(em);
  }
  
  session.getTransaction().commit();
  session.close();
 }
}

After running this example you will find the fallowing output on the console