HQL: pagination

Pagination is the process of dividing content into separate yet related pages. It is mainly used to reduce network traffic flow. For example, you are developing a search engine for an eCommerce company. If user searches for product “laptop”, you may get more than 10000 results. If you send all 10000 results at a time, traffic will be more. So you take first 100 items and send it to the user, if user ask some more you send next 100 (from 101-200) items and so on. We can achieve this by using HQL.

Query q = session.createQuery("...");

q.setFirstResult(start);

q.setMaxResults(length);

setFirstResult method specifies, from which offset query should return values.

setMaxResults method specifies how many results it query should return.

package myFirstHibernate;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;


@Entity
public class Employee {
  @Id @GeneratedValue
  private int id;
  private String firstName;
  private String lastName;
  
  public int getId() {
    return id;
  }
  
  public void setId(int id) {
    this.id = id;
  }
  
  public String getFirstName() {
    return firstName;
  }
  
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  
  public String getLastName() {
    return lastName;
  }
  
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  
}

hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

  <session-factory>
  
    <!--  Database Connection settings -->
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="connection.url">jdbc:mysql://localhost/sample</property>
    <property name="connection.username">root</property>
    <property name="connection.password">tiger</property>
    
    <!-- Enable the logging of all the generated SQL statements to the console -->
    <property name="show_sql">true</property>
    
    <!-- Format the generated SQL statement to make it more readable, -->
    <property name="format_sql">false</property>
    
    <!-- Hibernate will put comments inside all generated SQL statements to hint what’s the generated SQL trying to do -->
    <property name="use_sql_comments">false</property>
    
    <!-- This property makes Hibernate generate the appropriate SQL for the chosen database. -->
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    
    <!-- Drop and re-create the database schema on startup -->
    <property name="hbm2ddl.auto">create</property>
    
    <!-- mappings for annotated classes -->
    <mapping class="myFirstHibernate.Employee" />
    
  </session-factory>
  
</hibernate-configuration>

package myFirstHibernate;

import java.util.List;
import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import java.io.*;

public class TestEmployee {
  
  /* Step 1: Create session factory */
  private static SessionFactory getSessionFactory() {
    Configuration configuration = new Configuration().configure();
    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder().
    applySettings(configuration.getProperties());
    SessionFactory factory = configuration.buildSessionFactory(builder.build());
        return factory;
    }
  
  public static void main(String args[])throws Exception{
    
    BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
    SessionFactory sessionFactory = getSessionFactory();
    Session session = sessionFactory.openSession();
    session.beginTransaction();
    for(int i=1; i<=10000; i++){
      Employee emp = new Employee();
      emp.setId(i);
      emp.setFirstName("first name" + i);
      emp.setLastName("last name" + i);
      session.save(emp);
    }
  
    Query query = session.createQuery("from Employee");
    int choice = 1;
    int start = 0, maxResults=10;
    while(choice == 1){
      query.setFirstResult(start);
      query.setMaxResults(maxResults);
      start = start + maxResults;
      
      List<Employee> empList = query.list();
      for(Employee emp : empList){
        System.out.println(emp.getId() +" " + emp.getFirstName() + " " + emp.getLastName());
      }
      System.out.println("Enter 1 for next 10 items, 0 to quit");
      choice = Integer.parseInt(br.readLine());
    }
    
    
    
    session.getTransaction().commit();
    session.close(); 
    
  }
}

Run TestEmployee class, you will get output like below.

Hibernate: select employee0_.id as id1_0_, employee0_.firstName as firstNam2_0_, employee0_.lastName as lastName3_0_ from Employee employee0_ limit ?
1 first name1 last name1
2 first name2 last name2
3 first name3 last name3
4 first name4 last name4
5 first name5 last name5
6 first name6 last name6
7 first name7 last name7
8 first name8 last name8
9 first name9 last name9
10 first name10 last name10
Enter 1 for next 10 items, 0 to quit
1
Hibernate: select employee0_.id as id1_0_, employee0_.firstName as firstNam2_0_, employee0_.lastName as lastName3_0_ from Employee employee0_ limit ?, ?
11 first name11 last name11
12 first name12 last name12
13 first name13 last name13
14 first name14 last name14
15 first name15 last name15
16 first name16 last name16
17 first name17 last name17
18 first name18 last name18
19 first name19 last name19
20 first name20 last name20
Enter 1 for next 10 items, 0 to quit
1
Hibernate: select employee0_.id as id1_0_, employee0_.firstName as firstNam2_0_, employee0_.lastName as lastName3_0_ from Employee employee0_ limit ?, ?
21 first name21 last name21
22 first name22 last name22
23 first name23 last name23
24 first name24 last name24
25 first name25 last name25
26 first name26 last name26
27 first name27 last name27
28 first name28 last name28
29 first name29 last name29
30 first name30 last name30
Enter 1 for next 10 items, 0 to quit
0

Prevoius                                                 Next                                                 Home